• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

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"/>

<!-- http://groups.yahoo.com/group/xml-doc/message/2666      -->
      <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()"/>
      <!-- Connect to the database with minimal error detection -->
      <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>
      <!--2. Execute the query -->
     
              <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>

 

      
      <!-- 3. Close the connection -->
      <xsl:value-of select="sql:close($db)"/>
      
0
adam391
Asked:
adam391
  • 5
  • 4
1 Solution
 
anthonywjones66Commented:
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.
0
 
ramazanyichCommented:
Actually If you do
<xsl:copy-of select="$table"/>
you will see in output all elements that returned by sql extension
0
 
adam391Author Commented:
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

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ramazanyichCommented:
Did you change something since yesterday in your JDK_HOME/jre/lib/endorsed and JRE_HOME/lib/endorsed directories ?
0
 
adam391Author Commented:
in C:\j2sdk1.4.2_07\jre\lib\endorsed i have not changed anything the same jar file is there


0
 
ramazanyichCommented:
try also to add mysql connector jar file to classpath:
java -classpath mysql_connector...jar ...
0
 
adam391Author Commented:
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
0
 
ramazanyichCommented:
try to start:
c:\j2sdk.1.4.2_07\bin\java -cp....
0
 
ramazanyichCommented:
After you solve the problem with connection.
replace
 <xsl:apply-templates select="$table/sql/row-set/row"/>
by
 <xsl:apply-templates select="$table/child::*"/>
0
 
adam391Author Commented:
top help again! all works and i get my records
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now