Thread: ECPG and dynamic SQL
I'm porting an application from a proprietary database format to PostgreSQL using ECPG. My problem is that the application uses a single function to get a record from the database. Based upon which table is being accessed, it dynamically calculates column sizes and returns the record in a structure. Creating the query itself is fairly straightforward, but I am unable to figure out how to retrieve the _results_ of the query as I have no place to store them (since I don't know what they will be until the function is called I can't use a EXEC SQL DECLARE section). It seems Oracle's sqlda would be a possible solution, but I understand that isn't implemented in PostgreSQL. What is the best way to store the results of a dynamic query without knowing what the results will be beforehand (using ECPG, if possible)? I'm using PostgreSQL 7.2.1 on SCO OpenSwerver. -- Cliff Wells, Software Engineer Logiplex Corporation (www.logiplex.net) (503) 978-6726 x308 (800) 735-0555 x308
The app GtkSQL does what you need, but it is implimented in libpq. It will give you a general idea of how to do it. Do a google search. There is another app that is similar (libpq also) a little less complex, so easier to follow, called dbui-0.4.0 A Google search will give you a URL cheers Jim Parker On 29 Jul 2002, Cliff Wells wrote: > Date: 29 Jul 2002 15:23:30 -0700 > To: pgsql-interfaces <pgsql-interfaces@postgresql.org> > From: Cliff Wells <LogiplexSoftware@earthlink.net> > Sender: pgsql-interfaces-owner@postgresql.org > Subject: [INTERFACES] ECPG and dynamic SQL > > I'm porting an application from a proprietary database format to > PostgreSQL using ECPG. My problem is that the application uses a single > function to get a record from the database. Based upon which table is > being accessed, it dynamically calculates column sizes and returns the > record in a structure. Creating the query itself is fairly > straightforward, but I am unable to figure out how to retrieve the > _results_ of the query as I have no place to store them (since I don't > know what they will be until the function is called I can't use a EXEC > SQL DECLARE section). > > It seems Oracle's sqlda would be a possible solution, but I understand > that isn't implemented in PostgreSQL. > > What is the best way to store the results of a dynamic query without > knowing what the results will be beforehand (using ECPG, if possible)? > > I'm using PostgreSQL 7.2.1 on SCO OpenSwerver. > > -- > Cliff Wells, Software Engineer > Logiplex Corporation (www.logiplex.net) > (503) 978-6726 x308 (800) 735-0555 x308 > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Mon, 2002-07-29 at 16:37, Jim Parker wrote: > The app GtkSQL does what you need, but it is implimented in libpq. It will > give you a general idea of how to do it. > > Do a google search. > > There is another app that is similar (libpq also) a little less complex, so > easier to follow, called dbui-0.4.0 A Google search will give you a URL Yeah, I was afraid there was no way to do it using ecpg... oh well. Thanks for the info. > cheers > Jim Parker > > On 29 Jul 2002, Cliff Wells wrote: > > Date: 29 Jul 2002 15:23:30 -0700 > > To: pgsql-interfaces <pgsql-interfaces@postgresql.org> > > From: Cliff Wells <LogiplexSoftware@earthlink.net> > > Sender: pgsql-interfaces-owner@postgresql.org > > Subject: [INTERFACES] ECPG and dynamic SQL > > > > I'm porting an application from a proprietary database format to > > PostgreSQL using ECPG. My problem is that the application uses a single > > function to get a record from the database. Based upon which table is > > being accessed, it dynamically calculates column sizes and returns the > > record in a structure. Creating the query itself is fairly > > straightforward, but I am unable to figure out how to retrieve the > > _results_ of the query as I have no place to store them (since I don't > > know what they will be until the function is called I can't use a EXEC > > SQL DECLARE section). > > > > It seems Oracle's sqlda would be a possible solution, but I understand > > that isn't implemented in PostgreSQL. > > > > What is the best way to store the results of a dynamic query without > > knowing what the results will be beforehand (using ECPG, if possible)? > > > > I'm using PostgreSQL 7.2.1 on SCO OpenSwerver. > > > > -- > > Cliff Wells, Software Engineer > > Logiplex Corporation (www.logiplex.net) > > (503) 978-6726 x308 (800) 735-0555 x308 > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Cliff Wells, Software Engineer Logiplex Corporation (www.logiplex.net) (503) 978-6726 x308 (800) 735-0555 x308
Cliff Wells schrieb:
> On Mon, 2002-07-29 at 16:37, Jim Parker wrote:
>
>>The app GtkSQL does what you need, but it is implimented in libpq. It will
>>give you a general idea of how to do it.
>>
>>Do a google search.
>>
>>There is another app that is similar (libpq also) a little less complex, so
>>easier to follow, called dbui-0.4.0 A Google search will give you a URL
>
>
> Yeah, I was afraid there was no way to do it using ecpg... oh well.
>
> Thanks for the info.
Of course there is a way to do it in ecpg! Ecpg simply does not provide
the (IMO) overloaded sqlda interface but uses the new lean get
descriptor interface (SQL standard). See interfaces/ecpg/tests/dyntest*
for an example.
Christof
PS: If you want to use this: you can even fetch into a char **results=0;
but have to free the array afterwards.
On Mon, 2002-08-05 at 08:45, Christof Petig wrote: > Cliff Wells schrieb: > > On Mon, 2002-07-29 at 16:37, Jim Parker wrote: > > > >>The app GtkSQL does what you need, but it is implimented in libpq. It will > >>give you a general idea of how to do it. > >> > >>Do a google search. > >> > >>There is another app that is similar (libpq also) a little less complex, so > >>easier to follow, called dbui-0.4.0 A Google search will give you a URL > > > > > > Yeah, I was afraid there was no way to do it using ecpg... oh well. > > > > Thanks for the info. > > Of course there is a way to do it in ecpg! Ecpg simply does not provide > the (IMO) overloaded sqlda interface but uses the new lean get > descriptor interface (SQL standard). See interfaces/ecpg/tests/dyntest* > for an example. > > Christof > > PS: If you want to use this: you can even fetch into a char **results=0; > but have to free the array afterwards. > Ah. Thank you very much! I don't know how I missed that. It seems the quickest way to have someone tell you how to do something is to suggest it can't be done ;) Regards, -- Cliff Wells, Software Engineer Logiplex Corporation (www.logiplex.net) (503) 978-6726 x308 (800) 735-0555 x308