FireDAC Create table with TFDTable class at SQL Postgres 14

[shalapai 0]

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?

Edited July 10, 2023 by shalapai

[Lajos Juhász 295]

I cannot help here. If you're going to feel better I have similar issues with auto generated SQLs on Informix. My solution is to generate SQL in my code and don't rely on auto generated statements.

[Lars Fosdal 1793]

I saw an example that added

  IndexDefs.Clear;

before IndexDefs.Add...

 

Edit: Another thing - are you certain that the field list is semi-colon delimited?

[shalapai 0]

18 minutes ago, Lars Fosdal said:

IndexDefs.Clear;

Yes, but that is not case of issue! I've fixed this.

 

18 minutes ago, Lars Fosdal said:

Edit: Another thing - are you certain that the field list is semi-colon delimited?

Yes, I'am sure, you have to list them separated by semi-colon. In any case if there will be only one field, it does not helps. Also as you can see log of postgres, generator provided right statement in the part of defining primary key field list.

Edited July 10, 2023 by shalapai

[Lars Fosdal 1793]

What was the solution?

[Lars Fosdal 1793]

2 minutes ago, shalapai said:

you have to list them separated by semi-colon

In my experience, list separators tend to follow the locale - hence the question.

[shalapai 0]

1 minute ago, Lars Fosdal said:

What was the solution?

I meant that I've added 

IndexDefs.Clear; 
But it does not helped/

[Lars Fosdal 1793]

> pk_test_table" PRIMARY KEY ("login", ticket)

Why is login quoted, but not ticket?

[shalapai 0]

1 minute ago, Lars Fosdal said:

> pk_test_table" PRIMARY KEY ("login", ticket)

Why is login quoted, but not ticket?

Suppose that login is a keyword in postgres, ticket - not

[Lars Fosdal 1793]

Does using field name 'loginid' instead of 'login' have any effect? 

I am merely guessing here now - I have not used PG much.

[shalapai 0]

12 minutes ago, Lars Fosdal said:

Does using field name 'loginid' instead of 'login' have any effect? 

I am merely guessing here now - I have not used PG much.

No, the same behavior:

 

Postgres directly points at constraint name: "test_schem"."pk_test_table". if I manualy edit statment to 'ALTER TABLE test_schem.test_table ADD CONSTRAINT "pk_test_table" PRIMARY KEY (loginid)' and run it, it works perfectly

[programmerdelphi2k 237]

4 hours ago, shalapai said:

vTable.IndexDefs.Add(format('%s_pkey', [vTable.TableName]), 'login;ticket', [ixPrimary]);

here "LOGIN" shouldnt be quoted too?

[shalapai 0]

1 minute ago, programmerdelphi2k said:

here "LOGIN" shouldnt be quoted too?

In last post I've used another field name: loginid - and that does not helped. The problem not with field name at all! The main problem is with CONSTRAINT NAME "test_schem"."pk_test_table", it must not be named with schema name, but as I defined in vTable.IndexDefs.Add(format('%s_pkey', [vTable.TableName]), 'loginid', [ixPrimary]); at the first parameter

[programmerdelphi2k 237]

7 minutes ago, shalapai said:

it must not be named with schema name,

I dont use PG, then "schema" is really necessary in your definition, say, here: "vTable.SchemaName:='test_schem'; "

[shalapai 0]

8 minutes ago, programmerdelphi2k said:

I dont use PG, then "schema" is really necessary in your definition, say, here: "vTable.SchemaName:='test_schem'; "

I need table to be created in apropriate schem, with primary key, if I not define "vTable.SchemaName:='test_schem';", then table will be created in schem which defined by default ("public") or in "search_path" parameter for conected user, yes I can try to override default schema name, but what if task needs to create tables in 10, 50 schems, everytime override default schema?... I think that is bad solution. For now I see the best solution provided @Lajos Juhász , it's to generate SQL in code manualy and don't rely on auto generated statements. But with regarding to above, I am starting to think that this is bug, may be I am wrong and someone can point me to mistake or something that I do not consider in code or just confirm that this is really bug in FireDAC

[weirdo12 21]

If you use the public schema does everything work as expected? I just want to confirm that. I am assuming it does. You don't assign any value to SchemaName and everything works. Or do you assign 'public' to SchemaName?

 

Quote

if I not define "vTable.SchemaName:='test_schem';", then table will be created in schem which defined by default ("public") or in "search_path" parameter for conected user

 

[shalapai 0]

3 hours ago, weirdo12 said:

If you use the public schema does everything work as expected? I just want to confirm that. I am assuming it does. You don't assign any value to SchemaName and everything works. Or do you assign 'public' to SchemaName?

 

 

Hello, it works if I assign 'public' to SchemaName and if DB user is connected with default schema (search_path = '')

[weirdo12 21]

On 7/11/2023 at 4:34 AM, shalapai said:

Hello, it works if I assign 'public' to SchemaName and if DB user is connected with default schema (search_path = '')

Did you try including the schema name with the table name?

 

vTable.TableName:='test_schem.test_table';

 

 

[weirdo12 21]

This works in RAD Studio 11.3 with PostgreSQL 14.8 running on Ubuntu. Without the SET search_path code, the table is always being added to public even if SchemaName was set to dba or if the TableName was qualified like dba.test_table.

 

 

std::unique_ptr<TFDTable> vTable(new TFDTable(0));

vTable->Connection = db_connection;
vTable->TableName = "test_table";
vTable->FieldDefs->Add("login", ftLargeint, 0, true);
vTable->FieldDefs->Add("ticket", ftLargeint, 0, true);
vTable->FieldDefs->Add("exec_time", ftDateTime, 0, true);
vTable->FieldDefs->Add("req_price", ftFloat, 0, true);

TFDIndex* idx = vTable->Indexes->Add();

idx->Name = System::Sysutils::Format("%s_pkey", String("test_table"));
idx->Fields = "login;ticket";
idx->Active = true;

db_connection->ExecSQL("SET search_path TO dba, public");

if (!vTable->Exists) {
	vTable->CreateTable(False);
}

db_connection->ExecSQL("SET search_path TO public, dba");

 

 

 

Edited July 13, 2023 by weirdo12
Deleted an image