DIAdem

cancel
Showing results for 
Search instead for 
Did you mean: 

access database query import

Brad

Attached is the actual error that the SQL wizard gets when it approaches the 80% mark in the load sequence.

0 Kudos
Message 21 of 40
(1,722 Views)

Hi Jim,

I believe all of the possible points in the SQL Wizard application which could throw an error have been error protected so that the application does not terminate but instead shows an error mesage in the bottom text window.  That suggests to me that the error may be coming from your data base or the (ODBC) data base driver.  My guess is that we are exceeding the allowable heap memory for one of those layers.  The fact that it's happening on buffer 3 or 4 points to a memory leak or a delayed memory release or increased memory usage in other applications halfway through the load.  Anyway, my best guess how to avoid this problem would be to reduce the buffer size of the SQL record set transfer.  The default is 200,000 records per buffer.  Let's try inserting the following line of code at line 13 of the SQL Wizard.VBS to reduce the buffer size by a factor of 4:

SQL_BlockSize = 50000

Let me know how that does,
Brad Turpin
DIAdem Product Support Engineer
Naitonal Instruments

0 Kudos
Message 22 of 40
(1,718 Views)

Brad

I do not see anything regarding SQL_Blocksize anywhere in the SQL_Wizard.vbs when I load it into DIAdem.  (I also looked in the Extra_SQL_Functions.vbs and View_All_Channels.vbs) Is this the right file to edit or is this line somewhere else like the SUD?

Also, just a note that when the wizard stops due to the error above, if I look at the data that was imported to that point, there are 600,000 entries of the 750,000 (which is a mutiple of 200,000).  Could be a coincidence but thought I would mention it anyways.

0 Kudos
Message 23 of 40
(1,718 Views)

Hi Jim,

Before you started using the SQL Wizard application, I had never had to change the SQL buffering block size, or for that matter ever had to buffer the query results at all-- 200,000 records was always enough.  So what I'm asking you to do is to type in a new line that does not exist in the SQL Wizard.VBS that you have right now.  Create a new line where I mentioned in my last post with the following content:

SQL_Blocksize = 50000

I am very hopeful that this will enable you to retrieve the full query results.  If not, try an even smaller buffer size such as 10000.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

0 Kudos
Message 24 of 40
(1,698 Views)

Sorry about missing that about inserting the text rather than changing the value which I thought you stated was already there.

In any event, it works slightly better but it appears to be still getting that error when it is trying to import the last section of data.  For example, here is the number of datapoints imported (by looking at view window of data) when I get the erorr with different buffer values:

Buffer = 50000 ---> 93% of records imported (700,000)
Buffer = 10000 ---> 99% of records imported (740,000)
Buffer = 5000 ---> 99% of records imported (745,000)
Buffer = 2000 ---> 99% of records imported (747,000)

It looks like to me that since the last group of data is smaller then the buffer count that it is generating an error based on that (if that makes sense).  For example, buffer=10000, it does the import loop 74 times and the last time there is less than 10000 points remaining to be imported (approx 7400) so that is why it appears to error.  Based on this theory, it insure I get all my points I would have to have a buffer count of 1 !!!!

0 Kudos
Message 25 of 40
(1,693 Views)

Hi Jim,

I'm perplexed.  On my computer I get all 747338 records regardless of how big or small I set the buffer size.  I would sure think that a buffer size of 2000 would be small enough for even the stingiest memory constraints or grumpiest of data base drivers.

I'm working on a revised version of this application for DIAdem 10.1 and later that will use pure ADO to run the queries and load the data.  I should have a prototype I can send you today.  With any luck this new access method will bypass whatever the problem is on your end which I can not reproduce.

Just to be sure that I haven't changed something important in the SQL Wizard.SUD, I'm attaching the latest version which I used to query the 747338 records mentioned above.  You might just try replacing your current SQL Wizard.SUD with the new one and trying one more time.  It's a long shot, but it would be a shame not to try it if that were the reason for the difference between the application's behavior on your computer and mine.

What operating system do you have?

Thanks for your patience,
Brad Turpin
DIAdem Product Support Engineer
National Instruments

0 Kudos
Message 26 of 40
(1,686 Views)

Brad,

I am as confused as you.  You keep mentioning the database driver and/or OBDC driver possibly being an issue.  Is this something I can download the latest or check the version of for comparison to what you are using?

Also, I downloaded the latest SQL_Wizard.SUD and tried it again but I get the same behavior.  I am going to find someone else with DIAdem and try the SQL_Wizard.vbs on their machine to see if it acts the same.  As far as my operating system here is a snapshot of my system configuration

Thanks for your continued assistance on this issue.

Jim

0 Kudos
Message 27 of 40
(1,695 Views)

Hi Jim,

I got the SQL Wizard application completely converted over to ADO, though I have not yet weaned it away from requiring a DSN.  If the culprit is the data base driver, then it may be that you will see no difference in the amount of data loaded until we can specify an arbitrary data base driver in the ADO connection string (rather than relying on the data base driver declared by your DSN).

Still give this version a try and let me know what you think.  It seems to load the data faster on my computer.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

0 Kudos
Message 28 of 40
(1,664 Views)
Brad
 
I unzipped the 2 files and I changed the path to point to the location of my database and tried to run it.  However, I get an error when it tries to create the dsn.  I attached the screenshot of the error which occurs in the CheckIfDBok function.
0 Kudos
Message 29 of 40
(1,651 Views)

Brad

FYI: The ODBC/Access drivers being different keeps coming up so I thought  I would show you the list of )DBC drivers that are installed on my machine along with their versions to see if there is a difference between your PC and mine.

 

Download All
0 Kudos
Message 30 of 40
(1,648 Views)