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/



Mon, 19 Jan 2004 16:13:33 +0200
Shachar Shemesh <psql@shemesh.biz>
Getting the results columns before execution
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 !

Mon, 19 Jan 2004 15:48:16 +0100
Darko Prenosil <darko.prenosil@finteh.hr>
Re: Getting the results columns before execution
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

Mon, 19 Jan 2004 10:13:26 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Getting the results columns before execution 
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/



Mon, 19 Jan 2004 18:09:28 +0200
Shachar Shemesh <psql@shemesh.biz>
Re: Getting the results columns before execution
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

Mon, 19 Jan 2004 11:27:42 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Getting the results columns before execution 
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, Pennsylvania 19073

Sat, 24 Jan 2004 23:07:28 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Getting the results columns before execution
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

Sun, 25 Jan 2004 15:55:23 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Getting the results columns before execution 
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 #


Thu, 29 Jan 2004 10:31:40 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: Getting the results columns before execution
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, Pennsylvania 19073

Fri, 30 Jan 2004 05:44:30 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Getting the results columns before execution
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, Pennsylvania 19073

Fri, 30 Jan 2004 05:50:32 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Getting the results columns before execution