Results 1 to 7 of 7

Thread: [RESOLVED] Looping problematic connection

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jan 2022
    Posts
    211

    Resolved [RESOLVED] Looping problematic connection

    Hi. I am new to your forum and this will be my first post

    I've got a problematic ADO.Net connector for Netsuite (Pretty popular in the UK)

    At least 20% of the time I get an exception on Command.ExecuteDataReader. Netsuite support says "keep trying until it connects" and give no support at all. I have put the exception message as comment in the exception block below.

    Failing all attempts to address the actual error, which I have never seen until working with this connector, I would like to loop the connection until it does actually connect without exception. The below strategy is not working, I need to actually close the form and start it again in order to successfully make the connection, otherwise it always throws the same exception no matter how long I let it run.

    Does anyone have any ideas?

    Code:
    Imports NetSuite.SuiteAnalyticsConnect
    
    Public Class TestForm
        Dim TableName As String = ""
        Dim AccountID As String = "xxxxxx"
        Dim RoleID As String = "xxxx"
        Dim Username As String = "xxxxx@xxxxxxx.co.uk"
        Dim PWD As String = "xxxxx"
        Dim Hostname As String = "xxxxx.xxxxx.api.netsuite.com"
        Dim Port As String = "xxxx"
        Dim Dsource As String = "xxxxx.com"
        Dim NetsuiteConnString As String = String.Format("Host={0};Port={1};ServerDatasource={2};User Id={3};Password={4};
    EncryptionMethod=SSL;CustomProperties='AccountID={5};RoleID={6}'", Hostname, Port, Dsource, Username, PWD, AccountID, RoleID)
    
        Dim IsConn As Boolean = False
        Dim ErrCt As Integer = 0
        Private Sub TestForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Do While Not IsConn
    
                Try
                    Using Conn As New OpenAccessConnection(NetsuiteConnString)
                        Conn.Open()
    
                        Using Cmd As New OpenAccessCommand("SELECT * FROM OA_TABLES", Conn)
                            Using Rdr As OpenAccessDataReader = Cmd.ExecuteReader 'Exception Start
                                IsConn = True
                            End Using
                        End Using
                    End Using
                Catch ex As Exception
                    '{"[SuiteAnalyticsConnect 9516] Internal error: ""Internal error, memory allocation failed. @oasrv.swsoa.c:6947: ""."}
    
                    ErrCt += 1
                    Label1.Text = ErrCt.ToString
                End Try
    
            Loop
        End Sub
    
    End Class

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Looping problematic connection

    To be clear, is it the OpenAccessConnection.Open call that is failing or the OpenAccessCommand.ExecuteReader call? Make sure that you actually debug (set a breakpoint and step through the code) to find out.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jan 2022
    Posts
    211

    Re: Looping problematic connection

    Hello jm, and thanks for the reply.
    The exception is thrown on ExecuteReader. I have used breakpoint and walked it through and its the same, always on ExecuteReader. It doesnt matter how many rows or column I ask for either.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Looping problematic connection

    It seems quite odd that a connection would succeed but a command would fail, but it seems quite bizarre that closing and reopening the form would make any difference at all. Do you actually mean restarting the application?

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Jan 2022
    Posts
    211

    Re: Looping problematic connection

    Yes, Application.Restart works, but not so hot for debugging. Running the build seems stable. Maybe I will try on a new form and closing/reshowing the form may have an effect, but I doubt it.

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Looping problematic connection

    Thinking about it further, I would guess that connection pooling is having an effect here. The way ADO.NET is designed, the DbConnection object that you use is generally quite light-weight and the actual connection object is at a lower level. When you open your DbConnection, a low-level connection object is created and connects to the database. When you close and discard your DbConnection, the low-level connection remains open. If you open another DbConnection within a specific period of time with the same connection string, it reuses the same low-level connection, i.e. the low-level connection provides a pool that multiple DbConnection objects can use. I suspect that your low-level connection is broken somehow and so it doesn't matter how many DbConnection objects you create.

    You should see whether connection pooling can be disabled for that provider. It might be done via a property of the DbConnection object or perhaps via the connection string. If you can't work out how to do that, see if there's some way that you can vary the connection string, e.g. set some attribute that will have no effect to a random number each time. That will hopefully cause a different low-level connection object to be created each time and thus any broken ones will not be too big a deal. If you use the random number option, you should probably only change the number on a failure, because each different connection string will result in a new low-level connection object that will remain open for some time. It's best to keep the number of those connections as low as possible.

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Jan 2022
    Posts
    211

    Re: Looping problematic connection

    JM, it looks like disabling pooling is a way forward. I still get the exception, however I am now able to close the connection, reopen it and not reuse the same broken connection(s) over and over again. Thank you for your time and knowledge.

Posting Permissions

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



Click Here to Expand Forum to Full Width