Hi Christine, I am using MySQL 4.0.14 (mysql-connector-java-3.0.10-stable-bin.jar) and Oracle 8.1.7. My workaround is running under MySQL and the result is wrong:
<HTML> <HEAD> <META http-equiv="Content-Type" content="text/html; charset=UTF-8"> <TITLE>List of products</TITLE> </HEAD> <BODY> <TABLE border="1"> <TR> <TH>index</TH><TH>name</TH> </TR> <TR> <TD>3</TD><TD>test3</TD> </TR> <TR> <TD>3</TD><TD>test3</TD> </TR> <TR> <TD>3</TD><TD>test3</TD> </TR> </TABLE> </BODY> </HTML> I will try to run the workaround with oracle and validate the result... Juraj -----Ursprungliche Nachricht----- Von: Christine Li [mailto:[EMAIL PROTECTED] Gesendet: Montag, 2. Februar 2004 16:06 An: Lenharcik, Juraj Cc: [EMAIL PROTECTED] Betreff: Re: AW: SQL extension is not returning the correct resultset??! Hi, Juraj Well, I've tested the sql extension sample code with MySQL 3.23.55 & MySQL JDBC 3.0.8. It works fine with the workaround. I don't know whether it is related to different version of Mysql. This is the stylesheet I used. You can try this, although I don't see the different between this one and yours. <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"; version="1.0" xmlns:sql="org.apache.xalan.lib.sql.XConnection" extension-element-prefixes="sql"> <xsl:output method="html" indent="yes"/> <!-- parameter setting to connect to MySQL --> <xsl:param name="driver" select="'com.mysql.jdbc.Driver'"/> <xsl:param name="datasource" select="'jdbc:mySQL://localhost/****'"/> <xsl:param name="query" select="'SELECT * FROM test'"/> <xsl:param name="username" select="'***'"/> <xsl:param name="passwd" select="'***'"/> <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> <xsl:variable name="table" select='sql:query($db, $query)'/> <HTML> <HEAD> <TITLE>List of products</TITLE> </HEAD> <BODY> <TABLE border="1"> <xsl:if test="not($table)" > <xsl:message>Error in Query</xsl:message> <xsl:copy-of select="sql:getError($db)/ext-error" /> </xsl:if> <TR> <xsl:for-each select="$table/sql/metadata/column-header"> <TH><xsl:value-of select="@column-label"/></TH> </xsl:for-each> </TR> <xsl:apply-templates select="$table/sql/row-set"/> </TABLE> </BODY> </HTML> <xsl:value-of select="sql:close($db)"/> </xsl:template> <xsl:template match="row-set"> <xsl:apply-templates select="row"/> </xsl:template> <xsl:template match="row"> <TR><xsl:apply-templates select="col"/></TR> </xsl:template> <xsl:template match="col"> <TD><xsl:value-of select="text()"/></TD> </xsl:template> </xsl:stylesheet> Christine Li XSLT Development IBM Toronto Lab Tel: (905)413-2601 Email: [EMAIL PROTECTED] [EMAIL PROTECTED] -systems.com To 02/02/2004 09:39 Christine Li/Toronto/[EMAIL PROTECTED] AM cc [EMAIL PROTECTED] Subject AW: SQL extension is not returning the correct resultset??! Hi Christine, I have tried it, but it has no effect... With the variable it is running, but if the data is from db I get wrong data. The table is correct: +-------+-------+ | index | name | +-------+-------+ | 1 | test1 | | 2 | test2 | | 3 | test3 | +-------+-------+ Also the stylesheet: <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"; version="1.0" xmlns:sql="org.apache.xalan.lib.sql.XConnection" extension-element-prefixes="sql" xmlns:xalan="http://xml.apache.org/xalan"; exclude-result-prefixes="xalan" > <xsl:output method="html" indent="yes"/> <!-- parameter setting to connect to DB2<xsl:param name="driver" select="'COM.ibm.db2.jdbc.app.DB2Driver'"/><xsl:param name="datasource" select="'jdbc:db2:sample'"/>--> <!-- parameter setting to connect to MySQL --> <xsl:param name="driver" select="'com.mysql.jdbc.Driver'"/> <xsl:param name="datasource" select="'jdbc:mySQL://localhost/test'"/> <xsl:param name="query" select="'SELECT * FROM testtabelle'"/> <xsl:param name="username" select="'root'"/> <xsl:param name="passwd" select="''"/> <xsl:variable name="test"> <row-set> <row> <col>1</col> <col>test1</col> </row> <row> <col>2</col> <col>test2</col> </row> <row> <col>3</col> <col>test3</col> </row> </row-set> </xsl:variable> <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> <HTML> <HEAD> <TITLE>List of products</TITLE> </HEAD> <BODY> <TABLE border="1"> <xsl:variable name="table" select='sql:query($db, $query)'/> <!-- Let's include Error Checking, the error is actually stored in the connection since $table will be either data or null --> <xsl:if test="not($table)" > <xsl:message>Error in Query</xsl:message> <xsl:copy-of select="sql:getError($db)/ext-error" /> </xsl:if> <TR> <xsl:for-each select="$table/sql/metadata/column-header"> <xsl:message><xsl:value-of select="@column-label"/></xsl:message> </xsl:for-each> </TR> <xsl:apply-templates select="$table/sql/row-set"/> <!-- <xsl:variable name="testtabelle2" select="xalan:nodeset($test)"/> <xsl:apply-templates select="$testtabelle2/row-set"/>--> </TABLE> </BODY> </HTML> <xsl:value-of select="sql:close($db)"/> </xsl:template> <xsl:template match="row-set"> <!-- <xsl:for-each select="./row"> <xsl:apply-templates select="."/> </xsl:for-each> --> <xsl:apply-templates select="row"/> </xsl:template> <xsl:template match="row"> <xsl:apply-templates select="./col"/> </xsl:template> <xsl:template match="col"> <xsl:message><xsl:value-of select="./text()"/></xsl:message> </xsl:template> </xsl:stylesheet> Juraj -----Ursprungliche Nachricht----- Von: Christine Li [mailto:[EMAIL PROTECTED] Gesendet: Montag, 2. Februar 2004 15:31 An: Lenharcik, Juraj Cc: [EMAIL PROTECTED] Betreff: Re: SQL extension is not returning the correct resultset??! Hi, Juraj It may related to bug 12337 @ http://nagoya.apache.org/bugzilla/show_bug.cgi?id=12337 . It provides a workaround by adding a dummy template. If you twist your stylesheet a bit by changing <xsl:template match="row-set"> <xsl:for-each select="./row"> <xsl:apply-templates select="."/> </xsl:for-each> </xsl:template> To <xsl:template match="row-set"> <xsl:apply-templates select="row"/> </xsl:template> It should work. You can refer to the extension sample codes shipped with Xalan. Christine Li XSLT Development IBM Toronto Lab Tel: (905)413-2601 Email: [EMAIL PROTECTED] [EMAIL PROTECTED] -systems.com To 02/02/2004 06:25 [EMAIL PROTECTED] AM cc Subject SQL extension is not returning the correct resultset??! Hello, I am using the SQl extension from xalan to retrieve some data form a database. After the connection if I iterate over the resultset I get still the same row. The interation index is correct. For example: Table: 1 | test1 2 | test2 3 | test3 I get: Zeilennummer148; Spaltennummer15; 3 Zeilennummer148; Spaltennummer15; test3 Zeilennummer148; Spaltennummer15; 3 Zeilennummer148; Spaltennummer15; test3 Zeilennummer148; Spaltennummer15; 3 Zeilennummer148; Spaltennummer15; test3 If I use the same code on a local created variable, with the same xml-strucure I get the correct values: Zeilennummer148; Spaltennummer15; 1 Zeilennummer148; Spaltennummer15; test1 Zeilennummer148; Spaltennummer15; 2 Zeilennummer148; Spaltennummer15; test2 Zeilennummer148; Spaltennummer15; 3 Zeilennummer148; Spaltennummer15; test3 Has someone the same effect? Juraj XSL: <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"; version="1.0" xmlns:sql="org.apache.xalan.lib.sql.XConnection" extension-element-prefixes="sql" xmlns:xalan="http://xml.apache.org/xalan"; exclude-result-prefixes="xalan" > <xsl:output method="html" indent="yes"/> <!-- parameter setting to connect to DB2<xsl:param name="driver" select="'COM.ibm.db2.jdbc.app.DB2Driver'"/><xsl:param name="datasource" select="'jdbc:db2:sample'"/>--> <!-- parameter setting to connect to MySQL --> <xsl:param name="driver" select="'com.mysql.jdbc.Driver'"/> <xsl:param name="datasource" select="'jdbc:mySQL://localhost/test'"/> <xsl:param name="query" select="'SELECT * FROM testtabelle'"/> <xsl:param name="username" select="'root'"/> <xsl:param name="passwd" select="''"/> <xsl:variable name="testt"> <row-set> <row> <col>1</col> <col>test1</col> </row> <row> <col>2</col> <col>test2</col> </row> <row> <col>3</col> <col>test3</col> </row> </row-set> </xsl:variable> <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> <HTML> <HEAD> <TITLE>List of products</TITLE> </HEAD> <BODY> <TABLE border="1"> <xsl:variable name="table" select='sql:query($db, $query)'/> <!-- Let's include Error Checking, the error is actually stored in the connection since $table will be either data or null --> <xsl:if test="not($table)" > <xsl:message>Error in Query</xsl:message> <xsl:copy-of select="sql:getError($db)/ext-error" /> </xsl:if> <TR> <xsl:for-each select="$table/sql/metadata/column-header"> <xsl:message><xsl:value-of select="@column-label"/></xsl:message> </xsl:for-each> </TR> <!-- <xsl:apply-templates select="$table/sql/row-set"/> --> <xsl:variable name="testtabelle2" select="xalan:nodeset($testt)"/> <xsl:apply-templates select="$testtabelle2/row-set"/> </TABLE> </BODY> </HTML> <xsl:value-of select="sql:close($db)"/> </xsl:template> <xsl:template match="row-set"> <xsl:for-each select="./row"> <xsl:apply-templates select="."/> </xsl:for-each> </xsl:template> <xsl:template match="row"> <xsl:apply-templates select="./col"/> </xsl:template> <xsl:template match="col"> <xsl:message><xsl:value-of select="./text()"/></xsl:message> </xsl:template> </xsl:stylesheet>
