MVSFORUMS.com

Bithead

We have a program which builds a dynamic cursor. This cursor has the ability to select on a wild-card. When not using the wild-card, the DECLARE X CURSOR FOR Y works fine. When we code LIKE 'ABC%' it gives us a condition code +100.

All the doc. that I have found so far indicates that we should receive the +100 on the FETCH not the DECLARE.

What might be going on?

kolusu · Posted: Wed Apr 02, 2003 12:17 pm Post subject:

Bithead,

Are you sure that it is on the DECLARE cursor?? I beleive that sqlcode +100 is for OPEN cursor.

Just guess do you have AND condition in your where clause of the cursor? May be when use a wildcard search you need an OR condition instead of an AND cond.

Please post the declare cursor syntax and let us see if we are missing something

kolusu

Bithead · Posted: Wed Apr 02, 2003 12:28 pm Post subject:

Here is the working storage:

01 SQL-POINTER PIC S9(4) COMP.
01 SQL-DYNAMIC-REQUEST.
49 SQL-LENGTH PIC S9(4) COMP.
49 SQL-COMMAND PIC X(1400).

Here is the code:

DISPLAY '!' SQL-COMMAND (1: SQL-LENGTH) '!'.

EXEC SQL
DECLARE C_CURRORDS CURSOR FOR SQL-STMT
END-EXEC.

EVALUATE SQLCODE
WHEN +0
CONTINUE
WHEN OTHER
MOVE SQLCODE TO WS-DISPLAY-SQLCODE
DISPLAY ' SQL CODE RETURNED FROM DECLARE = '
WS-SQLCODE
CALL 'ABEND'
END-EVALUATE.

I have searched the program for duplicate cursors and display messages and there aren't any.

I have not included the logic that builds the statement because it is very complicated. When I look at the SQL that was displayed, it looks good. Anyway, it is not referenced at the point of abend.

kolusu · Posted: Wed Apr 02, 2003 12:37 pm Post subject:

Bithead,

EXEC SQL
DECLARE C_CURRORDS CURSOR FOR SQL-STMT
END-EXEC.

Shouldn't it be SQL-COMMAND instead of SQL-STMT in the cursor declaration?

or Did you miss the MOVE SQL-COMMAND TO SQL-STMT in the above post?

Kolusu

Bithead · Posted: Wed Apr 02, 2003 12:40 pm Post subject:

Kolusu,

The statement is correct. The next statement is

PREPARE SQL-STMT from :SQL-DYNAMIC-REQUEST

Phil

kolusu · Posted: Wed Apr 02, 2003 1:03 pm Post subject:

Bithead,
try this

Bithead · Posted: Wed Apr 02, 2003 1:08 pm Post subject:

Kolusu,

I am not getting to the PREPARE statement. It is failing on the DECLARE.

kolusu · Posted: Wed Apr 02, 2003 1:20 pm Post subject:

Bithead,

Now I am confused. Usually the prepare statement precedes before the open or declare stmt. Are you saying that you have the declare stmt before the prepare stmt??

usally this is how I code the program

1.Code declaration of cursor in the working-storage section.
2.Build the SQL
3.PREPARE
4.OPEN CURSOR

In your case you have the declare in the procedure division which is ok, but the prepare statement should execute prior to the declare

Hope this helps...

Kolusu

Bithead · Posted: Wed Apr 02, 2003 1:47 pm Post subject:

We have it running succesfully this way round in other applications so I guess that it doesn't matter. It is also working in this program under certain circumstances. I am stumped!!

Glenn · Beginner Joined: 23 Mar 2003 Posts: 56 Topics: 3

FWIW, in my experience, it doesn't work without providing the statement in a VARCHAR format...speaking of that, are you setting SQL-LENGTH in all cases?

Bithead · Posted: Thu Apr 03, 2003 9:38 am Post subject:

I am setting the length. The DISPLAY statement uses the length and is enclosed in '!' to determine if it set correctly. The output shows that it is.