PostgreSQL create database lazarus / 16-Databases ---------------------------------------------------------------------- SymbolicFrank · 2023-03-08 11:43 This worked for MS SQL, but not for Postgres: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var   PConn: TSpecialConnector;begin  PConn := TSpecialConnector.Create(nil);  PConn.KeepConnection := False;  PConn.ConnectorType := 'PostgreSQL';  PConn.UserName := '';  PConn.Password := '';  PConn.Close;  PConn.DatabaseName := 'bla';  PConn.CreateDB; ---------------------------------------------------------------------- zeljko · 2023-03-08 13:07 What is TSpecialConnector ? ---------------------------------------------------------------------- SymbolicFrank · 2023-03-08 13:38 Ah, my mistake. A TSQLConnector with this added: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TSpecialConnector.DoInternalConnect;begin  if ConnectorType = 'SQLite3' then TSQLite3Connection(Proxy).OpenFlags :=    [sofReadWrite, sofCreate, sofFullMutex, sofSharedCache];   inherited DoInternalConnect;end; ---------------------------------------------------------------------- rvk · 2023-03-08 17:50 So, what happens on line 11. "Doesn't work" isn't really a good problem description. Do you get an Excpetion? QuoteCreateDB will create a new database on the server. Whether or not this functionality is present depends on the type of the connection. The name for the new database is taken from the TSQLConnection.DatabaseName property, the user credentials are taken from the TSQLConnection.UserName and TSQLConnection.Password properties.QuoteIf the connection type does not support creating a database, then an EDatabaseError exception is raised. Other exceptions may be raised if the operation fails, e.g. when the user does not have the necessary access rights.https://www.freepascal.org/docs- html/fcl/sqldb/tsqlconnection.createdb.html Edit: It should run "CREATE DATABASE" for you. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TPQConnection.CreateDB;begin  ExecuteDirectPG('CREATE DATABASE ' +DatabaseName);end; Can you try it with a TPQConnection directly? ---------------------------------------------------------------------- MarkMLl · 2023-03-08 20:28 Does the user/role have the right to create a database? Apart from that, this sort of thing is a portability minefield. I've run PostgreSQL and Firebird in parallel before now, and while they have a lot of functionality in common this most definitely didn't extend to administrative operations and I concluded that that sort of thing would be best left to the management software (i.e. pgsql etc.). MarkMLl ---------------------------------------------------------------------- Zvoni · 2023-03-09 11:25 Everyone: You did see that he left out UserName and Password blank? So, Mark's hint about credentials/user role is valid (and probably the culprit) ---------------------------------------------------------------------- SymbolicFrank · 2023-03-09 12:37 I did supply a username and password, perhaps '' and '' would have been clearer. With the same user I can create a new database in pgAdmin. The error message is: "this operation is not supported" or something like that. I'll try with a TPQConnection, but I'm using a TSQLConnector because I'm using that code for multiple, different databases (SQLite, MS SQL and PostgreSQL). ---------------------------------------------------------------------- rvk · 2023-03-09 12:52 Quote from: SymbolicFrank on March 09, 2023, 01:37:57 pmThe error message is: "this operation is not supported" or something like that. EXACT error messages are VERY important. Especially if you want others to find solutions. You are probably getting: "Operation is not supported by this type of database" This is because of this function: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TSQLConnection.CreateDB;begin  DatabaseError(SNotSupported); // SNotSupported = 'Operation is not supported by this type of database';end; That is the NOT-overruled procedure for CreateDB in TSQLConnection. But when choosing "PostgreSQL" it should create an instance of TPQConnection automatically (I thought it worked that way). Apparently it doesn't do it correctly. Maybe this is due to your TSpecialConnection. Could you (besides the TPQConnection) try the TSQLConnection directly (without the TSpecialConnection). Did you add pqconnection in your uses clause? (maybe it only works when you do that) Otherwise the postgres unit (with TPQConnection) might not be registered and TSQLConnection can't make a TPQConnection from it. ---------------------------------------------------------------------- SymbolicFrank · 2023-03-09 12:57 Thanks. Yes, without pqconnection, you cannot select that kind of TSQLConnector. Which should be equivalent with a TPQConnection, but perhaps it isn't. I'll check it out. ---------------------------------------------------------------------- rvk · 2023-03-09 13:00 Quote from: SymbolicFrank on March 09, 2023, 01:57:37 pmThanks. Yes, without pqconnection, you cannot select that kind of TSQLConnector. Which should be equivalent with a TPQConnection, but perhaps it isn't. I'll check it out. Ah, yes, I see it gets added automatically when setting "PostgresSQL". But you are setting that in code, so it might have been missing in your own unit  The other option would be tat TSpecialConnector doesn't inherit properly. ---------------------------------------------------------------------- rvk · 2023-03-09 13:19 BTW I'm not really sure the CreateDB is supported via inheritence in TSQLConnector. I don't see anywhere that CreateDB is called on the FProxy (which is the connection of TSQLConnector to the TPQConnection). But you said it worked with MSSQL ???? (That shouldn't be true if that's the case) If it wasn't true ( ) then you could add the override call in your TSpeciaConnector yourself. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TSpecialConnector.CreateDB;begin  CheckProxy;  FProxy.CreateDB;end; Then the underlying TPQConnection.CreateDB should be called when using TSpecialConnector.CreateDB. (You could also do this for DropDB.) ---------------------------------------------------------------------- MarkMLl · 2023-03-11 08:10 Quote from: rvk on March 09, 2023, 01:52:10 pmEXACT error messages are VERY important. Especially if you want others to find solutions. You are probably getting: "Operation is not supported by this type of database" I agree, but equally important are the actual query and response strings (i.e. captured as close to the network connection as possible): and I don't believe the RTL etc. provide a way of hooking those other than going in deep with a debugger. However in all cases the first test after any hint of a problem should be using a *SUPPORTED* maintenance tool, i.e. psql in the case of PostgreSQL. Be it ever so good, pgAdmin does not fall into that category. MarkMLl