[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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