Database Query With Variable Submission

[Ghost21]

I need to be able to submit a query request and pass a variable to the database along with it..

$Recordset.Open('exec TombstonedDevices', $Connection, 1, 3) <-- Thats what I use for a query. But I need the part to add the variable to it ???

[spudw2k]

Can you elaborate a bit more? Which variable and how are you planning to use it withing the func?

[Ghost21]

Lets say a $pc <-- eg. a pc name then in a Query you can have a prompted variable request. like a inputbox or a predefined variable.. I want to call the query and say to run this query with the PC variable and give me the results..

[spudw2k]

Would you provide the variable to the TombstonedDevices procedure? Could you not append the variable to the "exec" string?

[Ghost21]

If I knew how to...

[Ghost21]

Here is the Sql Line..

SELECT PCData.PC, PCData.PING, PCData.Tombstoned, PCData.LASTUPDATED FROM PCData WHERE (((PCData.PC) Is Not Null) AND ((PCData.Tombstoned)=-1))ORDER BY PCData.PC, PCData.PING, PCData.LASTUPDATED;

[spudw2k]

Is that is what is being run by executing the stored procedure? I can't tell where the variables fit in.

I'm not too fresh on Stored Procedures, but here's a basic example of using an SP with arguments/variables:

CREATE PROCEDURE sp_myStoredProcedure

@myInput int

AS

Select column1, column2 From Table1

Where column1 = @myInput

Go

Then call it like this:

sp_myStoredProcedure 0

[Ghost21]

Is the " @ " sign that I need to add to the database side to do the variable submission ?

[spudw2k]

yes, when you create a procedure the @varname designates a var.

this is where I copied and pasted from.

http://www.4guysfromrolla.com/webtech/111499-1.shtml

There is another example on that page for multiple variables in an SP.

[Ghost21]

I need to see working code as I have been trying this for so long ...

[Ghost21]

This is what I'm trying..

_dbopen()

$Recordset.Open('exec Query1 192.168.10.166', $Connection, 1, 3)

With $Recordset

If .Recordcount Then

MsgBox(0, .Recordcount & " Devices found.", "")

While Not .EOF

$pc = .Fields("PC" ).Value

MsgBox(0, "PC", $pc)

.MoveNext

$pc = ""

WEnd

EndIf

EndWith

$Connection.close

_dbclose()

[spudw2k]

At a glance that looks fine too me, but since I can't see the db and query you are working with I can't troubleshoot it much further.

How far does it get? Is there an error message?

[Ghost21]

Damn I wish sometimes there was a live chat option...

this is the code I'm trying now..

All I want to do is submit the variable to the query so it can use it look for the ip and tell me back the PC.. I want to keep as much of the sql statement in the database as its not needed in the program..

expandcollapse popup

Global $Recordset
Global $Connection
Global $DatabasePath = IniRead("config.ini", "Path", "DatabasePath", @ScriptDir)
Global $Filelocation = IniRead("config.ini", "Path", "Filelocation", @ScriptDir)
Global $PsToolsFilelocation = IniRead("config.ini", "Path", "PsToolsFilelocation", @ScriptDir)
Global $UtilsFilelocation = IniRead("config.ini", "Path", "UtilsFilelocation", @ScriptDir)
Global $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")

_dbopen()
$Recordset.Open("exec Query1 WHERE IP='192.168.10.94'", $Connection, 1, 3)
;$Recordset.Open("Select * FROM WH WHERE PC='" & $pc & "'", $Connection, 1, 3)
With $Recordset
    If .Recordcount Then
        MsgBox(0, .Recordcount & " Devices found.", "")
        While Not .EOF
            $pc = .Fields("PC" ).Value
            MsgBox(0, "PC", $pc)
            .MoveNext
            $pc = ""
        WEnd
    EndIf
EndWith
$Connection.close
_dbclose()

Func _dbOpen()
    $Connection = ObjCreate("ADODB.Connection")
    $Recordset = ObjCreate("ADODB.Recordset")
    If FileExists($DatabasePath & "sw.mdb") Then
        $Connection.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=" & $DatabasePath & "sw.mdb")
    Else
        MsgBox(0, "Database Not Found", "Please Install DataBase", 25)
        Exit
    EndIf
EndFunc   ;==>_dbOpen

Func _dbClose()
    $Connection = 0
EndFunc   ;==>_dbClose

Func MyErrFunc()
    Global $HexNumber = Hex($oMyError.number, 8)
    If $CmdLine[0] = 0 Then
        MsgBox(0, "COM Error", "COM Error Details:" & @CRLF & @CRLF & _
                "err.description is: " & @TAB & $oMyError.description & @CRLF & _
                "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
                "err.number is: " & @TAB & $HexNumber & @CRLF & _
                "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
                "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
                "err.source is: " & @TAB & $oMyError.source & @CRLF & _
                "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
                "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
                )
    Else
    EndIf
    Return SetError($HexNumber)
EndFunc   ;==>MyErrFunc

[spudw2k]

if you run the exec query without a variable does it return results?

[Ghost21]

NO: If I change the query to already have a variable there then it will respond. When I run it right now I get no error's.

[spudw2k]

Sounds like either a malformed query or you are attempting to find a record that doesn't exist.

Your last post also has something strange in it. When calling a procedure you only supply the variable values (don't supply WHERE).

[Ghost21]

OMFG I figured it out...

SO STUPID I'm GOING TO SCREAM...

$Recordset.Open("exec Query1 '192.168.10.94'", $Connection, 1, 3)

if you create a query and use this sql stement it works .... YEEHAW....

SELECT PCData.PC, [@IP] AS Expr1, PCData.IP
FROM PCData
WHERE ((([@IP])=[IP]));

That makes the query and then you can push variables into the database so that all the processing is done there..

:)

[spudw2k]

bingo. good job

edit:

I'm pretty sure you don't need to "SELECT" [@IP] As Expr1, but I cannot test this.

CREATE PROCEDURE Query1

@IP as varchar(16)

AS

Select PCData.PC, PCData.IP From PCData

Where IP = @IP

Go

Edited May 2, 2011 by spudw2k