Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    L.A
    Posts
    19

    Unanswered: 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
    -------------------------------------------------------

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Where?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Location
    L.A
    Posts
    19
    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!

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If a user who can not create tables runs this, yes, it could run forever. Fortunately, Microsoft makes everything run as "administrator".

  5. #5
    Join Date
    Mar 2004
    Location
    L.A
    Posts
    19
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •