Jan,
thank you very much for your answer. I already switched back to the emulated prepared statements, but will yet have to try the doCalcDefaults option.
I would like to take advantage of MySQLs automatic update of timestamps (using both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP).
In TZMySQLDatabaseMetadata.UncachedGetColumns() I found that a default CURRENT_TIMESTAMP is already exempted from being converted to a string value. The problem is, that I would like to have higher precision on the timestamp fields, so this logic would have to recognize also the CURRENT_TIMESTAMP(n) syntax.
I only want the modification time of a record to be changed, only if anything in the record (other than the timestamp fields) actually changes. Therefore I cannot include these timestamp columns in an UPDATE and I have now switched my code to using TZUpdateSQL components instead of the automatic INSERT and UPDATE code generated by TZQuery.
Which brings me to the next problem:
In order to determine the columns in the table which I need to excempt from an UPDATE or INSERT and to generate the required SQL dynamically, I would like to use the TZSQLMetadata component. When I try to do that, it works only once for every table, on the reuse of the TZSQLMetadata component for the same table, the result set will be empty (recordcount = 0, EOF, BOF = true). I tried disabling the caching of metadata information and it worked then, but it makes the application too slow. I think that the actual rows in the Metadata result set are freed for some reason and am trying to debug that.
I first tried with a single TZSQLMetadata component on a datamodule and then also with multiple TZSQLMetadata components created temporarily. I had the same problem with both. Here is an excerpt from my code. The idea is to prepare TZQuery (TDBQuery is declared type TDBQuery = class(TZQuery) with an FUpdateSQL member variable) objects for every select statement required by the application. The select statements are specified in a constant array of records (maybe they will later be read from a configuration file). All of those query components are stored in a Hashtable. If a select needs to have corresponding UPDATE and INSERT statements, I specify the names of the affected tables which allows me to construct TZUpdateSQL components for those queries. Other queries are also used readonly and will not create TZUpdateSQL.
Code: Select all
procedure TTPDataModule.DataModuleCreate(Sender: TObject);
var
i, j : integer;
query : TDBQuery;
function GetModifySQL(tablenames : array of string; keyfields : array of string) : string;
var
name : string;
fielddef : TFieldDef;
fc : integer;
sql : string;
i : integer;
dbquery : TDBQuery;
tablename : string;
fieldnames : TStringList;
dbmetadata : TZSQLMetadata;
begin
sql := '';
for tablename in tablenames do begin
dbmetadata := TZSQLMetadata.Create(nil);
dbmetadata.Connection := DBCOnnection;
DBMetadata.MetadataType := mdColumns;
DBMetadata.TableName := tablename;
sql := sql + 'UPDATE '+tablename+' SET ';
DBMetadata.Active := true;
DBMetadata.first;
fieldnames := TStringList.Create;
while not DBMetadata.Eof do begin
fieldnames.Add(DBMetadata.FieldByName('COLUMN_NAME').AsString);
DBMetadata.Next;
end;
DBMetadata.Active := false;
fc := 0;
for name in fieldnames do begin
if Name.startsWith('ts_') then continue;
// exclude timestamp fields from update
if fc > 0 then begin
sql := sql + ', ';
end;
sql := sql + name +'='+':'+name;
Inc(fc);
end;
for i := low(keyfields) to high(keyfields) do begin
if (i=low(keyfields)) then begin
sql := sql + ' WHERE ';
end else begin
sql := sql + ' AND ';
end;
sql := sql + '('+keyfields[i]+'='+':'+keyfields[i]+')';
end;
sql := sql + ';'#$0D#$0A;
fieldnames.Free;
dbmetadata.free;
end;
result := sql;
end;
function GetInsertSQL(tablenames : array of string) : string;
var
name : string;
fielddef : TFieldDef;
fc : integer;
sql : string;
i : integer;
dbquery : TDBQuery;
tablename : string;
fieldnames : TStringList;
dbmetadata : TZSQLMetadata;
begin
sql := '';
for tablename in tablenames do begin
dbmetadata := TZSQLMetadata.Create(nil);
dbmetadata.Connection := DBConnection;
DBMetadata.MetadataType := mdColumns;
DBMetadata.TableName := tablename;
DBMetadata.Open;
fieldnames := TStringList.Create;
while not DBMetadata.Eof do begin
fieldnames.Add(DBMetadata.FieldByName('COLUMN_NAME').AsString);
DBMetadata.Next;
end;
DBMetadata.Close;
sql := sql + 'INSERT INTO '+tablename+' (';
fc := 0;
for name in fieldnames do begin
if Name.startsWith('ts_') then continue;
// exclude timestamp fields from insert
if fc > 0 then begin
sql := sql + ', ';
end;
sql := sql + name;
Inc(fc);
end;
sql := sql + ') VALUES(';
fc := 0;
for name in fieldnames do begin
if Name.startsWith('ts_') then continue;
// exclude timestamp fields from insert
if fc > 0 then begin
sql := sql + ', ';
end;
sql := sql + ':'+name;
Inc(fc);
end;
sql := sql + ');'#$0D#$0A;
fieldnames.free;
dbmetadata.free;
end;
result := sql;
end;
function GetDeleteSQL(tablenames : array of string; keyfields : array of string) : string;
var
sql : string;
tablename : string;
i : integer;
begin
sql := '';
for tablename in tablenames do begin
sql := sql + 'DELETE FROM '+tablename;
for i := low(keyfields) to high(keyfields) do begin
if (i=low(keyfields)) then begin
sql := sql + ' WHERE ';
end else begin
sql := sql + ' AND ';
end;
sql := sql + '('+keyfields[i]+'='+':'+keyfields[i]+')';
end;
end;
result := sql;
end;
begin
DBConnection.Connected := true;
FTableQuerys := THashTable<string,TDBQuery>.Create;
FSelectStrings := THashTable<string,TQuerySQL>.Create;
with FSelectStrings do begin
for i := low(table_selects) to high(table_selects) do begin
FSelectStrings.put(table_selects[i].FQueryID, table_selects[i], TQuerySQL.Empty);
query := TDBQuery.Create(self);
query.Connection := DBConnection;
query.RequestLive := true;
query.SQL.Text := table_selects[i].FSQLText; // the SELECT statement for this query (usually requests all columns and uses names params in the WHERE part)
if Length(table_selects[i].FTableNames) > 0 then begin // The list of tables which need to be updated
query.FUpdateSQL := TZUpdateSQL.Create(self);
with query.FUpdateSQL do begin
DeleteSQL.Text := GetDeleteSQL(table_selects[i].FTableNames, table_selects[i].FParamNames);
ModifySQL.Text := GetModifySQL(table_selects[i].FTableNames, table_selects[i].FParamNames);
InsertSQL.Text := GetInsertSQL(table_selects[i].FTableNames);
end;
query.UpdateObject := query.FUpdateSQL;
end;
for j := 0 to query.Params.Count-1 do begin // I use mostly GUIDs for the keys
query.Params.Items[j].DataType := ftGuid;
query.Params.Items[j].Size := 16;
end;
for j := 0 to length(table_selects[i].FParamTypes)-1 do begin
if j < query.Params.count then begin
if query.Params.Items[j].DataType <> ftGUID then begin // handle the exceptions
query.Params.Items[j].DataType := table_selects[i].FParamTypes[j];
end;
end;
end;
query.Prepare;
FTableQuerys.put(table_selects[i].FQueryID, query, nil);
end;
end;
What happens is, that for every first usage of a tablename, GetModifySQL will give the correct result while GetInsertSQL will find no column names and generate an "empty" INSERT statement. On further execution for the same tablename, GetModifySQL will also contain an "empty" UPDATE statement. While debugging the rest of my application, it sometimes seemed, that SELECt result sets were suddenly reduced to empty and also a Refresh on the TZQuery did not change that. These effects could still be due to errors in my usage of the components, but maybe they are related to the same problem the TZSQLMetadata resultsets have?
I do not understand, what I am doing wrong here. I do not like the idea of hard-coding the database model into the application by using explicit TZQuery or TZUpdateSQL components.
Can it be related to some changes in the Delphi 10.2 implementation of TDataset? Is MySQL 5.7 not supported?
Do you have any ideas?
Thanks and best regards,
Gunnar
