4

I want to query database using java tool SAXON

  1. Use an ODBC connection to connect to mysql database
  2. Query database ( probably information schema -read schema )
  3. Export the results into xml

Is this a licensed feature in saxon ( e.g saxon9ee ) ?
If yes Is there any other open-source option to achieve same level of functionality ?
I have downloaded saxon9ee.jar + saxon9he.jar and play around a bit.

what I have done so far:

XSLT:connect.xslt

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  exclude-result-prefixes="#all"
  xmlns:sql="http://saxon.sf.net/sql"
  extension-element-prefixes="sql"
  version="2.0">

    <xsl:output method="xml" encoding="UTF-8" indent="yes"/>

    <xsl:param name="driver" select="'com.mysql.jdbc.Driver'"/>
    <xsl:param name="database" select="'jdbc:mysql://localhost/databaename'"/>
    <xsl:param name="user" select="'usr'"/>
    <xsl:param name="password" select="'pwd'"/>

    <xsl:template match="//databaseObjects">
            <xsl:message>Connecting to database...</xsl:message>

            <!-- The "connection" variable establishes JDBC connection by selecting as its value the SQL connection to the database.!-->

           <xsl:variable name="connection" as="java:java.lang.Object" xmlns:java="http://saxon.sf.net/java-type">
                <sql:connect driver="{$driver}" database="{$database}" user="{$user}" password="{$password}">

                   <!-- Used primarily for debugging, if, 
                   for whatever reason, the credentials or something incorrect is passed in the connect statement, 
                   the process will terminate with the following message -->
                    <xsl:fallback>
                        <xsl:message terminate="yes">Connection to MySQL failed.</xsl:message>
                    </xsl:fallback>
                   </sql:connect>
            </xsl:variable>


     <sql:close connection="$connection"/>
 </xsl:template>
</xsl:stylesheet>

XML: objects.xml

(rough idea )

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseObjects>
    <object type="triggers"/>
    <object type="functions"/>
    <object type="procedures"/>
    <object type="views"/>
    <object type="events"/>
</databaseObjects>

command:

java -jar ~/saxon/saxon9he.jar   objects.xml connect.xslt

output:

No license file found - running with licensable features disabled Connecting to database... Error on line 17 of connect.xslt: XTDE1450: Unknown extension instruction in built-in template rule Transformation failed: Run-time errors were reported

any kind of help is appreciable, thanks

Paul Sweatte
  • 24,148
  • 7
  • 127
  • 265
sakhunzai
  • 13,900
  • 23
  • 98
  • 159
  • The SQL extension is not available in Saxon HE: http://www.saxonica.com/feature-matrix.html. – mzjn Jan 23 '13 at 07:55
  • What about Saxon EE , what are other options ? – sakhunzai Jan 23 '13 at 08:22
  • @mzjn thanks, I am looking for Open source solution, in that case – sakhunzai Jan 23 '13 at 08:56
  • 1
    If you've got more time than money, then the SQL extension to Saxon is actually published as open source code and you can compile it and integrate it with Saxon-HE yourself. It just doesn't come integrated with Saxon-HE "out of the box". – Michael Kay Jan 27 '13 at 12:51
  • @MichaelKay, thanks for the suggestion, that seems good opportunity – sakhunzai Jan 28 '13 at 06:14

1 Answers1

1

Use the following process:

  • Install MySQL Connector/J (the official JDBC driver for MySQL)
  • Install Apache Ant
  • Create/Modify an Ant task to:
    • connect to MySQL
    • run query
    • get XML output
    • process XML via Saxon

References

Paul Sweatte
  • 24,148
  • 7
  • 127
  • 265