Hi,
Using the revision 3952 (last) of svn://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.2 with delphi 2009 and postgresql 9.2 running on Windows, i identified 3 issues. In attach there is a project that simulates.
I think that issues are introduced with the Postgresql+CAPI prepared statements, in old Zeos 7.0 this issues not happens.
I try to enable the the "ZEOS_TEST_ONLY" with the DbcConnection.SetTestMode(1) but nothing changes.
Zeos + Postgresql - Issues
Zeos + Postgresql - Issues
You do not have the required permissions to view the files attached to this post.
-
marsupilami
- Platinum Boarder
- Posts: 1956
- Joined: 17.01.2011, 14:17
Re: Zeos + Postgresql - Issues
Hello Franklin,
I had a look into the issues:
I wonder why you expext issue numer one (Parameter order) to return two rows. Even if you write it out in SQL, it will either return 0 rows, when the value of P0 is not in the table, one row if the value of P0 is in the table and all rows of the table if P0 is null. Usually I would expect this SQL to not compile at all and fail with the error that the type of the second P0 occurence an't be determined...
Regarding issue number two (Zero time) : That is interesting although I am not sure wether that is a bug in PostgreSQL or a bug in Zeos.
Regarding issue number three: Again I am amazed that this SQL will even compile. I can only assume that the following happens. In the old, non-CAPI times, Zeos would replace every parameter by the SQL version of its value. The SQL sent to the server would be 'select ... from .... order by 1' which will do what you expect.
Now the following SQL will get sent to the server: 'select ... from .... order by ?'. I can only assume that PostgreSQL will not treat this as an ordinal value for the sorting but instead will sort by the value of that parameter, as if it were an additional column to the query. Sorting all rows by the value 1 just means as much as returning them in any order the server wishes to use. With the current Zeos I can only suggest for you to use the column name in this example and not to try to send it to the server as a parameter.
What do you think?
Jan
I had a look into the issues:
I wonder why you expext issue numer one (Parameter order) to return two rows. Even if you write it out in SQL, it will either return 0 rows, when the value of P0 is not in the table, one row if the value of P0 is in the table and all rows of the table if P0 is null. Usually I would expect this SQL to not compile at all and fail with the error that the type of the second P0 occurence an't be determined...
Regarding issue number two (Zero time) : That is interesting although I am not sure wether that is a bug in PostgreSQL or a bug in Zeos.
Regarding issue number three: Again I am amazed that this SQL will even compile. I can only assume that the following happens. In the old, non-CAPI times, Zeos would replace every parameter by the SQL version of its value. The SQL sent to the server would be 'select ... from .... order by 1' which will do what you expect.
Now the following SQL will get sent to the server: 'select ... from .... order by ?'. I can only assume that PostgreSQL will not treat this as an ordinal value for the sorting but instead will sort by the value of that parameter, as if it were an additional column to the query. Sorting all rows by the value 1 just means as much as returning them in any order the server wishes to use. With the current Zeos I can only suggest for you to use the column name in this example and not to try to send it to the server as a parameter.
What do you think?
Jan
-
marsupilami
- Platinum Boarder
- Posts: 1956
- Joined: 17.01.2011, 14:17
Re: Zeos + Postgresql - Issues
One addendum:
Regarding issue number two: I tested that code on my computer (PostgreSQL 9.6, Windows 32 Bitz, Current Zeos 7.2 from SVN, Delphi XE7) and didn't see any problems. I also used a PostgreSQL 9.3 server on one of our Linux boxes to test (still with the 9.6 client library) and didn't get any unexpected results there...
With best regards,
Jan
Regarding issue number two: I tested that code on my computer (PostgreSQL 9.6, Windows 32 Bitz, Current Zeos 7.2 from SVN, Delphi XE7) and didn't see any problems. I also used a PostgreSQL 9.3 server on one of our Linux boxes to test (still with the 9.6 client library) and didn't get any unexpected results there...
With best regards,
Jan