December 6, 2004 at 9:51 pm
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
December 7, 2004 at 10:15 am
Not sure what esqlc is, but can you do this:
EXEC SQL EXECUTE aek_proc1 :p1, :p2 OUTPUT, :p3 OUTPUT, :p4 OUTPUT;
December 7, 2004 at 8:34 pm
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
June 30, 2006 at 5:25 pm
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);
}
}
}
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply