multiple sql queries with 1 mysql connection ?

[lowrider2025]

Hi,

I'm having some trouble with a script that needs to do work on multiple tables. In vbs you can close the recordset but it will not be destroyed until you do "set rs = nothing" when you do rs.close in autoit the object is gone apparently because I get an error when I try to open a new sql query with the existing recordset object. If I remove the rs.close it doesn't protest for not closing the recordset, but the following sql query generates an error. Any ideas how you can do multiple sql queries with 1 recordset object ?

sample code:

$sServerIP = "ip.ip.ip.ip"
        $sUsername = "user"
        $sPassword = "password"
        $sDatabase = "database"
        $sConnectionString = "DRIVER=" & $sDriver & ";Server=" & $sServerIP & ";Uid=" & $sUsername & ";Password=" & $sPassword & ";Database=" & $sDatabase & ";Option=" & $iOption
        $sSQL = "SELECT * FROM logins ORDER BY id desc limit 1"

        $oRS = ObjCreate("ADODB.RecordSet")
        $oRS.CursorType = $iAdOpenStatic
        $oRS.LockType = $iAdLockOptimistic
        $oRS.ActiveConnection = $sConnectionString
        $oRS.Open($sSQL)
        $oRS.Addnew
        $oRS("alias") = @UserName
        $oRS("hostname") = @ComputerName
        $oRS("ip") = $Nic.IPAddress(0)
        $oRS("date") = _NowDate()
        $oRS("time") = _NowTime()
        $oRS("log_in_out") = "login"
        $oRS.Update
        $oRS.Close

[hannes08]

Hi lowrider,

did you try ProgAndy's Without having a closer look at it, this should be what you are looking for.

[lowrider2025]

Thx for the reply. But using the UDF, you would have to use a complete different syntax which I do not fully master at the moment. Is there no way to do it the way I'm doing it now ?

Hi lowrider,

did you try ProgAndy's Without having a closer look at it, this should be what you are looking for.

[lowrider2025]

I solved my own problem. I got the vbs script from a colleague. He doesn't have any problems apparently. But when I translate it to autoit I have to explicitly make a ADODB.Connection object which I can use till I close it and so I can make multiple recordsets.

[spudw2k]

i guarantee you can make autoit do the same thing as the vbscript.

I see no reason why you can't have multiple record sets.

edit:

i guarantee autoit can be made do the same thing as the vbscript.

Edited May 6, 2011 by spudw2k

[DarkestHour]

I do not know if this will help you, but this is what i do in one of my programs to pull data from multiple tables using the same connection without having to close anything.

Also of note is that my Arrays are declared globally and stay populated when the connection is ultimately closed. I do not know for sure how your record sets fuction that way, but there is no reason you need to close your connection until your done with it.

Hope this helps!

$DBFc = ObjCreate('ADODB.Connection') ; initializes ADODB connection
$DBFc.Open ('Provider=vfpoledb.1;Data Source="C:\Databases\";Password="";Collating Sequence=MACHINE') ; sets VFP connection and data location
$DBFr = ObjCreate('ADODB.Recordset') ; initializes ADODB record processing
$DBFr.CursorType = 1 ;
$DBFr.LockType = 3 ;
$DBFr.Open ("select jadesc, jypqty, jacqty, jylevl from prolink where jypart = '"&$PARTNO&"' and jhjob = '"&$JOBNO&"' and jaseq = "&$OPNO&" order by jaseq asc", $DBFc)
$MVDATA = $DBFr.GetRows() ; Returns data from sql command into 2d array
$DBFop = ObjCreate('ADODB.Recordset') ; initializes ADODB record processing
$DBFop.CursorType = 1 ;
$DBFop.LockType = 3 ;
$DBFop.Open("select jaseq, jadesc from prolink where jypart = '"&$PARTNO&"' and jhjob = '"&$JOBNO&"' order by jaseq asc", $DBFc)
$OPLISTDATA = $DBFop.GetRows()
; This goes on for some time until,
$DBFc.Close

[DarkestHour]

On another note after looking at an ADODB Reference, You might be able to do what you want with a Requery, using your code as an example,

$oRS = ObjCreate("ADODB.RecordSet")
        $oRS.CursorType = $iAdOpenStatic
        $oRS.LockType = $iAdLockOptimistic
        $oRS.ActiveConnection = $sConnectionString
        $oRS.Open($sSQL)
        $oRS.Addnew
        $oRS("alias") = @UserName
        $oRS("hostname") = @ComputerName
        $oRS("ip") = $Nic.IPAddress(0)
        $oRS("date") = _NowDate()
        $oRS("time") = _NowTime()
        $oRS("log_in_out") = "login"
        $oRS.Update

        $oRS.Requery

        $oRS.Addnew
        $oRS("alias") = @UserName
        $oRS("hostname") = @ComputerName
        $oRS("ip") = $Nic.IPAddress(0)
        $oRS("date") = _NowDate()
        $oRS("time") = _NowTime()
        $oRS("log_in_out") = "login"
        $oRS.Update
        $oRS.Close

Requery is essentially doing a Close and Open in the same command.