Creating Scalar UDF

[deepdata]

Hi,
I am trying to create a function which returns a scalar data. My
function tries to insert a row in a table as well.

/**

CREATE function getManufacturerId
(
manufacturer VARCHAR(128)
)
RETURNS BIGINT
Language sql
modifies sql data
BEGIN ATOMIC

declare manufacturer_row bigint;
set manufacturer_row = (select manufacturer_id from manufacturer where
manufacturer_name = manufacturer);
IF(manufacturer_row IS NULL ) THEN

set manufacturer_row = nextval for manufacturer_seq ;
INSERT INTO manufacturer VALUES(manufacturer_row, manufacturer);

END IF;
return manufacturer_row;
END@
*/

I am getting following error.

DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
returned:
SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL
DATA" clause are present. LINE NUMBER=18. SQLSTATE=42613

After googling for a while i found out that only table functions can
declare "modifies sql data". This raises the question whether i can
create a table function to get only one value from the function and use
the return value in "call procedure statement".
In my procedure i have to use the above function

call getManufacturerId(manufacturer)

And the above statement will return me the result set if i use table
function, how can i use that result set in procedure to get value.

Similarly instead of using function which has plenty of restrictions,
if i create procedure can i get get one value by calling a procedure
from a procedure.

I hope I am able to explain my problem, if not please inform me i will
try to explain again.

Any hint to solve above problem will be highly appreciated.

Thanks!
--
deepdata
------------------------------------------------------------------------
deepdata's Profile: http://www.dbtalk.net/m335
View this thread: http://www.dbtalk.net/t305851

[Serge Rielau]

deepdata wrote:

Hi,
I am trying to create a function which returns a scalar data. My
function tries to insert a row in a table as well.

/**

CREATE function getManufacturerId
(
manufacturer VARCHAR(128)
)
RETURNS BIGINT
Language sql
modifies sql data
BEGIN ATOMIC

declare manufacturer_row bigint;
set manufacturer_row = (select manufacturer_id from manufacturer where
manufacturer_name = manufacturer);
IF(manufacturer_row IS NULL ) THEN

set manufacturer_row = nextval for manufacturer_seq ;
INSERT INTO manufacturer VALUES(manufacturer_row, manufacturer);

END IF;
return manufacturer_row;
END@
*/

I am getting following error.

DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
returned:
SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL
DATA" clause are present. LINE NUMBER=18. SQLSTATE=42613

After googling for a while i found out that only table functions can
declare "modifies sql data". This raises the question whether i can
create a table function to get only one value from the function and use
the return value in "call procedure statement".
In my procedure i have to use the above function

call getManufacturerId(manufacturer)

And the above statement will return me the result set if i use table
function, how can i use that result set in procedure to get value.

Similarly instead of using function which has plenty of restrictions,
if i create procedure can i get get one value by calling a procedure
from a procedure.

To return one value from a table function simply use e.g.:
....RETURNS TABLE(INT)
..... RETURN VALUES 1

But it really looks like you want a stored procedure.
Stored procedures support INOUT and OUT parameters.
E.g.
CREATE PROCEDURE plus(IN a INT, IN b INT, OUT c INT)
SET c = a + b;

When using an OUT (or INOUT) parameter you need to pass a ? or variable
so the result can be stored.

CALL plus(a, b, ?)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[deepdata]

Hi,

Thanks for replying.

I was thinking function would be appropriate if i have to return a
single scalar value. But with the restrictions i guess, procedure would
be best option.

The other solution which you have provided i.e.

...Returns table(int)
...Return values 1

How can I use the values return by this function table in some
procedure? Does DB2 provides way to handle resultset (i presume the
table returned from the funciton is resultset)?

If you can provide this information it will be really helpful to solve
other problem.

Thanks!
Regards,

Dipesh
--
deepdata
------------------------------------------------------------------------
deepdata's Profile: http://www.dbtalk.net/m335
View this thread: http://www.dbtalk.net/t305851

[Serge Rielau]

deepdata wrote:

I was thinking function would be appropriate if i have to return a
single scalar value. But with the restrictions i guess, procedure would
be best option. You have to start with the usage.
Procedures encapsulate application logic. It is simply an extension of
your app logic.
Functions extend SQL capabilities.
That is a procedure gets CALL-ed using the CALL statement.
A function can be invoked by any SQL statement where ever an expression
(scalar) or a table (table function) is allowed.
The other solution which you have provided i.e.

..Returns table(int)
..Return values 1

How can I use the values return by this function table in some
procedure? Does DB2 provides way to handle resultset (i presume the
table returned from the funciton is resultset)?

A table function in invoked where a table is invoked.
It is described under "table-reference" in the docs.
<table-reference>
::= <table> ....
| TABLE(<functionname>(<parameters>)) AS <tablealias>

So:
SELECT * FROM TABLE(foo(15, 'hello')) AS F
or
SELECT * FROM T LEFT OUTER JOIN TABLE(foo(T.c1, T.c2)) AS F ON T.c3 = F.A

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[deepdata]

Hi,

Thanks for replying. I will try that and inform you whether i am able
to do that or not.

Thanks for explaining usage of function and procedures.
If I ever need a function to return a value (single value) then i have
to create a function they way you have described and use it in the
procedure as you have described here.

Thanks!

PS: By the way which docs you are referring to. I am using db2 for the
first time. I am porting my database from firebird to db2 and reading
most of the stuff from online only.
--
deepdata
------------------------------------------------------------------------
deepdata's Profile: http://www.dbtalk.net/m335
View this thread: http://www.dbtalk.net/t305851

[Serge Rielau]

deepdata wrote:

PS: By the way which docs you are referring to. I am using db2 for the
first time. I am porting my database from firebird to db2 and reading
most of the stuff from online only.

http://publib.boulder.ibm.com/infoce...n/r0000875.htm
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab