I am trying to connect to a firebird database and execute a stored procedure which returns multiple rows. I could not get this to

work with EXECUTE PROCEDURE so I am using a SELECT * FROM (PROCEDURE NAME). My problem is that my method seems to be using a lot of

system resources. Anyone know if this is correct? After this I use a isc_dsql_fetch call to get all of the data and then I free the

statement and close the transaction.


Brian


To Connect:
{
char m_dpb_buffer[256];
ISC_STATUS m_connStatus[20];
isc_db_handle m_database = NULL;

char *dpb = m_dpb_buffer;

*dpb++ = isc_dpb_version1;

// Set the db user name
*dpb++ = isc_dpb_user_name;
*dpb++ = strlen(DB_USER);
strcpy(dpb, DB_USER);
dpb += strlen(DB_USER);

// set the db password
*dpb++ = isc_dpb_password;
*dpb++ = strlen(DB_PASS);
strcpy(dpb, DB_PASS);
dpb += strlen(DB_PASS);

isc_attach_database(m_connStatus, strlen(DB_NAME), DB_NAME, &m_database, dpb - m_dpb_buffer, m_dpb_buffer);
if(m_connStatus[0] == 1 && m_connStatus[1] != 0)
{
//error here
}
}

To Exec Procedure:
{
ISC_STATUS m_transStatus[20];
isc_stmt_handle m_stmt = NULL;
isc_tr_handle m_trans = NULL;
XSQLDA *m_out_sqlda;

isc_start_transaction(m_status, &m_trans, 1, &m_database, 0, NULL);
isc_dsql_allocate_statement(m_status, &m_database, &m_stmt);

m_out_sqlda = (XSQLDA *)(malloc(XSQLDA_LENGTH(numOParams)));
memset(m_out_sqlda, 0, (XSQLDA_LENGTH(numOParams)));

m_out_sqlda->version = SQLDA_VERSION1;
m_out_sqlda->sqln = numOParams;

isc_dsql_prepare(m_status, &m_trans, &m_stmt, 0, "SELECT * FROM [STORED PROC NAME] (param1, param2, ....)", 1, m_out_sqlda))

if(m_out_sqlda->sqld > m_out_sqlda->sqln)
{
int n = m_out_sqlda->sqld;

FreeOutputData();

m_out_sqlda = (XSQLDA *)(malloc(XSQLDA_LENGTH(n)));
memset(m_out_sqlda, 0, XSQLDA_LENGTH(n));

m_out_sqlda->sqln = n;
m_out_sqlda->version = SQLDA_VERSION1;

isc_dsql_describe(m_status, &m_stmt, 1, m_out_sqlda);
}

for(i=0, var = m_out_sqlda->sqlvar;i<m_out_sqlda->sqld; i++, var++)
{
var->sqldata = (char *)malloc(var->sqllen);
memset(var->sqldata, 0, var->sqllen);

if(var->sqltype & 1)
{
var->sqlind = (short *)malloc(sizeof(short));
*var->sqlind = 0;
}
}

isc_dsql_execute(m_status, &m_trans, &m_stmt, 1, NULL);
}