ESQL/C Arrays Query
I am just dipping my feet into ESQL/C, and was looking for any help on the
net, but found very little. I did find some examples, but am having trouble
implementing it.
My problems is this:
I wish to load an array of integers TXNO[] with data from a table tx,
spcifically field txno. I am currently using the following:
EXEC SQL declare cursor1 cursor for
select txno
into :currec
from tx;EXEC SQL open cursor1;
for (;;)
{
EXEC SQL fetch cursor1;
if (strncmp(SQLSTATE, "00",2) != 0)
break;
TXNO[i++]=currec;
}
From the example I found on the net (applied to Sybase I think) this was used instead:
EXEC SQL SELECT txno INTO :SLTXNO
FROM tx;
Producing the error:
error -33202: Incorred dimension on array variable SLTXRF.
Is there any way anything this efficient will work for Informix ESQL/C?
Also, given that a table TXNO contains a list of numbers, is there a slick way of performing a join with an informix table, producing another set of data - other than creating a parameterised select statement and opening/fetching for each element in TXNO?
Ta, Darren
> From the example I found on the net (applied to Sybase I think) this was
used instead:
> EXEC SQL SELECT txno INTO :SLTXNO
> FROM tx;
I'm not sure that Sybase support this.
In Informix (and in Oracle) there is no way to select data from a table
other then using consequent fetch from cursor.
Darren Rees <darren.rees@ntlworld.com> ''''' '
''''''''':NAKG5.8431$NQ4.194935@news2-win.server.ntlworld.com...
> I am just dipping my feet into ESQL/C, and was looking for any help on the
> net, but found very little. I did find some examples, but am having
trouble
> implementing it.
> My problems is this:
> I wish to load an array of integers TXNO[] with data from a table tx,
> spcifically field txno. I am currently using the following:
> EXEC SQL declare cursor1 cursor for
> select txno
> into :currec
> from tx;> EXEC SQL open cursor1;
> for (;;)
>
> EXEC SQL fetch cursor1;
> if (strncmp(SQLSTATE, "00",2) != 0)
> break;
> TXNO[i++]=currec;
> }
>
> From the example I found on the net (applied to Sybase I think) this was
used instead:
> EXEC SQL SELECT txno INTO :SLTXNO
> FROM tx;
> Producing the error:
> error -33202: Incorred dimension on array variable SLTXRF.
>
> Is there any way anything this efficient will work for Informix ESQL/C?
>
> Also, given that a table TXNO contains a list of numbers, is there a slick
way of performing a join with an informix table, producing another set of
data - other than creating a parameterised select statement and
opening/fetching for each element in TXNO?
>
> Ta, Darren
>
>
>
>
Hi,
please see comments below.
Darren Rees wrote:
> I am just dipping my feet into ESQL/C, and was looking for any help on the
> net, but found very little. I did find some examples, but am having trouble
> implementing it.
> My problems is this:
> I wish to load an array of integers TXNO[] with data from a table tx,
> spcifically field txno. I am currently using the following:
> EXEC SQL declare cursor1 cursor for
> select txno
> into :currec
> from tx;> EXEC SQL open cursor1;
> for (;;)
> {
> EXEC SQL fetch cursor1;
> if (strncmp(SQLSTATE, "00",2) != 0)
> break;
> TXNO[i++]=currec;
> }
>
> From the example I found on the net (applied to Sybase I think) this was used instead:
> EXEC SQL SELECT txno INTO :SLTXNO
> FROM tx;
> Producing the error:
> error -33202: Incorred dimension on array variable SLTXRF.
>
> Is there any way anything this efficient will work for Informix ESQL/C?
You can use array fetch, but you have to use sqlda structures - check the ESQL/C manual ( http://www.informix.com/answers/english/docs/23sdk/5423.pdf ), page 15-36, "Using a Fetch Array". There is a step by step description of what to do including sample
function (e.g. init_sqlda()) that do correct memory allocation).
> Also, given that a table TXNO contains a list of numbers, is there a slick way of performing a join with an informix table, producing another set of data - other than creating a parameterised select statement and opening/fetching for each element in TXNO?
Within ESQL/C you can use all possible types of select statements, including joins. So whatever table you want to join with - it's no problem (obviously, the join would be with the table 'tx', not the array 'txno').
Hope this helps, Heiko
> Ta, Darren
"A fetch array enables you to increase the number of rows that a single
FETCH
statement returns from the fetch buffer to an sqlda structure in your
program. A fetch array is especially useful when you fetch
simple-large-object."
INFORMIX-ESQL/C
Programmer's Manual
If you does not use SLOB ( as in this case ) fetch array is not good way to
select simple data from the table (because more difficult to coding).
For simple tables ( without SLOB ) there is not difference between 'array
fetch' and consequence fetch.
1) Array fetch : a) engine fill 'fetch buffer' with data from sql server b)
engine fill 'sqlda buffer' with data from 'fetch buffer'
2) Consequent fetch: a) engine fill 'fetch buffer' with data from sql server
b) every time you call 'fetch' engine 'fill sqlda buffer' with one row data.
In both cases only first 'fetch' produce data exchenge between server and
client. All subsequent 'fetch ' produce data excenge between memory buffers
on client side.
Good luck.
Sergey.
Heiko Giesselmann <heiko.giesselmann@informix.com> ''''' '
''''''''':39EDDE75.52131916@informix.com...
> Hi,
>
> please see comments below.
>
> Darren Rees wrote:
>
> > I am just dipping my feet into ESQL/C, and was looking for any help on
the
> > net, but found very little. I did find some examples, but am having
trouble
> > implementing it.
> > My problems is this:
> > I wish to load an array of integers TXNO[] with data from a table tx,
> > spcifically field txno. I am currently using the following:
> > EXEC SQL declare cursor1 cursor for
> > select txno
> > into :currec
> > from tx;> > EXEC SQL open cursor1;
> > for (;;)
> > {
> > EXEC SQL fetch cursor1;
> > if (strncmp(SQLSTATE, "00",2) != 0)
> > break;
> > TXNO[i++]=currec;
> > }
> >
> > From the example I found on the net (applied to Sybase I think) this was
used instead:
> > EXEC SQL SELECT txno INTO :SLTXNO
> > FROM tx;
> > Producing the error:
> > error -33202: Incorred dimension on array variable SLTXRF.
> >
> > Is there any way anything this efficient will work for Informix ESQL/C?
>
> You can use array fetch, but you have to use sqlda structures - check the
ESQL/C manual
( http://www.informix.com/answers/english/docs/23sdk/5423.pdf ), page 15-36,
"Using a Fetch Array". There is a step by step description of what to do
including sample
> function (e.g. init_sqlda()) that do correct memory allocation).
>
> > Also, given that a table TXNO contains a list of numbers, is there a
slick way of performing a join with an informix table, producing another set
of data - other than creating a parameterised select statement and
opening/fetching for each element in TXNO?
>
> Within ESQL/C you can use all possible types of select statements,
including joins. So whatever table you want to join with - it's no problem
(obviously, the join would be with the table 'tx', not the array 'txno').
>
> Hope this helps, Heiko
>
> > Ta, Darren
>
"Sergey E. Volkov" wrote:
>
> "A fetch array enables you to increase the number of rows that a single
> FETCH
> statement returns from the fetch buffer to an sqlda structure in your
> program. A fetch array is especially useful when you fetch
> simple-large-object."
>
> INFORMIX-ESQL/C
> Programmer's Manual
>
> If you does not use SLOB ( as in this case ) fetch array is not good way to
> select simple data from the table (because more difficult to coding).
> For simple tables ( without SLOB ) there is not difference between 'array
> fetch' and consequence fetch.
> 1) Array fetch : a) engine fill 'fetch buffer' with data from sql server b)
> engine fill 'sqlda buffer' with data from 'fetch buffer'
> 2) Consequent fetch: a) engine fill 'fetch buffer' with data from sql server
> b) every time you call 'fetch' engine 'fill sqlda buffer' with one row data.
>
> In both cases only first 'fetch' produce data exchenge between server and
> client. All subsequent 'fetch ' produce data excenge between memory buffers
> on client side.
Only partially true, Sergey. The engine will pass a FET_BUF_SIZE buffer on
the first fetch (default 4K) and on each sebsequent FETCH that cannot be
filled from the previous buffer. If your select set is less than
FET_BUF_SIZE then, yes, there is little to be gained from using the array
fetch coding, however, on larger queries there are gains from using this
method, especially in combination with a maximum sized fetch buffer (32767
or rowsize * FET_ARR_SIZE whichever is smaller) and array size. Witness
the performance of my dbcopy utility which is about 3X faster than
INSERT INTO....SELECT FROM when fetching from one server and inserting into
another and even outperforms INSERT INTO...SELECT FROM... on a local server
(where is can use shared memory for only one of the two connections it uses)
by a significant margin. The gains are from the reduction in network
traffic, yes, as you imply, but much is due to being able to deblock the
column arrays in a manner specific to the application, with one less memory
to memory copy, which the ESQL library must do using general purpose code
which includes some data type conversion code.
Yes, the coding is more complex, and for general use it is not needed,
however, for some bulk copy/unload uses it is a powerful and fast technique.
Art S. Kagel
> Good luck.
>
> Sergey.
>
> Heiko Giesselmann <heiko.giesselmann@informix.com> ïèøåò â
> ñîîáùåíèè:39EDDE75.52131916@informix.com...
> > Hi,
> >
> > please see comments below.
> >
> > Darren Rees wrote:
> >
> > > I am just dipping my feet into ESQL/C, and was looking for any help on
> the
> > > net, but found very little. I did find some examples, but am having
> trouble
> > > implementing it.
> > > My problems is this:
> > > I wish to load an array of integers TXNO[] with data from a table tx,
> > > spcifically field txno. I am currently using the following:
> > > EXEC SQL declare cursor1 cursor for
> > > select txno
> > > into :currec
> > > from tx;> > > EXEC SQL open cursor1;
> > > for (;;)
> > > {
> > > EXEC SQL fetch cursor1;
> > > if (strncmp(SQLSTATE, "00",2) != 0)
> > > break;
> > > TXNO[i++]=currec;
> > > }
> > >
> > > From the example I found on the net (applied to Sybase I think) this was
> used instead:
> > > EXEC SQL SELECT txno INTO :SLTXNO
> > > FROM tx;
> > > Producing the error:
> > > error -33202: Incorred dimension on array variable SLTXRF.
> > >
> > > Is there any way anything this efficient will work for Informix ESQL/C?
> >
> > You can use array fetch, but you have to use sqlda structures - check the
> ESQL/C manual
> ( http://www.informix.com/answers/english/docs/23sdk/5423.pdf ), page 15-36,
> "Using a Fetch Array". There is a step by step description of what to do
> including sample
> > function (e.g. init_sqlda()) that do correct memory allocation).
> >
> > > Also, given that a table TXNO contains a list of numbers, is there a
> slick way of performing a join with an informix table, producing another set
> of data - other than creating a parameterised select statement and
> opening/fetching for each element in TXNO?
> >
> > Within ESQL/C you can use all possible types of select statements,
> including joins. So whatever table you want to join with - it's no problem
> (obviously, the join would be with the table 'tx', not the array 'txno').
> >
> > Hope this helps, Heiko
> >
> > > Ta, Darren
> >