I am running into some issues while writing to an SQL database table.

I have written a Basic routine in OI version 9.4.6 that connects the the Microsoft SQL database. Then it selects an OI table and attempts to loop through roughly 23000 rows. With each loop it writes to the SQL database using a SQL command "INSERT". It is successful almost 18,000 iterations. Then I get an error message:

Microsoft Visual C++ Runtime Library.

It then proceeds to lock up OI for a moment and boots me out of OI.

I also log any error message that comes back from the Insert command. I get this error message.

Not enough memory resources are available to complete this operation.

My question is how do I either release memory or solve this issue. I have tried to issue a "RELEASE" SQL command and that does not work.

Any suggestions from the experts!

Thanks,

Jeff Miller

Miller's Software Services

I am running into some issues while writing to an SQL database table.

I have written a Basic routine in OI version 9.4.6 that connects the the Microsoft SQL database. Then it selects an OI table and attempts to loop through roughly 23000 rows. With each loop it writes to the SQL database using a SQL command "INSERT". It is successful almost 18,000 iterations. Then I get an error message:

Microsoft Visual C++ Runtime Library.

It then proceeds to lock up OI for a moment and boots me out of OI.

I also log any error message that comes back from the Insert command. I get this error message.

Not enough memory resources are available to complete this operation.

My question is how do I either release memory or solve this issue. I have tried to issue a "RELEASE" SQL command and that does not work.

Any suggestions from the experts!

Thanks,

Jeff Miller

Miller's Software Services

Are you using ADO as we had discussed? If so, are you using SRP_COM or the native BASIC+ COM routines?

Don Bakke

SRP Computer Solutions, Inc.

I am using SRP_COM. Like we had discussed.

This is the code that I am using

Statement = "INSERT INTO ":TableName:" (":NewCol:") VALUES(":NewData:")"

objRecordSet = SRP_Com(objConnection, 'CALL', 'Execute', Statement)

Jeff Miller

Miller's Software Services

I am using SRP_COM. Like we had discussed.

This is the code that I am using

Statement = "INSERT INTO ":TableName:" (":NewCol:") VALUES(":NewData:")"

objRecordSet = SRP_Com(objConnection, 'CALL', 'Execute', Statement)

Jeff Miller

Miller's Software Services

SRP_COM requires that you use the RELEASE method for all objects you create otherwise you will incur memory leaks. You would do this when you are done with any given object.

Don Bakke

SRP Computer Solutions, Inc.

I am looping through about 23000 rows. I do a release every 1,000 interations. Then I go through the connection string again:

If SRP_Com(objConnection, 'CREATE', 'ADODB.Connection') then

SRP_Com(objConnection, 'CALL', 'Open', ConnectionString)

If SRP_Com('', 'ERROR') then

      End

End

I do this 18 times. Then it gives me the Runtime error.

Are you suggesting I do a release after each write (Insert) command? It looked like I needed to do the CREATE and CALL commands each time I did a Release.

Jeff Miller

Miller's Software Services

I am looping through about 23000 rows. I do a release every 1,000 interations. Then I go through the connection string again:

If SRP_Com(objConnection, 'CREATE', 'ADODB.Connection') then

SRP_Com(objConnection, 'CALL', 'Open', ConnectionString)

If SRP_Com('', 'ERROR') then

      End

End

I do this 18 times. Then it gives me the Runtime error.

Are you suggesting I do a release after each write (Insert) command? It looked like I needed to do the CREATE and CALL commands each time I did a Release.

Jeff Miller

Miller's Software Services

It's unclear to me what code is within your loop. Do you call the 'CREATE' method at each iteration or do you only do this for every 1000 iterations?

Don Bakke

SRP Computer Solutions, Inc.

I do the create once for every 1,000 iterations (writes). Not every iteration.

Jeff Miller

Miller's Software Services

I do the create once for every 1,000 iterations (writes). Not every iteration.

Jeff Miller

Miller's Software Services

Hmmm…then I would expect your code to run without any problems. However, just to prove a point, do the CREATE and RELEASE for each iteration and see if it gets further along.

Don Bakke

SRP Computer Solutions, Inc.

I was thinking the same thing. I have rewritten the code to do just that. I will post my results in a little bit.

Jeff Miller

Miller's Software Services

Update on the problem:

I re-wrote the program to do the Release after each write. I had to do the re-connect of the table to each time I wrote to the table.

I had the same problem. It gave me the C++ runtime error.

I put that program to the side and ran another batch of SQL uploads. It got through 2 small updates then gave me the same error message.

Don, I am thinking you have done larger batches before. So I hope there is a solution.

Jeff Miller

Miller's Software Services

Update on the problem:

I re-wrote the program to do the Release after each write. I had to do the re-connect of the table to each time I wrote to the table.

I had the same problem. It gave me the C++ runtime error.

I put that program to the side and ran another batch of SQL uploads. It got through 2 small updates then gave me the same error message.

Don, I am thinking you have done larger batches before. So I hope there is a solution.

Jeff Miller

Miller's Software Services

Jeff - Yes, we have systems running small and large SQL updates 24/7. Your symptoms reminded me of situations where our custom code forgot to close objects. Now that we use well tested libraries from our toolset, we no longer have any issues. All I can do is trust that your code is well formed.

If you are up to it, you can rule out SRP_COM and use the native BASIC+ COM routines. It's a pretty easy convsion since there is nearly one-for-one functionality between the two. At the very least create a quick test version.

On the issue of large batches, I did have a couple of other ideas I intended to mention once we got the C++ runtime error resolved, but I'll bring them up now since they might prove to work around the problem:

1. Instead of pushing through one SQL query at a time, contactenate them using semi-colons or CR/LF and send them as one big statement. You can break this down to chunks at a time if you wish.
2. Consider using BULK INSERT instead. This is optimized for the amount of data you are trying to push. It requires that your OI and SQL server have access to a shared folder, but it allows OI to do what it does best and SQL to do what it does best.

Don Bakke

SRP Computer Solutions, Inc.

I agree with Don, the BULK_INSERT command is the way to go. I typically BULK_INSERT data into a set of staging tables with varchar datatypes and no integrity constraints, then use data transformation or stored procedures to move the date into formal SQL tables.

Thank you for the information.

I am working through some syntax issues. I will post any success.

Jeff Miller

Miller's Software Services

View this thread on the Works forum...