Archive for the ‘Java’ Category
MySQL, XSLT & Xalan Queries
I posted how to connect to an Oracle database from an XSLT library file back in August 2008. It’s an event driven XML approach that can support web page development. One of my students wanted to do the same thing against MySQL. He quickly saw that it was simply a matter of the switching the JDBC library. He’s got the whole idea bundled on his blog here.
XSLT Library File to Query MySQL Database ↓
Unfold this if you’d like to see the XLST code he’s posted on his blog for MySQL. You’ll find that only line #10 (below) required a change. If you’re new to XSLT, you may find Doug Tidwell’s XSLT, 2nd Edition book very helpful.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | <?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:sql="org.apache.xalan.lib.sql.XConnection" extension-element-prefixes="sql"> <xsl:output method="html" /> <xsl:template match="/"> <xsl:variable name="movies" select="sql:new('com.mysql.jdbc.Driver','jdbc:mysql:///storedb','student','student')" /> <xsl:variable name="streaming" select="sql:disableStreamingMode($movies)" /> <xsl:variable name="queryResults" select="sql:query($movies,'SELECT i.item_title, i.item_asin, i.item_release_date FROM storedb.item i')" /> <html> <head><title>MySQL Result Set</title></head> <body style="font-family: sans-serif;"> <table border="1" cellpadding="5"> <tr> <xsl:for-each select="$queryResults/sql/metadata/column-header"> <th><xsl:value-of select="@column-label" /></th> </xsl:for-each> </tr> <xsl:apply-templates select="$queryResults/sql/row-set/row" /> </table> </body> </html> <xsl:value-of select="sql:close($movies)" /> </xsl:template> <xsl:template match="row"> <tr><xsl:apply-templates select="col" /></tr> </xsl:template> <xsl:template match="col"> <td><xsl:value-of select="text()" /></td> </xsl:template> </xsl:stylesheet> |
Hope this helps those looking for a solution.
No Java in Oracle XE
While helping in the forum, I noticed that folks don’t know that Java isn’t deployed with the Oracle 10g XE. However, it strikes me that you might be able to fudge it but I’m not sure that’s allowed in the EULA. If you want Java inside the database, why wouldn’t you install the licensed product?
There was an OTN article that listed three limitations but this wasn’t one in the article. Maybe I’ll run across the marketing note sometime in the furture or somebody will post the URL as a comment, which is always appreciated.
Anyway, the presence or lack of Java inside the database is pretty easy to test. You only need to do this:
SQL*Plus: Release 10.2.0.1.0 - Production ON Thu Nov 26 21:19:42 2009 Copyright (c) 1982, 2005, Oracle. ALL rights reserved. Connected TO: Oracle DATABASE 10g Express Edition Release 10.2.0.1.0 - Production SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS 2 public class HelloWorldSQL { 3 public static String hello() { 4 RETURN "Hello World."; } 5 } 6 / CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS * ERROR at line 1: ORA-29538: Java NOT installed
It also explains the lack of loadjava or dropjava from the $ORACLE_HOME/bin directory.
Database trigger logic in Java?
I saw a post on the forum and fielded a question from my students on how you can write a database trigger that uses Java for the programming logic. I provided two approaches in this blog page. One lets Java raise the exception, which becomes an unhandled exception in SQL. The other implements the library as a function, and uses an IF statement to raise an exception – with RAISE_APPLICATION_ERROR.
I’m partial to the second approach but think the Fusion middleware may yet present a better option in the future. You should take a peak at the oracle.dss.util.TypeNotSupportedException.
Subscribe via RSS