db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: creating a SQL function in derby
Date Mon, 10 Aug 2009 21:41:14 GMT
Hi Thomas,

User-coded functions and procedures in Derby are simply public, static 
Java methods. What you need to do is write a Java method and register it 
using the CREATE FUNCTION command, as described here: 
http://db.apache.org/derby/docs/10.5/ref/ref-single.html#crefsqlj95081 
Detailed examples of how to write and declare Derby functions can be 
found in the Derby source code in the java/demo/scores directory. For an 
example of a function which reads a table and returns a scalar value, 
see the vetChoice() method in 
java/demo/scores/java/server/org/apache/derbyDemo/scores/proc/Functions.java 
Your function declaration would look something like this:

create function sfGetNextID( vcIDName varchar( 200 ) )
returns bigint
language java
parameter style java
contains sql
external name 'yourpackage.yourclass.sfGetNextID'

Hope this helps,
-Rick

Thomas.K.Hill@t-online.de wrote:
> Hi,
>  
> I am currently looking into derby and being used to TSQL, PL/PGSQL 
> can't quite figure out how to migrate one of my postgreSQL functions 
> to derby.
>  
> I have searched the web, the derby wiki and found some (/ few) 
> examples on how to write a function in derby, but none of the examples 
> was covering what I was looking for.
>  
> Here is the PL/PGSQL funtion as a reference on what I am trying to 
> rebuild in derby:
> CREATE OR REPLACE FUNCTION rte."sfGetNextID"(in "vcIDName" name)
>   RETURNS bigint AS
> $BODY$
>    DECLARE
>       "iID" bigint;
>    BEGIN
>       SELECT "NextVal" INTO "iID" FROM rte."IDs" WHERE "IDName" = 
> "vcIDName" FOR UPDATE;
>       UPDATE rte."IDs" SET "NextVal" = "iID" + 1 WHERE "IDName" = 
> "vcIDName";
>       RETURN "iID";
>    END;
>    $BODY$
>   LANGUAGE 'plpgsql' VOLATILE
>  
> This function is selecting the next ID to be assigned to a new row 
> from an ID table. As the table has multiple rows, i.e. one row for 
> each "entity" for which ID generation is accomplished via this table, 
> it needs to receive the entity name (e.g. ClientID) as parameter. 
> Select for update is used to ensure transaction isolation.
>  
> This function can be called from any other plpgsql function as 
> follows: iClientID := rte."sfGetNextID"('CLIENTID') or iOrderID := 
> rte."sfGetNextID"('ORDERID')
>  
> The base table used to store the last used value in this case would be
> IDName    NextValue
> ===============
> CLIENTID    10
> ORDERID    1000
>  
> In derby I am getting as far as:
>  
> CREATE FUNCTION rte"sfGetNextID"("vcIDName" character varying(128))
> RETURNS INTEGER
> PARAMETER STYLE JAVA
> but latest after this I am lost and do not know how to define the 
> function element and tell derby this function will read and modify 
> data (aside from problem that I also have mot found a sample for a 
> select update anywhere).
>  
> Might be asking a lot here, but hope to find someone supporting.
>  
> Thanks


Mime
View raw message