Jump to content

Help for multiple read/write connections to PostgreSQL from AutoIT script.

Recommended Posts

This is my script.

$Conn = ObjCreate("ADODB.Connection")
$Record = ObjCreate("ADODB.Recordset")
$DataRecord = ObjCreate("ADODB.Recordset")

Func Create_Conn()
[indent=1]$strConn = "DSN=PostgreSQL32;DATABASE=MyDB DB;SERVER=localhost;Port=5432;Uid=postgres;Pwd=MyPass"[/indent]
[indent=1]If @error Then MsgBox(0,"","Error Opening Connection")[/indent]

Func Close_Conn()

Func ID()
[indent=1]Local $Task,$strReturn[/indent]
[indent=1]$Task = "SELECT name,id FROM list WHERE name = '" & $Name  "';"[/indent]
[indent=1]If @error Then MsgBox(0,"","Error Executing SQL in: Func ID")[/indent]
[indent=1]$strReturn = $Record.recordcount[/indent]

[indent=1]If $strReturn > 0 Then[/indent]
[indent=2]$strReturn = $Record.GetRows()[/indent]
[indent=2]$ID = $strReturn[0][1][/indent]
[indent=2]MsgBox(0,"","Name: " & $strReturn[0][0] & " ID: " & $ID)[/indent]


The skript is working, but the problem is that is very slow.

When I start querry in pqAdmin response time is 0.05 sec. But from my script is more that 30 sec. And I don't know how to improve. Any idea???


Edited by BigyBG
Link to post
Share on other sites
  • Moderators

BigyBG, please do not bump your posts in less than 24 hours. This gives us plenty of time to try to assist you.

Also, please place your code in AutoIt tags. This will make it much easier to read.

Edited by JLogan3o13

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to post
Share on other sites

About your initial question: all ADO/ODBC drivers I know off allow for unbounded number of simultaneous connections (up to available RAM and such).

As it is your script is certainly not "taking 30s" since it does nothing beyond declaring 3 objects and 3 functions (never invoked) and so would return almost instantly.

When you post your actual script of failing short reproducer, someone can eventually help.

Also something generic (valid for PostGres and almost any DB engine AFAIK): use available escape mecanism for escaping possible single quotes in the litteral values you feed (may depend on actual engine, but is commonly made by doubling inner quote(s)). In your example ID function, if $name contains a quote, the SQL gets invalid. If it doesn't it's open to SQL injection. Now where $name comes from is a mystery.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...