Topic
2 replies Latest Post - ‏2007-02-21T03:18:26Z by Jean_Anderson
SystemAdmin
SystemAdmin
1525 Posts
ACCEPTED ANSWER

Pinned topic Read file from java stored procedure

‏2007-02-20T12:45:44Z |
Hi folks,
I'm struggling with a problem, hope somebody can help...
The situation is as following:

1) I have a java stored procedure in a jar file (e.g.: mySP.jar), and other library jars used by the java stored procedure.

2) I correctly deployed all the jars on cloudscape through SQLJ.INSTALL_JAR and initialized the property derby.database.classpath using the command SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY.

3) the java stored procedure tries to read a xml file contained in one of the library jar files, but I get a FileNotFoundException at runtime.

It seems that the .class files in the jars registered with SQLJ.INSTALL_JAR get resolved, but not other resources (xml, xsd, properties).
Any suggestions? Is there a different command to register libraries in Cloudscape?

Thank you in advance,
Pier
Updated on 2007-02-21T03:18:26Z at 2007-02-21T03:18:26Z by Jean_Anderson
  • Jean_Anderson
    Jean_Anderson
    179 Posts
    ACCEPTED ANSWER

    Re: Read file from java stored procedure

    ‏2007-02-21T03:16:59Z  in response to SystemAdmin
    Make sure that multiple jars in the derby.database.classpath are separated with a colon (':')[1] -- not separating multiple jars with a colon is a common problem.

    A coworker put together an example, which I'm including here. A SQL procedure called TESTPARSEJAXP() invokes a static method in a class that parses an xml file in another jar (when you run this, ignore the xml document validation errors).

    Two files are attached to this post and SQLJ.INSTALL_JAR assumes they are in c:\temp:
    testJAXP.jar - has the class (and source) for the TESTPARSEJAXP() procedure
    jaxpexample.jar - has the xml file read by the TESTPARSEJAXP() procedure

    Here is the SQL:codeconnect 'jdbc:derby:jaxpdb;create=true';
    CALL SQLJ.INSTALL_JAR('file:c:/temp/jaxpexample.jar','JAXPEX',0);
    CALL SQLJ.INSTALL_JAR('file:c:/temp/testJAXP.jar','TESTJAXP',0);
    CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','APP.TESTJAXP:APP.JAXPEX');
    CREATE PROCEDURE TESTPARSEJAXP() LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL EXTERNAL NAME 'testJAXP.myParse';
    call TESTPARSEJAXP();[/code]
    I hope this helps.

    regards,

    -jean

    [1] http://db.apache.org/derby/docs/dev/devguide/cdevdeploy21645.html
    Updated on 2007-02-21T03:16:59Z at 2007-02-21T03:16:59Z by Jean_Anderson
    • Jean_Anderson
      Jean_Anderson
      179 Posts
      ACCEPTED ANSWER

      Re: Read file from java stored procedure

      ‏2007-02-21T03:18:26Z  in response to Jean_Anderson
      > Two files are attached to this post and
      > SQLJ.INSTALL_JAR assumes they are in c:\temp:
      > testJAXP.jar - has the class (and source) for the
      > TESTPARSEJAXP() procedure
      > jaxpexample.jar - has the xml file read by the
      > TESTPARSEJAXP() procedure

      oops, only one attachment made it into the previous post :-). The second is attached to this post.

      regards,

      -jean
      Updated on 2007-02-21T03:18:26Z at 2007-02-21T03:18:26Z by Jean_Anderson