ADO problem with multiple SQL statements 
Author Message
 ADO problem with multiple SQL statements

I've discovered a problem using ADO to execute multiple commands in
one batch. I am accessing a Sybase ASE 12.5 database server using the
ODBC OLEDB provider. I have a parent-child relationship where I need
to call multiple procs to add a new parent and multiple children. I
want to do this in a transaction.

If I create a string with multiple "exec <procname>" statements and
execute it with the ADO connection object's Execute method, I can only
trap errors that occur on the first SQL statement. Any errors further
in the batch are not raised in VB and are not placed in the
connection's Errors collection. In fact, I can call a stored procedure
that doesn't exist in the database and not get an error! If there is a
rollback called in a proc, the transaction is rolled back
appropriately, but VB doesn't know because there is no error.

The code used to execute the SQL is in a common class we use in
multiple applications. The call looks like this:

        If (in_bTransaction = True) Then
            Call m_objAdoCn.BeginTrans
            Call m_objAdoCn.Execute(in_sSQL)
            Call m_objAdoCn.CommitTrans

        Else
            Call m_objAdoCn.Execute(in_sSQL)
        End If

The transaction is rolled back in the error handler if there is an
error.

The code to create the SQL looks like this:
   sSQL = "exec del_proj_flow_step 0xcbc3dbaf7559b98d" & vbNewLine _
      & "exec ins_proj_flow_step 0xcbc3dbaf7559b98d,
0xd5291a997559b98d, 1, 'N'" & vbNewLine _
      & "exec do_proj_match_proj_to_fred 0xcbc3dbaf7559b98d"

The last procedure does not exist on my server.

It appears that executing the same batch using the recordset's open
method DOES catch the errors. Using a command object does NOT catch
the errors.

Has anyone else observed this behavior? I hesitate to use the
recordset's open method, since who knows if that behavior will remain
in the next MDAC release. It looks like for now I will execute each
command with a separate connection execute statement and maintain the
transaction in my VB code. More round trips, but safer. Any better
ideas?



Tue, 04 May 2004 03:26:50 GMT
 ADO problem with multiple SQL statements

I have run into a similar problem.  When executing multiple statements in a
batch, each statement should return a recordset (it will be closed for
non-select statements).  To get the return status for each statement you
have to call the nextrecordset method on the previously returned recordset.
The problem is that the call to execute or nextrecordset will not return a
recordset if the statement fails, even if subsequent statements in the batch
have been successfully executed.

This appears to be a bug as far as I can tell.  In order for batches to work
correctly the provider should return a closed recordset even when the
statement fails. Though IMHO this is a pretty non-intuitive way to return
error information for multiple statement batches.



Wed, 05 May 2004 04:25:57 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. ADO problem with multiple SQL statements using execute method

2. ADO problem with multiple SQL statements using execute method

3. Help: multiple select statements in SQL stored proc called by ADO

4. ado/ multiple db platforms/ on sql statement

5. Multiple SQL Insert statements in a single ADO.NET Command Object call

6. One SQL Statement for Multiple Datebases - ADO / FoxPro????

7. Interesting problem??? multiple COUNT(*) in one SQL statement

8. VB6 - Problem passing multiple parameters to Command SQL statement

9. Multiple SELECT statements using ODBC & ADO

10. Newbie: Problem with multiple connections to same data object (ADO / SQL Server 7)

11. Selecting XML into an ADO Stream using multiple select statements


 
Powered by phpBB® Forum Software