Sign in to follow this  
Followers 0
dickep

MSSQL 2005 Stored Procedure call

9 posts in this topic

I know this has been asked, but have not seen an answer that I can understand/use

My function is below.

Func mQueryDatabase(ByRef $Cat, ByRef $Attrib, $DefaultValue = 100, $DefaultUsed = 0)
    $oADODB = _SQL_Startup()
    If $oADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())

    If _sql_Connect($oADODB, "Server", "MyDB", "UserName", "MyPassword") = $SQL_ERROR Then
        MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
        _SQL_Close()
        Exit
    EndIf
    
    $FNPSelect1 = "SELECT Col3 FROM MyParameters "
    $FNPSelect2 = "WHERE Cat = '" & $Cat & "' AND Attrib = '" & $Attrib & "';"

    
    _SQL_Close()

EndFunc

Now, I just want to execute the stored procedure "spGetSomething" with the parameters above. I have gotten this far, but am not sure how to continue. I just want to get the single value I am looking for (namely just the "Col3" value that matches the "WHERE" statement).

I am using _sql.au3 for this and have looked at the _SQL_FetchData and _SQL_Execute but either I am too dense to understand how to use these or they really cannot be used like I want.

Let me know what I can do to finish up this function - it will be used many times if I can get it to work.

Thanks

E

Share this post


Link to post
Share on other sites



Thanks but the search engine did not like it when I put in "$oConn.Execute($SQL)" in the search box. Came back with message ::No results found for '$oconnexecute$sql'.::

Maybe need some help with the search function - can't seem to do what I would like to do.

E

Share this post


Link to post
Share on other sites

Tried but it seems the internal search engine keeps dropping the period(.) and makes it one word. Any more ideas? I am very frustrated on the search ability here.

E

Share this post


Link to post
Share on other sites

Tried but it seems the internal search engine keeps dropping the period(.) and makes it one word. Any more ideas? I am very frustrated on the search ability here.

E

Search must have changed somehow :-(

Search for :

objcreate ADODB Connection

And also search in Examples section only.

Share this post


Link to post
Share on other sites

Zedna, tried your suggestions. Did not get any results that pertain to how to call a stored procedure that returns values. Stored procedures can return values but can't figure out how to access that - calling it correctly might help.

Thanks all

E

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

Example code snippet from my working script how it can be called in AU3 (this can be accomodated also for stored procedures -->adCmdText change to adCmdStoredProc)

$oConn = ObjCreate("ADODB.Connection")
    $oConn.ConnectionTimeout = 3 ; default is 15 s 
    
    $oRS = ObjCreate("ADODB.Recordset")
    $oRS.CursorType = 3 ; adOpenStatic
    $oRS.LockType = 3 ; adLockOptimistic

    $DSN = "Driver={SQL Server};Server=%server%;Database=%databaze%;Uid=%jmeno%;Pwd=%heslo%;"
    $DSN = StringReplace($DSN,"%server%", $ini_server)
    $DSN = StringReplace($DSN,"%databaze%", $ini_databaze)
    $DSN = StringReplace($DSN,"%jmeno%", $ini_jmeno)
    $DSN = StringReplace($DSN,"%heslo%", $ini_heslo)
    
    $oConn.Open($DSN)

    $rowcount = -1
    $SQL_command = "..."
    $oConn.Execute($SQL_command, $rowcount, 129) ; adCmdText=1 + adExecuteNoRecords=0x80 (128)
Edited by Zedna

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