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).
And the code that works fine in Zeos:
[code]
var
VCon: TZConnection;
begin
VCon := TZConnection.Create(nil);
try
VCon.Protocol := 'postgresql-9';
VCon.HostName := '127.0.0.1';
VCon.Database := 'postgres';
VCon.User := 'postgres';
VCon.Password := 'postgres';
VCon.Connect;
VCon.ExecuteDirect(
'CREATE DATABASE "mydatabase" 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/]
Taking a look at the internal TPQConnection code, I found the method
"ExecuteDirectPG", and propositally I moved it to public area and tested
again using it:
[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.ExecuteDirectPG(
'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]
Finally, it worked fine. So I have two questions:
Using SQLdb, how to execute scripts directly in the driver outside a
transaction?
Why methods like "ExecuteDirectPG", "CheckConnectionStatus",
"CheckResultError", "TranslateFldType" and "GetExtendedFieldInfo" are not
declared as protected methods?
Thank you!
PS. please apply this patch to fix a typo in dbconsts unit:
http://bugs.freepascal.org/view.php?id=28107
--
Silvio Clécio
My public projects - github.com/silvioprog
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
