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 => replace('^.*\[client\]', '', 's') => replace('\[[^\]]+\].*$', '', 's')" as="xs:string"/>
<xsl:variable name="host" select="analyze-string($client, 'host\s+=\s+(.*)')/*:match/*:group/text()" as="xs:string"/>
<xsl:variable name="database" select="analyze-string($client, 'database\s+=\s+(.*)')/*:match/*:group/text()" as="xs:string"/>
<xsl:variable name="user" select="analyze-string($client, 'user\s+=\s+(.*)')/*:match/*:group/text()" as="xs:string"/>
<xsl:variable name="password" select="analyze-string($client, 'password\s+=\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
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.