Hi,
I'm having a problem using Zeos 6.5.1 (alpha) with Postgresql 8.1.
I'm using a TZQuery and a TZUpdate. The query works fine, all data is retrieved, but when I post a new record I get the following error:
"SQL Error: ERROR: column "client" of relation "client" does not exist."
I have checked all the TZUpdate generated SQL statements and everything is Ok. Can you help me?
Problem with Zeos and PostgreSQL
Moderators: gto, cipto_kh, EgonHugeist
I fixed the problem, the TZUpdade Sql was generating SQL statements with the table prefixed to the field names, like:
INSERT INTO clients (clients.name, clients.address, .... ) VALUES (:name,:address, ...)
and I don't know why this was generating the error, because I think this is a valid SQL statement. To fix the problem I changed manually the statements removing the prefixed table name:
INSERT INTO clients (name, address, .... ) VALUES (:name,:address, ...)
and everything is working fine now. Anybody knows why this is happening and how I can change the TZUpdate sql component to not prefix the table to the fields names (when using postgresql), avoiding the work of fixing the generated SQL manually? Thanks.
INSERT INTO clients (clients.name, clients.address, .... ) VALUES (:name,:address, ...)
and I don't know why this was generating the error, because I think this is a valid SQL statement. To fix the problem I changed manually the statements removing the prefixed table name:
INSERT INTO clients (name, address, .... ) VALUES (:name,:address, ...)
and everything is working fine now. Anybody knows why this is happening and how I can change the TZUpdate sql component to not prefix the table to the fields names (when using postgresql), avoiding the work of fixing the generated SQL manually? Thanks.
To work without problems with postgresql I havemodified the ZUpdateSqlEditor.pas.
The function GetTableRef now returns a empty string, so the table name is not prefixed to the generated SQL statements.
function TZUpdateSQLEditForm.GetTableRef(const TabName: string): string;
begin
REsult := '';
exit;
{ Original Code
if QuoteChar <> '' then
Result := TabName + '.' else
REsult := '';
}
end;
The function GetTableRef now returns a empty string, so the table name is not prefixed to the generated SQL statements.
function TZUpdateSQLEditForm.GetTableRef(const TabName: string): string;
begin
REsult := '';
exit;
{ Original Code
if QuoteChar <> '' then
Result := TabName + '.' else
REsult := '';
}
end;
Guys, I think your conclusions are rather premature and your overall approach to this issue is wrong. The issue is not whether there is a bug but WHERE the bug is.
Looking at the thread as some sort of issue tracking thread the following notes can be made.
1. The sql code for the table's definition is not shown
2. The exact query passed to the server for execution is not shown. In a volunteer project of this kind this alone makes the issue a non starter.
3. There is no indication that the code has been run against other versions of PostgreSQL.
4. There is no indication that new some syntax features in PostgreSQL 8.1 could be the cause of the so called bug.
5. There is no indication that the code has been run against other databases such as MySQL, MSSQL or any others.
6. There is no indication that the actual source lies in TZUpdateSQLEditForm itself, or in particular has anything to do with prefixing the column names with the table name. It could be due to some flaws in some other part of Zeos's parsing or string handling routines, or some other client libraries.
7.The possible effect of this so called fix on other users code does not appear to have been considered in the least, whether they are users of other versions of PostgreSQL or other databases altogether.
Guys, this is not the way to proceed.
This project is not going to develop properly with such an approach.
mpmaia, I suggest you post some more messages to find out the exact code despatched to the server and where and how that code is generated. If you still unable to locate the problem after doing so, ask for more help or pass it on to those who know more about Zeoslib's internals.
A contribution from a zeoslib user.
Looking at the thread as some sort of issue tracking thread the following notes can be made.
1. The sql code for the table's definition is not shown
2. The exact query passed to the server for execution is not shown. In a volunteer project of this kind this alone makes the issue a non starter.
3. There is no indication that the code has been run against other versions of PostgreSQL.
4. There is no indication that new some syntax features in PostgreSQL 8.1 could be the cause of the so called bug.
5. There is no indication that the code has been run against other databases such as MySQL, MSSQL or any others.
6. There is no indication that the actual source lies in TZUpdateSQLEditForm itself, or in particular has anything to do with prefixing the column names with the table name. It could be due to some flaws in some other part of Zeos's parsing or string handling routines, or some other client libraries.
7.The possible effect of this so called fix on other users code does not appear to have been considered in the least, whether they are users of other versions of PostgreSQL or other databases altogether.
Guys, this is not the way to proceed.
This project is not going to develop properly with such an approach.
mpmaia, I suggest you post some more messages to find out the exact code despatched to the server and where and how that code is generated. If you still unable to locate the problem after doing so, ask for more help or pass it on to those who know more about Zeoslib's internals.
A contribution from a zeoslib user.
I'd like to clarify that:
INSERT INTO table_name (table_name.field_name) VALUES ('Test value');
is not compatible with PostgreSQL. It needs to be:
INSERT INTO table_name (field_name) VALUES ('Test value');
I've tested the above with 8.0.x and 8.1.x.
I agree that the above patch looks the wrong way to go but there is a bug there somewhere. I don't find it a problem as I manually rewrite the SQL myself.
BTW would it be more compatible to default to the PostgreSQL syntax as I assume it would work with most other SQL servers.
Ben
INSERT INTO table_name (table_name.field_name) VALUES ('Test value');
is not compatible with PostgreSQL. It needs to be:
INSERT INTO table_name (field_name) VALUES ('Test value');
I've tested the above with 8.0.x and 8.1.x.
I agree that the above patch looks the wrong way to go but there is a bug there somewhere. I don't find it a problem as I manually rewrite the SQL myself.
BTW would it be more compatible to default to the PostgreSQL syntax as I assume it would work with most other SQL servers.
Ben