Stored Procedure Output parameter issue using esql/c


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Stored Procedure Output parameter issue using esql/c

Hybrid View

  1. #1
    Join Date
    Nov 2005
    Posts
    4

    Question Stored Procedure Output parameter issue using esql/c

    Hi,

    I am trying to use some esql/c to run a stored procedure which should return 2 output parameters. I have tested the stored proc in Query analyser and it works fine however when I run the esql/c code I get a -179 error which apparantly indicates that I am trying to send constants to the stored proc when using the Output param which is illegal! As I have not declared any of the varaibles as constants I have no idea why I am getting this error, can anyone help?

    The stored proc is defined as:

    CREATE PROCEDURE p_stl_esqltest
    @input_int INT ,
    @input_char VARCHAR(255) OUTPUT ,
    @rc INT OUTPUT,
    @error_desc VARCHAR(255) OUTPUT
    AS
    BEGIN

    set @rc = @input_int * 10
    set @error_desc = 'Heres your input char:' + @input_char
    insert into temp (dd,inint,outint,instr,outstr) values (getdate(),@input_int,@rc,@input_char,@error_desc)
    END
    GO


    If I run it through query analyser it works fine:

    /* sql run thro query analyser - and works
    declare @input_int INT
    declare @input_char VARCHAR(255)
    declare @rc INT
    declare @error_desc VARCHAR(255)

    select @input_int=10
    select @input_char='MyInput'

    exec p_stl_esqltest @input_int, @input_char, @rc output, @error_desc output
    print @input_int
    print @input_char
    print @rc
    print @error_desc


    However as soon as I uses embedded sql to run the stored procedure it doesn't run:

    int dotemp()
    {
    exec sql begin declare section;
    int iin=10;
    int iout=0;
    int iout2=0;
    char szIn[256]="MyInString:";
    char szOut[256];
    int iret = 0;
    exec sql end declare section;

    EXEC SQL exec p_stl_esqltest :iin, :szIn, :iout output, :szOut output;

    iret = sqlca.sqlcode;
    DBSWITCH_COMMIT
    return iret;
    }


    Have tried all these in the place of the EXEC SQL in the esql/c but to no avail:

    /* do not work */
    EXEC SQL EXEC p_stl_esqltest :iin, :szIn, :iout, :szOut into iout2;
    EXEC SQL select exec p_stl_esqltest :iin, :szIn, :iout, :szOut into iout2;
    EXEC SQL exec p_stl_esqltest :iin, :szIn, output :iout,output :szOut;
    EXEC SQL EXEC p_stl_esqltest :iin, :szIn, :iout output, :szOut output;


    /* works but out values not returned */
    EXEC SQL exec p_stl_esqltest :iin, :szIn, :iout, :szOut;

  2. #2
    Join Date
    Apr 2008
    Posts
    1

    Stored Procedure Output parameter issue using esql/c

    Hi Darkrider,

    I'm struggling on the same problem right now.
    After reading your thread I start to fear that it's not too easy to get it done.

    Did you find a solution for it how to call a stored proc from esqc/c
    with an output parameter, and put it into a variabele??

    I tried already a few different syntaxes and I allways get the sqlcode -102
    wich means : operation attempted on an unopened cursor.

    This is my current try:

    EXEC SQL BEGIN DECLARE SECTION;
    short dummy_ind;
    long sql_id_type;
    long sql_id_max;
    long sql_max_id_retcode;
    EXEC SQL END DECLARE SECTION;

    sql_id_type = IDType;

    EXEC SQL exec :sql_max_id_retcode = up_maxidentif
    :sql_id_type, '', :sql_id_max output;

    lSqlcode = SQLCODE;


    The third variable in the stored proc is an output parameter.


    thanks in advance for any help !!!

    kind regards,

    Koen

Similar Threads

  1. Replies: 2
    Last Post: 11-22-2006, 06:18 PM
  2. Replies: 4
    Last Post: 12-21-2002, 01:32 AM
  3. Replies: 0
    Last Post: 01-04-2001, 04:49 PM
  4. Stored Procedure Parameter
    By John Hadzima in forum Database
    Replies: 2
    Last Post: 06-29-2000, 11:42 AM
  5. date parameter in Oracle Stored Procedure
    By Naveen in forum VB Classic
    Replies: 1
    Last Post: 06-13-2000, 07:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center