on 10-08-2010 11:48 AM
Hello,
I'm trying to migrate from MaxDb 7.6. to MaxDb 7.7. Some code I run on 7.6. (NOT UNICODE DB instance), works correctly, but on 7.7 makes problems.
I'm running 2 MaxDb engines on WinXP SP3 (32bit):
1. MaxDb 7.6.06.03
2. MaxDb 7.7.07.16
CREATE DBPROC TEST RETURNS CURSOR AS
VAR statement Varchar(4000);
BEGIN
SET statement = 'DECLARE ' || $CURSOR || ' CURSOR FOR SELECT 1 FROM ADMIN.DUAL';
EXECUTE statement;
END;
//
CALL TEST
CALL TEST returns me this error: [-2010]: Assignment impossible, char value too long.
because of line: SET statement = 'DECLARE ' || $CURSOR || ' CURSOR FOR SELECT 1 FROM ADMIN.DUAL';
What am I doing wrong ? This code is on MaxDb 7.6 working (returns cursor 1 row and 1 column).
How to return result from DB procedure using dynamically created SQL statement ?
Thanks for support,
Dusan
Hi,
yes, this is one difference.
But the main topic is: I do initialize the variable $CURSOR with some name!
And when I tested with 7.7 without this initialization I received -2010, too.
And with
$CURSOR = 'TESTCURSOR';
in my example, the procedure worked.
Elke
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hallo,
I saw a working example:
$CURSOR = 'TESTCURSOR';
TRY
DECLARE :$CURSOR CURSOR FOR
SELECT * FROM ADMIN.DUAL ;
RETURN;
CATCH
IF $RC = 100 THEN CONTINUE EXECUTE;
STOP ($RC, $ERRMSG);
Elke
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I propose to use SUBSTR around your SQL command so that the kernel knows the maximum lengh for sure.
Something like this:
SET statement = SUBSTR('DECLARE ' || $CURSOR || ' CURSOR FOR SELECT 1 FROM ADMIN.DUAL', 0, 4000);
regards,
Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello,
I changed my procedure to :
CREATE DBPROC TEST RETURNS CURSOR AS
VAR statement Varchar(4000);
BEGIN
SET statement = SUBSTR ('DECLARE ' || $CURSOR || ' CURSOR FOR SELECT 1 FROM ADMIN.DUAL', 1, 1000);
EXECUTE statement;
END;
//
CALL TEST
but the result is the same:
Error Executing 'CALL TEST' [-2010]: Assignment impossible, char value too long.
It seams that $CURSOR makes problem in the assignment.
Does somebody use dynamic cursors returned from DB procedure ?
How to write it correctly in MaxDB version 7.7 ?
Thanks for support,
Dusan
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
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.