[Info-ingres] OpenAPI Calling a database procedure

DATABASE

    Sponsored Links

    Next

  • 1. Table partitioning question
    When a table is partitioned using partitions hashed on a given column, does that column have to be the only column in a where clause for the optimizer to know which partition the data is stored in? Or does the optimizer scan the query looking for any column in the where clause that may be in the partitioning rules? I guess I'm trying to understand what the conditions must be for partitions to improve performance for queries against larger tables. Maybe judicious use of secondary indexes would be better, but I've run into problems with "can't find tuple in index"(ph) errors, so I've removed all secondary indexes. Thanks Dennis d underscore roesler at agilent dot com
  • 2. SQL Help needed....
    Hi, I have a piece of SQL that takes a long time to complete. Any ideas on how the sql can be enhanced in order to improve the speed? SELECT h.dept, h.ref, h.date, h.type, l.seq, l.amount, l.value FROM header h JOIN line l ON h.seq = l.seq AND h.dept = :PASSED_IN_dept AND h.sub_dept = h.dept AND h.cancel = 0 AND l.mkr = 0 AND h.type = 'A' AND l.msis = :PASSED_IN_MSIS AND l.amount <> 0 Header table - contains 30,000 records ============ dept(char4) Key 1 acc(int) Key 2 doc(int) Key 3 seq(int) Key 4 ref(char9) date(date) cancel(int) type(char1) sub_dept(char4) Line table - contains 500,000 records ========== seq(int) Key 1 page(int) Key 2 msis(char8) amount(float) value(float) mkr(int) Cheers, Manny PS Are there any documents/reference guides/books you can recommend for writing efficient SQL?
  • 3. JDBC 3.0 and Ingres 2.6
    Hi Is there any supported way to get the JDBC driver from Ingres 2006 working against the Ingres 2.6 DBMS. We'd quite like to use some of the 3.0 features in our next release, but don't want the testing overhead of upgrading the DBMS if we can help it. Chloe Crowder The British Library
  • 4. Efficient delete from table with compound keys?
    Using II 9.0.4 (hp2.us5/105) What is the most efficient way to do the following? TableA is btree with col1, col2, col3 being the compound key and has about 12.5M rows of data. TableA is also compressed and stored across 3 locations if it makes a difference. delete from tableA where col1+col2+col3 in (select col1+col2+col3 from tableB) Thanks Dennis d underscore roesler at agilent dot com

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

Postby 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

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

Postby 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(&

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

Postby 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




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

Postby 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

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

Postby 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.




Similar Threads:

1.[Info-ingres] OpenAPI Caling a database procedure

Hi Grant et al,

I'm trying call a database procedure synchronously from an OpenAPI 
application. The procedure has no parameters, all I want is the return 
value. My understanding is that I set IIapi_query to say I'm executing a 
database procedure. It will not do anything until the necessary 
parameters (including procedure name and owner) are supplied and 
filled in.

However, the code seems to hang at the initial iiapi_query() call. 

I am using in order the following:
IIapi_query()
	QueryParm.qy_queryType=IIAPI_QT_EXEC_PROCEDURE;
	QueryParm.qy_queryText=NULL;
	QueryParm.qy_parameters=TRUE; 
    I don't wait on gp_completed.

IIapi_setDescriptor()
	setDescParm.sd_stmtHandle=QueryParm.qy_stmtHandle;
	setDescParm.sd_descriptorCount=2;

    I then allocate storage for two parameter descriptors for the 
    array setDescParm.sd_descriptor. In each element of which I 
    set ds_dataType = IIAPI_CHA_TYPE,
    ds_nullable = FALSE, 
    ds_columnType = IIAPI_COL_SVCPARM
    ds_columnName = NULL

    I wait on gp_completed.

IIapi_putParms()
	Fill in the procedure name, owner values described above, call 
the procedure an wait on gp_completed.

At this point I wait on the IIapi_query to complete.
Then I call IIapi_get_QueryInfo to pick up the procedure return value.

Martin Bowes
Random Farscape Quote #26:
John     - I think we have to put Grandma in a home.
Ka'Dargo - We could burn her.
John     - You burn your old people!
Ka'Dargo - No, but in her case...

2.[Info-ingres] Calling Ingres Database Procedures through .Net Data Provider

This is a multi-part message in MIME format.

Ingres r3 supports row-producing procedures.  The Ingres .NET Data
Provider reads the result set produced by that procedure just as any
result set produced by a query.

 

If the procedure is something like this:

 

drop procedure rowproc \p\g

 

create procedure rowproc

   result row(char(32)) as

declare tabname char(32);

begin

   for select table_name into :tabname from iitables

   do

      return row(:tabname);

   endfor;

end;

\p\g

 

The .NET application would execute the procedure and read the result set
like this:

 

            IDataReader reader = null;

      cmd = new IngresCommand(

            "rowproc", conn, txn); //"{ call rowproc}", conn, txn);

      cmd.CommandType = CommandType.StoredProcedure;

      reader = cmd.ExecuteReader();

 

      Console.Write("ColumnName='" + reader.GetName(0) + "'");

      Console.WriteLine();

 

      while (reader.Read())

      {

            Console.Write(reader.GetString(0) + "\t");

      }

      Console.WriteLine();

      reader.Close();

 

Hope this helps,

Dave


<html>

<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered)">
<style>
<!--
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:#606420;
	text-decoration:underline;}
span.EmailStyle17
	{font-family:Arial;
	color:windowtext;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=EN-US link=blue vlink="#606420">

<div class=Section1>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Ingres r3 supports row-producing procedures.  The
Ingres .NET Data Provider reads the result set produced by that procedure just
as any result set produced by a query.</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>If the procedure is something like this:</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>drop procedure rowproc \p\g</span></font></p>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'> </span></font></p>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>create procedure rowproc</span></font></p>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>   result row(char(32)) as</span></font></p>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>declare tabname char(32);</span></font></p>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>begin</span></font></p>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>   for select table_name
into :tabname from iitables</span></font></p>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>   do</span></font></p>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>     
return row(:tabname);</span></font></p>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>   endfor;</span></font></p>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>end;</span></font></p>

<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>\p\g</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>The .NET application would execute the procedure and read the
result set like this:</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>           
</span></font><font size=2 face="Courier New"><span style='font-size:10.0pt;
font-family:"Courier New"'>IDataReader reader = null;</span></font></p>

<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>      cmd
= <font color=blue><span style='color:blue'>new</span></font> IngresCommand(</span></font></p>

<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>            "rowproc",
conn, txn); <font color=green><span style='color:green'>//"{ call
rowproc}", conn, txn);</span></font></span></font></p>

<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>      cmd.CommandType
= CommandType.StoredProcedure;</span></font></p>

<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>      reader
= cmd.ExecuteReader();</span></font></p>

<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> </span></font></p>

<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>      Console.Write("ColumnName='"
+ reader.GetName(0) + "'");</span></font></p>

<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>      Console.WriteLine();</span></font></p>

<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> </span></font></p>

<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>      <font
color=blue><span style='color:blue'>while</span></font> (reader.Read())</span></font></p>

<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>      {</span></font></p>

<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>            Console.Write(reader.GetString(0)
+ "\t");</span></font></p>

<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>      }</span></font></p>

<p class=MsoNormal style='text-autospace:none'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>      Console.WriteLine();</span></font></p>

<p class=MsoNormal><font size=2 face="Courier New"><span style='font-size:10.0pt;
font-family:"Courier New"'>      reader.Close();</span></font></p>

<p class=MsoNormal><font size=2 face="Courier New"><span style='font-size:10.0pt;
font-family:"Courier New"'> </span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Hope this helps,</span></font></p>

<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Dave</span></font></p>

</div>

</body>

</html>

3.[Info-ingres] Calling Ingres Database Procedures through .Net Data Provider

4.[Info-ingres] OpenAPI error function called in wrong state

Hi Everyone,

As I'm just starting the joy of using API - why?Dont ask! Lets just say 
the curiosity got the better of me.

I've managed to get a connection to the database! Yay! Well at least it 
claims to have connected to the database. There is no sign of a 
connection being started in iircp.log....

Now I'm struggeling to get a non select query to execute. It keeps 
bitching about an API function called in the wrong state.

Anyone got any ideas?

The output from setting II_API_TRACE=5 and II_API_LOG to a suitable 
file shows:

!IIapi_initAPI: initializing API.
!IIapi_initialize: INGRES API initialized, envHndl = 00000001400ED020
!IIapi_connect: connect to DBMS Server
!IIapi_connect: envHndl = 00000001400ED020, connHndl = 
0000000000000000, tranHndl= 0000000000000000
!IIapi_connect: target = bowtest
!IIapi_thread(-1071947776): allocated local storage 
0000000140097840
!IIapi_uiDispatch: dispatching event IIAPI_EV_CONNECT_FUNC
!Dispatch(-1071947776): SQL Conn [00000001400ED220] IDLE --> 
REQ, 2 action(s)
!Dispatch(-1071947776): action[1] REMC
!Dispatch(-1071947776): action[2] CONN
!IIapi_query: starting a query
!IIapi_query: connHndl = 00000001400ED220, tranHndl = 
0000000000000000, queryType = 0
!IIapi_query: queryText = drop table next_id
!IIapi_uiDispatch: dispatching event IIAPI_EV_QUERY_FUNC
!SQL Conn Evaluate: API function called in wrong state
!Dispatch(-1071947776): SQL Conn [00000001400ED220] REQ --> 
REQ, 1 action(s)
!Dispatch(-1071947776): action[1] CBIF
!IIapi_appCallback: request completed, status = FAILURE
!IIapi_getErrorInfo: retrieving errors from API
!IIapi_getErrorInfo: handle = 00000001400ED220

Martin Bowes
--
Random Titus Quote #0:
Be normal and the crowd will accept you.
Be deranged, and they'll make you their leader.


<?xml  version="1.0" ?><html>
<head>
<title></title>
</head>
<body>
<div align="left"><font face="Arial"><span style="font-size:10pt">Hi Everyone,</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">As I'm just starting the joy of using API - why?Dont ask! Lets just say 
the curiosity got the better of me.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">I've managed to get a connection to the database! Yay! Well at least it 
claims to have connected to the database. There is no sign of a 
connection being started in iircp.log....</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Now I'm struggeling to get a non select query to execute. It keeps 
bitching about an API function called in the wrong state.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Anyone got any ideas?</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">The output from setting II_API_TRACE=5 and II_API_LOG to a suitable 
file shows:</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!IIapi_initAPI: initializing API.</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!IIapi_initialize: INGRES API initialized, envHndl = 00000001400ED020</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!IIapi_connect: connect to DBMS Server</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!IIapi_connect: envHndl = 00000001400ED020, connHndl = 
0000000000000000, tranHndl= 0000000000000000</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!IIapi_connect: target = bowtest</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!IIapi_thread(-1071947776): allocated local storage 
0000000140097840</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!IIapi_uiDispatch: dispatching event IIAPI_EV_CONNECT_FUNC</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!Dispatch(-1071947776): SQL Conn [00000001400ED220] IDLE --> 
REQ, 2 action(s)</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!Dispatch(-1071947776): action[1] REMC</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!Dispatch(-1071947776): action[2] CONN</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!IIapi_query: starting a query</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!IIapi_query: connHndl = 00000001400ED220, tranHndl = 
0000000000000000, queryType = 0</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!IIapi_query: queryText = drop table next_id</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!IIapi_uiDispatch: dispatching event IIAPI_EV_QUERY_FUNC</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt"><b>!SQL Conn Evaluate: API function called in wrong state</b></span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!Dispatch(-1071947776): SQL Conn [00000001400ED220] REQ --> 
REQ, 1 action(s)</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!Dispatch(-1071947776): action[1] CBIF</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!IIapi_appCallback: request completed, status = FAILURE</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!IIapi_getErrorInfo: retrieving errors from API</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">!IIapi_getErrorInfo: handle = 00000001400ED220</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Martin Bowes</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">Random Titus Quote #0:</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Be normal and the crowd will accept you.</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Be deranged, and they'll make you their leader.</span></font></div>
<div align="left"></div>
</body>
</html>

5.Calling Ingres Database Procedures through .Net Data Provider

Thole, David J wrote:
> Ingres r3 supports row-producing procedures.  The Ingres .NET Data 
> Provider reads the result set produced by that procedure just as any 
> result set produced by a query.
> 
>  
> 
> If the procedure is something like this:
> 
>  
> 
> drop procedure rowproc \p\g
> 
>  
> 
> create procedure rowproc
> 
>    result row(char(32)) as
> 
> declare tabname char(32);
> 
> begin
> 
>    for select table_name into :tabname from iitables
> 
>    do
> 
>       return row(:tabname);
> 
>    endfor;
> 
> end;
> 
> \p\g
> 
>  
> 
> The .NET application would execute the procedure and read the result set 
> like this:
> 
>  
> 
>             IDataReader reader = null;
> 
>       cmd = new IngresCommand(
> 
>             "rowproc", conn, txn); //"{ call rowproc}", conn, txn);
> 
>       cmd.CommandType = CommandType.StoredProcedure;
> 
>       reader = cmd.ExecuteReader();
> 
>  
> 
>       Console.Write("ColumnName='" + reader.GetName(0) + "'");
> 
>       Console.WriteLine();
> 
>  
> 
>       while (reader.Read())
> 
>       {
> 
>             Console.Write(reader.GetString(0) + "\t");
> 
>       }
> 
>       Console.WriteLine();
> 
>       reader.Close();
> 
>  
> 
> Hope this helps,
> 
> Dave
> 

That's almost what I'm looking for too.  I need to return data from a 
stored procedure in a way that it is accepted with open arms into a 
DataSet.

I'm also wondering which method is better practice when calling a stored 
procedure - specifying the parameters using the provided classes lie this :

IngresCommand iCmd = new IngresCommand("usr_add", iConn);
         iCmd.CommandType = CommandType.StoredProcedure;

         IngresParameter parm1 = new 
IngresParameter("in_username",IngresType.NVarChar,45);
         IngresParameter parm2 = new 
IngresParameter("in_password",IngresType.NVarChar,45);

         parm1.Value = usrName;
         parm2.Value = pwd;

         parm1.Direction = ParameterDirection.Input;
         parm2.Direction = ParameterDirection.Input;

         iCmd.Parameters.Add(parm1);
         iCmd.Parameters.Add(parm2);

         try
         {
             int numRows = iCmd.ExecuteNonQuery();
             return true;
         }

or just embedding the parameters in the command string itself :

         string iCmdText = @"execute procedure usr_delete 
(in_username='" + usrName + @"', in_password='" + pwd @"')";
         IngresCommand iCmd = new IngresCommand(iCmdText, iConn);

where usrName and pwd are strings...

Morgan.

6. [Info-ingres] Calling 4GL procedures

7. [Info-ingres] Database Procedures

8. [Info-ingres] Identifying database procedure hierarchies



Return to DATABASE

 

Who is online

Users browsing this forum: No registered users and 30 guest