Table"s Field Info

Working in the following environment:

Borland VCL ADO Components -> Microsoft OLE DB Provider for ODBC Drivers ->
PostgreSQL ODBC Driver -> PostgreSQL Server

and executing SQL statements like this:

SELECT * FROM mytable WHERE ...

I was surprised the driver issues the statement "SELECT * FROM mytable" just
before executing my custom statement. I guess it tries to retreive the field
info for "mytable". But what if "mytable" contains mios of rows?! The
execution will continue forever... Yes, I am aware of the option "Disallow
Premature". But my Borland TADOQuery object crashes on

begin;declare cursor .. for select ...;fetch backward in ..;close ..;commit
(unexpected EOF of client connection)

I think this is the easiest way to get no row but the field info:

SELECT * FROM mytable WHERE 0=1

Isn't it?





---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Table"s Field Info

Hello,

> Working in the following environment:
>
> Borland VCL ADO Components -> Microsoft OLE DB Provider for ODBC Drivers ->
> PostgreSQL ODBC Driver -> PostgreSQL Server

We need more :-) What about psqlODBC version? What's datasource setings?

> and executing SQL statements like this:
>
> SELECT * FROM mytable WHERE ...
>
> I was surprised the driver issues the statement "SELECT * FROM mytable" just
> before executing my custom statement. I guess it tries to retreive the field
> info for "mytable". But what if "mytable" contains mios of rows?! The

I'm not familiar with this part of code. And I don't have enough time
until end of week.

> execution will continue forever... Yes, I am aware of the option "Disallow
> Premature". But my Borland TADOQuery object crashes on
>
> begin;declare cursor .. for select ...;fetch backward in ..;close ..;commit
> (unexpected EOF of client connection)

How can we fast reproduce your problem? Could you post mylog output?
It's ideal to post one mylog with "SELECT * FROM mytable" and second
one with crash. Or you could post me example source or executable with
data specification.

> I think this is the easiest way to get no row but the field info:
>
> SELECT * FROM mytable WHERE 0=1
>
> Isn't it?

Are you sure PgSQL handle specially this kind of query? I remember Tom
Lane wrote in another mail list that this isn't as fast as you think.
But it is some time ago so it could be handled better now.

I hope Tom or someone else who knows it will drop here a note.

I think it could be better to use LIMIT. But is it same with
joins or more complex queries? Doesn't LIMIT breake it?

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Table"s Field Info

> > Working in the following environment:
> >
> > Borland VCL ADO Components -> Microsoft OLE DB Provider for ODBC Drivers ->
> > PostgreSQL ODBC Driver -> PostgreSQL Server
>
> We need more :-) What about psqlODBC version? What's datasource setings?

I see no psqlODBC version and no datasource settings in your reply.

> > and executing SQL statements like this:
> >
> > SELECT * FROM mytable WHERE ...
> >
> > I was surprised the driver issues the statement "SELECT * FROM mytable"
> > just before executing my custom statement. I guess it tries to retreive
> > the field info for "mytable". But what if "mytable" contains mios of
> > rows?! The
>
> I'm not familiar with this part of code. And I don't have enough time
> until end of week.

I take a look and the problem is quite beside the point. Not psqlODBC
driver issues statement ;-) The psqlODBC get SQLPrepare from your
test application. So I can't modify this behaviour.
But ...
The statement "SELECT * FROM mytable" is issued becouse the driver returns
empty table name for field (I don't know why BC++ have to get table name)
but it could be changed when you turn on option Parse Statement.
It could you help Use declare/fetch option too.
There is bug in Parse statement until 08.01.0104 (maybe later).
So please try 08.01.0107 development snapshot from pgfoundry.org.

> > execution will continue forever... Yes, I am aware of the option "Disallow
> > Premature". But my Borland TADOQuery object crashes on

Where is the option? Do you mean something in psqlodbc or in BC++?

> > begin;declare cursor .. for select ...;fetch backward in ..;close ..;commit
> > (unexpected EOF of client connection)
>
> How can we fast reproduce your problem? Could you post mylog output?
> It's ideal to post one mylog with "SELECT * FROM mytable" and second
> one with crash. Or you could post me example source or executable with
> data specification.

How can I reproduce this failure with your test app?

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Table"s Field Info

Ludek,
Thanks for your attention,

"Ludek Finstrle" wrote in message
news:20060120164637.GB20633@soptik.pzkagis.cz...
>> > Working in the following environment:
>> >
>> > Borland VCL ADO Components -> Microsoft OLE DB Provider for ODBC
>> > Drivers ->
>> > PostgreSQL ODBC Driver -> PostgreSQL Server
>>
>> We need more :-) What about psqlODBC version? What's datasource setings?
>
> I see no psqlODBC version and no datasource settings in your reply.

Doesn't this part of my post contain the psqlODBC version and the datasource
settings?
2. On a PostgreSQL server (I'm using v. 8.1.0) create a new database named
'test'. 3. Open the pgAdmin III Query Tool, load and execute my script
DB\MakeDB.sql against the new database. It will create a table 't1' and
insert 10000 rows with random values into it. 4. On your client PC, make
sure you have the 'Microsoft OLE DB Provider for ODBC drivers' and the
psqlODBC drivers installed (I'm using version 8.01.01.05). 5. Using the ODBC
Data Source Administrator, create a DSN with driver 'PostgreSQL ANSI'.
Specify 'Data Source' = 'Test', 'Database' = 'test'. Set 'Server', 'User
Name' and 'Password' fields to your custom values. Leave the rest of the DSN
settings to their defaults.

>> > and executing SQL statements like this:
>> >
>> > SELECT * FROM mytable WHERE ...
>> >
>> > I was surprised the driver issues the statement "SELECT * FROM mytable"
>> > just before executing my custom statement. I guess it tries to retreive
>> > the field info for "mytable". But what if "mytable" contains mios of
>> > rows?! The
>>
>> I'm not familiar with this part of code. And I don't have enough time
>> until end of week.
>
> I take a look and the problem is quite beside the point. Not psqlODBC
> driver issues statement ;-) The psqlODBC get SQLPrepare from your
> test application. So I can't modify this behaviour.

Maybe (even definitely), it gets SQLPrepare. The problems appear mainly in
ADO->ODBC interop. The Borland VCL components are just the wrappers for the
standard ADO components. So you could test it using Visual Basic 6.0, for
example. Anyway, the ODBC driver should handle it correctly. "SELECT * FROM
mytable" is the worst solution.

> But ...
> The statement "SELECT * FROM mytable" is issued becouse the driver returns
> empty table name for field (I don't know why BC++ have to get table name)

Oh, how does it conform to you phrase above: "Not psqlODBC driver issues
statement"?

> but it could be changed when you turn on option Parse Statement.
> It could you help Use declare/fetch option too.

"Use declare/fetch" forces server-side cursors. It isn't the best solution
for me.

> There is bug in Parse statement until 08.01.0104 (maybe later).
> So please try 08.01.0107 development snapshot from pgfoundry.org.

I'll try, thanks.

>> > execution will continue forever... Yes, I am aware of the option
>> > "Disallow
>> > Premature". But my Borland TADOQuery object crashes on
>
> Where is the option? Do you mean something in psqlodbc or in BC++?

?!?!?!
Yes, yes, "SOMETHING" :-) in psqlodbc:
Windows ODBC Data Source Administrator -> A custom PostgreSQL DSN
configuration -> "Datasource" button -> "Page 2" button -> "Disallow
Premature" checkbox.

>> > begin;declare cursor .. for select ...;fetch backward in ..;close
>> > ..;commit
>> > (unexpected EOF of client connection)

This statement is issued when the option "Disallow Premature" is checked.
DEAR DEVELOPERS!
COULD ANYONE EXPLAIN ME THE DIFFERENCE IN RESULTS BETWEEN THE STATEMENT
ABOVE AND MY PROPOSED SOLUTION: "SELECT * FROM mytable WHERE 0=1"

>> How can we fast reproduce your problem? Could you post mylog output?
>> It's ideal to post one mylog with "SELECT * FROM mytable" and second
>> one with crash. Or you could post me example source or executable with
>> data specification.
>
> How can I reproduce this failure with your test app?

I doubt, what I have posted? Haven't you received this:

Next, close the application, check the DSN option 'Disallow Premature' and
start the application again. Clicking the 'Open Dataset' button will cause
the program to crash. See MyLog\mylog_4008.log for this case.

> Regards,
>
> Luf
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

Regards,
Dmitry.



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Table"s Field Info

>> > execution will continue forever... Yes, I am aware of the option
>> > "Disallow
>> > Premature". But my Borland TADOQuery object crashes on
>
> Where is the option? Do you mean something in psqlodbc or in BC++?
>
>> > begin;declare cursor .. for select ...;fetch backward in ..;close
>> > ..;commit
>> > (unexpected EOF of client connection)
>>

See some info about premature execution by Hiroshi Inoue here:
http://archives.postgresql.org/pgsql-odbc/2002-03/msg00071.p hp

> Regards,
>
> Luf
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

Dmitry.



---------------------------(end of broadcast)---------------------------
TIP 1: 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