Stored procedure with parameters

[xoen]

Hello, I've searched the forums but couldn't find any info.

I'm trying to call a stored procedure giving it some parameter.

Can you post me an example or some documentation on how to do it in Autoit?

Thanks

[Richard Robertson]

Define "stored procedure".

If you mean a function pointer, I don't believe AutoIt has a built in way to call those yet.

[zfisherdrums]

Hello, xoen;

I cannot make any guarantee on its correctness, but the code below is a port from this example written in VBScript. It uses an ADO command object to accomplish this task. Your connection string, stored procedure name and parameters will vary (obviously), but the general idea is captured.

Func z_testInterrogation($strParam1, $strParam2, $intParam3, $decParam4, $datParam5)

    Dim $returnVal      
    Dim $cmd                = ObjCreate("ADODB.Command")        ; Command Object
    $cmd.ActiveConnection   = "DSN=LocalServer;UID=SA;PWD="
    $cmd.CommandType        = 4                                 ; 4 = Stored Procedure
    $cmd.CommandText        = "sp_MyStoredProcedure"

    $cmd.Parameters.Append ($cmd.CreateParameter("RETURN_VALUE", 3, 4, 0, $returnVal))
    $cmd.Parameters.Append ($cmd.CreateParameter("@strParam1", 200, 1, 14, $strParam1))
    $cmd.Parameters.Append ($cmd.CreateParameter("@strParam2", 200, 3, 18, $strParam2))
    $cmd.Parameters.Append ($cmd.CreateParameter("@intParam3", 3, 1, 0, $intParam3))
    $cmd.Parameters.Append ($cmd.CreateParameter("@decParam4", 131, 1, 0, $decParam4))
    $cmd.Parameters.Append ($cmd.CreateParameter("@datParam5", 135, 1, 0, $datParam5))

    $cmd.Parameters("@decParam4").Precision = 18

    $cmd.Execute

    $strParam2 = $cmd.Parameters("@strParam2").Value
    $returnVal = $cmd.Parameters("RETURN_VALUE").Value

    $cmd = 0
    
    Return $returnVal
    
EndFunc

Also, you should check out the Parameters Collection and the Parameters.Append Method.

My linkhttp://msdn.microsoft.com/en-us/library/ms675869(VS.85).aspx

My linkhttp://msdn.microsoft.com/en-us/library/ms677209(VS.85).aspx

Hope this helps you in your journey!

Zach...

Edited July 9, 2009 by zfisherdrums

[xoen]

Hello zfisherdrums, thank you for your help.

The thing I was missing was the RETURN_VALUE notion, i.e.

$cmd.Parameters.Append ($cmd.CreateParameter("RETURN_VALUE", 3, 4, 0, $returnVal))

...

$returnVal = $cmd.Parameters("RETURN_VALUE").Value

Now I am able to call the stored procedure correctly.

Thanks again!

[gandaria700]

DB2® Spatial Extender stored procedures are invoked implicitly when you enable and use Spatial Extender from the DB2 Control Center or when you use the DB2 Spatial Extender CLP (db2se). You can invoke stored procedures explicitly in an application program or from the DB2 command line.

This topic describes how to diagnose problems when stored procedures are invoked explicitly in application programs or from the DB2 command line. To diagnose stored procedures invoked implicitly, you use the messages returned by the DB2 Spatial Extender CLP or the messages returned by the DB2 Control Center. These messages are discussed in separate topics.

DB2 Spatial Extender stored procedures have two output parameters: the message code (msg_code) and the message text (msg_text). The parameter values indicate the success or failure of a stored procedure.

Edited August 31, 2009 by Valik