Lazarus

Programming => Databases => Topic started by: SymbolicFrank on March 08, 2023, 12:43:21 pm

Title: PostgreSQL create database
Post by: SymbolicFrank on March 08, 2023, 12:43:21 pm
This worked for MS SQL, but not for Postgres:

Code: Pascal  [Select][+][-]
  1. var
  2.    PConn: TSpecialConnector;
  3. begin
  4.   PConn := TSpecialConnector.Create(nil);
  5.   PConn.KeepConnection := False;
  6.   PConn.ConnectorType := 'PostgreSQL';
  7.   PConn.UserName := '';
  8.   PConn.Password := '';
  9.   PConn.Close;
  10.   PConn.DatabaseName := 'bla';
  11.   PConn.CreateDB;
Title: Re: PostgreSQL create database
Post by: zeljko on March 08, 2023, 02:07:51 pm
What is TSpecialConnector ?
Title: Re: PostgreSQL create database
Post by: SymbolicFrank on March 08, 2023, 02:38:22 pm
Ah, my mistake. A TSQLConnector with this added:

Code: Pascal  [Select][+][-]
  1. procedure TSpecialConnector.DoInternalConnect;
  2. begin
  3.   if ConnectorType = 'SQLite3' then TSQLite3Connection(Proxy).OpenFlags :=
  4.     [sofReadWrite, sofCreate, sofFullMutex, sofSharedCache];
  5.  
  6.   inherited DoInternalConnect;
  7. end;
  8.  
Title: Re: PostgreSQL create database
Post by: rvk on March 08, 2023, 06:50:43 pm
So, what happens on line 11.
"Doesn't work" isn't really a good problem description.
Do you get an Excpetion?

Quote
CreateDB 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.
Quote
If 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][+][-]
  1. procedure TPQConnection.CreateDB;
  2. begin
  3.   ExecuteDirectPG('CREATE DATABASE ' +DatabaseName);
  4. end;

Can you try it with a TPQConnection directly?
Title: Re: PostgreSQL create database
Post by: MarkMLl on March 08, 2023, 09:28:33 pm
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
Title: Re: PostgreSQL create database
Post by: Zvoni on March 09, 2023, 12:25:54 pm
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)
Title: Re: PostgreSQL create database
Post by: SymbolicFrank on March 09, 2023, 01:37:57 pm
I did supply a username and password, perhaps '<USERNAME>' and '<PASSWORD>' 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).
Title: Re: PostgreSQL create database
Post by: rvk on March 09, 2023, 01:52:10 pm
Quote from: SymbolicFrank on March 09, 2023, 01:37:57 pm
The 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][+][-]
  1. procedure TSQLConnection.CreateDB;
  2. begin
  3.   DatabaseError(SNotSupported); // SNotSupported = 'Operation is not supported by this type of database';
  4. 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.
Title: Re: PostgreSQL create database
Post by: SymbolicFrank on March 09, 2023, 01:57:37 pm
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.
Title: Re: PostgreSQL create database
Post by: rvk on March 09, 2023, 02:00:19 pm
Quote from: SymbolicFrank on March 09, 2023, 01:57:37 pm
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.
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  :D

The other option would be tat TSpecialConnector doesn't inherit properly.
Title: Re: PostgreSQL create database
Post by: rvk on March 09, 2023, 02:19:59 pm
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 ( ;D ) then you could add the override call in your TSpeciaConnector yourself.

Code: Pascal  [Select][+][-]
  1. procedure TSpecialConnector.CreateDB;
  2. begin
  3.   CheckProxy;
  4.   FProxy.CreateDB;
  5. end;

Then the underlying TPQConnection.CreateDB should be called when using TSpecialConnector.CreateDB.
(You could also do this for DropDB.)

Title: Re: PostgreSQL create database
Post by: MarkMLl on March 11, 2023, 09:10:07 am
Quote from: rvk on March 09, 2023, 01:52:10 pm
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"

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
TinyPortal © 2005-2018