[Info-ingres] OpenAPI Calling a database procedure

by **Croker, Grant** » Thu, 14 Jul 2005 23:25:28 GMT

i.

If you are executing a procedure without params then sd_descriptorCount
should be 1. This could be the cause of the hang? The code below is what
I do within the PHP extension to support procedures without params. I
have tagged on the end the code for getting the return code from a
procedure.

queryParm.qy_genParm.gp_callback = NULL;
queryParm.qy_genParm.gp_closure = NULL;
queryParm.qy_connHandle = connHandle;
queryParm.qy_tranHandle = tranHandle;
queryParm.qy_stmtHandle = NULL;
queryParm.qy_queryType = IIAPI_QT_EXEC_PROCEDURE;
queryParm.qy_parameters = TRUE;
queryParm.qy_queryText = NULL;

IIapi_query(&queryParm);

while( queryParm.qy_genParm.gp_completed == FALSE )
IIapi_wait( &waitParm );

if (waitParm.wt_status != IIAPI_ST_SUCCESS)
{
... handle error ...
}

DescrBuffer = (IIAPI_DESCRIPTOR
*)ecalloc(sizeof(IIAPI_DESCRIPTOR),1);
setDescrParm.sd_genParm.gp_callback = NULL;
setDescrParm.sd_genParm.gp_closure = NULL;
setDescrParm.sd_stmtHandle = stmtHandle;
setDescrParm.sd_descriptorCount = 1; /* no params just
the name
of the
procedure */
setDescrParm.sd_descriptor = DescrBuffer;

setDescrParm.sd_descriptor[0].ds_dataType = IIAPI_CHA_TYPE;
setDescrParm.sd_descriptor[0].ds_length = strlen(procname);
setDescrParm.sd_descriptor[0].ds_nullable = FALSE;
setDescrParm.sd_descriptor[0].ds_precision = 0;
setDescrParm.sd_descriptor[0].ds_scale = 0;
setDescrParm.sd_descriptor[0].ds_columnType = IIAPI_COL_SVCPARM;
setDescrParm.sd_descriptor[0].ds_columnName = NULL;

IIapi_setDescriptor( &setDescrParm );

while( setDescrParm.sd_genParm.gp_completed == FALSE )
IIapi_wait( &waitParm );

if (waitParm.wt_status != IIAPI_ST_SUCCESS)
{
... handle error ...
}

DataBuffer = (IIAPI_DATAVALUE
*)calloc(sizeof(IIAPI_DATAVALUE),1);
putParmParm.pp_genParm.gp_callback = NULL;
putParmParm.pp_genParm.gp_closure = NULL;
putParmParm.pp_stmtHandle = stmtHandle;
putParmParm.pp_parmCount = setDescrParm.sd_descriptorCount;
putParmParm.pp_parmData = DataBuffer;
putParmParm.pp_moreSegments = 0;

putParmParm.pp_parmData[0].dv_null = FALSE;
putParmParm.pp_parmData[0].dv_length = strlen(procname );
putParmParm.pp_parmData[0].dv_value = procname;
IIapi_putParms( &putParmParm );

while( putParmParm.pp_genParm.gp_completed == FALSE )
IIapi_wait( &waitParm );

if (waitParm.wt_status != IIAPI_ST_SUCCESS)
{
... handle error ...
}

getDescrParm.gd_genParm.gp_callback = NULL;
getDescrParm.gd_genParm.gp_closure = NULL;
getDescrParm.gd_stmtHandle = stmtHandle;

IIapi_getDescriptor(&getDescrParm);

while( getDescrParm.gd_genParm.gp_completed == FALSE )
IIapi_wait( &waitParm );

if (waitParm.wt_status != IIAPI_ST_SUCCESS)
{
... handle error ...
}
getQInfoParm.gq_genParm.gp_callback = NULL;
getQInfoParm.gq_genParm.gp_closure = NULL;
getQInfoParm.gq_stmtHandle = queryParm.qy_stmtHandle;

IIapi_getQueryInfo( &getQInfoParm );

while( getQInfoParm.gq_genParm.gp_completed == FALSE )
IIapi_wait( &waitParm );

if (waitParm.wt_status != IIAPI_ST_SUCCESS)
{
... handle error ...
}

if ( getQInfoParm.gq_mask & IIAPI_GQ_PROCEDURE_RET )
{
/* we have a return value */

returnvalue = getQInfoParm.gq_procedureReturn;

}

Note if you want to return rows via a procedure a call to
IIapi_getQueryInfo() will destroy the row data being

by **martin.bowes** » Thu, 14 Jul 2005 23:51:33 GMT

i Grant,

Solved the hang by installing the generic Parameter values
correctly for the IIapi_query call. Now the program runs to completion
but...

The value returned from the procedure is 0 and not the 100 it
would be if this procedure was executed from ESQLC.

I use the code...
if (queryInfoParm.gq_mask & IIAPI_GQ_PROCEDURE_RET) {
printf("A returned value was available=%f\n",
queryInfoParm.gq_procedureReturn
);
printf("gq_flags=%x\n", queryInfoParm.gq_flags);
returned_value=queryInfoParm.gq_procedureReturn; /*May not be
reliable*/
};

The gq_flags was 0x80 ie. IIAPI_GQF_TRANSACTION_INACTIVE

I'm wondering if the procedure return status and the value returned by
the procedure using return [return_status] are the same thing?

I note in the SQL Manual:
The optional return_status returns a value to the calling application when the
return statement executes. Return_status must be a non-null integer constant,
variable, or parameter whose data type is comparable with the data type of the
variable to which its value will be assigned. If the return_status is not
specified or if a return statement is not executed, then the procedure returns 0
to the calling application.

In this case the procedure returns an integer variable. I'm assiging this
to a long. I would have thought this would be acceptable.

Marty

<?xml version="1.0" ?><html>
<head>
<title></title>
</head>
<body>
<div align="left"><font face="Arial"><span style="font-size:10pt">Hi Grant,</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">            Solved the hang by installing the generic
Parameter values
correctly for the IIapi_query call. Now the program runs to completion
but...</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">            The value returned from the procedure
is 0 and not the 100 it
would be if this procedure was executed from ESQLC.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">I use the code...</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">if (queryInfoParm.gq_mask & IIAPI_GQ_PROCEDURE_RET) {</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">    printf("A returned value was available=%f\n",</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">            queryInfoParm.gq_procedureReturn</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">            );</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">    printf(&

by **Croker, Grant** » Fri, 15 Jul 2005 00:09:48 GMT

AFAIK queryInfoParm.gq_procedureReturn is the value sent back via a procedure 'return' call. queryInfoParm.gq_procedureReturn is defined as II_LONG which, on windows at least, is typedef'd to long so what you are doing there is ok.

Did you change setDescrParm.sd_descriptorCount to 1? If so then at some point an error is occurring. Your best bet is to run an API trace to see at what point its happening or check the respective status after each IIapi_* call for IIAPI_ST_SUCCESS. If you like, you can send me the trace offline and I will have a look.

g

________________________________________
From:  XXXX@XXXXX.COM  [mailto: XXXX@XXXXX.COM ] 
Sent: Wednesday, July 13, 2005 4:52 PM
To: Croker, Grant
Cc:  XXXX@XXXXX.COM 
Subject: RE: OpenAPI Calling a database procedure

Hi Grant,

Solved the hang by installing the generic Parameter values correctly for the IIapi_query call. Now the program runs to completion but...

The value returned from the procedure is 0 and not the 100 it would be if this procedure was executed from ESQLC.

I use the code...
if (queryInfoParm.gq_mask & IIAPI_GQ_PROCEDURE_RET) {
?printf("A returned value was available=%f\n",
queryInfoParm.gq_procedureReturn
?);
?printf("gq_flags=%x\n", queryInfoParm.gq_flags);
?returned_value=queryInfoParm.gq_procedureReturn; /*May not be reliable*/
?};

The gq_flags was 0x80 ie. IIAPI_GQF_TRANSACTION_INACTIVE

I'm wondering if the procedure return status and the value returned by the procedure using return [return_status] are the same thing?

I note in the SQL Manual:
The optional return_status returns a value to the calling application when the
return statement executes. Return_status must be a non-null integer constant,
variable, or parameter whose data type is comparable with the data type of the
variable to which its value will be assigned. If the return_status is not specified or if a return statement is not executed, then the procedure returns 0 to the calling application.

In this case the procedure returns an integer variable. I'm assiging this to a long. I would have thought this would be acceptable.

Marty

by **martin.bowes** » Fri, 15 Jul 2005 18:46:46 GMT

i Grant et al,
Ah yes, but on Tru64 its typedef'd to int! Howls of abuse!

And so, once I allowed for that it all started working! Aaagh!
Yep, that's the last time I ignore Compiler warnings like:
cc: Warning: exec_proc_api.c, line 726: In this statement, this
argument to printf and conversion specifier "%f" combine integer and
floating-point types. Behavior can be unpredictable. (outfloatint)
queryInfoParm.gq_procedureReturn

But now that its all working properly, I can at least stop whaking
my head on the desk.

Thanks for the input.

Marty
--
Random Duckman Quote #27:
Duckman - No doubt about it, the only solution left is to hickory smoke
my
brain to perfection with 10,000 volts of electricity.

<?xml version="1.0" ?><html>
<head>
<title></title>
</head>
<body>
<div align="left"><font face="Arial"><span style="font-size:10pt">Hi Grant </span></font><font face="Arial"><span style="font-size:10pt">et al,</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">> </span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">> AFAIK queryInfoParm.gq_procedureReturn is the value sent back via a</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">> procedure 'return' call. queryInfoParm.gq_procedureReturn is defined</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">> as II_LONG which, on windows at least, is typedef'd to long so what</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">> you are doing there is ok.</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">> </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">            Ah yes, but on Tru64 its typedef'd
to int! Howls of abuse!</span></font></div>
<div align="left"><br/></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">            And so, once I allowed for that it
all started working! Aaagh!</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Yep, that's the last time I ignore Compiler warnings like:</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">cc: Warning: exec_proc_api.c, line 726: In this statement, this
argument to printf and conversion specifier "%f" combine integer and
floating-point types. Behavior can be unpredictable. (outfloatint)</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">        queryInfoParm.gq_procedureReturn</span></font></div>
<div ali

by **Croker, Grant** » Fri, 15 Jul 2005 19:51:06 GMT

It's typedef'd that way since sizeof(long) on Tru64 is 8 bytes not 4, as it is with 32 bit systems. I did spot that %f in your printf and was curious... obviously not curious enough. Best to use the II_ types for working with API to make your code more portable.

g

________________________________________
From:  XXXX@XXXXX.COM  [mailto: XXXX@XXXXX.COM ] 
Sent: Thursday, July 14, 2005 11:47 AM
To: Croker, Grant
Cc:  XXXX@XXXXX.COM 
Subject: Re: [Info-ingres] RE: OpenAPI Calling a database procedure

Hi Grant et al,
Ah yes, but on Tru64 its typedef'd to int! Howls of abuse!

And so, once I allowed for that it all started working! Aaagh!
Yep, that's the last time I ignore Compiler warnings like:
cc: Warning: exec_proc_api.c, line 726: In this statement, this argument to printf and conversion specifier "%f" combine integer and floating-point types.?Behavior can be unpredictable. (outfloatint)
?queryInfoParm.gq_procedureReturn

But now that its all working properly, I can at least stop whaking my head on the desk.

Thanks for the input.

Marty
Random Duckman Quote #27:
Duckman - No doubt about it, the only solution left is to hickory smoke my
?brain to perfection with 10,000 volts of electricity.

[Info-ingres] OpenAPI Calling a database procedure

[Info-ingres] OpenAPI Calling a database procedure

Jump to:

Sponsored Links

Next

[Info-ingres] RE: OpenAPI Calling a database procedure

[Info-ingres] RE: OpenAPI Calling a database procedure

[Info-ingres] RE: OpenAPI Calling a database procedure

Re: [Info-ingres] RE: OpenAPI Calling a database procedure

RE: [Info-ingres] RE: OpenAPI Calling a database procedure

Similar Threads:

Who is online