cancel
Showing results for 
Search instead for 
Did you mean: 

Use hdbsql to create a stored procedure

Former Member
0 Kudos

Hi,

I've created a procedure within hdbstudio but I am miserably failing to import it using hdbsql.

Maybe someone can give me a hint what's wrong:

-- Drop and create table

DROP TABLE RANDOMDATA;

CREATE COLUMN TABLE RANDOMDATA( STR1 NCLOB );

DROP PROCEDURE RANDOMGENERATOR;

CREATE PROCEDURE RANDOMGENERATOR LANGUAGE SQLSCRIPT AS

CNTR2 INTEGER;

RDNR DOUBLE;

RSTR NCLOB;

TOPVAL INTEGER;

BEGIN

CNTR2 := 0;

RDTR := '';

-- RAND() = 18 Chars - >100.000 you might get out-of-memory

TOPVAL := ROUND(50000/18,-1);

RDNR := RAND();

-- Loop to create the very long string

WHILE CNTR2 < :TOPVAL DO RSTR := RSTR || RDNR;

CNTR2 := CNTR2 + 1;

END WHILE;

-- Remove old and insert new data

DELETE FROM RANDOMDATA;

INSERT INTO RANDOMDATA SELECT SUBSTR(:RSTR,0,4096) FROM DUMMY;

END;

CALL RANDOMGENERATOR;

As said, it's working great inside hdbstudio but using hdbsql it fails as soon as the PROCEDURE part is starting.

Thanks for helping,

HP

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor

Hi Peter,

the problem here is that hdbsql is relying on command separators to execute statements and the default for the command separator is the semi-colon (;).

But that semi-colon delimiter is also used inside the SQLScript language to separate commands.

So effectively hdbsql mistreats the semi-colons in your procedure as command separators and tries to run each line of the procedure on its own.

The solution is a bit of a workaround:

1. set an alternative command delimiter for your hdbsql session by using the -c switch

2. replace the semi-colons between the single commnds in your script by that command separator.

That would then look like this:

-- Drop and create table 

DROP TABLE RANDOMDATA// 

CREATE COLUMN TABLE RANDOMDATA( STR1 NCLOB )//

DROP PROCEDURE RANDOMGENERATOR//

CREATE PROCEDURE RANDOMGENERATOR LANGUAGE SQLSCRIPT AS 

CNTR2 INTEGER; 

RDNR DOUBLE; 

RSTR NCLOB; 

TOPVAL INTEGER; 

BEGIN 

CNTR2 := 0; 

RDNR := ''; 

-- RAND() = 18 Chars - >100.000 you might get out-of-memory 

TOPVAL := ROUND(50000/18,-1); 

RDNR := RAND(); 

-- Loop to create the very long string 

WHILE CNTR2 < :TOPVAL DO RSTR := RSTR || RDNR; 

CNTR2 := CNTR2 + 1; 

END WHILE; 

-- Remove old and insert new data 

DELETE FROM RANDOMDATA; 

INSERT INTO RANDOMDATA SELECT SUBSTR(:RSTR,0,4096) FROM DUMMY; 

END;

//

CALL RANDOMGENERATOR//

Call the script like this:

hdbsql -U <your_store_ logo_ data_ key> -c //

hdbsql <SID> => \i <your_script.sql>

As you see, I replaced the ; with // and set this // as the command separator.

There was one typo in your procedure (you'll find it ) , but the script is now correctly executed.

- Lars

Former Member
0 Kudos

Excellent. I was already playing with "-c" but of course I was also replacing the other ";".

Oh, and yes ... I know where the typo is. Because this is just a tiny subset of what I really created and actually it's "RSTR := '';" to initialize the string.

Thanks!

HP

Answers (0)