You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Briggs <ac...@gmail.com> on 2008/01/24 21:57:57 UTC
Installing and Using Java Procedures.
I'm having a bit of a problem installing a java procedure in derby
(10.3). I thought I understood what I could do with them but now...
I have created a simple "hello world" procedure:
--------------
package derby.proc;
public class HelloWorldProcedure {
public static String helloWorld(final String name) {
return "hello " + name;
}
}
--------------
Then created a jar file called DerbyProcs.jar in my temp directory
(with the above class).
Then I run the following commands:
CALL SQLJ.install_jar('c:\tmp\DerbyProcs.jar', 'APP.DERBY_PROCS', 0);
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','APP.DERBY_PROCS');
CREATE PROCEDURE APP.HELLO_WORLD(
IN NAME VARCHAR(20),
OUT HELLO_YOU VARCHAR(30)
)
PARAMETER STYLE JAVA
READS SQL DATA
LANGUAGE JAVA
EXTERNAL NAME 'derby.proc.HelloWorldProcedure.helloWorld';
-- CALL THE PROC
CALL APP.HELLO_WORLD('world');
The result I get is:
Error code 30000, SQL state 42Y03: 'APP.HELLO_WORLD' is not recognized
as a function or procedure.
Line 13, column 1
What am I missing? Am I wrong on what I am expecting to happen?
Thanks for your time,
Briggs.
--
"Conscious decisions by conscious minds are what make reality real"
Re: Installing and Using Java Procedures.
Posted by Briggs <ac...@gmail.com>.
I do have a feeling that I am suppose to be dealing with result sets
and have completely missed that. I am sure, without even trying, that
the example I posted would work as a function. Doh. I think I am
getting on the right track now.
On Jan 24, 2008 3:57 PM, Briggs <ac...@gmail.com> wrote:
> I'm having a bit of a problem installing a java procedure in derby
> (10.3). I thought I understood what I could do with them but now...
>
> I have created a simple "hello world" procedure:
>
> --------------
>
> package derby.proc;
>
> public class HelloWorldProcedure {
> public static String helloWorld(final String name) {
> return "hello " + name;
> }
> }
>
> --------------
>
> Then created a jar file called DerbyProcs.jar in my temp directory
> (with the above class).
>
> Then I run the following commands:
>
> CALL SQLJ.install_jar('c:\tmp\DerbyProcs.jar', 'APP.DERBY_PROCS', 0);
>
> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','APP.DERBY_PROCS');
>
> CREATE PROCEDURE APP.HELLO_WORLD(
> IN NAME VARCHAR(20),
> OUT HELLO_YOU VARCHAR(30)
> )
> PARAMETER STYLE JAVA
> READS SQL DATA
> LANGUAGE JAVA
> EXTERNAL NAME 'derby.proc.HelloWorldProcedure.helloWorld';
>
>
> -- CALL THE PROC
>
> CALL APP.HELLO_WORLD('world');
>
>
> The result I get is:
>
> Error code 30000, SQL state 42Y03: 'APP.HELLO_WORLD' is not recognized
> as a function or procedure.
> Line 13, column 1
>
>
> What am I missing? Am I wrong on what I am expecting to happen?
>
> Thanks for your time,
>
> Briggs.
>
> --
> "Conscious decisions by conscious minds are what make reality real"
>
--
"Conscious decisions by conscious minds are what make reality real"
Re: Installing and Using Java Procedures.
Posted by Briggs <ac...@gmail.com>.
Hey, thanks. I'll look at em.
On Jan 25, 2008 8:53 AM, Rick Hillegas <Ri...@sun.com> wrote:
>
> Dyre.Tjeldvoll@Sun.COM wrote:
> > Briggs <ac...@gmail.com> writes:
> >
> >
> >> Hey, thanks for your reply. I am still way confused on this. And
> >> trying to find examples of the SQL/JRT spec is almost impossible. I
> >> am trying to make some sense of the examples of creating store
> >> procedures off the http://wiki.apache.org/db-derby/DerbySQLroutines
> >> wiki page with no avail. I am just trying to find the actual
> >> rules/spec/documentation on doing this.
> >>
> >
> > Don't know if it helps, but you could take a look at one of Derby's unit
> > tests for procedures. For example
> >
> > https://svn.apache.org/repos/asf/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java
> >
> >
> >
> Hi Briggs,
>
> You can also take a look at the demo applications which are bundled with
> Derby. These applications live in the demo subdirectory--demo is a top
> level directory parallel to lib and bin. The "scores" demo gives some
> examples of how to declare and use procedures and functions. If you grep
> the demo tree for "ScoreTestTaking", you will see an example of how to
> declare and use a procedure. The Database class in the scores demo shows
> you how to:
>
> o Load a jar file into the database.
> o Wire the jar file into the classpath.
> o Declare some functions and procedures
>
> Hope this helps,
> -Rick
>
--
"Conscious decisions by conscious minds are what make reality real"
Re: Installing and Using Java Procedures.
Posted by Rick Hillegas <Ri...@Sun.COM>.
Dyre.Tjeldvoll@Sun.COM wrote:
> Briggs <ac...@gmail.com> writes:
>
>
>> Hey, thanks for your reply. I am still way confused on this. And
>> trying to find examples of the SQL/JRT spec is almost impossible. I
>> am trying to make some sense of the examples of creating store
>> procedures off the http://wiki.apache.org/db-derby/DerbySQLroutines
>> wiki page with no avail. I am just trying to find the actual
>> rules/spec/documentation on doing this.
>>
>
> Don't know if it helps, but you could take a look at one of Derby's unit
> tests for procedures. For example
>
> https://svn.apache.org/repos/asf/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java
>
>
>
Hi Briggs,
You can also take a look at the demo applications which are bundled with
Derby. These applications live in the demo subdirectory--demo is a top
level directory parallel to lib and bin. The "scores" demo gives some
examples of how to declare and use procedures and functions. If you grep
the demo tree for "ScoreTestTaking", you will see an example of how to
declare and use a procedure. The Database class in the scores demo shows
you how to:
o Load a jar file into the database.
o Wire the jar file into the classpath.
o Declare some functions and procedures
Hope this helps,
-Rick
Re: Installing and Using Java Procedures.
Posted by Dy...@Sun.COM.
Briggs <ac...@gmail.com> writes:
> Hey, thanks for your reply. I am still way confused on this. And
> trying to find examples of the SQL/JRT spec is almost impossible. I
> am trying to make some sense of the examples of creating store
> procedures off the http://wiki.apache.org/db-derby/DerbySQLroutines
> wiki page with no avail. I am just trying to find the actual
> rules/spec/documentation on doing this.
Don't know if it helps, but you could take a look at one of Derby's unit
tests for procedures. For example
https://svn.apache.org/repos/asf/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ProcedureTest.java
--
dt
Re: Installing and Using Java Procedures.
Posted by Briggs <ac...@gmail.com>.
Hey, thanks for your reply. I am still way confused on this. And
trying to find examples of the SQL/JRT spec is almost impossible. I
am trying to make some sense of the examples of creating store
procedures off the http://wiki.apache.org/db-derby/DerbySQLroutines
wiki page with no avail. I am just trying to find the actual
rules/spec/documentation on doing this.
But, again, thanks for your help.
On Jan 24, 2008 4:23 PM, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Briggs wrote:
> > I'm having a bit of a problem installing a java procedure in derby
> > (10.3). I thought I understood what I could do with them but now...
> >
> > I have created a simple "hello world" procedure:
> >
> > --------------
> >
> > package derby.proc;
> >
> > public class HelloWorldProcedure {
> > public static String helloWorld(final String name) {
> > return "hello " + name;
> > }
> > }
> >
> > --------------
> >
> > Then created a jar file called DerbyProcs.jar in my temp directory
> > (with the above class).
> >
> > Then I run the following commands:
> >
> > CALL SQLJ.install_jar('c:\tmp\DerbyProcs.jar', 'APP.DERBY_PROCS', 0);
> >
> > CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','APP.DERBY_PROCS');
> >
> > CREATE PROCEDURE APP.HELLO_WORLD(
> > IN NAME VARCHAR(20),
> > OUT HELLO_YOU VARCHAR(30)
> > )
> > PARAMETER STYLE JAVA
> > READS SQL DATA
> > LANGUAGE JAVA
> > EXTERNAL NAME 'derby.proc.HelloWorldProcedure.helloWorld';
> >
> >
> > -- CALL THE PROC
> >
> > CALL APP.HELLO_WORLD('world');
> >
> >
> > The result I get is:
> >
> > Error code 30000, SQL state 42Y03: 'APP.HELLO_WORLD' is not recognized
> > as a function or procedure.
> > Line 13, column 1
> >
> >
> > What am I missing? Am I wrong on what I am expecting to happen?
>
> You defined an procedure that takes two arguments, but then only passed
> one, so no matching procedure will be found. Then as your later e-mail
> says this needs to be a function, OUT parameters are returned using one
> element arrays in the Java method's argument list.
>
> See:
>
> http://wiki.apache.org/db-derby/DerbySQLroutines
>
> this page has a list of useful topics:
>
> http://wiki.apache.org/db-derby/HintsAndTips
>
> Dan.
>
--
"Conscious decisions by conscious minds are what make reality real"
Re: Installing and Using Java Procedures.
Posted by Daniel John Debrunner <dj...@apache.org>.
Briggs wrote:
> I'm having a bit of a problem installing a java procedure in derby
> (10.3). I thought I understood what I could do with them but now...
>
> I have created a simple "hello world" procedure:
>
> --------------
>
> package derby.proc;
>
> public class HelloWorldProcedure {
> public static String helloWorld(final String name) {
> return "hello " + name;
> }
> }
>
> --------------
>
> Then created a jar file called DerbyProcs.jar in my temp directory
> (with the above class).
>
> Then I run the following commands:
>
> CALL SQLJ.install_jar('c:\tmp\DerbyProcs.jar', 'APP.DERBY_PROCS', 0);
>
> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','APP.DERBY_PROCS');
>
> CREATE PROCEDURE APP.HELLO_WORLD(
> IN NAME VARCHAR(20),
> OUT HELLO_YOU VARCHAR(30)
> )
> PARAMETER STYLE JAVA
> READS SQL DATA
> LANGUAGE JAVA
> EXTERNAL NAME 'derby.proc.HelloWorldProcedure.helloWorld';
>
>
> -- CALL THE PROC
>
> CALL APP.HELLO_WORLD('world');
>
>
> The result I get is:
>
> Error code 30000, SQL state 42Y03: 'APP.HELLO_WORLD' is not recognized
> as a function or procedure.
> Line 13, column 1
>
>
> What am I missing? Am I wrong on what I am expecting to happen?
You defined an procedure that takes two arguments, but then only passed
one, so no matching procedure will be found. Then as your later e-mail
says this needs to be a function, OUT parameters are returned using one
element arrays in the Java method's argument list.
See:
http://wiki.apache.org/db-derby/DerbySQLroutines
this page has a list of useful topics:
http://wiki.apache.org/db-derby/HintsAndTips
Dan.