Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    6

    Lightbulb Unanswered: Esql/C calling stored procedure with output parameters

    I'm trying to write an esqlc program that will run a stored procedure that returns several output parameters. I haven't been able to find any documentation to date that explains how to run the "EXEC SQL EXECUTE procname" command and specify the output parameters.

    My stored procedure "aek_proc1" takes one input parameter (p1 - an 8-character string) and 3 output parameters (p2 - an integer; p3 - an 8-character string, and p4 a 40-character string).

    My esqlc program contains the following code….

    EXEC SQL BEGIN DECLARE SECTION;
    char p1[9];
    int p2;
    char p3[9];
    char p4[41];
    WXEC SQL END DECLARE SECTION;

    sprintf(&p1[0], "GL");
    p2 = 0;
    sprintf(&p3[0], "");
    sprintf(&p4[0], "");

    EXEC SQL EXECUTE aek_proc1 1,
    2 OUTPUT,
    3 OUTPUT,
    4 OUTPUT;

    I am getting errors at runtime about constants being passed for OUTPUT parameters.

    I can run the same stored procedure in Query Analyser and it works beautifully (see below)

    declare @p1 char(8)
    declare @p2 integer
    declare @p3 char(8)
    declare @p4 char(40)

    set @p1 = 'GL'

    execute aek_proc1 @p1, @p2 output, @p3 output, @p4 output

    select @p1 p1, @p2 p2, @p3 p3, @p4 p4

    Any idea what I'm doing wrong or how it should be coded?

    I'd really appreciate any advice you can offer!!

    I've spend hours browsing this newsgroup and found lots of examples of how to do this in VB and from Query Analyser but I can't find any examples for ESQL/C that work.

    So, please help!!!

    Thanks,

    AllanK

  2. #2
    Join Date
    Dec 2004
    Posts
    6
    Oops.

    Don't know why the host variable names got turned into smileys, but that bit should have read....


    EXEC SQL EXECUTE aek_proc1 :p1,
    :p2 OUTPUT,
    :p3 OUTPUT,
    :p4 OUTPUT;

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Have you tried to replace : with @?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Dec 2004
    Posts
    6
    I tried changing the EXEC SQL EXEC command in the ESQL/C program to read....

    EXEC SQL EXEC aek_proc1 @p1, @p2 OUTPUT, @p3 OUTPUT, @p4

    after which I got the runtime error...

    "0137- Must declare the variable '@p1'."

    I tried adding....

    EXEC SQL DECLARE @p1 char(8);
    EXEC SQL DECLARE @p2 int;
    EXEC SQL DECLARE @p3 char(8);
    EXEC SQL DECLARE @p4 char(40);

    EXEC SQL SET @p1 = 'GL';
    EXEC SQL SET @p2 = 0;
    EXEC SQL SET @p3 = '';
    EXEC SQL SET @p4 = '';

    EXEC SQL EXEC aek_proc1 @p1, @p2 OUTPUT, @p3 OUTPUT, @p4 OUTPUT;

    but the error persists.

    Any further suggestions?

    Thanks,

    AllanK

Posting Permissions

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