Strange behavior on insert after 'create table' (Solved)

[technobot]

Hi.

I'm using Delphi7 with MySQL 5 and a fresh copy of the latest testing branch of zeoslib (revision 378, I think). Protocol is mysql-5.

I'm trying to demonstrate runtime creation of a table. I use a TZSQLProcessor to run a 'create table' query (listed below), and then try to fill the table data via a TZTable component (with cached updated enabled):

Code: Select all

  // mysql table name:
  NameStr := MySqlConnection.Database + '.' + MySqlTable.TableName;

  // create mysql table:

  TableList := TStringList.Create;
  try
    MySqlConnection.Connect;
    MySqlConnection.GetTableNames(MySqlTable.TableName, TableList);
    if (TableList.Count > 0) then
      ShowMessage('mysql: table ''' + NameStr + ''' already exists.')
    else
    begin
      ShowMessage('mysql: table ''' + NameStr + ''' not found. creating....');
      CreateTableProcessor.Connection := MySqlConnection;
      CreateTableProcessor.Execute;
    end;
  finally
    FreeAndNil(TableList);
  end;

  // temporary fix for insert problem:
{  MySqlConnection.Disconnect;
  MySqlConnection.Connect;{}

  // load mysql table:
  ShowMessage('mysql: loading existing data from table ''' + NameStr + '''...');
  MySqlTable.Active := True;

  // populate mysql table:

  if (not MySqlTable.IsEmpty) then
    ShowMessage('mysql: table ''' + NameStr + ''' already contains data.')
  else
  with MySqlTable do
  begin
    ShowMessage('mysql: inserting data into table ''' + NameStr + '''...');
    DisableControls;
    Append;
    Fields[1].AsInteger := 5;
    Fields[2].AsString := 'five';
    Append;
    Fields[1].AsInteger := 12;
    Fields[2].AsString := 'twelve';
    Append;
    Fields[1].AsInteger := 40;
    Fields[2].AsString := 'fourty';
    Append;
    Fields[2].AsString := 'zero';
    Post;
    ApplyUpdates;
    EnableControls;
  end;

The SQL script of CreateTableProcessor is:

Code: Select all

CREATE TABLE sqlitetest (
  Id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
  IntData INTEGER DEFAULT 0,
  TextData CHAR(10)
);

Running this when the table doesn't exist causes an exception in TZAbstractRODataset.SetFieldData when I try to set the field value, with the message 'Field 'IntData' cannot be modified'. But if I put:

MySqlConnection.Disconnect;
MySqlConnection.Connect;

immediately after creating the table, then the insert runs just fine. If I run the program when the table already exists, it also works fine. But calling MySqlConnection.Reconnect instead of Disconnect and Connect doesn't fix the problem.

One final note: before I tried the latest testing branch, I was using 6.6.2rc, and then I was getting a 'Cannot update this query type' exception during the ApplyUpdates call (with the same code). Iirc it was raised in TZGenericCachedResolver.DefineTableName.

[mdaems]

The problem you face may be linked to the Metadata collection call (GetTableNames) you execute before the 'Create table' statement.
The resolver for the TZTable probably uses this old cached data to check what can be updated. As it can't find the new made table in the cache it's impossible to resolve the right column definitions (including updatability). Closing the connection cleans the metadata cache.

Just look in the code if you can find a way to clean the connection metadata caches. Eventually I could help you searching when you attach your project to this post. (Project files, no exe to keep it small enough.) This would not be immediate, however. I think I remember other posts on this forum. You might find a way to clean the cached metadata by using the search facility.

Reconnect is made to avoid a full refresh of all components/caches linked to a connection. Otherwise you would lose all data in a query when the database connection drops. Reconnect enables you to recover the connection without losing the data queried and updated before.

Mark

EDIT : See http://zeos.firmos.at/viewtopic.php?t=618

[technobot]

I had a feeling it had something to do with the metadata not being fetched after create table. As suggested in http://zeos.firmos.at/viewtopic.php?t=618, I tried using

MySqlConnection.DbcConnection.GetMetadata.ClearCache(GetTablesMetaDataCacheKey('','','',nil));

It didn't help. Then I tried it without the Key parameter, and it worked:

MySqlConnection.DbcConnection.GetMetadata.ClearCache;

This suggests that this is the correct approach, but GetTablesMetaDataCacheKey('','','',nil) doesn't return what I need.. So the question now is, how to get the correct key (or keys) to avoid clearing everything? I attach the sources of my test app, if you want to have a look.

P.S.: Also thanks for the explanation regarding Reconnect. It makes a lot of sence.

[technobot]

Ok, I figured it out. I needed to pass the same table name pattern to GetTablesMetaDataCacheKey as I did to GetTableNames. So in my case, the call should look like this:

MySqlConnection.DbcConnection.GetMetadata.ClearCache(GetTablesMetaDataCacheKey('','',MySqlTable.TableName,nil));

[mdaems]

Isn't this a problem with the cached metadata? Doesn't this mean you can have multiple cached metadata result sets, depending on the way the set was generated?

Can you imagine a solution?

Mark

[technobot]

The solution is simple (or at least the principle is simple): using my cahce key retrieval api patch, simply modify the appropriate GetxxxxCacheKey method to handle the parameters whichever way you feel is correct (e.g. ignore the table pattern completely).

But is it really a problem? I'm not sure. Currently there is a single metadata entry for each table name pattern. I'd say that's probably ok. Although on the other hand, if you specify an empty pattern, it loads the matadata for all the tables (I think..), in which case you might have some duplicate data in the cache...