Question 255183 in Mysql Question posted in
The official MySQL documentation can be found here.

Configuring Saxon for MySQL

I’m having difficulty getting Saxon configured to use MySQL, in oXygen and in eXist-db. Here is my attempt in eXist-db xquery. There are no errors, I do get a connection object (so the fields extracted from my.cnf do appear to work), but no results are returned from sql:execute(). Any suggestions?

xquery version "3.1";
declare namespace transform="http://exist-db.org/xquery/transform";

let $xml := <root/>
let $xsl := <xsl:stylesheet
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:file="http://expath.org/ns/file"
    xmlns:wq="https://winequest.com"
    xmlns:map="http://www.w3.org/2005/xpath-functions/map"
    xmlns:sql="http://saxon.sf.net/sql"
    exclude-result-prefixes="file wq xs map sql"
    version="3.0" expand-text="yes">
    <xsl:mode on-no-match="shallow-copy"/>
    
    <xsl:template match="/">
        <xsl:variable name="cnf" select="file:read-text('/etc/my.cnf')"/>
        <xsl:variable name="client" select="$cnf =&gt; replace('^.*[client]', '', 's') =&gt; replace('[[^]]+].*$', '', 's')" as="xs:string"/>
        <xsl:variable name="host" select="analyze-string($client, 'hosts+=s+(.*)')/*:match/*:group/text()" as="xs:string"/>
        <xsl:variable name="database" select="analyze-string($client, 'databases+=s+(.*)')/*:match/*:group/text()" as="xs:string"/>
        <xsl:variable name="user" select="analyze-string($client, 'users+=s+(.*)')/*:match/*:group/text()" as="xs:string"/>
        <xsl:variable name="password" select="analyze-string($client, 'passwords+=s+(.*)')/*:match/*:group/text()" as="xs:string"/>
        <xsl:variable name="connection-map" select="map{{   'database':'jdbc:mysql://'||$host||':3306/'||$database||'?serverTimezone=America/Los_Angeles',   'driver':'com.mysql.jdbc.Driver',   'user':$user,   'password':$password,   'autoCommit':true()}}"/>
        <xsl:variable name="connection" select="sql:connect($connection-map)"/>
        <sql-results>
            <xsl:sequence select="sql:execute($connection, 'SHOW DATABASES')"/>
        </sql-results>
    </xsl:template>
</xsl:stylesheet>

let $parameters := 
    <parameters>
        <param name="exist:stop-on-error" value="yes"/>
    </parameters>

let $config := 
    <configuration xmlns="http://saxon.sf.net/ns/configuration">
        <xslt>
            <extensionElement namespace="http://saxon.sf.net/sql" factory="net.sf.saxon.option.sql.SQLElementFactory"/>
        </xslt>
    </configuration>

let $results := transform:transform($xml, $xsl, $parameters, $config, ())
return $results
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks to Michael Kay for pointing me in the right direction. Here is the change that got results to return. Note, no $config was needed.

    xquery version "3.1";
declare namespace transform="http://exist-db.org/xquery/transform";

let $xml := <root/>
let $xsl := <xsl:stylesheet
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:file="http://expath.org/ns/file"
    xmlns:wq="https://winequest.com"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:map="http://www.w3.org/2005/xpath-functions/map"
    xmlns:sql="http://saxon.sf.net/sql"
    exclude-result-prefixes="file wq xs map sql"
    version="3.0" expand-text="yes">
    <xsl:mode on-no-match="shallow-copy"/>
    
    <xsl:template match="/">
        <xsl:variable name="cnf" select="file:read-text('/etc/my.cnf')"/>
        <xsl:variable name="client" select="$cnf =&gt; replace('^.*[client]', '', 's') =&gt; replace('[[^]]+].*$', '', 's')" as="xs:string"/>
        <xsl:variable name="host" select="analyze-string($client, 'hosts+=s+(.*)')/*:match/*:group/text()" as="xs:string"/>
        <xsl:variable name="database" select="analyze-string($client, 'databases+=s+(.*)')/*:match/*:group/text()" as="xs:string"/>
        <xsl:variable name="user" select="analyze-string($client, 'users+=s+(.*)')/*:match/*:group/text()" as="xs:string"/>
        <xsl:variable name="password" select="analyze-string($client, 'passwords+=s+(.*)')/*:match/*:group/text()" as="xs:string"/>
        <xsl:variable name="database" select="'jdbc:mysql://'||$host||':3306/'||$database||'?serverTimezone=America/Los_Angeles'"/>
        <xsl:variable name="connection-map" select="map{{   'database':$database,   'driver':'com.mysql.jdbc.Driver',   'user':$user,   'password':$password,   'autoCommit':true()}}"/>
        <xsl:variable name="connection" select="sql:connect($connection-map)"/>
        <sql-results>
            <xsl:for-each select="sql:prepared-query($connection, 'SELECT menu_template_name FROM menu_templates ORDER BY menu_template_name')()">
                <result test="{{?menu_template_name}}"/>
            </xsl:for-each>   
        </sql-results>
    </xsl:template>
</xsl:stylesheet>

let $parameters := 
    <parameters>
        <param name="exist:stop-on-error" value="yes"/>
    </parameters>

let $results := transform:transform($xml, $xsl, $parameters)
return $results

  2. 0

    The documentation of sql:execute() says that it returns no results, so it appears to be working as designed. It’s designed to enable execution of SQL commands for the sake of their side effects (like DROP TABLE).

    I’m not sure how the SHOW DATABASE command operates at the JDBC level. I guess you would do a call on Statement.execute() followed by a call on Statement.getResultSet(). Unfortunately I don’t think there’s anything in Saxon’s SQL function library that quite corresponds to that.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search