Esql/C calling stored procedure with output parameters

• Allan Krause

  Old Hand

  Points: 388

  More actions

  December 6, 2004 at 9:51 pm

  #87210

  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];

  EXEC SQL END DECLARE SECTION;

  sprintf(&p1[0], "GL");

  p2 = 0;

  sprintf(&p3[0], "");

  sprintf(&p4[0], "");

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

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

  parameters.

  Also, if I specify the parameter names (see below)...

  EXEC SQL EXECUTE aek_proc1 @p1 = :p1, @p2 = :p2 OUTPUT, @p3 = :p3 OUTPUT,

  @p4 = :p4 OUTPUT;

  I get the error...

      0170-  Line 1: Incorrect syntax near '='.

  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!!

  AllanK

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738063

  More actions

  December 7, 2004 at 10:15 am

  #532872

  Not sure what esqlc is, but can you do this:

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

• Allan Krause

  Old Hand

  Points: 388

  More actions

  December 7, 2004 at 8:34 pm

  #532966

  Steve,

  esql/c = Embedded SQL in C

  Tried that and does not work.

  The SQLServer C preprocessor (nsqlprep) turns the command...

  EXEC SQL EXEC aek_proc1 :par1, :par2, :par3, :par4 OUTPUT, :par5 OUTPUT;

  into the series of calls...

  #line 3473

  /*

  EXEC SQL EXEC aek_proc1 :par1, :par2, :par3, :par4 OUTPUT, :par5 OUTPUT;

  */

  #line 3473

  #line 3473

  {

  #line 3473

   sqlastrt((void far *)pid, (void far *)0, (struct tag_sqlca far *)sqlca);

  #line 3473

   sqlaaloc(2, 5, 13, (void far *)0);

  #line 3473

   sqlasetv(2, 0, 462, (short) SQLLENMAX(sizeof(par1)), (void far *)par1, (void far *)0, (void far *)0L);

  #line 3473

   sqlasetv(2, 1, 462, (short) SQLLENMAX(sizeof(par2)), (void far *)par2, (void far *)0, (void far *)0L);

  #line 3473

   sqlasetv(2, 2, 462, (short) SQLLENMAX(sizeof(par3)), (void far *)par3, (void far *)0, (void far *)0L);

  #line 3473

   sqlasetv(2, 3, 462, (short) SQLLENMAX(sizeof(par4)), (void far *)par4, (void far *)0, (void far *)0L);

  #line 3473

   sqlasetv(2, 4, 462, (short) SQLLENMAX(sizeof(par5)), (void far *)par5, (void far *)0, (void far *)0L);

  #line 3473

   sqlxcall(24, 13, 2, 0, 70, (char far *)"       aek_proc1 @p1   , @p2   , @p3   , @p4    OUTPUT, @p5    OUTPUT ");

  #line 3473

   SQLCODE = sqlca->sqlcode;

  #line 3473

   sqlastop((void far *)0L);

  #line 3473

  }

  #line 3474

  In profiler I can see the command hitting the dataabse is ...

      aek_proc1 'GL'   , 'xxd997 param2'   , 'xxd997 param3'   , ''    OUTPUT, ''    OUTPUT

  Looks like the call to the database had replaced the parameter names/placeholders with their literal values - in the case of the two output parameters, empty strings hence the '' which I assume the database is complaining about being constants.

  Any other ideas, anyone??

  Getting desperate,

  AllanK

   

   

   

   

• alejandro espinoza-343958

  SSC Journeyman

  Points: 87

  More actions

  June 30, 2006 at 5:25 pm

  #647083

  For those that have still have this problem, try this:

  void paem_get_val(int p_empr,char *p_tipo,char *p_paem)

  {

  EXEC SQL BEGIN DECLARE SECTION;

  char query[2500];

  /* variables de salida */

  char valor[50];

  char existe[2];

  char mensaje[250];

  EXEC SQL END DECLARE SECTION;

  sprintf(query,"BEGIN\n");

  sprintf(query,"%s DECLARE @PAEM_VALO_PAEM varchar(30) \n",query);

  sprintf(query,"%s DECLARE @EXISTE varchar(1) \n",query);

  sprintf(query,"%s DECLARE @MENSAJE varchar(250)\n",query);

  sprintf(query,"%s EXEC PAEM_GET_VAL %d, '%s', '%s', @PAEM_VALO_PAEM OUTPUT , @EXISTE OUTPUT , @MENSAJE OUTPUT\n",query,p_empr,p_tipo,p_paem);

  sprintf(query,"%s SELECT @EXISTE, ISNULL(@MENSAJE,'-'), @PAEM_VALO_PAEM\n",query);

  sprintf(query,"%sEND\n",query);

  EXEC SQL DECLARE cur_paem_get_val CURSOR FOR STMT;

  EXEC SQL PREPARE STMT from :query;

  EXEC SQL SET CURSORTYPE CUR_BROWSE;

  EXEC SQL open cur_paem_get_val;

  if (SQLCODE == 0)

  {

  EXEC SQL fetch cur_paem_get_val into :existe,:mensaje,:valor;

  if (SQLCODE == 0)

  {

  printf("[%s][%s][%s]\n",valor,existe,mensaje);

  }

  }

  }