Thread: 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?
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
> > 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
Ludek, Thanks for your attention, "Ludek Finstrle" <luf@pzkagis.cz> 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.
>> > 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.php > Regards, > > Luf > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > Dmitry.