Re: calling stored procedures

Dennis, In order to test my stored procedures from the command line, I wrote an ESQL/C program. I=B4m sending the code. Basically, the program uses a= DESCRIBE statement to know the quantity and data type of the returned values. When a stored procedure returns no values, you can run it with an EXECUTE statement, but if the stored procedure returns values, you have to open, fetch and close a cursor. One problem: the program only support stored procedures returning only INT and CHAR data types, but you can extent it to support others Informix data types. all the code is unsupported. You can compile it with: esql exesp.ec -o exesp In order to execute an stored procedure try: exesp database "stored_procedure(par1,,parN)" Buena Suerte - #include <disclaimer.h> Ignacio ------------------------------------------------------- #include <stdio.h> EXEC SQL include sqlca; EXEC SQL include sqltypes; void check_error(); main(argc, argv) int argc; char *argv[]; { EXEC SQL BEGIN DECLARE SECTION; char command[100]; int cuantos, nro_col; short tipo_col ; long int int_data; string char_data[500]; EXEC SQL END DECLARE SECTION; int fetch_err_code; if (argc !=3D 3){ printf("%s: Use %s database \\"procedure(par1,...,parn)\\"\\n",argv[0]),argv[0]; exit(-1); } sprintf(command, "DATABASE %s", argv[1]); EXEC SQL PREPARE cd FROM :command; check_error("preparing connect", sqlca.sqlcode); EXEC SQL EXECUTE cd; check_error("executing connect"); EXEC SQL ALLOCATE DESCRIPTOR 'proce' ; check_error("allocating descriptor"); sprintf(command, "EXECUTE PROCEDURE %s",argv[2]); EXEC SQL PREPARE exproc FROM :command; check_error("preparing execute procedure"); EXEC SQL DESCRIBE exproc USING SQL DESCRIPTOR 'proce'; /* check_error("describing execute procedure"); */ /* we obtain how many values will return the procedure */ EXEC SQL GET DESCRIPTOR 'proce' :cuantos =3D COUNT; check_error("get descriptor count"); if ( cuantos =3D=3D 0 ){ /* Procedure returns no values, an Execute inmediate is enough=20 */ EXEC SQL EXECUTE exproc; check_error("execute inmediate"); } else { /* Procedure return many values, we have to use a cursor */ EXEC SQL DECLARE cur1 CURSOR FOR exproc; check_error("executing execute procedure"); EXEC SQL OPEN cur1; check_error("open cursor"); while (1){ EXEC SQL FETCH cur1 USING SQL DESCRIPTOR 'proce'; if ( SQLCODE =3D=3D SQLNOTFOUND ) break; check_error("fetch"); for ( nro_col =3D 1 ; nro_col <=3D cuantos ; ++nro_col){ EXEC SQL GET DESCRIPTOR 'proce'=09 VALUE :nro_col :tipo_col =3D TYPE; check_error("get descriptor values"); switch ( tipo_col ){ case SQLINT: case SQLSERIAL: EXEC SQL GET DESCRIPTOR 'proce' VALUE :nro_col :int_data =3D DATA; check_error("get descriptor integer"); printf("<%d>",int_data); break; case SQLCHAR: case SQLVCHAR: EXEC SQL GET DESCRIPTOR 'proce' VALUE :nro_col :char_data =3D DATA; check_error("get descriptor integer"); printf("<%s>",char_data); break; default: fprintf(stderr, "The stored procedure is returning a data type not supported\\n"); exit(-1); } } printf("\\n"); } EXEC SQL CLOSE cur1; check_error("close cursor"); /* free all the resources */ EXEC SQL FREE exproc; check_error("free prepare"); EXEC SQL FREE cur1; check_error("free cursor"); EXEC SQL DEALLOCATE DESCRIPTOR 'proce'; check_error("deallocate"); } exit(0); } void check_error(msg) char *msg; { char buf[200]; if (SQLCODE !=3D 0) {=20 rgetmsg( (short) SQLCODE, buf, sizeof ( buf ) ); fprintf(stderr, "%s Error SQL:%d\\n%s\\n",msg, SQLCODE, buf); exit(-1); } return; } ------------------------------------------------------- At 07:55 AM 11/12/96 -0600, you wrote: >I need to write a program which will call a stored procedure >without compiling the name of the stored procedure into the >program. The program will get the name of the stored procedure >at run time. Can this be done with Informix? There are ways >to do this with Oracle (OCI API) and Sybase (rpc API) that use >proprietary API's rather than embedded SQL. > >Thanks, >Dennis McCarthy > > ------------------------------------------------------------------------ Ignacio Bisso Telefono: (541) 310-8888 Informix Software Argentina Fax: (541) 310-8800 Bouchard 547 piso 29 E-mail bisso@informix.com Buenos Aires - Argentina