UDF

[shsandeep]

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.

[4.spam]

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.

[Serge Rielau]

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/

[shsandeep]

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

[Serge Rielau]

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/

[4.spam]

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