Sign in to follow this  
Followers 0
lowrider2025

multiple sql queries with 1 mysql connection ?

7 posts in this topic

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

Share this post


Link to post
Share on other sites



Hi lowrider,

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

:unsure:


Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites

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.

:unsure:

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by spudw2k

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


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
Sign in to follow this  
Followers 0