ESQL/C - Multiple cursors open to different databa
Larry asked for ESQL/C examples that keep cursors open against two different databases at once, fetching from one and unloading from another. Art Kagel's key tip: declare connections WITH CONCURRENT TRANSACTIONS, otherwise cursors close when you switch connections, and pointed to his utils2_ak package (dostats_ng.ec, dbcopy.ec, myschema) on the IIUG site. Marco Greco suggested SQSL as a no-code alternative. For the follow-up question about unloading a cross-database join, Jonathan Leffler noted you use database1:table1 syntax (colon, not dot), and that UNLOAD is a DB-Access/SQLCMD/SQSL verb, not available in ESQL/C or SPL.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Does anyone have some sample programs using ESQL/C with a couple of database
connections (two different databases)? I need to open a SELECT cursor from one
database table and use the results on each FETCH INTO to UNLOAD data from
another database table. As I SELECT INTO from one database table, I then
UNLOAD from another database and table.
Thanks
Larry
Look at the code for most of the tools in utils2_ak. Dostats_ng.ec,
dbcopy.ec, and myschema all use multiple connections.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Dec 13, 2011 at 4:33 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> Does anyone have some sample programs using ESQL/C with a couple of
> database
> connections (two different databases)? I need to open a SELECT cursor from
> one
> database table and use the results on each FETCH INTO to UNLOAD data from
> another database table. As I SELECT INTO from one database table, I then
> UNLOAD from another database and table.>
> Thanks
>
> Larry
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340cefeddc8204b4000a58
Oh, the secret to having cursors open in multiple connections is to create
the connections with the "WITH CONCURRENT TRANSACTIONS" clause, otherwise
the cursors in a connection will close when you switch away from it to
another connection!
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Dec 13, 2011 at 4:33 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> Does anyone have some sample programs using ESQL/C with a couple of
> database
> connections (two different databases)? I need to open a SELECT cursor from
> one
> database table and use the results on each FETCH INTO to UNLOAD data from
> another database table. As I SELECT INTO from one database table, I then
> UNLOAD from another database and table.>
> Thanks
>
> Larry
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340a51b9a1cb04b4001aa0
And your software is available at the IIUG site, or is it somewhere else?
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: ESQL/C - Multiple cursors open to differen.... [25600]
> Date: Tue, 13 Dec 2011 16:40:56 -0500
>
> Oh, the secret to having cursors open in multiple connections is to create
> the connections with the "WITH CONCURRENT TRANSACTIONS" clause, otherwise
> the cursors in a connection will close when you switch away from it to
> another connection!
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Tue, Dec 13, 2011 at 4:33 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
>
> > Does anyone have some sample programs using ESQL/C with a couple of
> > database
> > connections (two different databases)? I need to open a SELECT cursor from
> > one
> > database table and use the results on each FETCH INTO to UNLOAD data from
> > another database table. As I SELECT INTO from one database table, I then
> > UNLOAD from another database and table.> >
> > Thanks
> >
> > Larry
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --14dae9340a51b9a1cb04b4001aa0
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
At the IIUG site (www.iiug.org/software/software_index.html) under both the
"Database Administration" and the "ESQL/C -ESQL/C Environment or ESQL/C
Source" sections (the latter list is shorter). Download the package:
utils2_ak.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Dec 13, 2011 at 5:47 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> And your software is available at the IIUG site, or is it somewhere else?
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: ESQL/C - Multiple cursors open to differen.... [25600]
> > Date: Tue, 13 Dec 2011 16:40:56 -0500
> >
> > Oh, the secret to having cursors open in multiple connections is to
> create
> > the connections with the "WITH CONCURRENT TRANSACTIONS" clause, otherwise
> > the cursors in a connection will close when you switch away from it to
> > another connection!
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
> nor
> > those of the entities themselves.
> >
> > On Tue, Dec 13, 2011 at 4:33 PM, LARRY SORENSEN <lsorensen25@msn.com>
> wrote:
> >
> > > Does anyone have some sample programs using ESQL/C with a couple of
> > > database
> > > connections (two different databases)? I need to open a SELECT cursor
> from
> > > one
> > > database table and use the results on each FETCH INTO to UNLOAD data
> from
> > > another database table. As I SELECT INTO from one database table, I
> then
> > > UNLOAD from another database and table.> > >
> > > Thanks
> > >
> > > Larry
> > >
> > >
> > >
> > >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --14dae9340a51b9a1cb04b4001aa0
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae934115112661b04b401478f
On 13/12/11 22:47, LARRY SORENSEN wrote:
> And your software is available at the IIUG site, or is it somewhere else?
>
>> To: ids@iiug.org
>> From: art.kagel@gmail.com
>> Subject: Re: ESQL/C - Multiple cursors open to differen.... [25600]
>> Date: Tue, 13 Dec 2011 16:40:56 -0500
>>
>> Oh, the secret to having cursors open in multiple connections is to create
>> the connections with the "WITH CONCURRENT TRANSACTIONS" clause, otherwise
>> the cursors in a connection will close when you switch away from it to
>> another connection!
>>
>> Art
>>
>> Art S. Kagel
>> Advanced DataTools (www.advancedatatools.com)
>> Blog: http://informix-myview.blogspot.com/
>>
>> Disclaimer: Please keep in mind that my own opinions are my own opinions
>> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
>> other organization with which I am associated either explicitly,
>> implicitly, or by inference. Neither do those opinions reflect those of
>> other individuals affiliated with any entity with which I am affiliated nor
>> those of the entities themselves.
>>
>> On Tue, Dec 13, 2011 at 4:33 PM, LARRY SORENSEN<lsorensen25@msn.com> wrote:
>>
>>> Does anyone have some sample programs using ESQL/C with a couple of
>>> database
>>> connections (two different databases)? I need to open a SELECT cursor from
>>> one
>>> database table and use the results on each FETCH INTO to UNLOAD data from
>>> another database table. As I SELECT INTO from one database table, I then
>>> UNLOAD from another database and table.>>>
>>> Thanks
>>>
>>> Larry
>>>
>>>
>>>
>>>
>>
>
Have a look at SQSL - dbaccess like tool. It will do that double select /
unload without you even touching esql/c.
Sample SQSL syntax to get source and target table names from a database, move
data from a second into a third:
CONNECT TO "db1@engine1" AS "db1";
CONNECT TO "db2@engine2" AS "db2";
CONNECT TO "db3@engine3" AS "db3";
FOREACH SELECT intab, outtab FROM tab1 INTO intabname, outtabname CONNECTION
"db1";
SELECT col1 FROM <+get intabname+> CONNECTION "db2" INSERT INTO <+getouttabname+> VALUES(?) CONNECTION "db3";
END FOREACH
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm
Is the same information contained in the utils_ak program? I waited until I
was onsite to try and download your program and ftp is turned blocked. I can
use view to look at utils_ak, however.
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: ESQL/C - Multiple cursors open to differen.... [25602]
> Date: Tue, 13 Dec 2011 18:04:51 -0500
>
> At the IIUG site (www.iiug.org/software/software_index.html) under both the
> "Database Administration" and the "ESQL/C -ESQL/C Environment or ESQL/C
> Source" sections (the latter list is shorter). Download the package:
> utils2_ak.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Tue, Dec 13, 2011 at 5:47 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
>
> > And your software is available at the IIUG site, or is it somewhere else?
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: ESQL/C - Multiple cursors open to differen.... [25600]
> > > Date: Tue, 13 Dec 2011 16:40:56 -0500
> > >
> > > Oh, the secret to having cursors open in multiple connections is to
> > create
> > > the connections with the "WITH CONCURRENT TRANSACTIONS" clause, otherwise
> > > the cursors in a connection will close when you switch away from it to
> > > another connection!
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > > other organization with which I am associated either explicitly,
> > > implicitly, or by inference. Neither do those opinions reflect those of
> > > other individuals affiliated with any entity with which I am affiliated
> > nor
> > > those of the entities themselves.
> > >
> > > On Tue, Dec 13, 2011 at 4:33 PM, LARRY SORENSEN <lsorensen25@msn.com>
> > wrote:
> > >
> > > > Does anyone have some sample programs using ESQL/C with a couple of
> > > > database
> > > > connections (two different databases)? I need to open a SELECT cursor
> > from
> > > > one
> > > > database table and use the results on each FETCH INTO to UNLOAD data
> > from
> > > > another database table. As I SELECT INTO from one database table, I
> > then
> > > > UNLOAD from another database and table.> > > >
> > > > Thanks
> > > >
> > > > Larry
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --14dae9340a51b9a1cb04b4001aa0
> > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --14dae934115112661b04b401478f
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
No, none of the tools in utils_ak use multiple connections. I'll send you
the package.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Dec 14, 2011 at 9:57 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> Is the same information contained in the utils_ak program? I waited until I
> was onsite to try and download your program and ftp is turned blocked. I
> can
> use view to look at utils_ak, however.
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: ESQL/C - Multiple cursors open to differen.... [25602]
> > Date: Tue, 13 Dec 2011 18:04:51 -0500
> >
> > At the IIUG site (www.iiug.org/software/software_index.html) under both
> the
> > "Database Administration" and the "ESQL/C -ESQL/C Environment or ESQL/C
> > Source" sections (the latter list is shorter). Download the package:
> > utils2_ak.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
> nor
> > those of the entities themselves.
> >
> > On Tue, Dec 13, 2011 at 5:47 PM, LARRY SORENSEN <lsorensen25@msn.com>
> wrote:
> >
> > > And your software is available at the IIUG site, or is it somewhere
> else?
> > >
> > > > To: ids@iiug.org
> > > > From: art.kagel@gmail.com
> > > > Subject: Re: ESQL/C - Multiple cursors open to differen.... [25600]
> > > > Date: Tue, 13 Dec 2011 16:40:56 -0500
> > > >
> > > > Oh, the secret to having cursors open in multiple connections is to
> > > create
> > > > the connections with the "WITH CONCURRENT TRANSACTIONS" clause,
> otherwise
> > > > the cursors in a connection will close when you switch away from it
> to
> > > > another connection!
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel
> > > > Advanced DataTools (www.advancedatatools.com)
> > > > Blog: http://informix-myview.blogspot.com/
> > > >
> > > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor
> any
> > > > other organization with which I am associated either explicitly,
> > > > implicitly, or by inference. Neither do those opinions reflect those
> of
> > > > other individuals affiliated with any entity with which I am
> affiliated
> > > nor
> > > > those of the entities themselves.
> > > >
> > > > On Tue, Dec 13, 2011 at 4:33 PM, LARRY SORENSEN <lsorensen25@msn.com
> >
> > > wrote:
> > > >
> > > > > Does anyone have some sample programs using ESQL/C with a couple of
> > > > > database
> > > > > connections (two different databases)? I need to open a SELECT
> cursor
> > > from
> > > > > one
> > > > > database table and use the results on each FETCH INTO to UNLOAD
> data
> > > from
> > > > > another database table. As I SELECT INTO from one database table, I
> > > then
> > > > > UNLOAD from another database and table.> > > > >
> > > > > Thanks
> > > > >
> > > > > Larry
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> >
>
>
*******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > >
> > > >
> > > > --14dae9340a51b9a1cb04b4001aa0
> > > >
> > > >
> > > >
> > >
> > >
> >
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > >
> > >
> > >
> > >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --14dae934115112661b04b401478f
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf303bff667e8fb504b40f8d5c
I really appreciate the effort you put in.
Larry Sorensen
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: ESQL/C - Multiple cursors open to differen.... [25613]
> Date: Wed, 14 Dec 2011 11:06:46 -0500
>
> No, none of the tools in utils_ak use multiple connections. I'll send you
> the package.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Wed, Dec 14, 2011 at 9:57 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote:
>
> > Is the same information contained in the utils_ak program? I waited until I
> > was onsite to try and download your program and ftp is turned blocked. I
> > can
> > use view to look at utils_ak, however.
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: ESQL/C - Multiple cursors open to differen.... [25602]
> > > Date: Tue, 13 Dec 2011 18:04:51 -0500
> > >
> > > At the IIUG site (www.iiug.org/software/software_index.html) under both
> > the
> > > "Database Administration" and the "ESQL/C -ESQL/C Environment or ESQL/C
> > > Source" sections (the latter list is shorter). Download the package:
> > > utils2_ak.
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > > other organization with which I am associated either explicitly,
> > > implicitly, or by inference. Neither do those opinions reflect those of
> > > other individuals affiliated with any entity with which I am affiliated
> > nor
> > > those of the entities themselves.
> > >
> > > On Tue, Dec 13, 2011 at 5:47 PM, LARRY SORENSEN <lsorensen25@msn.com>
> > wrote:
> > >
> > > > And your software is available at the IIUG site, or is it somewhere
> > else?
> > > >
> > > > > To: ids@iiug.org
> > > > > From: art.kagel@gmail.com
> > > > > Subject: Re: ESQL/C - Multiple cursors open to differen.... [25600]
> > > > > Date: Tue, 13 Dec 2011 16:40:56 -0500
> > > > >
> > > > > Oh, the secret to having cursors open in multiple connections is to
> > > > create
> > > > > the connections with the "WITH CONCURRENT TRANSACTIONS" clause,
> > otherwise
> > > > > the cursors in a connection will close when you switch away from it
> > to
> > > > > another connection!
> > > > >
> > > > > Art
> > > > >
> > > > > Art S. Kagel
> > > > > Advanced DataTools (www.advancedatatools.com)
> > > > > Blog: http://informix-myview.blogspot.com/
> > > > >
> > > > > Disclaimer: Please keep in mind that my own opinions are my own
> > opinions
> > > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor
> > any
> > > > > other organization with which I am associated either explicitly,
> > > > > implicitly, or by inference. Neither do those opinions reflect those
> > of
> > > > > other individuals affiliated with any entity with which I am
> > affiliated
> > > > nor
> > > > > those of the entities themselves.
> > > > >
> > > > > On Tue, Dec 13, 2011 at 4:33 PM, LARRY SORENSEN <lsorensen25@msn.com
> > >
> > > > wrote:
> > > > >
> > > > > > Does anyone have some sample programs using ESQL/C with a couple of
> > > > > > database
> > > > > > connections (two different databases)? I need to open a SELECT
> > cursor
> > > > from
> > > > > > one
> > > > > > database table and use the results on each FETCH INTO to UNLOAD
> > data
> > > > from
> > > > > > another database table. As I SELECT INTO from one database table, I
> > > > then
> > > > > > UNLOAD from another database and table.> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > > Larry
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > >
> >
> >
>
*******************************************************************************
> > > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > > >
> > > > > >
> > > > >
> > > > > --14dae9340a51b9a1cb04b4001aa0
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> >
> >
>
*******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --14dae934115112661b04b401478f
> > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --20cf303bff667e8fb504b40f8d5c
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Another dilema -
I am looking for the easiest way to do the following in an automated
fashion, preferably in an ESQL/C program, but possibly using an sql script
as well: (two different databases on the same server)
UNLOAD TO 'somefile' select * from database1.table1 where column1 in (select
column 2 from database2.table2)
Thanks.
Larry
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LARRY
SORENSEN
Sent: Wednesday, December 14, 2011 7:57 AM
To: ids@iiug.org
Subject: RE: ESQL/C - Multiple cursors open to differen.... [25612]
Is the same information contained in the utils_ak program? I waited until I
was onsite to try and download your program and ftp is turned blocked. I can
use view to look at utils_ak, however.
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: ESQL/C - Multiple cursors open to differen.... [25602]
> Date: Tue, 13 Dec 2011 18:04:51 -0500
>
> At the IIUG site (www.iiug.org/software/software_index.html) under
> both the "Database Administration" and the "ESQL/C -ESQL/C Environment
> or ESQL/C Source" sections (the latter list is shorter). Download the
package:
> utils2_ak.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own
> opinions and do not reflect on my employer, Advanced DataTools, the
> IIUG, nor any other organization with which I am associated either
> explicitly, implicitly, or by inference. Neither do those opinions
> reflect those of other individuals affiliated with any entity with
> which I am affiliated nor those of the entities themselves.
>
> On Tue, Dec 13, 2011 at 5:47 PM, LARRY SORENSEN <lsorensen25@msn.com>
wrote:
>
> > And your software is available at the IIUG site, or is it somewhere
else?
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: ESQL/C - Multiple cursors open to differen....
> > > [25600]
> > > Date: Tue, 13 Dec 2011 16:40:56 -0500
> > >
> > > Oh, the secret to having cursors open in multiple connections is
> > > to
> > create
> > > the connections with the "WITH CONCURRENT TRANSACTIONS" clause,
otherwise
> > > the cursors in a connection will close when you switch away from
> > > it to another connection!
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> > > opinions and do not reflect on my employer, Advanced DataTools,
> > > the IIUG, nor any other organization with which I am associated
> > > either explicitly, implicitly, or by inference. Neither do those
> > > opinions reflect those of other individuals affiliated with any
> > > entity with which I am affiliated
> > nor
> > > those of the entities themselves.
> > >
> > > On Tue, Dec 13, 2011 at 4:33 PM, LARRY SORENSEN
> > > <lsorensen25@msn.com>
> > wrote:
> > >
> > > > Does anyone have some sample programs using ESQL/C with a couple
> > > > of database connections (two different databases)? I need to
> > > > open a SELECT cursor
> > from
> > > > one
> > > > database table and use the results on each FETCH INTO to UNLOAD
> > > > data
> > from
> > > > another database table. As I SELECT INTO from one database
> > > > table, I
> > then
> > > > UNLOAD from another database and table.> > > >
> > > > Thanks
> > > >
> > > > Larry
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
>
****************************************************************************
***
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --14dae9340a51b9a1cb04b4001aa0
> > >
> > >
> > >
> >
> >
>
****************************************************************************
***
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
> >
>
****************************************************************************
***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --14dae934115112661b04b401478f
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
On Mon, Dec 19, 2011 at 20:42, Larry Sorensen <lsorensen25@msn.com> wrote:
> Another dilemma -
>
> I am looking for the easiest way to do the following in an automated
> fashion, preferably in an ESQL/C program, but possibly using an sql script
> as well: (two different databases on the same server)
>
> UNLOAD TO 'somefile' select * from database1.table1 where column1 in
> (select
> column 2 from database2.table2)>
Assuming you can connect to both databases, change the '.' (dot) into a ':'
(colon):
UNLOAD TO 'somefile' SELECT * FROM database1:table1 WHERE column1 IN
(SELECT Column2 FROM database2:table2);
Run with DB-Access, SQLCMD, SQSL, ... any language that supports UNLOAD (a
range of options which does not include ESQL/C or SPL).
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--14dae93406cfbbf81504b4811fac
UNLOAD is a dbaccess command verb, the engine does not recognize it so you
can't do that from ESQL/C unless you write your own unload module in 'C'
yourself. From an SQL script, executed by dbaccess, no problem.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Mon, Dec 19, 2011 at 11:42 PM, Larry Sorensen <lsorensen25@msn.com>wrote:
> Another dilema -
>
> I am looking for the easiest way to do the following in an automated
> fashion, preferably in an ESQL/C program, but possibly using an sql script
> as well: (two different databases on the same server)
>
> UNLOAD TO 'somefile' select * from database1.table1 where column1 in
> (select
> column 2 from database2.table2)>
> Thanks.
>
> Larry
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> LARRY
> SORENSEN
> Sent: Wednesday, December 14, 2011 7:57 AM
> To: ids@iiug.org
> Subject: RE: ESQL/C - Multiple cursors open to differen.... [25612]
>
> Is the same information contained in the utils_ak program? I waited until I
> was onsite to try and download your program and ftp is turned blocked. I
> can
> use view to look at utils_ak, however.
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: ESQL/C - Multiple cursors open to differen.... [25602]
> > Date: Tue, 13 Dec 2011 18:04:51 -0500
> >
> > At the IIUG site (www.iiug.org/software/software_index.html) under
> > both the "Database Administration" and the "ESQL/C -ESQL/C Environment
> > or ESQL/C Source" sections (the latter list is shorter). Download the
> package:
> > utils2_ak.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
> > opinions and do not reflect on my employer, Advanced DataTools, the
> > IIUG, nor any other organization with which I am associated either
> > explicitly, implicitly, or by inference. Neither do those opinions
> > reflect those of other individuals affiliated with any entity with
> > which I am affiliated nor those of the entities themselves.
> >
> > On Tue, Dec 13, 2011 at 5:47 PM, LARRY SORENSEN <lsorensen25@msn.com>
> wrote:
> >
> > > And your software is available at the IIUG site, or is it somewhere
> else?
> > >
> > > > To: ids@iiug.org
> > > > From: art.kagel@gmail.com
> > > > Subject: Re: ESQL/C - Multiple cursors open to differen....
> > > > [25600]
> > > > Date: Tue, 13 Dec 2011 16:40:56 -0500
> > > >
> > > > Oh, the secret to having cursors open in multiple connections is
> > > > to
> > > create
> > > > the connections with the "WITH CONCURRENT TRANSACTIONS" clause,
> otherwise
> > > > the cursors in a connection will close when you switch away from
> > > > it to another connection!
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel
> > > > Advanced DataTools (www.advancedatatools.com)
> > > > Blog: http://informix-myview.blogspot.com/
> > > >
> > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > > opinions and do not reflect on my employer, Advanced DataTools,
> > > > the IIUG, nor any other organization with which I am associated
> > > > either explicitly, implicitly, or by inference. Neither do those
> > > > opinions reflect those of other individuals affiliated with any
> > > > entity with which I am affiliated
> > > nor
> > > > those of the entities themselves.
> > > >
> > > > On Tue, Dec 13, 2011 at 4:33 PM, LARRY SORENSEN
> > > > <lsorensen25@msn.com>
> > > wrote:
> > > >
> > > > > Does anyone have some sample programs using ESQL/C with a couple
> > > > > of database connections (two different databases)? I need to
> > > > > open a SELECT cursor
> > > from
> > > > > one
> > > > > database table and use the results on each FETCH INTO to UNLOAD
> > > > > data
> > > from
> > > > > another database table. As I SELECT INTO from one database
> > > > > table, I
> > > then
> > > > > UNLOAD from another database and table.> > > > >
> > > > > Thanks
> > > > >
> > > > > Larry
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> >
>
> ****************************************************************************
> ***
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > >
> > > >
> > > > --14dae9340a51b9a1cb04b4001aa0
> > > >
> > > >
> > > >
> > >
> > >
> >
>
> ****************************************************************************
> ***
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > >
> > >
> > >
> > >
> >
>
> ****************************************************************************
> ***
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --14dae934115112661b04b401478f
> >
> >
> >
>
> ****************************************************************************
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec51a867237916004b483e94b
Art,
In reviewing your dbcopy.ec program to learn more about opening concurrent
connections to multiple databases, you use the DESCRIBE statement several
times to set up SQLDA structures to dynamically receive the SELECT data. I
have a question on how that works.
It appears that the DESCRIBE directive only gives you information on the
structure that you will need to received the data, and then you must use that
information to malloc the actual necessary space for receiving the data. Is
this accurate?
Larry
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: ESQL/C - Multiple cursors open to differen.... [25671]
> Date: Tue, 20 Dec 2011 05:56:00 -0500
>
> UNLOAD is a dbaccess command verb, the engine does not recognize it so you
> can't do that from ESQL/C unless you write your own unload module in 'C'
> yourself. From an SQL script, executed by dbaccess, no problem.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Mon, Dec 19, 2011 at 11:42 PM, Larry Sorensen <lsorensen25@msn.com>wrote:
>
> > Another dilema -
> >
> > I am looking for the easiest way to do the following in an automated
> > fashion, preferably in an ESQL/C program, but possibly using an sql script
> > as well: (two different databases on the same server)
> >
> > UNLOAD TO 'somefile' select * from database1.table1 where column1 in
> > (select
> > column 2 from database2.table2)> >
> > Thanks.
> >
> > Larry
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > LARRY
> > SORENSEN
> > Sent: Wednesday, December 14, 2011 7:57 AM
> > To: ids@iiug.org
> > Subject: RE: ESQL/C - Multiple cursors open to differen.... [25612]
> >
> > Is the same information contained in the utils_ak program? I waited until I
> > was onsite to try and download your program and ftp is turned blocked. I
> > can
> > use view to look at utils_ak, however.
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: ESQL/C - Multiple cursors open to differen.... [25602]
> > > Date: Tue, 13 Dec 2011 18:04:51 -0500
> > >
> > > At the IIUG site (www.iiug.org/software/software_index.html) under
> > > both the "Database Administration" and the "ESQL/C -ESQL/C Environment
> > > or ESQL/C Source" sections (the latter list is shorter). Download the
> > package:
> > > utils2_ak.
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> > > opinions and do not reflect on my employer, Advanced DataTools, the
> > > IIUG, nor any other organization with which I am associated either
> > > explicitly, implicitly, or by inference. Neither do those opinions
> > > reflect those of other individuals affiliated with any entity with
> > > which I am affiliated nor those of the entities themselves.
> > >
> > > On Tue, Dec 13, 2011 at 5:47 PM, LARRY SORENSEN <lsorensen25@msn.com>
> > wrote:
> > >
> > > > And your software is available at the IIUG site, or is it somewhere
> > else?
> > > >
> > > > > To: ids@iiug.org
> > > > > From: art.kagel@gmail.com
> > > > > Subject: Re: ESQL/C - Multiple cursors open to differen....
> > > > > [25600]
> > > > > Date: Tue, 13 Dec 2011 16:40:56 -0500
> > > > >
> > > > > Oh, the secret to having cursors open in multiple connections is
> > > > > to
> > > > create
> > > > > the connections with the "WITH CONCURRENT TRANSACTIONS" clause,
> > otherwise
> > > > > the cursors in a connection will close when you switch away from
> > > > > it to another connection!
> > > > >
> > > > > Art
> > > > >
> > > > > Art S. Kagel
> > > > > Advanced DataTools (www.advancedatatools.com)
> > > > > Blog: http://informix-myview.blogspot.com/
> > > > >
> > > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > > > opinions and do not reflect on my employer, Advanced DataTools,
> > > > > the IIUG, nor any other organization with which I am associated
> > > > > either explicitly, implicitly, or by inference. Neither do those
> > > > > opinions reflect those of other individuals affiliated with any
> > > > > entity with which I am affiliated
> > > > nor
> > > > > those of the entities themselves.
> > > > >
> > > > > On Tue, Dec 13, 2011 at 4:33 PM, LARRY SORENSEN
> > > > > <lsorensen25@msn.com>
> > > > wrote:
> > > > >
> > > > > > Does anyone have some sample programs using ESQL/C with a couple
> > > > > > of database connections (two different databases)? I need to
> > > > > > open a SELECT cursor
> > > > from
> > > > > > one
> > > > > > database table and use the results on each FETCH INTO to UNLOAD
> > > > > > data
> > > > from
> > > > > > another database table. As I SELECT INTO from one database
> > > > > > table, I
> > > > then
> > > > > > UNLOAD from another database and table.> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > > Larry
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > >
> >
> >
****************************************************************************
> > ***
> > > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > > >
> > > > > >
> > > > >
> > > > > --14dae9340a51b9a1cb04b4001aa0
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> >
> >
****************************************************************************
> > ***
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
****************************************************************************
> > ***
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --14dae934115112661b04b401478f
> > >
> > >
> > >
> >
> >
****************************************************************************
> > ***
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
****************************************************************************
> > ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >@@
On Fri, Dec 23, 2011 at 08:29, LARRY SORENSEN <lsorensen25@msn.com> wrote: > Art, > > In reviewing your dbcopy.ec program to learn more about opening concurrent > connections to multiple databases, you use the DESCRIBE statement several > times to set up SQLDA structures to dynamically receive the SELECT data. I > have a question on how that works. > > It appears that the DESCRIBE directive only gives you information on the > structure that you will need to received the data, and then you must use > that > information to malloc the actual necessary space for receiving the data. Is > this accurate? > Yes, that is basically accurate. The sqlda structure returned by DESCRIBE tells what would be returned, but does not allocate the data space to receive those values. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --e89a8f235589935e9404b4c4fbb1
Larry,
The use of descriptors is not required to handle multiple open cursors and
multiple connections. The purpose of descriptors is to make it possible to
write programs when you don't know before hand what data types the table
will contain. For instance, the cdr utility makes heavy usage of
descriptors because when a users makes a request to check the data on a
table --- well we didn't code for that specific table when we wrote the
code. So we have to explore - and that means that we have to use
descriptors for just about everything.
While operations using descriptors allows for a high degree of dynamic
flexibility, they also require a bit more effort to use.
M.P.
From: "LARRY SORENSEN" <lsorensen25@msn.com>
To: ids@iiug.org
Date: 12/23/2011 10:30 AM
Subject: RE: ESQL/C - Multiple cursors open to differen.... [25723]
Sent by: ids-bounces@iiug.org
Art,
In reviewing your dbcopy.ec program to learn more about opening concurrent
connections to multiple databases, you use the DESCRIBE statement several
times to set up SQLDA structures to dynamically receive the SELECT data. I
have a question on how that works.
It appears that the DESCRIBE directive only gives you information on the
structure that you will need to received the data, and then you must use
that
information to malloc the actual necessary space for receiving the data. Is
this accurate?
Larry
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: ESQL/C - Multiple cursors open to differen.... [25671]
> Date: Tue, 20 Dec 2011 05:56:00 -0500
>
> UNLOAD is a dbaccess command verb, the engine does not recognize it so
you
> can't do that from ESQL/C unless you write your own unload module in 'C'
> yourself. From an SQL script, executed by dbaccess, no problem.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated
nor
> those of the entities themselves.
>
> On Mon, Dec 19, 2011 at 11:42 PM, Larry Sorensen
<lsorensen25@msn.com>wrote:
>
> > Another dilema -
> >
> > I am looking for the easiest way to do the following in an automated
> > fashion, preferably in an ESQL/C program, but possibly using an sql
script
> > as well: (two different databases on the same server)
> >
> > UNLOAD TO 'somefile' select * from database1.table1 where column1 in
> > (select
> > column 2 from database2.table2)> >
> > Thanks.
> >
> > Larry
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > LARRY
> > SORENSEN
> > Sent: Wednesday, December 14, 2011 7:57 AM
> > To: ids@iiug.org
> > Subject: RE: ESQL/C - Multiple cursors open to differen.... [25612]
> >
> > Is the same information contained in the utils_ak program? I waited
until
I
> > was onsite to try and download your program and ftp is turned blocked.
I
> > can
> > use view to look at utils_ak, however.
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: ESQL/C - Multiple cursors open to differen.... [25602]
> > > Date: Tue, 13 Dec 2011 18:04:51 -0500
> > >
> > > At the IIUG site (www.iiug.org/software/software_index.html) under
> > > both the "Database Administration" and the "ESQL/C -ESQL/C
Environment
> > > or ESQL/C Source" sections (the latter list is shorter). Download the
> > package:
> > > utils2_ak.
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> > > opinions and do not reflect on my employer, Advanced DataTools, the
> > > IIUG, nor any other organization with which I am associated either
> > > explicitly, implicitly, or by inference. Neither do those opinions
> > > reflect those of other individuals affiliated with any entity with
> > > which I am affiliated nor those of the entities themselves.
> > >
> > > On Tue, Dec 13, 2011 at 5:47 PM, LARRY SORENSEN <lsorensen25@msn.com>
> > wrote:
> > >
> > > > And your software is available at the IIUG site, or is it somewhere
> > else?
> > > >
> > > > > To: ids@iiug.org
> > > > > From: art.kagel@gmail.com
> > > > > Subject: Re: ESQL/C - Multiple cursors open to differen....
> > > > > [25600]
> > > > > Date: Tue, 13 Dec 2011 16:40:56 -0500
> > > > >
> > > > > Oh, the secret to having cursors open in multiple connections is
> > > > > to
> > > > create
> > > > > the connections with the "WITH CONCURRENT TRANSACTIONS" clause,
> > otherwise
> > > > > the cursors in a connection will close when you switch away from
> > > > > it to another connection!
> > > > >
> > > > > Art
> > > > >
> > > > > Art S. Kagel
> > > > > Advanced DataTools (www.advancedatatools.com)
> > > > > Blog: http://informix-myview.blogspot.com/
> > > > >
> > > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > > > opinions and do not reflect on my employer, Advanced DataTools,
> > > > > the IIUG, nor any other organization with which I am associated
> > > > > either explicitly, implicitly, or by inference. Neither do those
> > > > > opinions reflect those of other individuals affiliated with any
> > > > > entity with which I am affiliated
> > > > nor
> > > > > those of the entities themselves.
> > > > >
> > > > > On Tue, Dec 13, 2011 at 4:33 PM, LARRY SORENSEN
> > > > > <lsorensen25@msn.com>
> > > > wrote:
> > > > >
> > > > > > Does anyone have some sample programs using ESQL/C with a
couple
> > > > > > of database connections (two different databases)? I need to
> > > > > > open a SELECT cursor
> > > > from
> > > > > > one
> > > > > > database table and use the results on each FETCH INTO to UNLOAD
> > > > > > data
> > > > from
> > > > > > another database table. As I SELECT INTO from one database
> > > > > > table, I
> > > > then
> > > > > > UNLOAD from another database and table.> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > > Larry
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > >
> >
> >
****************************************************************************
> > ***
> > > > > > Forum Note: Use "Reply" to post a response in the discussion
forum.
> > > > > >
> > > > > >
> > > > >
> > > > > --14dae9340a51b9a1cb04b4001aa0
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> >
> >
****************************************************************************
> > ***
> > > > > Forum Note:
Yes. If you need to be completely dynamic, as dbcopy does, DESCRIBE tells
you the datatypes, etc. and you either mall space and attach it to the
sqlda structure or attach existing static memory, yes.
Art
On Dec 23, 2011 11:29 AM, "LARRY SORENSEN" <lsorensen25@msn.com> wrote:
> Art,
>
> In reviewing your dbcopy.ec program to learn more about opening concurrent
> connections to multiple databases, you use the DESCRIBE statement several
> times to set up SQLDA structures to dynamically receive the SELECT data. I
> have a question on how that works.
>
> It appears that the DESCRIBE directive only gives you information on the
> structure that you will need to received the data, and then you must use
> that
> information to malloc the actual necessary space for receiving the data. Is
> this accurate?
>
> Larry
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: ESQL/C - Multiple cursors open to differen.... [25671]
> > Date: Tue, 20 Dec 2011 05:56:00 -0500
> >
> > UNLOAD is a dbaccess command verb, the engine does not recognize it so
> you
> > can't do that from ESQL/C unless you write your own unload module in 'C'
> > yourself. From an SQL script, executed by dbaccess, no problem.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
> nor
> > those of the entities themselves.
> >
> > On Mon, Dec 19, 2011 at 11:42 PM, Larry Sorensen <lsorensen25@msn.com
> >wrote:
> >
> > > Another dilema -
> > >
> > > I am looking for the easiest way to do the following in an automated
> > > fashion, preferably in an ESQL/C program, but possibly using an sql
> script
> > > as well: (two different databases on the same server)
> > >
> > > UNLOAD TO 'somefile' select * from database1.table1 where column1 in
> > > (select
> > > column 2 from database2.table2)> > >
> > > Thanks.
> > >
> > > Larry
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > > LARRY
> > > SORENSEN
> > > Sent: Wednesday, December 14, 2011 7:57 AM
> > > To: ids@iiug.org
> > > Subject: RE: ESQL/C - Multiple cursors open to differen.... [25612]
> > >
> > > Is the same information contained in the utils_ak program? I waited
> until
> I
> > > was onsite to try and download your program and ftp is turned blocked.
> I
> > > can
> > > use view to look at utils_ak, however.
> > >
> > > > To: ids@iiug.org
> > > > From: art.kagel@gmail.com
> > > > Subject: Re: ESQL/C - Multiple cursors open to differen.... [25602]
> > > > Date: Tue, 13 Dec 2011 18:04:51 -0500
> > > >
> > > > At the IIUG site (www.iiug.org/software/software_index.html) under
> > > > both the "Database Administration" and the "ESQL/C -ESQL/C
> Environment
> > > > or ESQL/C Source" sections (the latter list is shorter). Download the
> > > package:
> > > > utils2_ak.
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel
> > > > Advanced DataTools (www.advancedatatools.com)
> > > > Blog: http://informix-myview.blogspot.com/
> > > >
> > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > > opinions and do not reflect on my employer, Advanced DataTools, the
> > > > IIUG, nor any other organization with which I am associated either
> > > > explicitly, implicitly, or by inference. Neither do those opinions
> > > > reflect those of other individuals affiliated with any entity with
> > > > which I am affiliated nor those of the entities themselves.
> > > >
> > > > On Tue, Dec 13, 2011 at 5:47 PM, LARRY SORENSEN <lsorensen25@msn.com
> >
> > > wrote:
> > > >
> > > > > And your software is available at the IIUG site, or is it somewhere
> > > else?
> > > > >
> > > > > > To: ids@iiug.org
> > > > > > From: art.kagel@gmail.com
> > > > > > Subject: Re: ESQL/C - Multiple cursors open to differen....
> > > > > > [25600]
> > > > > > Date: Tue, 13 Dec 2011 16:40:56 -0500
> > > > > >
> > > > > > Oh, the secret to having cursors open in multiple connections is
> > > > > > to
> > > > > create
> > > > > > the connections with the "WITH CONCURRENT TRANSACTIONS" clause,
> > > otherwise
> > > > > > the cursors in a connection will close when you switch away from
> > > > > > it to another connection!
> > > > > >
> > > > > > Art
> > > > > >
> > > > > > Art S. Kagel
> > > > > > Advanced DataTools (www.advancedatatools.com)
> > > > > > Blog: http://informix-myview.blogspot.com/
> > > > > >
> > > > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > > > > opinions and do not reflect on my employer, Advanced DataTools,
> > > > > > the IIUG, nor any other organization with which I am associated
> > > > > > either explicitly, implicitly, or by inference. Neither do those
> > > > > > opinions reflect those of other individuals affiliated with any
> > > > > > entity with which I am affiliated
> > > > > nor
> > > > > > those of the entities themselves.
> > > > > >
> > > > > > On Tue, Dec 13, 2011 at 4:33 PM, LARRY SORENSEN
> > > > > > <lsorensen25@msn.com>
> > > > > wrote:
> > > > > >
> > > > > > > Does anyone have some sample programs using ESQL/C with a
> couple
> > > > > > > of database connections (two different databases)? I need to
> > > > > > > open a SELECT cursor
> > > > > from
> > > > > > > one
> > > > > > > database table and use the results on each FETCH INTO to UNLOAD
> > > > > > > data
> > > > > from
> > > > > > > another database table. As I SELECT INTO from one database
> > > > > > > table, I
> > > > > then
> > > > > > > UNLOAD from another database and table.> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > > > Larry
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
>
> ****************************************************************************
> > > ***
> > > > > > > Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > > > > >
> > > > > > >
> > > > > >
> > > > > > --14dae9340a51b9a1cb04b4001aa0
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
>
> ****************************************************************************
> > > ***
> > > > > > Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
>
> ****************************************************************************
> > > ***
> > > > > Forum Note: Use "Reply" to p
Question on data types:
It appears that the following data type declarations are legal in ESQL/C. Is
this correct?
money(3,2) some_variable;
datetime year to fraction(2) another_variable;
Thank you for your continued help.
Larry
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: RE: ESQL/C - Multiple cursors open to differen.... [25726]
> Date: Fri, 23 Dec 2011 14:41:43 -0500
>
> Yes. If you need to be completely dynamic, as dbcopy does, DESCRIBE tells
> you the datatypes, etc. and you either mall space and attach it to the
> sqlda structure or attach existing static memory, yes.
>
> Art
> On Dec 23, 2011 11:29 AM, "LARRY SORENSEN" <lsorensen25@msn.com> wrote:
>
> > Art,
> >
> > In reviewing your dbcopy.ec program to learn more about opening concurrent
> > connections to multiple databases, you use the DESCRIBE statement several
> > times to set up SQLDA structures to dynamically receive the SELECT data. I
> > have a question on how that works.
> >
> > It appears that the DESCRIBE directive only gives you information on the
> > structure that you will need to received the data, and then you must use
> > that
> > information to malloc the actual necessary space for receiving the data. Is
> > this accurate?
> >
> > Larry
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: ESQL/C - Multiple cursors open to differen.... [25671]
> > > Date: Tue, 20 Dec 2011 05:56:00 -0500
> > >
> > > UNLOAD is a dbaccess command verb, the engine does not recognize it so
> > you
> > > can't do that from ESQL/C unless you write your own unload module in 'C'
> > > yourself. From an SQL script, executed by dbaccess, no problem.
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > > other organization with which I am associated either explicitly,
> > > implicitly, or by inference. Neither do those opinions reflect those of
> > > other individuals affiliated with any entity with which I am affiliated
> > nor
> > > those of the entities themselves.
> > >
> > > On Mon, Dec 19, 2011 at 11:42 PM, Larry Sorensen <lsorensen25@msn.com
> > >wrote:
> > >
> > > > Another dilema -
> > > >
> > > > I am looking for the easiest way to do the following in an automated
> > > > fashion, preferably in an ESQL/C program, but possibly using an sql
> > script
> > > > as well: (two different databases on the same server)
> > > >
> > > > UNLOAD TO 'somefile' select * from database1.table1 where column1 in
> > > > (select
> > > > column 2 from database2.table2)> > > >
> > > > Thanks.
> > > >
> > > > Larry
> > > >
> > > > -----Original Message-----
> > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > > > LARRY
> > > > SORENSEN
> > > > Sent: Wednesday, December 14, 2011 7:57 AM
> > > > To: ids@iiug.org
> > > > Subject: RE: ESQL/C - Multiple cursors open to differen.... [25612]
> > > >
> > > > Is the same information contained in the utils_ak program? I waited
> > until
> > I
> > > > was onsite to try and download your program and ftp is turned blocked.
> > I
> > > > can
> > > > use view to look at utils_ak, however.
> > > >
> > > > > To: ids@iiug.org
> > > > > From: art.kagel@gmail.com
> > > > > Subject: Re: ESQL/C - Multiple cursors open to differen.... [25602]
> > > > > Date: Tue, 13 Dec 2011 18:04:51 -0500
> > > > >
> > > > > At the IIUG site (www.iiug.org/software/software_index.html) under
> > > > > both the "Database Administration" and the "ESQL/C -ESQL/C
> > Environment
> > > > > or ESQL/C Source" sections (the latter list is shorter). Download the
> > > > package:
> > > > > utils2_ak.
> > > > >
> > > > > Art
> > > > >
> > > > > Art S. Kagel
> > > > > Advanced DataTools (www.advancedatatools.com)
> > > > > Blog: http://informix-myview.blogspot.com/
> > > > >
> > > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > > > opinions and do not reflect on my employer, Advanced DataTools, the
> > > > > IIUG, nor any other organization with which I am associated either
> > > > > explicitly, implicitly, or by inference. Neither do those opinions
> > > > > reflect those of other individuals affiliated with any entity with
> > > > > which I am affiliated nor those of the entities themselves.
> > > > >
> > > > > On Tue, Dec 13, 2011 at 5:47 PM, LARRY SORENSEN <lsorensen25@msn.com
> > >
> > > > wrote:
> > > > >
> > > > > > And your software is available at the IIUG site, or is it somewhere
> > > > else?
> > > > > >
> > > > > > > To: ids@iiug.org
> > > > > > > From: art.kagel@gmail.com
> > > > > > > Subject: Re: ESQL/C - Multiple cursors open to differen....
> > > > > > > [25600]
> > > > > > > Date: Tue, 13 Dec 2011 16:40:56 -0500
> > > > > > >
> > > > > > > Oh, the secret to having cursors open in multiple connections is
> > > > > > > to
> > > > > > create
> > > > > > > the connections with the "WITH CONCURRENT TRANSACTIONS" clause,
> > > > otherwise
> > > > > > > the cursors in a connection will close when you switch away from
> > > > > > > it to another connection!
> > > > > > >
> > > > > > > Art
> > > > > > >
> > > > > > > Art S. Kagel
> > > > > > > Advanced DataTools (www.advancedatatools.com)
> > > > > > > Blog: http://informix-myview.blogspot.com/
> > > > > > >
> > > > > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > > > > > opinions and do not reflect on my employer, Advanced DataTools,
> > > > > > > the IIUG, nor any other organization with which I am associated
> > > > > > > either explicitly, implicitly, or by inference. Neither do those
> > > > > > > opinions reflect those of other individuals affiliated with any
> > > > > > > entity with which I am affiliated
> > > > > > nor
> > > > > > > those of the entities themselves.
> > > > > > >
> > > > > > > On Tue, Dec 13, 2011 at 4:33 PM, LARRY SORENSEN
> > > > > > > <lsorensen25@msn.com>
> > > > > > wrote:
> > > > > > >
> > > > > > > > Does anyone have some sample programs using ESQL/C with a
> > couple
> > > > > > > > of database connections (two different databases)? I need to
> > > > > > > > open a SELECT cursor
> > > > > > from
> > > > > > > > one
> > > > > > > > database table and use the results on each FETCH INTO to UNLOAD
> > > > > > > > data
> > > > > > from
> > > > > > > > another database table. As I SELECT INTO from one database
> > > > > > > > table, I
> > > > > > then
> > > > > > > > UNLOAD from another database and table.> > > > > > > >
> > > > > > > > Thanks
> > > > > > > >
> > > > > > > > Larry
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> >
> >
*****************************************************************
Almost. You can declare DATETIME variables with a precision clause, so
DATETIME YEAR TO FRACTION(2) (or datetime year to fraction(2)) is legal,
but DECIMAL type host variables are just declared DECIMAL (or decimal). If
you are fetching data from a decimal column the precision of the dec_t
structure underlying the host variable will be set from the column. If you
are loading data into it using one of the decimal manipulation functions
you can set the fields in the dec_t structure yourself to set the precision
you want. If you are doing dynamic SQL and want a different precision than
the source data, then you can set the precision into the sqlda structure
(or DESCRIPTOR) and the engine will convert the original datatype and
precision of the returning data to the specified precision for you.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Dec 28, 2011 at 10:49 AM, LARRY SORENSEN <lsorensen25@msn.com>wrote:
> Question on data types:
>
> It appears that the following data type declarations are legal in ESQL/C.
> Is
> this correct?
>
> money(3,2) some_variable;
> datetime year to fraction(2) another_variable;
>
> Thank you for your continued help.
>
> Larry
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: RE: ESQL/C - Multiple cursors open to differen.... [25726]
> > Date: Fri, 23 Dec 2011 14:41:43 -0500
> >
> > Yes. If you need to be completely dynamic, as dbcopy does, DESCRIBE tells
> > you the datatypes, etc. and you either mall space and attach it to the
> > sqlda structure or attach existing static memory, yes.
> >
> > Art
> > On Dec 23, 2011 11:29 AM, "LARRY SORENSEN" <lsorensen25@msn.com> wrote:
> >
> > > Art,
> > >
> > > In reviewing your dbcopy.ec program to learn more about opening
> concurrent
> > > connections to multiple databases, you use the DESCRIBE statement
> several
> > > times to set up SQLDA structures to dynamically receive the SELECT
> data. I
> > > have a question on how that works.
> > >
> > > It appears that the DESCRIBE directive only gives you information on
> the
> > > structure that you will need to received the data, and then you must
> use
> > > that
> > > information to malloc the actual necessary space for receiving the
> data.
> Is
> > > this accurate?
> > >
> > > Larry
> > >
> > > > To: ids@iiug.org
> > > > From: art.kagel@gmail.com
> > > > Subject: Re: ESQL/C - Multiple cursors open to differen.... [25671]
> > > > Date: Tue, 20 Dec 2011 05:56:00 -0500
> > > >
> > > > UNLOAD is a dbaccess command verb, the engine does not recognize it
> so
> > > you
> > > > can't do that from ESQL/C unless you write your own unload module in
> 'C'
> > > > yourself. From an SQL script, executed by dbaccess, no problem.
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel
> > > > Advanced DataTools (www.advancedatatools.com)
> > > > Blog: http://informix-myview.blogspot.com/
> > > >
> > > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor
> any
> > > > other organization with which I am associated either explicitly,
> > > > implicitly, or by inference. Neither do those opinions reflect those
> of
> > > > other individuals affiliated with any entity with which I am
> affiliated
> > > nor
> > > > those of the entities themselves.
> > > >
> > > > On Mon, Dec 19, 2011 at 11:42 PM, Larry Sorensen <
> lsorensen25@msn.com
> > > >wrote:
> > > >
> > > > > Another dilema -
> > > > >
> > > > > I am looking for the easiest way to do the following in an
> automated
> > > > > fashion, preferably in an ESQL/C program, but possibly using an sql
> > > script
> > > > > as well: (two different databases on the same server)
> > > > >
> > > > > UNLOAD TO 'somefile' select * from database1.table1 where column1
> in
> > > > > (select
> > > > > column 2 from database2.table2)> > > > >
> > > > > Thanks.
> > > > >
> > > > > Larry
> > > > >
> > > > > -----Original Message-----
> > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> Behalf Of
> > > > > LARRY
> > > > > SORENSEN
> > > > > Sent: Wednesday, December 14, 2011 7:57 AM
> > > > > To: ids@iiug.org
> > > > > Subject: RE: ESQL/C - Multiple cursors open to differen.... [25612]
> > > > >
> > > > > Is the same information contained in the utils_ak program? I waited
> > > until
> > > I
> > > > > was onsite to try and download your program and ftp is turned
> blocked.
> > > I
> > > > > can
> > > > > use view to look at utils_ak, however.
> > > > >
> > > > > > To: ids@iiug.org
> > > > > > From: art.kagel@gmail.com
> > > > > > Subject: Re: ESQL/C - Multiple cursors open to differen....
> [25602]
> > > > > > Date: Tue, 13 Dec 2011 18:04:51 -0500
> > > > > >
> > > > > > At the IIUG site (www.iiug.org/software/software_index.html)
> under
> > > > > > both the "Database Administration" and the "ESQL/C -ESQL/C
> > > Environment
> > > > > > or ESQL/C Source" sections (the latter list is shorter). Download
> the
> > > > > package:
> > > > > > utils2_ak.
> > > > > >
> > > > > > Art
> > > > > >
> > > > > > Art S. Kagel
> > > > > > Advanced DataTools (www.advancedatatools.com)
> > > > > > Blog: http://informix-myview.blogspot.com/
> > > > > >
> > > > > > Disclaimer: Please keep in mind that my own opinions are my own
> > > > > > opinions and do not reflect on my employer, Advanced DataTools,
> the
> > > > > > IIUG, nor any other organization with which I am associated
> either
> > > > > > explicitly, implicitly, or by inference. Neither do those
> opinions
> > > > > > reflect those of other individuals affiliated with any entity
> with
> > > > > > which I am affiliated nor those of the entities themselves.
> > > > > >
> > > > > > On Tue, Dec 13, 2011 at 5:47 PM, LARRY SORENSEN <
> lsorensen25@msn.com
> > > >
> > > > > wrote:
> > > > > >
> > > > > > > And your software is available at the IIUG site, or is it
> somewhere
> > > > > else?
> > > > > > >
> > > > > > > > To: ids@iiug.org
> > > > > > > > From: art.kagel@gmail.com
> > > > > > > > Subject: Re: ESQL/C - Multiple cursors open to differen....
> > > > > > > > [25600]
> > > > > > > > Date: Tue, 13 Dec 2011 16:40:56 -0500
> > > > > > > >
> > > > > > > > Oh, the secret to having cursors open in multiple
> connections is
> > > > > > > > to
> > > > > > > create
> > > > > > > > the connections with the "WITH CONCURRENT TRANSACTIONS"
> clause,
> > > > > otherwise
> > > > > > > > the cursors
On Wed, Dec 28, 2011 at 07:49, LARRY SORENSEN <lsorensen25@msn.com> wrote:
> Question on data types:
>
> It appears that the following data type declarations are legal in ESQL/C.
> Is
> this correct?
>
> money(3,2) some_variable;
> datetime year to fraction(2) another_variable;
>
Empirically: YES.
xx.ec:
$ money(6,2) x;
$ decimal(14,4) y;
xx.c:
#include <sqlhdr.h>
#include <sqliapi.h>
/*
* $ money(6,2) x;
*/
#line 1 "xx.ec"
dec_t x;
/*
* $ decimal(14,4) y;
*/
#line 2 "xx.ec"
dec_t y;
#line 2 "xx.ec"
Note that there is nothing in those variables that actually limits them to
being 'money(6,2)' or `decimal(14,4)'; that requires more manipulation of
the values. And it might be that you'd see those limits imposed in an SQL
statement.
BUT: your question was 'can you write such declarations' and the answer
(by demonstration - on MacOS X 10.7.2 with ESQL/C 3.70.FC2) is YES.
PS: Please trim the email trail to that which is relevant. Where there is
nothing relevant from the trail, think about starting a new thread.
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--90e6ba6e836c5b41c204b529932a