Hello,

I need to create a PostgreSQL database from my application. The TPQConnection.CreateDB works fine, but I need to configure some properties of my database in your creation.

In pgAdmin, I execute:

CREATE DATABASE myuser
WITH ENCODING='UTF8'
OWNER=myowner
TEMPLATE=template1
LC_CTYPE='Portuguese_Brazil.1252'
CONNECTION LIMIT=-1

       TABLESPACE=pg_default;

But, how to create a database with TPQConnection passing the ENCODING, OWNER, TEMPLATE, LC_CTYPE and TABLESPACE properties?

Thank you!

ps. I tried ExecuteDirect, but: "CREATE DATABASE cannot be executed inside a transaction block".

Try setting them as 'Name=Value' pairs in the Params property of the connection component.

Michael.

On Wed, Oct 8, 2014 at 12:44 PM, Michael Van Canneyt <

Hello buddy, sorry for my long delay.

Thanks for the great tip, I tried it too, but unfortunately the postgre rejected that talking something like "unrecognized param for this operation".

Today I fixed that using a "mix of codes": creating the database using the original postgre driver for Java, and calling "java -jar create_mydb <params>" via TProcess. Now it worked like a charm!

Thank a lot!

silvioprog wrote

Today I fixed that using a "mix of codes": creating the database using the original postgre driver for Java, and calling "java -jar create_mydb <params>" via TProcess. Now it worked like a charm!

Please don't raise a bug tracker issue... especially one with a simple sample program and PostgreSQL DDL so the issue is easily reproducible for devs.

It's much better to keep using a workaround using another programming language and letting other programmers potentially face the same problem than getting it fixed in a newer version of FPC.
</sarcasm>

PS: Glad you did find a workaround but... well...

Damn, that looks difficult. You know that the bindings for the postgres driver are available for fpc? What you need is PQexec(Handle, 'create mydb <params'); That's all.

And sqldb is not some magical tool, it's just a wrapper around this driver. You could have and
TPQConnection.ExecuteDirectPG. That way you could have added the parameters that you want. (If I look at ExecuteDirectPG, the parameters are only passed when a connection to the DB is made, not when it's created. It's trivial to add, though)

Or you can just copy-paste the code and use a modified version of it in your application.

Well, calling Java with a TProcess works also obviously...

Joost.

On Tue, Oct 28, 2014 at 8:19 AM, Reinier Olislagers <

Oopss... sorry, I'll do that dude. =)

Yes, but I needed to fix the problem urgently, and unfortunatelly with SQLdb doesn't worked. =( The final (quick workaround) code was:

procedure TCriadorBanco.Criar(const ADatabase, AOwner, AUser, APassword,
  ALocale, ATemplate: string);
var
  VJavaExe: string;
  VProcess: TProcess;
  VResult: TStringList;
begin
  VJavaExe :=
{$IFDEF MSWINDOWS}
    IncludeTrailingPathDelimiter(GetEnvironmentVariable('JAVA_HOME'))
{$ELSE}
    '/usr/'
{$ENDIF};
  VJavaExe += 'bin' + DirectorySeparator + path.JAVA_EXE;
  if not FileExists(VJavaExe) then
    raise ECriadorBanco.CreateFmt(
      'Execut?vel do Java n?o encontrado: "%s".', [VJavaExe]);
  if not FileExists(path.CREATE_DB_JAR) then
    raise ECriadorBanco.CreateFmt(
      'Arquivo JAR n?o encontrado: "%s".', [path.CREATE_DB_JAR]);
  VProcess := TProcess.Create(nil);
  VResult := TStringList.Create;
  try
    VProcess.Executable := path.JAVA_EXE;
    VProcess.Options := VProcess.Options + [poWaitOnExit, poUsePipes,
      poStderrToOutPut];
    VProcess.Parameters.Add('-jar');
    VProcess.Parameters.Add(path.CREATE_DB_JAR);
    VProcess.Parameters.Add('-d=' + ADatabase);
    VProcess.Parameters.Add('-o=' + AOwner);
    VProcess.Parameters.Add('-u=' + AUser);
    VProcess.Parameters.Add('-p=' + APassword);
    VProcess.Parameters.Add('-l=' + ALocale);
    VProcess.Parameters.Add('-t=' + ATemplate);
    VProcess.Execute;
    VResult.LoadFromStream(VProcess.Output);
    if (VResult.Count = 2) and (VResult[0] = 'Role successfully created')
and
      (VResult[1] = 'Database successfully created') then
    else
      raise ECriadorBanco.Create(Trim(VResult.Text));
  finally
    VResult.Free;
    VProcess.Free;
  end;
end;

PS: Glad you did find a workaround but... well...

=P~ =D

driver are available for fpc? What you need is PQexec(Handle, 'create mydb <params'); That's all.

hehe...

I tried using the PG driver (modifying the internal ExecuteDirectPG) and it didn't work too. I'll send a small project showing all steps to reproduce the problem.

With TProcess + PSQL worked only on Linux. I'll send this test too.

And sqldb is not some magical tool, it's just a wrapper around this

driver. You could have and
TPQConnection.ExecuteDirectPG. That way you could have added the parameters that you want. (If I look at ExecuteDirectPG, the parameters are only passed when a connection to the DB is made, not when it's created. It's trivial to add, though)

Or you can just copy-paste the code and use a modified version of it in

your application.

Yes yes, I use that, and most of the time I send a diff to the bugtracker lol.

ps. I'm so very glad for SQLdb allows me to create classes like "TConnection.GetInstance.Insert/Update/Delete" that returns the record ID instead of rowsaffected and a "TConnection.GetInstance.RawQuery/Query" that returns my own TCursor or a TArrayList<TMyObject> instead of a tdataset. @.@

Well, calling Java with a TProcess works also obviously...

=)

I love sqldb and fcl-*, but the problem needed to be solved urgently, forcing me to a workaround. =D