db2 functions

fede_ff · #1 (**permalink**) 02-21-06, 08:05

Hi everyone. I've tried to write a function in db2 but it wouldnt compile. I've tried to compile one of the examples from the db2 help, but i get the following error:

Quote:

During SQL processing it returned:
SQL0104N An unexpected token "''" was found following "archar(4000) default".
Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=6.
SQLSTATE=42601

Any ideas??...

The code tested was:

Code:

create function tms.REVERSE(INSTR varchar(4000))
returns varchar(4000)
specific REVERSE
deterministic no external action contains sql
-- This function reverses a string.  
begin atomic
declare REVSTR, RESTSTR varchar(4000) default '';
declare LEN INT;
if INSTR is NULL then
return NULL;
end if;
  set (RESTSTR, LEN) = (INSTR, length(INSTR));
   while LEN > 0 do
     set (REVSTR, RESTSTR, LEN) = (substr(RESTSTR, 1, 1) concat REVSTR, substr(RESTSTR, 2, LEN - 1), LEN - 1);
   end while;
  return REVSTR;
end

sathyaram_s · #2 (**permalink**) 02-21-06, 08:22

In the SQL interface you run this, is ';' the default delimiter for SQL Statements ?

fede_ff · #3 (**permalink**) 02-21-06, 08:45

I am not what is the delimiter, but it seems its not ';'. Iam using db2 control center. anybody knows whats the delimiter for cc?
thanks

juliane26 · #4 (**permalink**) 02-21-06, 12:18

In control center, there is a tab: tools -> settings, there you can change the default delimiter.

AFAIK the default is of course ';' - you will need to change that for your statement to run.

Rob den Heijer · #5 (**permalink**) 03-01-06, 05:24

You have to set the delimiter, whether you work from the control center or not. The easiest way is to make it explicit. Remember: there are two delimiters when creating functions. One terminates lines within the function, the other terminates the function itself.

Example, straight from the DB2 Cookbook:

SET DELIMITER !

CREATE FUNCTION dpt1 (deptin SMALLINT)
RETURNS SMALLINT
BEGIN ATOMIC
DECLARE num_names SMALLINT;
FOR getnames AS
SELECT COUNT(*) AS #n
FROM staff
WHERE dept = deptin
DO
SET num_names = #n;
END FOR;
RETURN num_names;
END!
COMMIT!

Should work.
cheers,
Rob.

Internet Services

Web Development

Digital Marketing

Consumer Tech