on 07-22-2013 10:29 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.