Skip to Content
author's profile photo Former Member
Former Member

How to return dynamic cursor on MaxDb 7.7 ?

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 11, 2010 at 02:35 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 08, 2010 at 01:16 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • author's profile photo Former Member
    Former Member
    Posted on Oct 11, 2010 at 10:08 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.