shalapai 0 Posted July 10, 2023 (edited) 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 Share this post Link to post
Lajos Juhász 293 Posted July 10, 2023 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. 1 Share this post Link to post
Lars Fosdal 1792 Posted July 10, 2023 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? Share this post Link to post
shalapai 0 Posted July 10, 2023 (edited) 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 Share this post Link to post
Lars Fosdal 1792 Posted July 10, 2023 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. Share this post Link to post
shalapai 0 Posted July 10, 2023 1 minute ago, Lars Fosdal said: What was the solution? I meant that I've added IndexDefs.Clear; But it does not helped/ Share this post Link to post
Lars Fosdal 1792 Posted July 10, 2023 > pk_test_table" PRIMARY KEY ("login", ticket) Why is login quoted, but not ticket? Share this post Link to post
shalapai 0 Posted July 10, 2023 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 Share this post Link to post
Lars Fosdal 1792 Posted July 10, 2023 Does using field name 'loginid' instead of 'login' have any effect? I am merely guessing here now - I have not used PG much. Share this post Link to post
shalapai 0 Posted July 10, 2023 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 Share this post Link to post
programmerdelphi2k 237 Posted July 10, 2023 4 hours ago, shalapai said: vTable.IndexDefs.Add(format('%s_pkey', [vTable.TableName]), 'login;ticket', [ixPrimary]); here "LOGIN" shouldnt be quoted too? Share this post Link to post
shalapai 0 Posted July 10, 2023 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 Share this post Link to post
programmerdelphi2k 237 Posted July 10, 2023 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'; " Share this post Link to post
shalapai 0 Posted July 10, 2023 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 Share this post Link to post
weirdo12 20 Posted July 11, 2023 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 Share this post Link to post
shalapai 0 Posted July 11, 2023 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 = '') Share this post Link to post
weirdo12 20 Posted July 12, 2023 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'; Share this post Link to post
weirdo12 20 Posted July 13, 2023 (edited) 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 1 Share this post Link to post