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

2015-05-16 Thread silvioprog
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

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

2015-05-16 Thread 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.___
fpc-pascal maillist  -  fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

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

2015-05-16 Thread silvioprog
On Sat, May 16, 2015 at 1:51 PM, Michael Van Canneyt  wrote:
>
> On Sat, 16 May 2015, silvioprog wrote:
>
>> [...]
>>
> Set the stoUseImplicit option on the transaction:
>
> VCon.Transaction.options:=[stoUseImplicit]


Thanks for the fast reply buddy.

I have another problem: my FPC version is 2.6.4, and this option is not
available in this version. =/

But just to test, I did it using FPC from trunk, and the following code
returned another error:

[code]
var
  VCon: TPQConnection;
begin
  VCon := TPQConnection.Create(nil);
  VCon.Transaction := TSQLTransaction.Create(VCon);
  try
VCon.Transaction.Options := VCon.Transaction.Options + [stoUseImplicit];
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:

#0 PQCONNECTION$_$TPQTRANS_$__$$_REGISTERCURSOR$TPQCURSOR at :0
#1
PQCONNECTION$_$TPQCONNECTION_$__$$_EXECUTE$TSQLCURSOR$TSQLTRANSACTION$TPARAMS
at :0
#2 SQLDB$_$TSQLCONNECTION_$__$$_EXECUTEDIRECT$ANSISTRING$TSQLTRANSACTION at
:0
#3 SQLDB$_$TSQLCONNECTION_$__$$_EXECUTEDIRECT$ANSISTRING at :0
#4 TFORM1__BUTTON1CLICK(0x1dc248, ) at Unit1.pas:45
...


> Why methods like "ExecuteDirectPG", "CheckConnectionStatus",
>> "CheckResultError", "TranslateFldType" and "GetExtendedFieldInfo" are not
>> declared as protected methods?
>>
>
> No particular reason. That can be changed.
>
> Michael.


Very nice. I can create a patch to move this methods to the protected area.
=)

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