Hi,

I am having trouble writing some C code to call a stored procedure in
Firebird. I used the API guide as a model, but I keep
getting "invalid request handle." Any help on this is appreciated - I
am stumped.

The stored procedure is taken from the JDBC junit tests, so that
should be OK. A database containing this stored procedure can be
created using isql -i factorial.sql, included below.

FILE:FACTORIAL.SQL
create database 'sptest.fdb';

set term ^;
create procedure factorial (
max_rows integer,
mode integer )
returns (row_num integer, factorial integer ) as
declare variable temp integer;
declare variable counter integer;
begin
counter=0;
temp=1;
while (counter <= max_rows) do begin
row_num = counter;
if (row_num=0) then
temp=1;
else
temp=temp * row_num;
factorial=temp;
counter = counter + 1;
if (mode=1) then suspend;
end
if (mode=2) then suspend;
end
^

set term ; ^

select * from factorial (5,1);



The code in question follows. I added a comment /* ERROR invalid
request handle */ where the error message occurs. If the code doesn't
come across well in the posting, I can email it upon request.

Thanks!!!

-bill oliver



#ifdef WIN32
#include <windows.h>
#endif

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#include "ibase.h"

#define USERID "sysdba"
#define PASSWORD "masterkey"
#define CHARSET "NONE"

// -----------------------------
// Wrapper functions for FB calls
void fberror(
ISC_STATUS_ARRAY *status,
const char *msg)
{
printf ("Error message is %s: \n", msg);
isc_print_status ( status) ;
exit(1);
} // error


int fbconnect(
ISC_STATUS_ARRAY *status,
const char *database,
isc_db_handle *pdb)
{
char dpb_buffer[256];
char *dpb = dpb_buffer;
int rc;

// build dbp to connect
*dpb++ = isc_dpb_version1;

*dpb++ = isc_dpb_user_name;
*dpb++ = (char)strlen(USERID);
memcpy(dpb, USERID, strlen(USERID));
dpb += strlen(USERID);

*dpb++ = isc_dpb_password;
*dpb++ = (char)strlen(PASSWORD);
memcpy(dpb, PASSWORD, strlen(PASSWORD));
dpb += strlen(PASSWORD);

*dpb++ = isc_dpb_lc_ctype;
*dpb++ = (char)strlen(CHARSET);
memcpy(dpb, CHARSET, strlen(CHARSET));
dpb += strlen(CHARSET);

rc = isc_attach_database(status,
(short)strlen(database),
(char*)database,
pdb,
(short)(dpb-dpb_buffer),
dpb_buffer);


return rc;
} // connect


int fbdisconnect(
ISC_STATUS_ARRAY *status,
isc_db_handle *pdb)
{
int rc;

rc = isc_detach_database(status, pdb);

return rc;
} // disconnect


int fbbeginTran(
ISC_STATUS_ARRAY *status,
isc_db_handle *pdb,
isc_tr_handle *ptrans)
{
char tpb[8];
size_t tpbL = 0;
int rc;

tpb[tpbL++] = isc_tpb_version3;
tpb[tpbL++] = isc_tpb_write;
tpb[tpbL++] = isc_tpb_read_committed;
tpb[tpbL++] = isc_tpb_rec_version;
tpb[tpbL++] = isc_tpb_wait;

rc = isc_start_transaction(status,
ptrans,
1, pdb, tpbL, tpb);

return rc;
} // beginTran


int fballocStmt(
ISC_STATUS_ARRAY *status,
isc_db_handle *pdb,
isc_stmt_handle *pstmt)
{
int rc;

rc = isc_dsql_allocate_statement(
status, pdb, pstmt);

return rc;
} // allocStmt


int fbfreeStmt(
ISC_STATUS_ARRAY *status,
isc_stmt_handle *pstmt,
unsigned short option)
{
int rc;

rc = isc_dsql_free_statement(
status, pstmt, option);

return rc;
} // freeStmt


int fbprepexec(
ISC_STATUS_ARRAY *status,
isc_tr_handle *ptrans,
isc_stmt_handle *pstmt,
const char *sql)
{
int rc;

rc = isc_dsql_prepare(status, ptrans, pstmt,
(unsigned short)strlen(sql),
(char*)sql,
SQL_DIALECT_V6, NULL);

if (!rc)
rc = isc_dsql_execute(status, ptrans, pstmt,
SQL_DIALECT_V6,
NULL);

return rc;
} // prepexec


int fbexeci(
ISC_STATUS_ARRAY *status,
isc_db_handle *pdb,
isc_tr_handle *ptrans,
const char *sql)
{
int rc;

rc = isc_dsql_execute_immediate(status,
pdb,
ptrans,
(unsigned short)strlen(sql),
(char*)sql,
SQL_DIALECT_V6,
NULL);

return rc;
} // execi


int fbcommit(
ISC_STATUS_ARRAY *status,
isc_tr_handle *ptrans)
{
int rc;

rc = isc_commit_transaction(status, ptrans);

return rc;
} // commit



// Free memory associated with an sqlda struct
void fbfreeSqlda(
ISC_STATUS_ARRAY *status,
XSQLDA **psqlda)
{
int i;
XSQLVAR *var;

if (!psqlda || !*psqlda)
return;

// Free the sqlvar memory
for (var = (*psqlda)->sqlvar, i = 0;
i < (*psqlda)->sqln;
var++, i++)
{
if (var->sqldata)
free(var->sqldata);
if (var->sqlind)
free(var->sqlind);
}

free(*psqlda);
*psqlda = NULL;

} // freeSqlda


// Allocate an sqlda struct for the associated prepared statement
int fballocSqlda(
ISC_STATUS_ARRAY *status,
isc_stmt_handle *pstmt,
XSQLDA **psqlda)
{
if (!psqlda)
fberror(status, "invalid psqlda specified");

// Clean up existing SQLDA
fbfreeSqlda(status, psqlda);

// Allocate a new sqlda
*psqlda = malloc(XSQLDA_LENGTH(1));
if (!*psqlda)
fberror(status, "malloc failed");

memset(*psqlda, 0x00, XSQLDA_LENGTH(1));
(*psqlda)->version = SQLDA_VERSION1;
(*psqlda)->sqln = 1;

if (isc_dsql_describe(status, pstmt, SQLDA_VERSION1, *psqlda))
fberror(status, "isc_dsql_describe failed");

// Alloc this sqlda larger if there are more cols than
allocated ...
if ((*psqlda)->sqld > (*psqlda)->sqln)
{
int cnt = (*psqlda)->sqld;

*psqlda = realloc(*psqlda, XSQLDA_LENGTH(cnt));
if (!*psqlda)
fberror(status, "malloc failed");

memset(*psqlda, 0x00, XSQLDA_LENGTH(cnt));
(*psqlda)->version = SQLDA_VERSION1;
(*psqlda)->sqln = cnt;

// Now, fill it in completely
if (isc_dsql_describe(status, pstmt, SQLDA_VERSION1, *psqlda))
fberror(status, "isc_dsql_describe failed");

// Size must be the same as previously described
if ((*psqlda)->sqld != cnt)
fberror(status, "isc_dsql_describe returned 2 different
column counts");
}

// Allocate buffers for data and indicators ...
if ((*psqlda)->sqld)
{
int i;
XSQLVAR *var;

// Alloc the sqlvar memory
for (var = (*psqlda)->sqlvar, i = 0;
i < (*psqlda)->sqln;
var++, i++)
{
// Reserve space for VARYING len bytes
var->sqldata = (char*)malloc(var->sqllen + 2);
var->sqlind = (short*)malloc(sizeof(*(var->sqlind)));
if (!var->sqldata || !var->sqlind)
fberror(status, "malloc failed");
}

} // if result set

// we made it here so it must be OK ...
return 0;

} // allocSqlda


int fbfetch(
ISC_STATUS_ARRAY *status,
isc_stmt_handle *pstmt,
XSQLDA *sqlda)
{
int rc;

rc = isc_dsql_fetch(status, pstmt,
SQLDA_VERSION1, sqlda);

return rc;
} // fetch


int main(int argc, char* argv[])
{
int rc=0;
ISC_STATUS_ARRAY status;
char dpb_buffer[256];
char *dpb = dpb_buffer;
isc_db_handle db1 = {0};
isc_tr_handle tr1 = {0};
isc_stmt_handle stmt1 = {0};
char sql[256];
int row;
XSQLDA *isqlda, *osqlda;
XSQLVAR *ivar1, *ivar2;
XSQLVAR *ovar1, *ovar2;
short null_flag;
int dtype;
// storec procedure input parameters:
long max_rows=5;
long mode=0;
// stored proecure output parameters
int row_num=255;
int factorial=255;

if (argc <= 1)
{
printf("Usage:\t%s <db.fdb>\n\n", argv[0]);
printf("---\n");
printf("\n");
printf ("Example usage:\n");
printf ("\t%s %s.fdb\n", argv[0], argv[0]);
exit(1);
}

if (fbconnect(&status, argv[1], &db1))
fberror(status, "connect");
if (fballocStmt(&status, &db1, &stmt1))
fberror(status, "alloc statement");
if (fbbeginTran(&status, &db1, &tr1))
fberror(status, "beginTran");

sprintf (sql, "execute procedure factorial (?, ?);");

// have to allocate the output XSQLDA first
osqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(2));
osqlda->version = SQLDA_VERSION1;
osqlda->sqln = 2;
osqlda->sqld = 1;

// prepare the statement, fill in osql with information about
// the item to be returned by the statement (procedure).
isc_dsql_prepare(status, &tr1, &stmt1, 0, sql,
SQL_DIALECT_V6, osqlda);
if (status[0] == 1 && status[1]) {
isc_print_status(status);
return (1);
}

// setup output XSQLVAR structure for output variable 1
ovar1 = &(osqlda->sqlvar[0]);


dtype = (ovar1->sqltype & ~1); // drop null bit for now
switch (dtype) {
case SQL_SHORT:
ovar1->sqldata = (char *) malloc(sizeof
(short));
break;
case SQL_LONG:
ovar1->sqldata = (char *) malloc(sizeof
(long));
break;
default:
printf ("Invalid dtype #%d\n", dtype);
exit(0);
} // switch
if (ovar1->sqltype & 1) {
// assign a variable to hold null status
ovar1->sqlind = &null_flag;
}

// repeat for output variable 2
ovar2 = &(osqlda->sqlvar[1]);
dtype = (ovar2->sqltype & ~1); // drop null bit for now
switch (dtype) {
case SQL_SHORT:
ovar2->sqldata = (char *) malloc(sizeof
(short));
break;
case SQL_LONG:
ovar2->sqldata = (char *) malloc(sizeof
(long));
break;
default:
printf ("Invalid dtype #%d\n", dtype);
exit(0);
} // switch
if (ovar2->sqltype & 1) {
// assign a variable to hold null status
ovar2->sqlind = &null_flag;
}

// allocate and fill in input XSQLDA - 2 input params
isqlda = (XSQLDA *)malloc (XSQLDA_LENGTH(2));
isqlda->version = SQLDA_VERSION1;
isqlda->sqln = 2;
isqlda->sqld = 2;

ivar1 = &(isqlda->sqlvar[0]);
ivar1->sqltype = SQL_LONG;
ivar1->sqllen = sizeof(long);
ivar1->sqldata = (char *) malloc(sizeof(long));
* (long*) (ivar1->sqldata) = 5;

ivar2 = &(isqlda->sqlvar[1]);
ivar2->sqltype = SQL_LONG;
ivar2->sqllen = sizeof(long);
ivar2->sqldata = (char *) malloc(sizeof(long));
* (long*) (ivar2->sqldata) = 0;


isc_dsql_execute2(status, &tr1, &stmt1, 1, isqlda, osqlda);
if (status[0] ==1 && status[1]) {
isc_print_status(status);
return (1);
}

while ((rc = fbfetch(status, &stmt1, osqlda)) == 0) {
row_num = *(int *) osqlda->sqlvar[0].sqldata;
factorial = *(int *)osqlda->sqlvar[1].sqldata;
printf ("row_number: %d, factorial: %d\n", row_num,
factorial);
} // while rows to fetch

if (rc != 100) {
fberror(status, "error on fetch\n");
/* ERROR invalid request handle */
}

if (fbcommit(status, &tr1)) {
fberror(status, "error on commit\n");
isc_print_status(status);
}

if (fbfreeStmt(status, &stmt1, DSQL_drop))
fberror(status, "error when freeing statement\n");
if (fbdisconnect(status, &db1))
fberror(status, "error on disconnect\n");

return 0;
} // main