PostgreSQL create database

[SymbolicFrank]

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;

[zeljko]

What is TSpecialConnector ?

[SymbolicFrank]

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.  

[rvk]

So, what happens on line 11.
"Doesn't work" isn't really a good problem description.
Do you get an Excpetion?

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.

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?

[MarkMLl]

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]

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]

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).

[rvk]

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.

[SymbolicFrank]

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]

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 

The other option would be tat TSpecialConnector doesn't inherit properly.

[rvk]

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][+][-]

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.)

[MarkMLl]

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