Jump to content

Recommended Posts

Posted

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

Posted

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

Posted

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;

Posted

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

Posted

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.

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

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?

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

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

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

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

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
  • Recently Browsing   0 members

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