Обсуждение: Getting the results columns before execution
Hi all,
From the OLE DB manual:
> The consumer can also get information about the columns of the rowset
> by calling IColumnsInfo::GetColumnInfo, which all providers must
> support, or IColumnsRowset::GetColumnsRowset, which providers may
> support, on the command.
>
> Before calling GetColumnInfo or GetColumnsRowset, the consumer must
> set the command text and, if the provider supports command
> preparation, prepare the command object.
>
The problem - it appears there is no requirement to execute the command
before asking about the rowset returned. Only setting the actual command
and preparing it. This is a problem, as merely preparing a command is
not enough to get the returned rows information.
I'm wondering whether anyone on this list(s) have an idea how to solve
this? I can start the command in a cursor, and not retrieve information
until requested. I can also execute the command with "where 0" appended
to it. Neither solutions seem very practical or good performance wise.
Shachar
--
Shachar Shemesh
Lingnu Open Systems Consulting
http://www.lingnu.com/
On Monday 19 January 2004 15:13, Shachar Shemesh wrote: > Hi all, > > From the OLE DB manual: > > The consumer can also get information about the columns of the rowset > > by calling IColumnsInfo::GetColumnInfo, which all providers must > > support, or IColumnsRowset::GetColumnsRowset, which providers may > > support, on the command. > > > > Before calling GetColumnInfo or GetColumnsRowset, the consumer must > > set the command text and, if the provider supports command > > preparation, prepare the command object. > > The problem - it appears there is no requirement to execute the command > before asking about the rowset returned. Only setting the actual command > and preparing it. This is a problem, as merely preparing a command is > not enough to get the returned rows information. > > I'm wondering whether anyone on this list(s) have an idea how to solve > this? I can start the command in a cursor, and not retrieve information > until requested. I can also execute the command with "where 0" appended > to it. Neither solutions seem very practical or good performance wise. > > Shachar LIMIT 0 will do ? I do it often when I need to know structure of the result without returning any rows. Regards !
Shachar Shemesh <psql@shemesh.biz> writes:
> ... This is a problem, as merely preparing a command is
> not enough to get the returned rows information.
Sure it is, if you are using the V3 protocol (new in 7.4).
See the Describe message types.
regards, tom lane
Tom Lane wrote:
>Shachar Shemesh <psql@shemesh.biz> writes:
>
>
>>... This is a problem, as merely preparing a command is
>>not enough to get the returned rows information.
>>
>>
>
>Sure it is, if you are using the V3 protocol (new in 7.4).
>See the Describe message types.
>
> regards, tom lane
>
>
Are those exposed through the libpq interface?
If not, is there a way to use the libpq interface for most operations,
and only for the rest to use whatever it is you are suggesting I use
(couldn't locate it in the docs, yet)
Funnily enough, mandating protocol version 3 is not my problem at the
moment. I may have to do so for a host of other reasons as well. I
guess, if you want to use an earlier version of the database, you will
have to make do with the ole db to ODBC interface.
Shachar
--
Shachar Shemesh
Lingnu Open Systems Consulting
http://www.lingnu.com/
Shachar Shemesh <psql@shemesh.biz> writes:
> Tom Lane wrote:
>> Sure it is, if you are using the V3 protocol (new in 7.4).
>> See the Describe message types.
> Are those exposed through the libpq interface?
No, because libpq doesn't really have any concept of prepared statements.
It would probably make sense to add some more API to libpq to allow
creation and interrogation of prepared statements, but no one's got
around to that yet.
regards, tom lane
Tom Lane wrote: > Shachar Shemesh <psql@shemesh.biz> writes: > > Tom Lane wrote: > >> Sure it is, if you are using the V3 protocol (new in 7.4). > >> See the Describe message types. > > > Are those exposed through the libpq interface? > > No, because libpq doesn't really have any concept of prepared statements. > It would probably make sense to add some more API to libpq to allow > creation and interrogation of prepared statements, but no one's got > around to that yet. Uh, if libpq doesn't know about it, how is that information accessed? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>>> Are those exposed through the libpq interface?
>>
>> No, because libpq doesn't really have any concept of prepared statements.
>> It would probably make sense to add some more API to libpq to allow
>> creation and interrogation of prepared statements, but no one's got
>> around to that yet.
> Uh, if libpq doesn't know about it, how is that information accessed?
That's exactly the problem...
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: >> Tom Lane wrote: >>>> Are those exposed through the libpq interface? >>> >>> No, because libpq doesn't really have any concept of prepared statements. >>> It would probably make sense to add some more API to libpq to allow >>> creation and interrogation of prepared statements, but no one's got >>> around to that yet. > >> Uh, if libpq doesn't know about it, how is that information accessed? > > That's exactly the problem... Currently the only way to do that would be to have a user defined function that calls SPI_prepare() with the query string and returns the desired information to the client. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> Tom Lane wrote: > >>>> Are those exposed through the libpq interface? > >>> > >>> No, because libpq doesn't really have any concept of prepared statements. > >>> It would probably make sense to add some more API to libpq to allow > >>> creation and interrogation of prepared statements, but no one's got > >>> around to that yet. > > > >> Uh, if libpq doesn't know about it, how is that information accessed? > > > > That's exactly the problem... > > Currently the only way to do that would be to have a user defined > function that calls SPI_prepare() with the query string and returns the > desired information to the client. It was my understanding that the new protocol passes this information to the cilent, right? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Jan Wieck wrote: > Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> Tom Lane wrote: > >>>> Are those exposed through the libpq interface? > >>> > >>> No, because libpq doesn't really have any concept of prepared statements. > >>> It would probably make sense to add some more API to libpq to allow > >>> creation and interrogation of prepared statements, but no one's got > >>> around to that yet. > > > >> Uh, if libpq doesn't know about it, how is that information accessed? > > > > That's exactly the problem... > > Currently the only way to do that would be to have a user defined > function that calls SPI_prepare() with the query string and returns the > desired information to the client. > Oh, I understand now. Added to TODO: * Allow libpq to return information about prepared queries -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073