[fpc-pascal] Boring problem when I try to create a database using SQLdb (again)

[Michael Van Canneyt]

On Sat, 16 May 2015, silvioprog wrote:

> Hello,
> I need to create numbered databases like 09_0025, 09_0026 etc., that using any SQL tool like pgAdmin, I just execute this script, and it works fine:
> 
> CREATE DATABASE "09_0025" WITH ENCODING='UTF-8'
>   OWNER="postgres" TEMPLATE="template1" LC_COLLATE='English_United States.1252' LC_CTYPE='English_United States.1252'
>   CONNECTION LIMIT=-1 TABLESPACE="pg_default" 
> 
> Notice that I need to specify some details, like ENCODING, OWNER, LC_COLLATE/LC_CTYPE etc., so the TPQConnection.CreateDB can't be used because it doesn't allows to configure those
> details.
> 
> Another information before show my tests: this script above works fine in other libs like Zeos, UniDAC and JDBC, I tested it yesterday.
> 
> Now, please see my tests and their respective errors.
> 
> Test 1:
> 
> [code]
> var
>   VCon: TPQConnection;
> begin
>   VCon := TPQConnection.Create(nil);
>   try
>     VCon.HostName := '127.0.0.1';
>     VCon.DatabaseName := 'postgres';
>     VCon.UserName := 'postgres';
>     VCon.Password := 'postgres';
>     VCon.ExecuteDirect(
>       'CREATE DATABASE "09_0025" WITH ENCODING=''UTF8'' ' +
>       '  OWNER="postgres" TEMPLATE="template1" LC_COLLATE=''English_United States.1252'' LC_CTYPE=''English_United States.1252'' ' +
>       '  CONNECTION LIMIT=-1 TABLESPACE="pg_default"');
>   finally
>     VCon.Free;
>   end;
> end;
> [/code]
> 
> Error:
> 
> Transaction not set.
> 
> Test 2:
> 
> [code]
> var
>   VCon: TPQConnection;
> begin
>   VCon := TPQConnection.Create(nil);
>   VCon.Transaction := TSQLTransaction.Create(VCon);
>   try
>     VCon.HostName := '127.0.0.1';
>     VCon.DatabaseName := 'postgres';
>     VCon.UserName := 'postgres';
>     VCon.Password := 'postgres';
>     VCon.ExecuteDirect(
>       'CREATE DATABASE "09_0025" WITH ENCODING=''UTF8'' ' +
>       '  OWNER="postgres" TEMPLATE="template1" LC_COLLATE=''English_United States.1252'' LC_CTYPE=''English_United States.1252'' ' +
>       '  CONNECTION LIMIT=-1 TABLESPACE="pg_default"');
>   finally
>     VCon.Free;
>   end;
> end;
> [/code]
> 
> Error:
> 
> TPQConnection : Execution of query failed  (PostgreSQL: ERROR:  CREATE DATABASE cannot run inside a transaction block
> Severity: ERROR
> SQL State: 25001
> Primary Error: CREATE DATABASE cannot run inside a transaction block).

Set the stoUseImplicit option on the transaction:

VCon.Transaction.options:=[stoUseImplicit]

> 
> Why methods like "ExecuteDirectPG", "CheckConnectionStatus", "CheckResultError", "TranslateFldType" and "GetExtendedFieldInfo" are not declared as protected methods?

No particular reason. That can be changed.

Michael.