Getting SQLCODE -518, while running EXECUTE Statement after PREPARE Statement in COBOL

30 pts.
Tags:
Dynamic SQL
I am executing a dynamic SELECT query (No CURSOR- Normal SELECT)

WS-STRING - SELECT MEMBNO FROM ENBMAS WHERE MEMBNO = "3535"

 EXEC SQL                                     PREPARE SQLSELX FROM :WS-STRING  END-EXEC                              

SQLCODE = 0

EXEC SQL                  EXECUTE SQLSELX END-EXEC            

SQLCODE = -518 (THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT)

Please Help me out.. if I have done anything wrong



Software/Hardware used:
Iseries - V6R1M0
ASKED: May 26, 2012  1:38 PM
UPDATED: June 3, 2012  6:56 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    ...if I have done anything wrong It's hard to tell because there isn't enough actual program source to tell what you're trying to do. From the small bits that you show, it looks like you're just trying to prepare and execute a straight SELECT statement, and that is not a valid thing to do. A straight SELECT statement doesn't do anything. It just selects. What are you trying to do? Tom
    125,585 pointsBadges:
    report
  • COBOL400Praveen
    I Just want to check whether a record exist in the file or not for the condition
    30 pointsBadges:
    report
  • TomLiotta
    Here's a sample COBOL program that has two SELECT INTO statements. A SELECT INTO can be used in some cases, but it must select only a single row. Of course, if it errors out because more than one row is selected, you'd still know that a row existed.
           Identification Division.
           Program-ID.     TST1     .
    
           Environment Division.
    
           Configuration Section.
           Source-computer.    IBM-AS400.
           Object-computer.    IBM-AS400.
    
           Data Division.
    
           Working-storage Section.
    
               EXEC SQL
                  INCLUDE SQLCA
               END-EXEC.
    
               77 apFound              pic s9(18) binary.
    
           01  HOST-VARS.
               05 iCnt                 pic s9(18) binary.
               05 iTot                 pic s9(5) comp.
               05 minFile              pic x(10).
               05 maxFile              pic x(10).
    
           Procedure Division.
    
           00-Main-driver.
    
               EXEC SQL
                    SELECT COUNT(*), SUM(APNKYF), MIN(APFILE), MAX(APFILE)
                        INTO :HOST-VARS
                        FROM ACCPTH
               END-EXEC.
    
               EXEC SQL
                    SELECT COUNT(*)
                        INTO :apFound
                        FROM ACCPTH
                        WHERE APFILE = :minFile
               END-EXEC.
    
               goback.
    
           00-Main-driver-exit.  exit.
    The first SELECT INTO pulls summary values into a data structure. It's guaranteed to return a single row. The second SELECT INTO is also coded to return at most a single row. It returns a COUNT() of how many rows match the APFILE value returned from the first SELECT INTO. In your case, you could test for a match against your desired MEMBNO value. Note that any program variable name in the SQL must begin with a colon -- ":". The ACCPTH file is one that I created with DSPFD TYPE(*ACCPTH) to an outfile for testing. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    BTW, the only reason I coded two SELECT INTO statements was to have one that used a data structure and one that used an elementary variable. You would only need a single SELECT INTO. You might also use a VALUES INTO statement. But SQL isn't particularly appropriate if all you're doing is testing if a row exists. SQL is intended to be used to process sets, or groups, of related rows with single statements. A COBOL START statement might be better. Tom
    125,585 pointsBadges:
    report
  • COBOL400Praveen
    I'm using a dynamic SQL. For Example: I have two variables(LNK-VAR1, LNK-VAR2) as input to the program. SELECT EMPNAME, EMPDOB INTO :WS-FOUND FROM FILENAME WHERE EMPNAME = LNK-VAR1 AND EMPDOB = CASE WHEN LNK-VAR2 <> 0 THEN LNK-VAR2 ELSE EMPDOB END This is the SQL query I have. I want to convert it to a dynamic SQL query. if LNK-VAR2 is 0 then it should not be part of where condition else it should be part of where condition can we write an Dynamic SQL query without using dynamic cursor
    30 pointsBadges:
    report
  • TomLiotta
    can we write an Dynamic SQL query without using dynamic cursor First, a "dynamic" SQL statement will be processed first by a PREPARE statement and then an EXECUTE statement. A "dynamic" SQL statement will not contain references to program variables. Any portion of the statement that you want to modify dynamically will be noted by a parameter marker. After the statement is PREPAREd, you run it with an EXECUTE statement. However, the dynamic SQL statement cannot be a SELECT statement if you are going to use EXECUTE. You must declare a cursor and FETCH rows. You haven't been showing dynamic SQL. You've been showing static SQL.
    AND EMPDOB = CASE WHEN LNK-VAR2 <> 0
    THEN LNK-VAR2
    ELSE EMPDOB
    END
    You don't need CASE for that. Just use a simple test:
    AND (:LNK-VAR2 = 0 OR
          EMPDOB = :LNK-VAR2)
    
    And don't forget that host variables must be written with a colon (":") to mark tham. That must be done for LNK-VAR1, too. Also, if there is more than one row returned, the statement will fail. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following