sql code = 501 Cursor not open

[violinsoundcool]

Hey guys. I'm using some embedded sql in an sqlrpgle CGIDEV2 program. I've used it exactly like this before, but for some reason this time, it's giving me sqlcod = 501. Can anyone tell what's going on? Thanks.

     D quote           C                   CONST('"')
     D snglQuote       C                   CONST('''')
     D term            S                   LIKE(CINAME)
     D city            S                   LIKE(CINAME)
     D state           S                   LIKE(CIST)
     D limit           S              5  0
     D counter         S              5  0 INZ(0)
     D UP              C                   'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
     D low             C                   'abcdefghijklmnopqrstuvwxyz'

     D SqlString       S            450    Varying Inz
     D JSONstring      S          32767    Varying
     D SavedQryStr     S          32767    Varying

       //=============================================================
      /Free

       EXEC SQL
         SET OPTION CLOSQLCSR = *ENDMOD,
           DLYPRP = *YES,
           SRTSEQ = *LANGIDSHR;

       //Initialize JSON string
       JSONstring = 'Content-type: text/html'+x'25'+x'25'+'[';

       //Retrieve the value of City search string from the JSON string
       If ZhbGetInput(SavedQryStr: QUSEC) > 0;
         term = %xlate(low:UP : ZhbGetVar('term'));
         limit = 10;
       EndIf;

       SqlString = 'SELECT CINAME, CIST FROM CITIESL8 WHERE CINAME LIKE UCASE('+
          snglQuote+%TRIM(term)+'%'+snglQuote+')';

       EXEC SQL
       PREPARE SQL FROM :SqlString;

       EXEC SQL
       DECLARE @C1 CURSOR FOR SQL;

       EXEC SQL            //when debugging, sql code = 204
       OPEN @C1;

       EXEC SQL            //when debugging, sql code = 501
       FETCH NEXT FROM @C1 INTO
         :city, :state;

       If SQLCOD = 100;
         JSONstring += quote+'No Results...'+quote;
       Else;
         DoW sqlcod = 0;
           If counter > limit;
             Leave;
           ENDIF;
           If counter > 0;
             JSONstring += ',';
           ENDIF;
           JSONstring += '{'+quote+'label'+quote+':'+quote+city+'\, '+state+',';
           JSONstring += quote+'value'+quote+':'+quote+city+quote+'}';
           counter += 1;

           EXEC SQL
           Fetch NEXT From @C1 Into
             :city, :state;
         ENDDO;
       ENDIF;

       EXEC SQL
       CLOSE @C1;

       JSONstring += ']';
       wrtnosection(%addr(JSONstring)+2:%len(JSONstring));

       wrtsection('*fini');
       *InLR = *On;
      /End-Free

[MichaelCatalani]

Re: sql code = 501 Cursor not open

your error code 204 typically means that your file was not found. (ie it's not in your library list)

[pramendra]

Re: sql code = 501 Cursor not open

to see what actually cause error put this statement just before and Open the Cursor...

to see the error message u can include this sql statement:

EXEC SQL Get Diagnostics Condition 1 :msgt = MESSAGE_TEXT

where msgt is host variable.


for detail click the link..

http://www.code400.com/forum/showthr...ght=#post49897

[violinsoundcool]

Re: sql code = 501 Cursor not open

So apparently I needed to specify the library name along with the file name in the SQL statement. After I did that, everything worked. Thanks for your help, guys.

[MichaelCatalani]

Re: sql code = 501 Cursor not open

So apparently I needed to specify the library name along with the file name in the SQL statement. After I did that, everything worked. Thanks for your help, guys.

It's better to add the library of the file to the library list. This way the program can go thru the change management process without a production library being hard coded in the program.

[B.Hauser]

Re: sql code = 501 Cursor not open

It's better to add the library of the file to the library list. This way the program can go thru the change management process without a production library being hard coded in the program.

... and make sure you are using system naming (default in embedded SQL). With SQL naming only objects within a single library/schema can be accessed unqualified. All other objects must either be specified qualified or an alias must be created in the library for each object that resides outside the library.

Birgitta