[shanondink]

Hey guys,

VB.NET

Visual Studio 2003

New NS Driver

MySQL 1. something driver

Seems I can only PULL then INSERT 10,000 rows at a time. If I comment out the command to insert into MySQL, the prog will pull past 10,000. If I minimally redesign so as to not pull from NS but only insert goofy data into MySQL, the prog will INSERT more than 10,000 rows. But putting the 2 together, I get this error:

Code:
Unhandled Exception: System.Data.Odbc.OdbcException: ERROR [01000] [OpenAccess][OpenRDA ODBC]Closed Connection: next
at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcDataReader.Read()
at DeleteMe.Module1.Main() in C:Documents and SettingsShanonSwaffordMy DocumentsVisual Studio ProjectsDeleteMeModule1.vb:line 41
ABOVE: Notice all the OpenAccess/OpenRDA stuff which makes me think somehow it is the new NS driver. But I can’t find any documentation on it or how to handle it.

When I perform an ODBC trace:

Code:
ABPNetSuiteMirr ffc-a18 EXIT SQLGetData with return code 0 (SQL_SUCCESS)
HSTMT 033227E0
UWORD 1
SWORD -8
PTR 0x001F1DF0 [ 10] “12786”
SQLLEN 4092
SQLLEN * 0x0012F3D4 (10)

ABPNetSuiteMirr ffc-a18 ENTER SQLGetData
HSTMT 033227E0
UWORD 2
SWORD -8
PTR 0x001F1DF0
SQLLEN 4094
SQLLEN * 0x0012F3CC

ABPNetSuiteMirr ffc-a18 EXIT SQLGetData with return code 0 (SQL_SUCCESS)
HSTMT 033227E0
UWORD 2
SWORD -8
PTR 0x001F1DF0 [ 18] “Netledger”
SQLLEN 4094
SQLLEN * 0x0012F3CC (18)

ABPNetSuiteMirr ffc-a18 ENTER SQLFetch
HSTMT 033227E0

ABPNetSuiteMirr ffc-a18 EXIT SQLFetch with return code 0 (SQL_SUCCESS)
HSTMT 033227E0

ABPNetSuiteMirr ffc-a18 ENTER SQLGetData
HSTMT 033227E0
UWORD 1
SWORD -8
PTR 0x001F1DF0
SQLLEN 4092
SQLLEN * 0x0012F3D4

ABPNetSuiteMirr ffc-a18 EXIT SQLGetData with return code 0 (SQL_SUCCESS)
HSTMT 033227E0
UWORD 1
SWORD -8
PTR 0x001F1DF0 [ 10] “12787”
SQLLEN 4092
SQLLEN * 0x0012F3D4 (10)

ABPNetSuiteMirr ffc-a18 ENTER SQLGetData
HSTMT 033227E0
UWORD 2
SWORD -8
PTR 0x001F1DF0
SQLLEN 4094
SQLLEN * 0x0012F3CC

ABPNetSuiteMirr ffc-a18 EXIT SQLGetData with return code 0 (SQL_SUCCESS)
HSTMT 033227E0
UWORD 2
SWORD -8
PTR 0x001F1DF0 [ 22] “Blastengine”
SQLLEN 4094
SQLLEN * 0x0012F3CC (22)

ABPNetSuiteMirr ffc-a18 ENTER SQLFetch
HSTMT 033227E0

ABPNetSuiteMirr ffc-a18 EXIT SQLFetch with return code -1 (SQL_ERROR)
HSTMT 033227E0

DIAG [01000] [OpenAccess][OpenRDA ODBC]Closed Connection: next (0)

ABPNetSuiteMirr ffc-a18 ENTER SQLGetDiagRecW
SQLSMALLINT 3
SQLHANDLE 033227E0
SQLSMALLINT 1
SQLWCHAR * 0x00197138 (NYI)
SQLINTEGER * 0x0012F3AC
SQLWCHAR * 0x001C7A18 (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x0012F3A4

ABPNetSuiteMirr ffc-a18 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3
SQLHANDLE 033227E0
SQLSMALLINT 1
SQLWCHAR * 0x00197138 (NYI)
SQLINTEGER * 0x0012F3AC (0)
SQLWCHAR * 0x001C7A18 (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x0012F3A4 (49)

ABPNetSuiteMirr ffc-a18 ENTER SQLGetDiagRecW
SQLSMALLINT 3
SQLHANDLE 033227E0
SQLSMALLINT 2
SQLWCHAR * 0x00197138 (NYI)
SQLINTEGER * 0x0012F3AC
SQLWCHAR * 0x001C7A18 (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x0012F3A4

ABPNetSuiteMirr ffc-a18 EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)
SQLSMALLINT 3
SQLHANDLE 033227E0
SQLSMALLINT 2
SQLWCHAR * 0x00197138 (NYI)
SQLINTEGER * 0x0012F3AC
SQLWCHAR * 0x001C7A18 (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x0012F3A4

ABPNetSuiteMirr ffc-a18 ENTER SQLGetConnectAttrW
SQLHDBC 03322628
SQLINTEGER 1209
SQLPOINTER 0x001F1780
SQLINTEGER 1024
SQLINTEGER * 0x0012F3B8

ABPNetSuiteMirr ffc-a18 EXIT SQLGetConnectAttrW with return code -1 (SQL_ERROR)
SQLHDBC 03322628
SQLINTEGER 1209
SQLPOINTER 0x001F1780
SQLINTEGER 1024
SQLINTEGER * 0x0012F3B8

ABPNetSuiteMirr ffc-a18 ENTER SQLGetInfoW
HDBC 03322628
UWORD 6
PTR 0x001F1780
SWORD 1024
SWORD * 0x0012F3C0

ABPNetSuiteMirr ffc-a18 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 03322628
UWORD 6
PTR 0x001F1780 [ 16] “oaodbc32”
SWORD 1024
SWORD * 0x0012F3C0 (16)

ABPNetSuiteMirr ffc-a18 ENTER SQLGetConnectAttrW
SQLHDBC 03322628
SQLINTEGER 1209
SQLPOINTER 0x001F1780
SQLINTEGER 1024
SQLINTEGER * 0x0012F3D4

ABPNetSuiteMirr ffc-a18 EXIT SQLGetConnectAttrW with return code -1 (SQL_ERROR)
SQLHDBC 03322628
SQLINTEGER 1209
SQLPOINTER 0x001F1780
SQLINTEGER 1024
SQLINTEGER * 0x0012F3D4

Here is my test code:

Code:
Imports MySql.Data.MySqlClient
Imports System.Data.Odbc

Module Module1

Sub Main()

System.Console.WriteLine(“Enter your NS username and enter”)
Dim myusername As String = System.Console.ReadLine()
System.Console.WriteLine()
System.Console.WriteLine(“Enter your NS password and enter”)
Dim mypassword As String = System.Console.ReadLine()

Dim NS_CONN_STRING As String = “DSN=NetSuite.com;UID=” + myusername + “;PWD=” + mypassword
Dim SQL As String = “SELECT ENTITY_ID, ENTITY_TYPE FROM ENTITY ORDER BY ENTITY_ID”

‘ Create connection object
Dim conn As New OdbcConnection(NS_CONN_STRING)
‘ Create command object
Dim cmd As New OdbcCommand(SQL)
cmd.Connection = conn
‘ Open connection
conn.Open()

‘ Call command’s ExecuteReader
Dim reader As OdbcDataReader
reader = cmd.ExecuteReader()

Dim numrow As Integer = 1
Dim testsql As String
System.Console.WriteLine(“Enter MySQL server and enter:”)
Dim mysqlserver As String = System.Console.ReadLine()
System.Console.WriteLine(“Enter MySQL username and enter:”)
Dim mysqluser As String = System.Console.ReadLine()
Dim test_CONN_STRING As String = “server=web.abptech.com; user id=” + mysqluser + “; password=” + mypassword + “; database=netsuite”

System.Console.WriteLine(“SQL: ” + SQL)
Dim mconn As New MySqlConnection
Dim myCommand As New MySqlCommand
mconn.ConnectionString = test_CONN_STRING
myCommand.Connection = mconn
mconn.Open()

While reader.Read()
testsql = “REPLACE INTO `test` VALUES (” + reader.GetValue(0).ToString + “, ‘” + reader.GetValue(1).ToString + “‘, ‘username’);”
System.Console.WriteLine(numrow.ToString + ControlChars.Tab + testsql)
numrow = numrow + 1
myCommand.CommandText = testsql
Try
myCommand.ExecuteNonQuery()
Catch myerror As MySqlException
System.Console.WriteLine(“There was an error updating the database: ” & myerror.Message)
Finally
End Try
End While
myCommand.Dispose()
conn.Dispose()
conn.Close()
System.Console.WriteLine(“Number of Rows: ” + numrow.ToString)
reader.Close()
conn.Close()

‘System.Console.ReadLine()
End Sub

End Module
This is a cached copy. Click here to see the original post.

[shanondink]

RE: Could this be a driver problem?

Have an update which makes me really think it is definitely a problem with the new Netsuite driver or the Netsuite server:

1. Pulled data from NS into an Access database.

2. Built a new DSN to this database using the Microsoft Access Driver

3. Changed ONLY:

Code:
‘Dim NS_CONN_STRING As String = “DSN=NetSuite.com;UID=” + myusername + “;PWD=” + mypassword
Dim NS_CONN_STRING As String = “DSN=NetSuite_Access;UID=” + myusername + “;PWD=” + mypassword
And it works like a charm. No crashes!

It is currently past the 20,000th record of 48,000 TRANSACTIONS table entries. If it crashes before finishing, I’ll repost.

Please help!

Thanks,

Shanon

[ivan.parra]

RE: Could this be a driver problem?

This particular error is currently being tracked by issue 111946. A fix has already been gathered and should be released sometime next week that will either resolve this issue or assist to uncover the solution.

[shanondink]

RE: Could this be a driver problem?

Originally posted by ivan.parra

This particular error is currently being tracked by issue 111946. A fix has already been gathered and should be released sometime next week that will either resolve this issue or assist to uncover the solution.

Hi Ivan,

I just tried to pull 12,000 records and got the same error on the 10,001st record.

Do you have an update on this? That issue says that it was targeted for “5wk of August” but it is still happening.

I can help with testing or get you an ODBC trace if you need.

Thanks,

Shanon

[shanondink]

RE: Could this be a driver problem?

Another test of 12,000 records just crashed.

Is there an update on this?

Thanks,

Shanon

[ivan.parra]

RE: Could this be a driver problem?

This issue should now be resolved.

Thank you for your patience.

-Ivan

[longlam]

RE: Could this be a driver problem?

I was also experiencing a lot of random data errors. Can you be more specific on what this issue is and what was resolved to see if it was an issue on our end or with the ODBC driver.

[shanondink]

RE: Could this be a driver problem?

Originally posted by ivan.parra

This issue should now be resolved.

Thank you for your patience.

-Ivan

Hi Ivan, now it is worse. Fails at 2001 records.

[shanondink]

RE: Could this be a driver problem?

Hi Ivan

Can you give us some information on this?

My case 484320 is still “Not Started” after almost 6 months.

Excel and Access work fine. I suspect they paginate or something but I have no idea how I would do this with my .NET app.

Would you have any sample .NET code to Limit the number of records we try to pull?

Thanks,

[shanondink]

RE: Could this be a driver problem?

I found out why this was happening:

NS ODBC has something called “leaked connection detection” which closes any resultset that has been dormant for 5 minutes. Basic thinking that if NS doesn’t get a subsequest request for more data from the client, NS assumes the client has crashed and stops the session.

My vb.net app was doing the following:

load a datareader from NS

loop while (datareader has data) {

pull 1 record;

insert that record into a second database

}

I built it this way becuase I didn’t want to have to keep a 200,000 record array alive on my PC while the program was working if I needed to dump an entire table. We are a unix shop and we don’t have an Windows Servers around here so I have to use my personal PC to pull from NS ODBC to MySQL and want to be able to use that PC while the app is running.

Anyway, it turns out that NS ODBC or the vb.net datareader does some pagination in the background. Basically, NS gives the client 2000 records then pauses waiting for some sort of a “more data” ODBC command. Since the app received the 2000 records faster than it could insert them into the other database (on the internet), it wasn’t asking for more data in 5 minutes and NS had closed the connection by the time it got around to requesting more data.

So I rebuilt the app to:

load a datareader from NS

loop while (datareader has data) {

pull record;

insert that record into an array in memory

#No pagination built into anything but I can now see it pausing every 2000 records to pull another bunch.

}

loop while (array has data) {

insert data into new database

}

I think this is still memory intensive, but I also found a way to limit records returned on the query (search this UG for “rownum”) if need be.

I’ve tested the new app with 75,000 records so far and it has also worked fine the past couple of days on my normal daily keep-up-to-date queries.

Hopefully this will save somebody a few hours troubleshooting.

Regards,

Shanon

[ivan.parra]

RE: Could this be a driver problem?

We are currently testing a 30 minute time out solution instead of the current 5 minute solution. Once we are convinced that the 30 minute solution does not create any side affects, we will release it and your rewrite will no longer be necessary.

[Author]

Posts