[Lazarus] TPQConnection.CreateDB: ENCODING, OWNER, TEMPLATE, LC_CTYPE, TABLESPACE
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". -- Silvio Clécio My public projects - github.com/silvioprog -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TPQConnection.CreateDB: ENCODING, OWNER, TEMPLATE, LC_CTYPE, TABLESPACE
On Wed, 8 Oct 2014, silvioprog wrote: 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? Try setting them as 'Name=Value' pairs in the Params property of the connection component. Michael.-- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TPQConnection.CreateDB: ENCODING, OWNER, TEMPLATE, LC_CTYPE, TABLESPACE
On Wed, Oct 8, 2014 at 12:44 PM, Michael Van Canneyt wrote: > > On Wed, 8 Oct 2014, silvioprog wrote > >> 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? >> > > Try setting them as 'Name=Value' pairs in the Params property of the > connection component. > > Michael. 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 " via TProcess. Now it worked like a charm! Thank a lot! -- Silvio Clécio My public projects - github.com/silvioprog -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TPQConnection.CreateDB: ENCODING, OWNER, TEMPLATE, LC_CTYPE, TABLESPACE
On 28/10/2014 02:03, 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 > " 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. PS: Glad you did find a workaround but... well... -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TPQConnection.CreateDB: ENCODING, OWNER, TEMPLATE, LC_CTYPE, TABLESPACE
On 10/28/2014 02:03 AM, silvioprog wrote: 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 " via TProcess. Now it worked like a charm! 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 And sqldb is not some magical tool, it's just a wrapper around this driver. You could have looked at TPQConnection.CreateDB 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. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TPQConnection.CreateDB: ENCODING, OWNER, TEMPLATE, LC_CTYPE, TABLESPACE
On Tue, Oct 28, 2014 at 8:19 AM, Reinier Olislagers < reinierolislag...@gmail.com> wrote: > On 28/10/2014 02:03, 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 > > " 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. > Oopss... sorry, I'll do that dude. =) > 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. > > 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 -- Silvio Clécio My public projects - github.com/silvioprog -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] TPQConnection.CreateDB: ENCODING, OWNER, TEMPLATE, LC_CTYPE, TABLESPACE
On Wed, Oct 29, 2014 at 8:26 AM, Joost van der Sluis wrote: > > On 10/28/2014 02:03 AM, silvioprog wrote: >> >> 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 >> " via TProcess. Now it worked like a charm! > > > 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 And sqldb is not some magical tool, it's just a wrapper around this driver. You could have looked at TPQConnection.CreateDB 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 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 -- Silvio Clécio My public projects - github.com/silvioprog -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus