Jump to content

multiple sql queries with 1 mysql connection ?


Recommended Posts

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
Link to comment
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:

Link to comment
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.

Link to comment
Share on other sites

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
Spoiler

Things I've Made: Always On Top Tool ◊ AU History ◊ Deck of Cards ◊ HideIt ◊ ICU ◊ Icon Freezer ◊ Ipod Ejector ◊ Junos Configuration Explorer ◊ Link Downloader ◊ MD5 Folder Enumerator ◊ PassGen ◊ Ping Tool ◊ Quick NIC ◊ Read OCR ◊ RemoteIT ◊ SchTasksGui ◊ SpyCam ◊ System Scan Report Tool ◊ System UpTime ◊ Transparency Machine ◊ VMWare ESX Builder
Misc Code Snippets: ADODB Example ◊ CheckHover ◊ Detect SafeMode ◊ DynEnumArray ◊ GetNetStatData ◊ HashArray ◊ IsBetweenDates ◊ Local Admins ◊ Make Choice ◊ Recursive File List ◊ Remove Sizebox Style ◊ Retrieve PNPDeviceID ◊ Retreive SysListView32 Contents ◊ Set IE Homepage ◊ Tickle Expired Password ◊ Transpose Array
Projects: Drive Space Usage GUI ◊ LEDkIT ◊ Plasma_kIt ◊ Scan Engine Builder ◊ SpeeDBurner ◊ SubnetCalc
Cool Stuff: AutoItObject UDF â—Š Extract Icon From Proc â—Š GuiCtrlFontRotate â—Š Hex Edit Funcs â—Š Run binary â—Š Service_UDF

 

Link to comment
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
Link to comment
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.

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...