On 15/06/2012 12:29, Andrea Carlini wrote:
Thanks Michael.
 
Is this 9.4.0.4 ?
No, it will be 9.5. We never put new facilities in maintenance releases.

Michael Kay
Saxonica
Feature N°?
 
2012/6/15 Michael Kay <mike@saxonica.com>
Thanks; this suggestion has already been made and implemented, and will appear in the next release. From the change log:

<p>Added the value <code>#auto</code> for the <code>column-tag</code> and <code>row-tag</code> attributes
      of <code>sql:query</code>, and made this the default; when this value is used, the element names used
      for rows and cells are taken from the table name used in the query, and the column names returned in the
      query metadata. To get the old default behaviour, specify <code>row-tag="row"</code> and <code>column-tag="col"</code>.</p>

Michael Kay
Saxonica


On 15/06/2012 11:15, Andrea Carlini wrote:
I'm using the sql extension to dinamically retrieve some information from a database .
 
<!-- Setup the JDBC connection -->
<xsl:variable name="connection" as="java:java.sql.Connection" xmlns:java="http://saxon.sf.net/java-type">
<sql:connect database="jdbc:oracle:thin:@myserver.com:1521:DEMODB" driver="oracle.jdbc.driver.OracleDriver" user="DUMMY" password="xxx"/>
</xsl:variable>
<!-- Run the query -->
<xsl:variable name="queryResults">
<sql:query connection="$connection" table="REPORT_PARAMS" where="REPORT_ID=$id" column="PARAMETER_NAME,PARAMETER_VALUE,PARAMETER_TYPE" row-tag="row" column-tag="col"/>
</xsl:variable>
 
The result is something like :
 
<row>
 <col>Parameter1</col>
 <col>CLEAR</col>
 <col>T</col>
</row>
<row>
 <col>Parameter2</col>
 <col>35</col>
 <col>N</col>
</row>
 
I can retrieve data with
<xsl:value-of select="$queryResults/row[1]/col[1]"/>
But this is really bad, and it make things impossible when the position of the fields retrieved are not known.
 
The result I would like to have is :
 
<row>
 <PARAMETER_NAME>Parameter1</PARAMETER_NAME>
 <PARAMETER_VALUE>CLEAR</PARAMETER_VALUE>
 <PARAMETER_TYPE>T</PARAMETER_TYPE>
</row>
...
 
Is that possible to change SQLQuery.java in saxon-sql library in order to have a parameter that include tha field name in the tag instead a fixed one?
 
For example ( from an old SQLQuery.java ) :
 
                // -- Execute Statement
                rs = ps.executeQuery();

                // -- Print out Result
                Receiver out = context.getReceiver();
                int icol = rs.getMetaData().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);
                        out.startElement(colCode, StandardNames.XS_UNTYPED, locationId, 0);
                        if (result != null) {
                            out.characters(result, locationId, options);
                        }
                        out.endElement();
                    }
                    //System.out.println(rowEnd);
                    out.endElement();
                }
Can be changed in ( this is just an unvalidated suggestion ) :
 
                // -- Execute Statement
                rs = ps.executeQuery();

                // -- Print out Result
                Receiver out = context.getReceiver();
                ResultSetMetaData rmd = rs.getMetaData(); // ResultSet metadata
                int icol = rmd.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);
                        fieldName = rmd.getColumnName(col);
                        fieldCode = pool.allocate(fieldName);
                        out.startElement(fieldCode , StandardNames.XS_UNTYPED, locationId, 0);
                        if (result != null) {
                            out.characters(result, locationId, options);
                        }
                        out.endElement();
                    }
                    //System.out.println(rowEnd);
                    out.endElement();
                }
Thanks and Regards
Andy


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and 
threat landscape has changed and how IT managers can respond. Discussions 
will include endpoint security, mobile security and the latest in malware 
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/


_______________________________________________
saxon-help mailing list archived at http://saxon.markmail.org/
saxon-help@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/saxon-help 

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
saxon-help mailing list archived at http://saxon.markmail.org/
saxon-help@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/saxon-help