Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    8

    Unhappy Unanswered: Using Dynamic SQL Method 2(C Embedded SQL) for calling stored procedure

    I am writing a C++ application which supports dynamic sql. I use different method for SQL, PL/SQL block.
    For UPDATE/INSERT/DELETE and Stored Procedure i use method 1 "execute immediate" while I use method 4 for SELECT. It works well.
    However, I am not able to get output parameters using method 1 for Stored Procedure. I think I should use method 2 as i can use descriptor but when I prepare the sql statement, it complains the sql statement has syntax error. (Incorrect syntax near 'mytest')
    Below is the fragment of my program:

    strcpy(sqlStatement, "mytest 'Watch'")
    EXEC SQL SET CHAINED OFF;
    EXEC SQL BEGIN TRANSACTION;
    EXEC SQL PREPARE STATE_SP FROM :sqlStatement; <--error occurs here
    EXEC SQL ALLOCATE DESCRIPTOR SPDES with max QLCount;
    EXEC SQL DESCRIBE OUTPUT STATE_SEL USING SQL DESCRIPTOR SPDES QLCount = count;
    EXEC SQL EXEC STATE_SP USING SQL DESCRIPTOR SPDES;
    ..
    ..
    EXEC SQL AT COMMIT TRANSACTION;
    EXEC SQL SET CHAINED ON;

    I don't understand why it complains syntax error because i can run the stored procedure in SQL Advantage and in my program using Method 1.
    Can anyone here kindly advise me what's wrong with my program? All comments are welcome!!!!!!

  2. #2
    Join Date
    May 2004
    Posts
    8
    I have changed the sql statement to "begin exec mytest 'Martin' end" and it works well. However when i call a stored procedure which has output parameters, i am not able to get an expected SQLCount after executing the statement:

    strcpy(sqlStatement, "declare @passwd varchar(8) begin exec getPassword 'EurHSales', @passwd end")
    .
    .

    EXEC SQL DESCRIBE OUTPUT STATE_SEL USING SQL DESCRIPTOR SPDES QLCount = count;

    SQLCount is always 0, I expect 1. may anyone tell me what's wrong with my program?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •