Sharing database connection reference

[glen.meiring]

I have a LabVIEW 8.5 user interface application.  The application connects to a database to retrieve and save information about product images.  As the user draws ROI's on the image, I am using an event structure to select, insert or update records into a database. Initially, I was connecting / disconnecting from the database for every insert / update / select SQL statement.  As I moved from prototype to release, I decided to connect to the database once, and then re-use the connection reference stored in a global variable.

Now, sometimes I get the following error:

Exception occured in Microsoft OLE DB Provider for ODBC Drivers: Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets. in Conn Execute.vi->SaveCrackDimensions.vi->Main.vi

I am using an event structure to handle user events.  ie: when a user draws and ROI on the image, a "Mouse Up" event  captures some data, then calls a "SaveData" user event.  The "SaveData"  user event can be called from multiple places. It seems like maybe the user event is being executed twice in parallel.  I thought the event structure could only execute one event at a time.  The error implies that I am calling ExecSQL.vi more than once without freeing the recordset reference.  I looked and looked but I don't see anything. The "SaveData" user event frees all of the recordsets and does a commit or rollback for any transactions before exiting.

Does anyone have any idea why I would be getting this error?

Thanks,

Glen Meiring

[tst]

The event structure can only execute one event case at any given point in time. The error description comes from the ODBC driver, not from LabVIEW. I'm not entirely sure what constitues a transaction, but you should make sure to close any reference you get. Note that not all references are opened explicitly, so you need to make sure you catch all of them. My guess would be that you are not closing the recordset reference you get after executing your SQL query and that the driver does not allow you to execute another before you close it. If you can't find it, you should upload the relevant code.

[glen.meiring · Accepted solution]

I found the error.  In image1.png attached below, the first call to DB Tools Free Object.vi does not have anything to prevent the case structure and for loop from processing first.  So apparently, sometimes the for loop would execute before the first call to DB Tools Free Object.vi.  The DB Tools Execute Query.vi, circled in red, would execute and return an error.

See image2.png for the simple solution.  I just connected the error out to the case structure to ensure the ...Free Object.vi executes first.

[RavensFan]

Here is another little tip.  There is no need to split the error wire before the case structure prior to it going to the case selector ? mark.  Instead of wiring the error inside the case structure from the extra tunnel, you can wire it directly from the case selector ? mark.

Message Edited by Ravens Fan on 10-07-2008 07:44 PM

[Current_93]

Dear collegaues, I send two database connection references to another vi, see below. Am I right? but I have coercion dots, nevertheless it works fine.  My coercion dots in this figure will be grow memory leaking or not?

[DylanC]

Hi Current 93,

Since this thread is marked solved as well as your question being on a different topic it would be best if you created a new thread with your question. However, coercion dots do not automatically mean there is a memory leak and unless you are observing one you should be fine. You can read up on coercion dots at this link: Coercion Dot Forum Post.

Regards,

DylanC

Applications Engineering

National Instruments