Print Page - Using ADODB.Command to delete records in a SQL database

Title: Using ADODB.Command to delete records in a SQL database
Post by: milesg on May 19, 2015, 05:07:37 AM

How do I obtain the number of records deleted by the following code extract? (assuming sql_query_1 contains a valid SQL DELETE Statement)
   RS = CreateObject("ADODB.Command")
   RS.ActiveConnection = DB
   RS.CommandType = 1
   RS.CommandText = sql_query_1
   RS.Execute

Thanks in advance for your help.

Title: Re: Using ADODB.Command to delete records in a SQL database
Post by: td on May 19, 2015, 06:47:01 AM

Can't say that I have ever used it on a delete operation but based on the information provided by the WIL Type Viewer, try something like the following:

Code (winbatch) Select


RecordsAffected = 0
RS.Execute(RecordsAffected)
Message("Records Affected", RecordsAffected)

Title: Re: Using ADODB.Command to delete records in a SQL database
Post by: milesg on May 19, 2015, 08:57:22 AM

I tried exactly that the ADODB.Execute cmd complains about the type being incorrect

Title: Re: Using ADODB.Command to delete records in a SQL database
Post by: td on May 19, 2015, 10:52:54 AM

Then you need to specify the type information in the standard way. You might want to consider specify the name of the parameter since it is optional and just to be safe pass the query directly into the method instead of using a property.

Code (winbatch) Select


nAffected = 0
RS.Execute(sql_query_1, :: i4:RecordsAffected=nAffected)

COM Automation named and typed parameters are explained by examples in the COM help file section of the Consolidated WIL Help file.

Title: Re: Using ADODB.Command to delete records in a SQL database
Post by: milesg on May 19, 2015, 12:01:55 PM

Thanks for the help, I appear to be moving in the right direction now.

Title: Re: Using ADODB.Command to delete records in a SQL database
Post by: milesg on May 21, 2015, 04:27:53 AM

I have tested this call process using a SQL DELETE, SQL UPDATE and SQL INSERT and the .Execute process always returns a 0 value. I know that my SQL transactions are in fact actually INSERTING, DELETING or UPDATINF records correctly, as I have tested them outside of WINBATCH.
I set the RecordsAffected variable to 99 prior to the call and it is returned as 0 by the .Execute function so I know that the call is setting it.
Could the ADODB Command.Execute call have a problem in WINBATCH and the RecordsAffected value is not being set correctly upon return?

Title: Re: Using ADODB.Command to delete records in a SQL database
Post by: td on May 21, 2015, 07:52:47 AM

It is very doubtful that 'ADODB Command.Execute call have a problem in WINBATCH'. There are many layers of software between the database engine and WinBatch. Not all features are made available by all drivers/providers and this is a common problem reported by users of COM Automation clients other than WinBatch.

Title: Re: Using ADODB.Command to delete records in a SQL database
Post by: milesg on May 21, 2015, 10:17:15 AM

Thanks, I had to ask the question.

Title: Re: Using ADODB.Command to delete records in a SQL database
Post by: stanl on May 23, 2015, 04:33:10 AM

Have you considered using Connection.Execute rather than Command.Execute?

Title: Re: Using ADODB.Command to delete records in a SQL database
Post by: td on May 25, 2015, 09:21:25 AM

Also note that some databases support turning record counting on and off via an SQL command and may be configured to default to the off state for performance reasons. But the possibly is of course, source engine dependent.

WinBatch® Technical Support Forum

All Things WinBatch => WinBatch => Topic started by: milesg on May 19, 2015, 05:07:37 AM