Sign in to follow this  
Followers 0
Ghost21

Database Query With Variable Submission

18 posts in this topic

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 ???

Share this post


Link to post
Share on other sites



Share this post


Link to post
Share on other sites

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..

Share this post


Link to post
Share on other sites

Share this post


Link to post
Share on other sites

If I knew how to...

Share this post


Link to post
Share on other sites

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;

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#10 ·  Posted

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

Share this post


Link to post
Share on other sites

#11 ·  Posted

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()

Share this post


Link to post
Share on other sites

#12 ·  Posted

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?

Share this post


Link to post
Share on other sites

#13 ·  Posted

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..

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

Share this post


Link to post
Share on other sites

#15 ·  Posted

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.

Share this post


Link to post
Share on other sites

#16 ·  Posted

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).

Share this post


Link to post
Share on other sites

#17 ·  Posted

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..

:unsure::>;):D;):) :) :):D

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

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

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