UDF

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shsandeep - 03 Jul 2006 04:29 GMT

Hi,

I managed to get the below function running:

CREATE FUNCTION MYSCHEMA.SEQLOAD_ID()
RETURNS VARCHAR(20)

F1: BEGIN ATOMIC
DECLARE VARSQL VARCHAR(20);
SET VARSQL = CONCAT('Load_Id_Parm=',CHAR(NEXTVAL FOR
MYSCHEMA.LOAD_ID));
RETURN(VARSQL);
END

It needs to be modified a bit.
It should accept the sequence name as an input parameter rather than hard
coded (in the above function, it is hardcoded LOAD_ID)
I am getting errors telling me 'execute immediate' is not allowed.
Any ideas, suggestions?

Thanks.

Cheers,
San.

Reply to this Message

4.spam@mail.ru - 03 Jul 2006 07:56 GMT

Hello.
1.
Write simple generic external UDF that returns nextval (or prevval) for
any given sequence.
For example, java UDF:
-------------------------------
import java.sql.*;
public class DynSeq {
static final String PREV = "P";
static final String NEXT = "N";

public static int getInt(String seqName, String val_type) throws
Exception {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try
{
con = DriverManager.getConnection( "jdbc:default:connection" );
st = con.createStatement();
rs = st.executeQuery
(
"SELECT "+(NEXT.equals(val_type)?"NEXT":"PREV")+"VAL FOR
"+seqName+" "+
"FROM SYSIBM.SYSDUMMY1"
);
return rs.next()?rs.getInt(1):0;
} catch (SQLException ex)
{
throw ex;
} finally
{
if (st!=null) st.close();
if (rs!=null) rs.close();
if (con!=null) con.close();
}
}
}
-------------------------------
2.
Place comiled java class into sqllib/function directory.
-------------------------------
Register it:
3.
create function dynseq (varchar(128), varchar(1))
external name 'DynSeq.getInt'
returns integer
fenced not null call
not deterministic reads sql data
language java parameter style java
disallow parallel;
-------------------------------
4.
Use this function in your function like this:
SET VARSQL = CONCAT('Load_Id_Parm=',CHAR(dynseq('MYSCHEMA.LOAD_ID',
'N')));
-------------------------------

Sincerely,
Mark B.

> Hi,
>
[quoted text clipped - 20 lines]
> Cheers,
> San.

Reply to this Message

Serge Rielau - 03 Jul 2006 18:59 GMT

> Hi,
>
[quoted text clipped - 15 lines]
> I am getting errors telling me 'execute immediate' is not allowed.
> Any ideas, suggestions?

You can push the EXECUTE IMMEDIATE (actually you need PREPARE/OPEN here)
into a stored procedure and CALL the procedure from the UDF.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

shsandeep - 04 Jul 2006 06:36 GMT

Hi,

The below procedure is not working since SELECT and VALUES statements are
not allowed in EXECUTE IMMEDIATE. Any ideas as how to fix this? I need to
obtain the next value of a sequence which will be an input parameter to
the below proc.

CREATE PROCEDURE DEVSTGEV.SEQPROC (IN SEQNAME VARCHAR(20))
LANGUAGE SQL
P1: BEGIN
DECLARE vsql VARCHAR(200);

SET vsql = 'values nextval for ' || SEQNAME ;
EXECUTE vsql;
END P1

Reply to this Message

Serge Rielau - 04 Jul 2006 14:27 GMT

> Hi,
>
[quoted text clipped - 11 lines]
> EXECUTE vsql;
> END P1

Check my previous post. You need PREPARE and OPEN.

DECLARE val DECIMAL(31, 0);
DECLARE vsql='VALUES NEXTVAL FOR ' || SEQNAME;
DECLARE stmt STATEMENT;
DECLARE cur CURSOR FOR stmt;
PREPARE stmt FROM vsql;
OPEN cur;
FETCH cur INTO val;
CLOSE cur;

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

4.spam@mail.ru - 04 Jul 2006 14:37 GMT

create procedure DEVSTGEV.seqproc(in seqname varchar(20), out res int)
language sql
begin
DECLARE vsql VARCHAR(200);
declare c1 cursor for s1;
set vsql = 'values (nextval for '||seqname||')';
prepare s1 from vsql;
open c1;
fetch c1 into res;
close c1;
end@

> Hi,
>
[quoted text clipped - 11 lines]
> EXECUTE vsql;
> END P1

Reply to this Message