ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

sql code = 501 Cursor not open

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • sql code = 501 Cursor not open

    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.

    Code:
         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

  • #2
    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)
    Last edited by MichaelCatalani; March 8, 2010, 04:39 PM.
    Michael Catalani
    IS Director, eCommerce & Web Development
    Acceptance Insurance Corporation
    www.AcceptanceInsurance.com
    www.ProvatoSys.com

    Comment


    • #3
      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
      Young people knows how to run fast but old people knows the way..

      Comment


      • #4
        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.

        Comment


        • #5
          Re: sql code = 501 Cursor not open

          Originally posted by violinsoundcool View Post
          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.
          Michael Catalani
          IS Director, eCommerce & Web Development
          Acceptance Insurance Corporation
          www.AcceptanceInsurance.com
          www.ProvatoSys.com

          Comment


          • #6
            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

            Comment

            Working...
            X