-
Jan 17th, 2022, 04:06 PM
#1
Thread Starter
Addicted Member
[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
-
Jan 17th, 2022, 07:31 PM
#2
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.
-
Jan 18th, 2022, 04:00 AM
#3
Thread Starter
Addicted Member
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.
-
Jan 18th, 2022, 04:35 AM
#4
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?
-
Jan 18th, 2022, 04:54 AM
#5
Thread Starter
Addicted Member
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.
-
Jan 18th, 2022, 06:31 AM
#6
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.
-
Jan 18th, 2022, 07:43 AM
#7
Thread Starter
Addicted Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|