cancel
Showing results for 
Search instead for 
Did you mean: 

How to return dynamic cursor on MaxDb 7.7 ?

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

Hello,

the problem is solved.

The only think I MUST to do is define some name for the return cursor.

Thank you very much, Dusan

Answers (2)

Answers (2)

Former Member
0 Kudos

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

Former Member
0 Kudos

Hello,

yes you are right. Such code is working.

But, I need something else. I want to return result of select what I create inside my procedure.

Example you sent my executes fixed select only.

Thank you for trying to help

Dusan

lbreddemann
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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