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 Ivan Ooi <ol...@gmail.com> on 2006/08/04 03:56:18 UTC

using sql statement in procedure or function

hi all,

  Is there anyway that i can use SQL statement to query my data in procedure
or function ?
Will Derby going to implement that ? Why not take a look JoSQL.sf.net,
good if Derby can take this library in :-)

Thanks

Re: using sql statement in procedure or function

Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
Ivan Ooi wrote:
> hi all,
> 
>  Is there anyway that i can use SQL statement to query my data in procedure
> or function ?

Hi, Ivan,

SQL functions and procedures implemented in Java can execute SQL
statements. More information, and some sample functions and procedures,
are on the Derby Wiki:

http://wiki.apache.org/db-derby/DerbySQLroutines

> Will Derby going to implement that ? Why not take a look JoSQL.sf.net,
> good if Derby can take this library in :-)

I hadn't heard about JoSQL before -- thanks for the link.

 -jean

Re: using sql statement in procedure or function

Posted by Ivan Ooi <ol...@gmail.com>.
SQL-J can freely downloaded from otn.oracle.com It is free.

On 8/4/06, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Ivan Ooi wrote:
>
> > Nice if I can use SQLJ directly inside there :-) cleaner.
> >
> > String ls_code ;
> >
> > #sqlj{
> > select code
> >  into :ls_code
> >  from staff
> > where year = 1960
> > };
> >
> > nice to have and nice to dream off :-(
>
> In theory you can. The default SQL-J setup has worked with Cloudscape
> (pre-open source) whenever I've tried really simple tests, using the
> setup that converted SQL-J to vanilla JDBC. If it worked with an
> embedded connection then it should work within a procedure using the
> standard JDBC jdbc:default:connection. I don't see how it would be any
> different in Derby.
>
> It would be an interesting experiment to try and writeup, first problem
> might be where to get SQL-J.
>
> Also, I've spent some time on looking at supporting procedures written
> in Java.
> http://wiki.apache.org/db-derby/SqlPsmSupport
>
> Dan.
>
>
>
>

Re: using sql statement in procedure or function

Posted by Daniel John Debrunner <dj...@apache.org>.
Ivan Ooi wrote:

> Nice if I can use SQLJ directly inside there :-) cleaner.
> 
> String ls_code ;
> 
> #sqlj{
> select code
>  into :ls_code
>  from staff
> where year = 1960
> };
> 
> nice to have and nice to dream off :-(

In theory you can. The default SQL-J setup has worked with Cloudscape
(pre-open source) whenever I've tried really simple tests, using the
setup that converted SQL-J to vanilla JDBC. If it worked with an
embedded connection then it should work within a procedure using the
standard JDBC jdbc:default:connection. I don't see how it would be any
different in Derby.

It would be an interesting experiment to try and writeup, first problem
might be where to get SQL-J.

Also, I've spent some time on looking at supporting procedures written
in Java.
http://wiki.apache.org/db-derby/SqlPsmSupport

Dan.

Re: using sql statement in procedure or function

Posted by Ivan Ooi <ol...@gmail.com>.
Nice if I can use SQLJ directly inside there :-) cleaner.

String ls_code ;

#sqlj{
select code
  into :ls_code
  from staff
 where year = 1960
};

nice to have and nice to dream off :-(

On 8/4/06, Stanley Bradbury <St...@gmail.com> wrote:
>
> Ivan Ooi wrote:
> > hi all,
> >
> >   Is there anyway that i can use SQL statement to query my data in
> > procedure or function ?
> > Will Derby going to implement that ? Why not take a look JoSQL.sf.net
> > <http://JoSQL.sf.net>,
> > good if Derby can take this library in :-)
> >
> > Thanks
> Hi -
> The Derby WIKI page on Procedures and Functions is a good place to look
> for information and examples of doing this.
>
> URL:  http://wiki.apache.org/db-derby/DerbySQLroutines
>
> You will need to use a Procedure to return multiple values - a function
> can only return a single value.
>
>
>
>
>

Re: using sql statement in procedure or function

Posted by Stanley Bradbury <St...@gmail.com>.
Ivan Ooi wrote:
> hi all,
>
>   Is there anyway that i can use SQL statement to query my data in 
> procedure or function ?
> Will Derby going to implement that ? Why not take a look JoSQL.sf.net 
> <http://JoSQL.sf.net>,
> good if Derby can take this library in :-)
>
> Thanks
Hi -
The Derby WIKI page on Procedures and Functions is a good place to look 
for information and examples of doing this.

URL:  http://wiki.apache.org/db-derby/DerbySQLroutines

You will need to use a Procedure to return multiple values - a function 
can only return a single value.