asked on

XLST and SQL Query results

I have a xsl script that connects to a mysql database but i can only get it to print out the second row of my table.

Here is my the xlst code relating to the database interaction

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:sql="org.apache.xalan.lib.sql.XConnection" extension-element-prefixes="sql">
<xsl:output method="xml" indent="yes" encoding="UTF-8"/>
<xsl:output version="1.0"/>
<xsl:output doctype-public="ISO//Web3D//DTD X3D 3.0//EN"/>
<xsl:output doctype-system="http://www.web3d.org/specifications/x3d-3.0.dtd"/>


      <xsl:param name="driver" select="'com.mysql.jdbc.Driver'"/>
      <xsl:param name="datasource" select="'jdbc:mysql://localhost/environment'"/>
      <xsl:param name="query" select="'SELECT * FROM pcs'"/>
      <xsl:param name="username" select="'root'"/>
      <xsl:param name="passwd" select="'ratkarth'"/>

<xsl:template match="/">

      <xsl:variable name="db" select="sql:new()"/>
      
      <xsl:if test="not(sql:connect($db, $driver, $datasource, $username, $passwd))">
            <xsl:message>Error Connecting to the Database</xsl:message>
            <xsl:copy-of select="sql:getError($db)/ext-error"/>
      </xsl:if>
      

      <xsl:variable name="table" select='sql:query($db, $query)'/>
      <xsl:if test="not($table)" >
            <xsl:message>Error in Query</xsl:message>
            <xsl:copy-of select="sql:getError($db)/ext-error" />
      </xsl:if>

            <xsl:for-each select="$table/sql/metadata/column-header">
                  <test><xsl:value-of select="@column-label"/></test>

            </xsl:for-each>
      <xsl:for-each select="$table/">
            loop
      <xsl:apply-templates select="$table/sql/row-set/row"/>
      </xsl:for-each>


      
      <xsl:value-of select="sql:close($db)"/>

anthonywjones66

It doesn't appear as thought this xsl has outputs a root node?

What are you expection the path "$table/" to do ?

Can we assume there is a template that matches "row" somewhere else in your xsl?

What happens if you replace:-

<xsl:for-each select="$table/">
loop
<xsl:apply-templates select="$table/sql/row-set/row"/>
</xsl:for-each>

With simply:-

<xsl:apply-templates select="$table/sql/row-set/row"/>

?

Anthony.

ramazanyich

Actually If you do
<xsl:copy-of select="$table"/>
you will see in output all elements that returned by sql extension

adam391

ASKER

thanks, but now my connection seems not to work. so i can't test out what your suggest. maybe you cna help.

I get the following error
No suitable driver

ramazanyich

Did you change something since yesterday in your JDK_HOME/jre/lib/endorsed and JRE_HOME/lib/endorsed directories ?

adam391

ASKER

in C:\j2sdk1.4.2_07\jre\lib\endorsed i have not changed anything the same jar file is there

ramazanyich

try also to add mysql connector jar file to classpath:
java -classpath mysql_connector...jar ...

adam391

ASKER

i already do

java -cp .;mysql-connector-java-3.1.7-bin.jar org.apache.xalan.xslt.Process -in
office.xml -xsl officeV7.xsl -out test.xml

ramazanyich

try to start:
c:\j2sdk.1.4.2_07\bin\java -cp....

ASKER CERTIFIED SOLUTION

ramazanyich

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adam391

ASKER

top help again! all works and i get my records