Sign in to follow this  
Followers 0
xoen

Stored procedure with parameters

5 posts in this topic

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

Share this post


Link to post
Share on other sites



Define "stored procedure".

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

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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

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