OK, did all this, and now it works. Thanks one and all.
The only other thing I had to change was in the where clause which
used to read where="id='$pw'" and has to read where="id='{$pw}'".
Some comments:-
1) It is odd that it should fail so quietly. I would have thought that if
the tags were not being used that they would be emitted to the output file
as the rest of my XML template was.
2) The resultset format is not very friendly. Could we have an option to
use the column name as the tag name rather than using "col" or some other
constant for all columns. Also adding a sequence number to the rows as
an attribute would be useful. Then I could parse the result easily
with XPath. You already fetch the ResultSetMetaData for the ResultSet
to get the column count, getting the names as well would not be a great
overhead. Obviously it would need an option to request it to preserve
backward compatibility.
Thus the loop processing the resultset would look like (assuming a
boolean useName to be true if we are to use the column name rather
than the constant):-
ResultSetMetaData rsmd = rs.getMetaData( );
int icol = rsmd.getColumnCount();
while (rs.next()) { // next row
//System.out.print("<- SQL : "+ rowStart);
out.startElement(rowCode, StandardNames.XS_UNTYPED, locationId, 0);
for (int col = 1; col <= icol; col++) { // next column
// Read result from RS only once, because
// of JDBC-Specifications
result = rs.getString(col);
String label = useName ? rsmd.getColumn Label(k) : colCode;
out.startElement(label, StandardNames.XS_UNTYPED, locationId, 0);
if (result != null) {
out.characters(result, locationId, options);
}
out.endElement();
}
//System.out.println(rowEnd);
out.endElement();
}
Obviously the row number would also need to be inserted somehow, but I
have not delved into the working of Receiver to see how that might be
done.
The result of this in my case would be that rather than getting:-
<row>
<col>address1</col>
<col>siteinfo1</col>
</row>
I would get:-
<row seq='1'>
<address>address1</address>
<siteinfo>siteinfo1</siteinfo>
</row>
on which I would use XPath to extract //row/address etc rather than having
to use things like //row/col[position()=1].
David
On Wednesday 13 June 2007, Michael Kay wrote:
> Firstly, the variable that holds your connection isn't being evaluated
> because it is never used.
>
> Secondly, neither sql:connect nor sql:query is recognized as an extension
> element because the sql prefix isn't included in
> extension-element-prefixes.
>
> Finally, in sql:query the reference to $connection is out of scope; but
> this isn't treated as an error because sql:query isn't being recognized as
> an instruction.
>
> 1. Change the attribute to extension-element-prefixes="sql"
>
> 2. Change the apply-templates to
>
> <xsl:apply-templates>
> <xsl:with-param name="connection" select="$connection"/>
> </xsl:apply-templates>
>
> 3. Add to the template match="transaction"
>
> <xsl:param name="connection"/>
>
> I now get a JDBC connection failure, which is what I would expect.
>
> Michael Kay
> http://www.saxonica.com/
>
> > -----Original Message-----
> > From: saxon-help-bounces@...
> > [mailto:saxon-help-bounces@...] On Behalf
> > Of David Goodenough
> > Sent: 13 June 2007 19:44
> > To: Mailing list for SAXON XSLT queries
> > Subject: Re: [saxon] How to debug SQL tags
> >
> > OK, I have cut it down to as simple a test case as I can
> > manage. The XSLT script reads:-
> >
> > <xsl:stylesheet
> > xmlns:sql="java:/net.sf.saxon.sql.SQLElementFactory"
> > xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0"
> > xmlns:saxon="http://saxon.sf.net/"
> > xmlns:java="http://saxon.sf.net/java-type"
> > extension-element-prefixes="saxon"
> > exclude-result-prefixes="java sql">
> >
> > <xsl:output method='xml' version='1.0' encoding='UTF-8'
> > standalone='yes'
> > indent='yes'/>
> > <xsl:template match='/'>
> > <xsl:variable name='connection' as='java:java.sql.Connection'>
> > <sql:connect database='jdbc:postgresql:murphy'
> > driver='org.postgresql.Driver' user='david'>
> > <xsl:fallback>
> > <xsl:message terminate='yes'>SQL connection
> > failed</xsl:message>
> > </xsl:fallback>
> > </sql:connect>
> > </xsl:variable>
> > <xsl:apply-templates/>
> > </xsl:template>
> >
> > <xsl:template match='transaction'>
> > <xsl:variable name='pw'><xsl:value-of
> > select='segment[@Id="NAD"]//element[.="PW"]/../element[@Id="NA
> > D02"]'/></xsl:variable>
> > <xsl:variable name='pwresult'>
> > <sql:query connection='$connection' table='addresses'
> > column='address, siteinfo'
> > where="id='$pw'" disable-output-escaping='yes'>
> > <xsl:fallback>
> > <xsl:message terminate='yes'>Query failed</xsl:message>
> > </xsl:fallback>
> > </sql:query>
> > </xsl:variable>
> > <xsl:message terminate='no'>
> > <xsl:value-of select='count($pwresult//row)'/> rows for
> > <xsl:value-of select='$pw'/>
> > </xsl:message>
> > <xsl:copy-of select='$pwresult//row'/>
> > </xsl:template>
> >
> > </xsl:stylesheet>
> >
> > So when I run this through edireader I get:-
> >
> > Input file /home/david/Documents/DGA/Murphy/orders.edi.txt
> > opened Output file fred opened Stylesheet file test.xsl
> > opened 0 rows for 001553851 0 rows for 002698937 0 rows for
> > 002698937 Transformation complete
> >
> > and the output file read:-
> >
> > <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
> >
> > The output that I get from psql murphy -c "select * from addresses":-
> >
> > id | address | siteinfo
> > -----------+-----------+----------
> > 001553851 | address 1 | site 1
> > 002698937 | address 2 | site 2
> >
> > Now obviously this is silly date, but the query should have
> > read something.
> >
> > David
> >
> > On Wednesday 13 June 2007, Michael Kay wrote:
> > > Not really sure what to suggest! I've seen plenty of cases
> >
> > where it's
> >
> > > difficult to work out why the SQL connection is failing, but I've
> > > never seen a case where it appears to succeed but retrieves nothing.
> > >
> > > What happens if you try an erroneous SQL statement, for example a
> > > garbage WHERE condition?
> > >
> > > Are you perhaps failing to capture/display any errors that are
> > > generated (what happens when you call the error() function?)
> > >
> > > Or perhaps the query is succeeding and you're failing to
> >
> > display the
> >
> > > results?
> > >
> > > These probably all sound like wild suggestions, along the lines of
> > > "check that you're switched on at the mains", but without
> >
> > seeing any
> >
> > > source code it's hard to come up with anything more focused.
> > >
> > > Michael Kay
> > > http://www.saxonica.com/
> > >
> > > > -----Original Message-----
> > > > From: saxon-help-bounces@...
> > > > [mailto:saxon-help-bounces@...] On
> >
> > Behalf Of David
> >
> > > > Goodenough
> > > > Sent: 13 June 2007 17:02
> > > > To: saxon-help@...
> > > > Subject: [saxon] How to debug SQL tags
> > > >
> > > > I am currently trying to extract some data from some EDIFACT
> > > > datastreams and I found the edireader project which allows me to
> > > > treat the datastream as though it were an XML DOM. But I
> >
> > also need
> >
> > > > to take some data from the DOM and fill in additional
> >
> > data into the
> >
> > > > output XML datastream from an SQL database. Looking
> >
> > around I found
> >
> > > > the Saxon SQL extensions, and I have modified the startup
> >
> > script for
> >
> > > > edireader to that it includes the Saxon and Saxon-sql
> >
> > JARs, and used
> >
> > > > -D to point JAXP at Saxon.
> > > >
> > > > My XSLT script has XSLT 2.0 features in it, so it must be using
> > > > Saxon (before I had the -D it complained about any
> >
> > XSLT-2.0 features
> >
> > > > I tried to use).
> > > >
> > > > I am using Java 1.5 and Saxonb 8.9j.
> > > >
> > > > The problem is that I do not seem to get anything from
> >
> > the SQL tags.
> >
> > > > I get no results at all from an sql:query. I tried
> >
> > putting fallback
> >
> > > > tags into both the sql:connect and the sql:query statements with
> > > > xsl:message tags, but no messages.
> > > >
> > > > I know the postgesql setup on my machine is setup for use
> >
> > with JDBC
> >
> > > > as I have been developing Java web apps using Tomcat on
> >
> > this machine
> >
> > > > for ages. And I have added the postgresql-jdbc3.jar file to the
> > > > classpath.
> > > >
> > > > Anyone got any ideas for debugging this problem?
> > > >
> > > > David
> > > >
> > > > --------------------------------------------------------------
> > > > -----------
> > > > This SF.net email is sponsored by DB2 Express Download
> >
> > DB2 Express C
> >
> > > > - the FREE version of DB2 express and take control of
> >
> > your XML. No
> >
> > > > limits. Just data. Click to get it now.
> > > > http://sourceforge.net/powerbar/db2/
> > > > _______________________________________________
> > > > saxon-help mailing list
> > > > saxon-help@...
> > > > https://lists.sourceforge.net/lists/listinfo/saxon-help
> >
> > ----------------------------------------------------------------------
> >
> > > --- This SF.net email is sponsored by DB2 Express Download
> >
> > DB2 Express
> >
> > > C - the FREE version of DB2 express and take control of
> >
> > your XML. No
> >
> > > limits. Just data. Click to get it now.
> > > http://sourceforge.net/powerbar/db2/
> > > _______________________________________________
> > > saxon-help mailing list
> > > saxon-help@...
> > > https://lists.sourceforge.net/lists/listinfo/saxon-help
> >
> > --------------------------------------------------------------
> > -----------
> > This SF.net email is sponsored by DB2 Express Download DB2
> > Express C - the FREE version of DB2 express and take control
> > of your XML. No limits. Just data. Click to get it now.
> > http://sourceforge.net/powerbar/db2/
> > _______________________________________________
> > saxon-help mailing list
> > saxon-help@...
> > https://lists.sourceforge.net/lists/listinfo/saxon-help
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by DB2 Express
> Download DB2 Express C - the FREE version of DB2 express and take
> control of your XML. No limits. Just data. Click to get it now.
> http://sourceforge.net/powerbar/db2/
> _______________________________________________
> saxon-help mailing list
> saxon-help@...
> https://lists.sourceforge.net/lists/listinfo/saxon-help
|