Sign in to follow this  
Followers 0
bobneumann

ADODB command method

6 posts in this topic

I'm trying to run a SQL stored procedure that will take a long time.

I think if I can run the SP in async mode, it will work, but I'm having trouble getting the syntax right.

Here's a VBScript example of what I want to do:

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command

cmd.ActiveConnection = "DSN = test"

cmd.CommandTimeout = 180

cmd.CommandText = "sp_name"

cmd.CommandType = adCmdStoredProc

cmd.Execute , , adAsyncExecute

Here is my AutoIT interpretation:

$adoCon = ObjCreate("ADODB.Connection")
        $adoCon.Open ($DSN)
        $objCommand = ObjCreate("ADODB.Command")
        $objCommand.ActiveConnection = $adoCon
        $objCommand.CommandTimeout = 10
        $objCommand.CommandText = "_init_img_tables"
;$cmdtype="adcmdstoredproc"
        $objCommand.CommandType = 4;(I got  the value "4" as the CommandTypeEnum Value for "adCmdStoredProc' from http://www.w3schools.com/ADO/prop_comm_commandtype.asp)
        $objCommand.Execute = ", 400, adAsyncExecute";(See http://www.w3schools.com/ADO/met_comm_execute.asp)

I want to run " $objCommand.execute , 400, adAsyncExecute " which means execute <no return value>,<parameter input of "400">,<with Option "adAsyncExecute">

the console output is:

C:\Program Files\autoit\test.au3 (324) : ==> The requested action with this object has failed.:
$objCommand.Execute = ", 400, adAsyncExecute"
$objCommand.Execute = ", 400, adAsyncExecute"^ ERROR

I can successfully use the "execute" method plain. But how do I format the AutoIT script in order to pass these options?

Thanks in advance,

Bob

Share this post


Link to post
Share on other sites



Use the Default keyword for an empty parameter.

adAsyncExecute seems to be some constant, I'll use number 5 just for example.

So try this:

$objCommand.Execute(Default, 400, 5)

1 person likes this

Share this post


Link to post
Share on other sites

Use the Default keyword for an empty parameter.

adAsyncExecute seems to be some constant, I'll use number 5 just for example.

So try this:

$objCommand.Execute(Default, 400, 5)

Thanks for the reply. I try that as soon as I can. From what I've read, it is apparently significant whether the command gets passed with parentheses. (With parentheses means "I expect a response/acknowledgement" without parentheses means I don't. Is there a way to pass these without parentheses?

Thanks again,

Bob

Share this post


Link to post
Share on other sites

Use the Default keyword for an empty parameter.

adAsyncExecute seems to be some constant, I'll use number 5 just for example.

So try this:

$objCommand.Execute(Default, 400, 5)

With your inputk I got the script to run. I ended up looking up the enumvalues for the execute method on MSDN.

...execute(default,400,0x10) ran successfully. It didn't actually fire the stored proc. in the way I was looking for, but it did run without error. (The autoit script immediately carries on to the next step, but the stored procedure, which I expect to be running on the server/in the background, isn't. It runs for about a second then just vanishes.) I'm going to give up and set a really high .command.timeout value (10000 or so) and make the script wait....

Thanks again for your help.

Bob

Share this post


Link to post
Share on other sites

Is there a way to pass these without parentheses?

Glad to help!

AutoIt requires parentheses every time when calling functions/procedures/methods.

One exception: COM object's method without parameters can be left without parentheses.

So these are the same thing:

$oObject.SomeMethod()

$oObject.SomeMethod

Share this post


Link to post
Share on other sites

I use store procedures in this way, in this example is

can pass parameters to the store

Dim $oConn1
Dim $oCmd
Dim $strConnetion

$strConnetion = "Dsn=Order;Uid=;Pwd=;"
$oConn1 = ObjCreate("ADODB.Connection")
$oConn1.open($strConnetion)

$oCmd = ObjCreate("ADODB.Command")
$oCmd.ActiveConnection = $oConn1

$oCmd.CommandText = "call UpdateOrderTest('1234')"
$oCmd.Execute ( Default, 0, -1 )

$oConn1.close

MsgBox(0,"","Update OK !!!")

the my store is "UpdateOrderTest" and "1234" is tha parameter.

I use the database mysql

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