Friday, March 23, 2012

Infinite Loop in BOL code!?

This code is from BOL (in index type: "DDLs-SQL Server"):
Take a look at error handling .. what happens if one of the three cmd.execute within the [Done:] hanlde fails?

It looks to me like we would have an infinite loop! .. am I missing something here?

BOL CODE EXAMPLE:
----------
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command

' If the ADOTestTable does not exist, go to AdoError.
On Error GoTo AdoError

' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "MyServerName"
cn.Properties("Initial Catalog").Value = "northwind"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open

' Set up command object.
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "DROP TABLE ADOTestTable"
Cmd.CommandType = adCmdText
Cmd.Execute

Done:
Cmd.CommandText = "SET NOCOUNT ON"
Cmd.Execute
Cmd.CommandText = "CREATE TABLE ADOTestTable (id int, name char(100))"
Cmd.Execute
Cmd.CommandText = "INSERT INTO ADOTestTable values(1, 'Jane Doe')"
Cmd.Execute
Cn.Close
Exit Sub

AdoError:
Dim errLoop As Error
Dim strError As String

' Enumerate Errors collection and display properties of
' each Error object.
Set Errs1 = Cn.Errors
For Each errLoop In Errs1
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next

GoTo Done

End Sub
-----------------Where?|||Originally posted by Brett Kaiser
Where?

If an error occurs within the [Done:] handle then the compiler goes to [AdoError:] handle and when executing the last statement there "GoTo Done" we will end up going back to [Done:] ... then same error occurs .. then back to [AdoError:] .. etc .. that sure looks like an infinite loop!|||If a user who can not create tables runs this, yes, it could run forever. Fortunately, Microsoft makes everything run as "administrator".|||lack of permission to Insert would cause the same problem .. But it's not just lack of permission that might cause this code to run forever .. for instance a network failure might raise the same problem .. a server failure might also do the same thing .. the point is: Error handling is supposed to be very helpful but in this code it is not. Because it is not done right.

No comments:

Post a Comment