Search the Community

Hello! I am trying to create table at postgres SQL server with the following cod: procedure CreateTableTest(); var vTable : TFDTable; vPGConn : TFDConnection; vConDefs : TStringList; i, vSize : integer; begin vConDefs:=TStringList.Create(); vConDefs.AddPair('Server', '10.190.18.252'); vConDefs.AddPair('Port', '5432'); vConDefs.AddPair('User_Name', 'XXXXXX'); vConDefs.AddPair('Password', 'YYYYYYYYYYYYYYYY'); vConDefs.AddPair('Database', 'postgres'); vConDefs.AddPair('Pooled', 'True'); vConDefs.AddPair('POOL_MaximumItems', '100'); FDManager.AddConnectionDef('PG_TEST', 'PG', vConDefs); vConDefs.Clear(); vSize:=0; vTable:= TFDTable.Create(nil); vTable.TableName:='test_table'; vTable.SchemaName:='test_schem'; vTable.FieldDefs.Add('login', ftLargeint, vSize, true); vTable.FieldDefs.Add('ticket', ftLargeint, vSize, true); vTable.FieldDefs.Add('exec_time', ftDateTime, vSize, true); vTable.FieldDefs.Add('req_price', ftFloat, vSize, true); vTable.IndexDefs.Clear(); vTable.IndexDefs.Add(format('%s_pkey', [vTable.TableName]), 'login;ticket', [ixPrimary]); vPGConn:=TFDConnection.Create(nil); vTable.Connection := vPGConn; vTable.Connection.ConnectionDefName:='PG_TEST'; if not (vTable.Connection.Ping()) then vTable.Connection.Open(); if not(vTable.Exists) then try vTable.CreateTable(False); Log.WriteLog('<TFDCustomParams.CreateTable()> Table %s created at', [vTable.FullTableName]); except on e:EPgNativeException do begin Log.WriteLog('<TFDCustomParams.CreateTable()> Table %s create failed: [%s:%s]', [vTable.FullTableName, E.ClassName, E.Message], lmtERROR); for i:=0 to e.ErrorCount-1 do begin Log.WriteLog('%sDetails: [%s]', [#9, e.Errors[i].DetailedMessage], lmtERROR); Log.WriteLog('%sHint: [%s]', [#9, e.Errors[i].Hint], lmtERROR); Log.WriteLog('%sContext: [%s]', [#9, e.Errors[i].Context], lmtERROR); Log.WriteLog('%sInternalQuery: [%s]', [#9, e.Errors[i].InternalQuery], lmtERROR); Log.WriteLog('%sConstraintName: [%s]', [#9, e.Errors[i].ConstraintName], lmtERROR); end; end; on e:Exception do begin Log.WriteLog('<TFDCustomParams.CreateTable()> Table %s create failed: [%s:%s]', [vTable.FullTableName, E.ClassName, E.Message], lmtERROR); end; end; FreeAndNil(vPGConn); FreeAndNil(vConDefs); FreeAndNil(vTable); end; Code works perfectly, it creates table, but after creating it tries to define primary key, and at this moment it failes with message: "First chance exception at $00007FFAFEA0EF5C. Exception class EPgNativeException with message '[FireDAC][Phys][PG][libpq] ERROR: syntax error at or near "."'" I've look postgres logs and discovered the next error there: 2023-07-10 11:43:46.603 CEST [2263089] postgres@postgres ERROR: syntax error at or near "." at character 62 2023-07-10 11:43:46.603 CEST [2263089] postgres@postgres STATEMENT: ALTER TABLE test_schem.test_table ADD CONSTRAINT "test_schem"."pk_test_table" PRIMARY KEY ("login", ticket) Obviusly staetment generated by IFDPhysCommandGenerator is wrong after ADD CONSTRAINT there have to be just constraint name, without schema name. I am using Delphi 11.1 and postgres 14.8. Also I am connecting to default schem "public", if I create table in this schem it will works without errors and creates table with primary key as well. Well, the main question of this post is: please help me to define what I am doing wrong?

I am unable to delete a record. I get '[FireDAC][Phys][FB]-312. Exact update affected [0] rows, while [1] was requested'. This is my code using TFDTable BudSys_DB.Active := true; BudSys_DB.RecNo := recnum; BudSys_DB.Delete; This gives the same error if BudSys_DB.Locate('KEY', sysp^.key, []) then begin BudSys_DB.Delete; del_sys := true; end;

If I connect a TFDTable to a MariaDB table containing 2000 rows, and immediately call FindLast(), I get back True for success, but it positions on the 100th row of the table, not the 2000th. FDTable1.Open(); B:=FDTable1.FindLast(); Memo1.Lines.Add(Format('Return=%s A=%d', [NameFromBoolean(B), FDTable1A.AsInteger])); FDTable1.Close(); I'm running Delphi XE10.2. I've dug through the FireDAC source code and found it is applying LIMIT 100 to the initial select query to populate the TFDTable, but I'm not understanding why the FindLast runs in that initial query result rather than the whole table. Is there any workaround for this? Thanks.