469,602 Members | 1,838 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

UDF

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.

Jul 3 '06 #1
5 1913
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,

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.
Jul 3 '06 #2
shsandeep wrote:
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?
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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 3 '06 #3
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

Jul 4 '06 #4
shsandeep wrote:
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
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;
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jul 4 '06 #5
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,

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
Jul 4 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by William C. White | last post: by
2 posts views Thread by Albert Ahtenberg | last post: by
3 posts views Thread by James | last post: by
reply views Thread by Ollivier Robert | last post: by
1 post views Thread by Richard Galli | last post: by
4 posts views Thread by Albert Ahtenberg | last post: by
1 post views Thread by inderjit S Gabrie | last post: by
2 posts views Thread by Jack | last post: by
3 posts views Thread by Sandwick | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.