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

[aleroot]

Only on mySQL I get back this message in some situation after posting changes to a Ztable :

Code: Select all

aZTABLE.Post 

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

The error is displayed in a messageBox and i removed it commenting this define :

Code: Select all

{$DEFINE WITH_VALIDATE_UPDATE_COUNT}

Why with previous version (previous to 7.1.2) I didn't have this error, and on other database server (MSSQL, PostgreSQL) I don't get the error ?

[EgonHugeist]

Why with previous version (previous to 7.1.2) I didn't have this error, and on other database server (MSSQL, PostgreSQL) I don't get the error ?

what are previous versions? Sure your talking do you mean FireBird(there as an already fixed issue).

See: http://zeoslib.sourceforge.net/viewtopi ... =17&t=3767

[aleroot]

I don't use Firebird at all, I use only MSSQL and MySQL (where the problem occur, on MSSQL I don't have any problem).

The previous version was 6.6.6

[tuxracer78]

Hello,

I've got the same error message when posting certain zqueries with Postgresql 9.1 and Zeos DBO Stable 7.1.2.

Old environment without error messages:
Postgresql Server 8.4, Delphi 2006, Zeos DBO-6.6.1 beta

New Environment: Postgresql Server 9.1, Delphi 2006, Zeos DBO Stable 7.1.2

In my case, I can compare two zqueries, one working one not. Both are quite simple like 'select * from table where id=:id' (with parameter).
In both cases, id is defined as primary key (bigint) and should be unique.
I compared the settings of both zqueries, they are the same in my opinion, cached updates false in both cases. With ZQuery1 the post is successful without errors with ZQuery2 I get "0 record(s) updated. Only one record should have been updated".

Any ideas / suggestions?

Best regards

Gregor

[tuxracer78]

I think I know the problem now.
I checked both with the ZsqlMonitor.

In my case, I have tables in my databese with similar names, like:

My_Table
My_Tableextension1
My_Tableextension2

etc.

The problem is how version 7.1 is determining the primary key:

Old Zeos:
SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME, a.attnum AS KEY_SEQ, ci.relname AS PK_NAME FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND i.indisprimary AND ct.relname = 'My_Table' AND ct.relnamespace = n.oid AND n.nspname = 'public' ORDER BY table_name, pk_name, key_seq

New Version:
SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME, a.attnum AS KEY_SEQ, ci.relname AS PK_NAME FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct,
pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND i.indisprimary AND ct.relname like E'My_Table%'
AND ct.relnamespace = n.oid AND n.nspname = E'public' ORDER BY table_name, pk_name, key_seq;

In my case, " ct.relname like E'My_Table%'" in 7.1 gives me on one particulars table 3 rows, because of the wildcard (and the similar named 2 other tables), in version 6.6 (AND ct.relname = 'My_Table' ) it is only one result which is correct for that table.

Is this a known bug?

Best regards

Gregor Bramhoff

[EgonHugeist]

Hi Gregor,

Is this a known bug?

For me it is. I don't know the reason for that like + wildcard behavior. I assume the TZSQLMetadata-Component could be the reason. The project-manager and me already had a discussion about.

Imho is there a parameter missing which than decides if the wildcards came to shove or a unique determination is required. This would handle both cases. But this need a huge review everywhere. I don't know anything about the 6-series but your posts don't make me wondering.

I think we should restart the discussen again. Any suggestions?

[tuxracer78]

Hi Michael

Yes, I think it is the function ConstructNameCondition in the TZSQLMetadata-Component which is used to define the variable TableNameCondition for that SQL-Statement (and others) in ZDbcPostgreSqlMetadata.pas and analog db-components. Maybe, it would be better not to use this function in that particular SQL-Statement (or write a modified one for that) while it seems to work with other statements.

Best regards

Gregor

[marsupilami]

Hello tuxracer78, hello EgonHugeist,

I did some research on that. The trigger seems to be the _ in the table name. TZPostgreSQLDatabaseMetadata.UncachedGetPrimaryKeys uses ConstructNameCondition from the base class to construct the comparison expression. The _ is a wildcard and due to this ConstructNameCondition returns a like expression and also adds a % at the end. Although I do not understand why it adds the %...

It seems like this is done to enable the possibility to search for more than one primary key at a time by using a wildcard in the table name. So there seem to be several solutions:
1) UncachedGetPrimaryKeys can be changed to escape the wildcards in the table name. This would effectively remove the possibility to get information on more than one primary key at the time.
2) UncachedGetPrimaryKeys can be changed to not use ConstructNameCondition, but add a simple comparison. This also would effectively remove the possibility to get information on more than one primary key at the time.
3) The caller gets fixed to properly escape the table names. That sould be TZGenericCachedResolver.DefineWhereKeyColumns.

For me the third option seems the way to go. Unfortunately there seems to be no function to do the proper escaping because all escaping functions add quotes at the start and the end of the strings which seems incorrect in this context to me.

Best regards,

Jan

[mdaems]

I agree with Jan. Option 3 is the way to go. But afaik there is some function that escapes wildcards. I did create it together with some other user, a few years ago. For a very similar use case.

Mark

[mdaems]

The function I was refering to is function TZAbstractDatabaseMetadata.AddEscapeCharToWildcards(const Pattern: string): string;

But looking at TZAbstractDatabaseMetadata.ConstructNameCondition I wonder why I added % to the like string in case the pattern contains wildcards. I'm afraid I'll need an experiment to test if that's really necessary.

EDIT: Found that metadata for FB seems to give erros when removing the additional %. May be a FB only issue solved the wrong way.

[mdaems]

I did some experiments. Seems like the % sign is only needed for Firebird tables with an underscore included ???
For some reason a table named blob_values can't be found using '... like "BLOB_VALUES"', but '... like "BLOB_VALUES%"' seems to work. Does somebody know why? (Or is it an old FB bug, because my embedded firebird dll is quite old)

Does the patch below fix the issue (except for IB/FB)?

Code: Select all

Index: ZDbcInterbase6Metadata.pas
===================================================================
--- ZDbcInterbase6Metadata.pas	(revision 2934)
+++ ZDbcInterbase6Metadata.pas	(working copy)
@@ -203,11 +203,15 @@
   end;
 
   {** Implements Interbase6 Database Metadata. }
+
+  { TZInterbase6DatabaseMetadata }
+
   TZInterbase6DatabaseMetadata = class(TZAbstractDatabaseMetadata)
   private
     function GetPrivilege(Privilege: string): string;
   protected
     function CreateDatabaseInfo: IZDatabaseInfo; override; // technobot 2008-06-25
+    function ConstructNameCondition(Pattern: string; Column: string): string; override;
 
     function UncachedGetTables(const Catalog: string; const SchemaPattern: string;
       const TableNamePattern: string; const Types: TStringDynArray): IZResultSet; override;
@@ -1170,6 +1174,32 @@
   Result := TZInterbase6DatabaseInfo.Create(Self);
 end;
 
+function TZInterbase6DatabaseMetadata.ConstructNameCondition(Pattern: string;
+  Column: string): string;
+const
+  Spaces = '';
+var
+  WorkPattern: string;
+begin
+  Result := '';
+  if (Length(Pattern) > 2 * 31) then
+    raise EZSQLException.Create(SPattern2Long);
+
+  if (Pattern = '%') or (Pattern = '') then
+     Exit;
+  WorkPattern:=NormalizePatternCase(Pattern);
+  if HasNoWildcards(WorkPattern) then
+  begin
+    WorkPattern := StripEscape(WorkPattern);
+    Result := Format('%s = %s', [Column, EscapeString(WorkPattern)]);
+  end
+  else
+  begin
+    Result := Format('%s like %s',
+      [Column, EscapeString(WorkPattern+'%')]);
+  end;
+end;
+
 function TZInterbase6DatabaseMetadata.UncachedGetTriggers(const Catalog: string;
   const SchemaPattern: string; const TableNamePattern: string;
   const TriggerNamePattern: string): IZResultSet;
@@ -2731,8 +2761,9 @@
     end;
 end;
 
-function TZInterbase6DatabaseMetadata.UncachedGetSequences(const Catalog, SchemaPattern,
-  SequenceNamePattern: string): IZResultSet;
+function TZInterbase6DatabaseMetadata.UncachedGetSequences(
+  const Catalog: string; const SchemaPattern: string;
+  const SequenceNamePattern: string): IZResultSet;
 var
   SQL: string;
   LSequenceNamePattern: string;
Index: ZDbcMetadata.pas
===================================================================
--- ZDbcMetadata.pas	(revision 2934)
+++ ZDbcMetadata.pas	(working copy)
@@ -124,11 +124,11 @@
     FIC: IZIdentifierConvertor;
     function GetInfo: TStrings;
     function GetURLString: String;
-    function StripEscape(const Pattern: string): string;
-    function HasNoWildcards(const Pattern: string): boolean;
   protected
     FDatabase: String;
     WildcardsArray: array of char; //Added by Cipto
+    function StripEscape(const Pattern: string): string;
+    function HasNoWildcards(const Pattern: string): boolean;
     function EscapeString(const S: string): string; virtual;
     function DecomposeObjectString(const S: String): String; virtual;
     function CreateDatabaseInfo: IZDatabaseInfo; virtual; // technobot 2008-06-24
@@ -2153,7 +2153,7 @@
   else
   begin
     Result := Format('%s like %s',
-      [Column, EscapeString(WorkPattern+'%')]);
+      [Column, EscapeString(WorkPattern)]);
   end;
 end;
 

[tuxracer78]

Hello mdaems,

I'll have a look an that.
I'm not that familiar with patching your components: Could you please tell me in short how to apply this patch the easiest way?

Best regards

Gregor

[mdaems]

Thanks,

In this form it will be easiest to do the little patch manually: to remove the lines with a minus sign and add the lines with the plusses in the 2 named files. Of course, you can ignore comments and formatting only changes.
As i wasn't sure if you're running an svn working copy version (and which one) I didn't take the effort of making a real patch file. If you want one, let me know what branch and revision you're working with.

Mark

[tuxracer78]

Ok, I'll edit it manually.
I only had a short look on it. Do I need to apply the patch to ZDbcInterbase6Metadata.pas even when working with PostGreSQL?.
I Think, I have to reinstall the whole Zeos-Components after that (like a fresh install), right?

Best regards

Gregor

[mdaems]

No, you don't need the interbase change in that case. I included it for completeness.

Whether a re-install is necessary depends on your test case. If the full zeoslib source is on the search path it will be compiled automatically into your test program. Otherwise you'll have to re-install so the compiler links against the new component versions.
For development purposes my test programs always use the zeoslib sources directly to avoid problems with the installed components. But for you as an end user that shouldn't be necessary.

Mark