WinBatch Technical Support Forum

Archived Boards => COM Automation and dotNet => Topic started by: jwteunisse on February 12, 2016, 07:48:16 am

Title: ADODB Connection.Execute(SQLTxt, RecordsAffected) gives errror
Post by: jwteunisse on February 12, 2016, 07:48:16 am

I'm trying to use the following script part in order to update an oracle database using ADO DB and the Connection.Execute statement.
The second parameter returns the number of RecordsAffected by the SQL statement.
According to the ADDb object method description the variable RecordAffected is defined as a long variable.

here's the part for the update.
NrRecords = -1
recset = mySQLConn.Execute(SQLText, long:NrRecords)          ;    gives error
Message("Update sql", "NrRecords= %NrRecords%")
mySQLConn.Close()
mySQLRS = 0
mySQLConn = 0

If I skip the log:NrRecords part than the row in the database is updated without errors.
I have searched the Technbase and the forum but saw no article to help me.

How can I retrieve the Number of RecordsAffected in the variable NrRecords.

Thanks and best regards,

Title: Re: ADODB Connection.Execute(SQLTxt, RecordsAffected) gives errror
Post by: td on February 12, 2016, 10:21:06 am

The documentation for the parameter states:

"Optional. A Long variable to which the provider returns the number of records that the operation affected. The RecordsAffected parameter applies only for action queries or stored procedures. RecordsAffected does not return the number of records returned by a result-returning query or stored procedure. To obtain this information, use the RecordCount property. The Execute method will not return the correct information when used with adAsyncExecute, simply because when a command is executed asynchronously, the number of records affected may not yet be known at the time the method returns."

So if you are performing a result-returning query, you can use the RecordSet RecordCount property.  If you are not then we will need to figure out why the method gives an error.   If the error is just a COM exception error, you have a newer version of WinBatch, and the 'More Error Info' button is enabled, clicking it may help solve your problem.   

WinBatch fully supports COM Automation method's OUT parameters so that is not an issue.  However,  I do have some vague notion about this particular OUT parameter being a problem in the past.  I did a quick test using an old database and the old Microsoft.Jet.OLEDB provider and the COM exception additional error information said "Provider type mismatch".  This suggests that the provider does not support the records-affected parameter.  Perhaps you have encountered a similar issue with your provider.  If that is the case, you may be able to issue a properly constructed second query to obtain a count of the records affected from a returned recordset.

Perhaps someone else has a better insight into the issue.

Title: Re: ADODB Connection.Execute(SQLTxt, RecordsAffected) gives errror
Post by: jwteunisse on February 13, 2016, 09:10:53 am

Thanks for reply.
I get indeed a 'Provider type mismatch' error in Winbatch.

Using an Excel VBA script the code works:

Dim NrRecords As Long

NrRecords = -1
Set recset = conn.Execute(SQLText, NrRecords)         
MsgBox "Update sql: NrRecords= " & NrRecords    ' gives back the count of Records affected

So the provider works using VBA.

Trying in Winbatch:
NrRecords = ObjectType("I4", -1)
gives also the Provider type mismatch.

Any ideas ?

Title: Re: ADODB Connection.Execute(SQLTxt, RecordsAffected) gives errror
Post by: td on February 13, 2016, 10:51:23 am

Several ideas.  For one, it would be a good idea to look at the additional information when available before running off to this forum and reporting that info when you do run off to the forum.   

Since you know that your provider does support the parameter, you may need to tell WinBatch the type of the parameter because of some deficiency in the ADO interface's type library.  To do that you you prepend the 'i4:' string to the variable name when you pass as a parameter to the execute method. 

For Example:

Code: Winbatch

 rSet = sCon.Execute('SELECT "Jacobs, Russell" FROM AUTHORS', i4:nAffected)
 

Hopefully, this will fix your problem.

Title: Re: ADODB Connection.Execute(SQLTxt, RecordsAffected) gives errror
Post by: jwteunisse on February 14, 2016, 04:36:16 am

thanks for your suggestion.

adding i4: to NrRecords fixed the problem partly. I do not get an runtime error, but the variable NrRecords is set to a zero value, while it should be 1 (one), stating that one table row is updated.

I leave it by this reply.

Title: Re: ADODB Connection.Execute(SQLTxt, RecordsAffected) gives errror
Post by: td on February 14, 2016, 11:03:12 am

Quote from: jwteunisse on February 14, 2016, 04:36:16 am

I leave it by this reply.

Guessing you mean you are not pursuing the problem any further.  We will still look into it as time permits to make sure that WinBatch is handling the parameter correctly.

Title: Re: ADODB Connection.Execute(SQLTxt, RecordsAffected) gives errror
Post by: td on February 17, 2016, 08:09:48 am

The type library for ADODB related classes provides type information for the Execute's second parameter that is not correct because the Execute method does not accept a parameter of that type when the method is called.   Of course, WIL provides a mechanism for user supplied alternative method parameter type information using the type name followed by a colon syntax.  Unfortunately, the typing syntax does not support compound types as is needed in this instance.   Basically, the BYREF|I4 type and modifier type is needed to get the parameter to work correctly.

To that end the next release of WinBatch will have  support for a type with a type  modifier like BYREF|I4.   This will make it possible to get return information from this method parameter or any other method parameter with similar issues.

Thank you for bringing this problem to our attention.

Title: Re: ADODB Connection.Execute(SQLTxt, RecordsAffected) gives errror
Post by: jwteunisse on February 17, 2016, 01:29:23 pm

You're welcome, thanks for looking into the 'problem' and solving it in the next release.

Best regards,
Jan Willem Teunisse