<div dir="ltr">Hello,<div><br></div><div>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:</div><div><br></div><div><div>CREATE DATABASE "09_0025" WITH ENCODING='UTF-8'</div><div> OWNER="postgres" TEMPLATE="template1" LC_COLLATE='English_United States.1252' LC_CTYPE='English_United States.1252'</div><div> CONNECTION LIMIT=-1 TABLESPACE="pg_default" </div></div><div><br></div><div>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.</div><div><br></div><div>Another information before show my tests: this script above works fine in other libs like Zeos, UniDAC and JDBC, I tested it yesterday.</div><div><br></div><div>Now, please see my tests and their respective errors.</div><div><br></div><div>Test 1:</div><div><br></div><div>[code]</div><div><div>var</div><div> VCon: TPQConnection;</div><div>begin</div><div> VCon := TPQConnection.Create(nil);</div><div> try</div><div> VCon.HostName := '127.0.0.1';</div><div> VCon.DatabaseName := 'postgres';</div><div> VCon.UserName := 'postgres';</div><div> VCon.Password := 'postgres';</div><div> VCon.ExecuteDirect(</div><div> 'CREATE DATABASE "09_0025" WITH ENCODING=''UTF8'' ' +</div><div> ' OWNER="postgres" TEMPLATE="template1" LC_COLLATE=''English_United States.1252'' LC_CTYPE=''English_United States.1252'' ' +</div><div> ' CONNECTION LIMIT=-1 TABLESPACE="pg_default"');</div><div> finally</div><div> VCon.Free;</div><div> end;</div><div>end;</div></div><div>[/code]</div><div><br></div><div><div>Error:</div><div><br></div><div>Transaction not set.<br></div><div><br></div><div>Test 2:</div><div><br></div><div>[code]</div></div><div><div>var</div><div> VCon: TPQConnection;</div><div>begin</div><div> VCon := TPQConnection.Create(nil);</div><div> VCon.Transaction := TSQLTransaction.Create(VCon);</div><div> try</div><div> VCon.HostName := '127.0.0.1';</div><div> VCon.DatabaseName := 'postgres';</div><div> VCon.UserName := 'postgres';</div><div> VCon.Password := 'postgres';</div><div> VCon.ExecuteDirect(</div><div> 'CREATE DATABASE "09_0025" WITH ENCODING=''UTF8'' ' +</div><div> ' OWNER="postgres" TEMPLATE="template1" LC_COLLATE=''English_United States.1252'' LC_CTYPE=''English_United States.1252'' ' +</div><div> ' CONNECTION LIMIT=-1 TABLESPACE="pg_default"');</div><div> finally</div><div> VCon.Free;</div><div> end;</div><div>end;</div></div><div>[/code]</div><div><br></div><div>Error:</div><div><br></div><div><div>TPQConnection : Execution of query failed (PostgreSQL: ERROR: CREATE DATABASE cannot run inside a transaction block</div><div>Severity: ERROR</div><div>SQL State: 25001</div><div>Primary Error: CREATE DATABASE cannot run inside a transaction block).</div><div><br></div></div><div>And the code that works fine in Zeos:</div><div><br></div><div>[code]</div><div><div>var</div><div> VCon: TZConnection;</div><div>begin</div><div> VCon := TZConnection.Create(nil);</div><div> try</div><div> VCon.Protocol := 'postgresql-9';</div><div> VCon.HostName := '127.0.0.1';</div><div> VCon.Database := 'postgres';</div><div> VCon.User := 'postgres';</div><div> VCon.Password := 'postgres';</div><div> VCon.Connect;</div><div> VCon.ExecuteDirect(</div><div> 'CREATE DATABASE "mydatabase" WITH ENCODING=''UTF8'' ' +</div><div> ' OWNER="postgres" TEMPLATE="template1" LC_COLLATE=''English_United States.1252'' LC_CTYPE=''English_United States.1252'' ' +</div><div> ' CONNECTION LIMIT=-1 TABLESPACE="pg_default"');</div><div> finally</div><div> VCon.Free;</div><div> end;</div><div>end;</div></div><div>[code/]</div><div><br></div><div>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:</div><div><br></div><div>[code]</div><div><div>var</div><div> VCon: TPQConnection;</div><div>begin</div><div> VCon := TPQConnection.Create(nil);</div><div> try</div><div> VCon.HostName := '127.0.0.1';</div><div> VCon.DatabaseName := 'postgres';</div><div> VCon.UserName := 'postgres';</div><div> VCon.Password := 'postgres';</div><div> VCon.ExecuteDirectPG(</div><div> 'CREATE DATABASE "09_0025" WITH ENCODING=''UTF8'' ' +</div><div> ' OWNER="postgres" TEMPLATE="template1" LC_COLLATE=''English_United States.1252'' LC_CTYPE=''English_United States.1252'' ' +</div><div> ' CONNECTION LIMIT=-1 TABLESPACE="pg_default"');</div><div> finally</div><div> VCon.Free;</div><div> end;</div><div>end;</div></div><div>[/code]</div><div><br></div><div>Finally, it worked fine. So I have two questions:</div><div><br></div><div>Using SQLdb, how to execute scripts directly in the driver outside a transaction?</div><div><br></div><div>Why methods like "ExecuteDirectPG", "CheckConnectionStatus", "CheckResultError", "TranslateFldType" and "GetExtendedFieldInfo" are not declared as protected methods?</div><div><br></div><div>Thank you!</div><div><br></div><div>PS. please apply this patch to fix a typo in dbconsts unit: <a href="http://bugs.freepascal.org/view.php?id=28107">http://bugs.freepascal.org/view.php?id=28107</a></div><div><br></div><div>-- <br><div class="gmail_signature">Silvio Clécio<br>My public projects - <a href="http://github.com/silvioprog" target="_blank">github.com/silvioprog</a></div>
</div></div>