0 record(s) updated

[Morenz]

Hi everyone,
I'm building an app in Delphi 2010 with Postgresql 8.4 and I notice a strange behavior while doing a very simple operation sequence (edit a TZQuery dataset - enter a new integer value - post): I get an exception saying

0 record(s) updated. Only one record should have been updated.

I have checked that:
- The connection is OK (set postgresql-8 as driver)
- The query has no SQL errors
- The query dataset is not empty

What can it be?

Thanks in advance,
Moreno

[Wild_Pointer]

Morenz,

could you provide the SQL creation srcipt of the table you try to edit? If you are using pgAdmin - just copy script from SQL pane positioned in the right of the main window.

[Morenz]

Hello again,
working on the task today I found that the problem is present only when accessing a PGSQL 8.4 database. With earlier databases (I tried with 8.3 and 8.1) everything works fine (even if I access through postgresql-7 driver, that still does not work for an 8.4 database)

Hope this helps to find the problem (if there is a problem...)

Thanks
Moreno.

[Morenz]

@wild_pointer:

CREATE TABLE queue
(
codice int4 NOT NULL DEFAULT nextval('queue_codice_seq'::regclass),
codric int8 NOT NULL,
statomir int2 NOT NULL,
sac varchar(23),
oper varchar(1) NOT NULL,
codfis varchar(40) NOT NULL,
patharc varchar(254) NOT NULL,
mirdbxc varchar(50) NOT NULL,
xmltosend text NOT NULL,
errorcode text,
cftitolare varchar(40) NOT NULL,
CONSTRAINT queue_pkey PRIMARY KEY (codice)
)
WITHOUT OIDS;
ALTER TABLE queue OWNER TO micromir;


the field I'm trying to edit is statomir, changing its value from 51 to 53.
Thanks
Moreno

[Wild_Pointer]

Hello, Morenz,

I've created the table you provided on server v. 8.4.3, entered some data, edited the statomir column without problems.
Do you use downloaded zeos version (snapshot) or do you get it from svn repository? Mine is from repository... (revision 803).

Maybe there something in DB settings? I see no reason why Zeos should act differently on Pg 8.4 server...

[mdaems]

Hi this test was added to make sure the right record to update is found.
Seems like the query composed to do the update isn't completely correct. Best way to debug is look at what SQL zeoslib generates by adding a TZSQLMonitor to the project and write all statements sent to the server into a log file.

Most likely causes for this problem : no update by primary key but using all field values in the were clause.
Due to differences in data formatting between pg versions this might be a version problem indeed.
First step is checking which update/insert sql is generated. Does it use the Primary key?


Mark

[Morenz]

Hi everyone
@Wild_Pointer
I downloaded (6 months ago) from SVN; How can I get revision number? Where is located this info?

@mdaems
OK, I'll try to put down some lines to verify what you say.

Many thanks to both of you, for now.... I'll post feedback as far as I can.

Best regards,
Moreno.

[Wild_Pointer]

Morenz,

I don't know what svn client you use, but with Qsvn I select "Show Log" from pop up menu on Zeos project .

By the way - the beauty of TZSQLMonitor is that you don't have to "put down some lines" in code... Just drop the component, specify the filename and activate it. No big deal.

Good luck!

[Morenz]

Hello,
I used the TZSQLMonitor and ... a full sql capture wih just two mouse clicks!! Great!

So I think (but I'm not so sure) that I went into the problem: the backslash (\) and apex (') are read as escape sequences and: once I removed them, the program went after the line that caused the problem.

I think it's a collation (CT_COLLATE) problem.... or do I need to manage escape sequences (\\ instead of \ and \' instead of ' -- or somthing similar... but what else)?

Database is UTF8-encoded (I need to put XML code in it...)

Thanks for the support

[Wild_Pointer]

Morenz,

I'm not sure. Maybe if you could provide a test case (db and source) I could test it on my machine and find out what is wrong.

Anyway that would not be sooner then the day after tomorrow, as I have alot of work these days.

[mdaems]

Morenz,
Can you please give a sample of the update query that's generated by zeoslib to post hese updates? The error shown typically occurs when the 'WHERE' part of your query isn't correct.
If your table contains a primary key probably the solution is to set ZQuery.WhereMode=wmWhereKeyOnly (=use the primary key to locate the record)
Another usefull setting is ZQuery.UpdateMode=umUpdateChanged (only update the changed fields.
If these settings are already set or do not help, I have the impression there's something wrong with the primary key field of your table. Is the field available in the query dataset?

Mark