DIAdem

cancel
Showing results for 
Search instead for 
Did you mean: 

access database query import

Hi All listening to this thread,

I have completed converting the SQL Wizard application over to pure ADO data based SQL calls and completely weaned it from requiring an ODBC DSN in order to connect to each data base.  I am also now using the new ArrayToChannels() function to handle the buffering of the returned RecordSet rows to the channels in the DataPortal.  This makes for much prettier code and faster execution than the SQL_BindChannel() function and the ChnConcat() approach in the old SQL Wizard, but it also requires that the user have DIAdem 10.1 or later-- DIAdem 10.1 being the first version that supported the ArrayToChannels() function.

The ArrayToChannels() function is the only efficient way of buffering ADO queried records to a DIAdem data channel and does not have the 100 column limitation of the SQL_Results variable you have to use with the older ODBC approach and the SQL_...() functions in DIAdem.  The old SQL Wizard worked around this limitation by breaking each query with over 100 columns into smaller queries of 100 columns each and running them sequentially, but it made for ugly code.

Here is the full SQL Wizard application for DIAdem 10.1 or later, with all resource files and directory structure required.

Enjoy,
Brad Turpin
DIAdem Product Support Engineer
National Instruments

0 Kudos
Message 31 of 40
(1,768 Views)

Hi Jim,

The version I just posted above does not require a DSN and will not attempt to create one.  Try this new version-- and it should run correctly on your computer, assuming that the path you list to your Giant db is correct.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

0 Kudos
Message 32 of 40
(1,768 Views)

Brad

Looks like we can finally close this issue.  It appears to load all 747,000 data points for all 20 columns using the different databases that I tried.  I would personally like to thank you for your dedication and effort to resolve this issue.

Thanks again and if I come across anything else I will let you know.

Jim

0 Kudos
Message 33 of 40
(1,766 Views)
Hi Jim,
 
Hooray!  This may sound strange to you, but I'd like to personally thank you for posing this problem and sticking with it through all the debugging steps.  I created the SQL Wizard about 5 years ago, and it always bugged me that it used both ADO (for the tree view) and ODBC (for the data loading) and especially that it could only work with data bases that had an ODBC DSN.  I didn't even think about it also needing query buffering for huge data sets.
 
Your request provided the excuse I needed to finally focus a bunch of time back on this.  I've been wanting to convert it to pure ADO for years now, but DIAdem just didn't have the features to make that efficient.  Then DIAdem 10.1 came out last Dec., and I realized DIAdem finally did have the features, but for 5 months I just didn't have the time to get around to it.  When you sent in your Giant db, I first added the SQL buffering in with the ODBC functions, because it was a quick easy fix, and also because this feature expansion would work on DIAdem 8.1 and above and cover a ton of customers.  But I secretly wanted to convert it to pure ADO.  When the ODBC addition still didn't work for you, I had all the excuse I needed to lavish the time on this example that I had been wanting. to.  I'm thrilled to have it converted now, and It's doubly satisfying that it solves your needs as well.  What a great way to end the week!
 
Just so you know, I still have one more dream for this example.  There's a new feature in posted DIAdem 10.2 Beta which would enable me to have an arbitrarily large number of conditions-- instead of the current static maximum of 6 conditions.  So if you  run out of query conditions, let me know...
 
Cheers,
Brad Turpin
DIAdem Product Support Engineer
National Instruments
0 Kudos
Message 34 of 40
(1,750 Views)

Brad

Glad it worked out for both of us.  I did notice in the script the condition of a maximum of 6 query conditions.  Just out of curiousity, do you know off hand what would happen if it was attempted to load a query with more than 6 query conditions?  Would it generate an error or just ignore the extra conditions?

Have a good weekend!

 

Jim

0 Kudos
Message 35 of 40
(1,751 Views)

Hi Jim,

The 6 query conditions are a limitation of the SUDialog GUI.  What you see near the bottom of the dialog is a series of condition "rows", and it looks like a table which should be extendable, but actually every single control is a separate control.  In fact, the control that gives you the condition value enumerations is a separate control on top of or underneath the control where you can freely type in a condition value.  So each "row" of conditions takes up vertical space and creates 10 new controls on the dialog, and you have to copy over and adapt the callback code for each control from its sibling above.  Ugly.  In DIAdem 10.2 Beta there is a new SUDialog control called the XTable which should solve this problem.  It should consolidate the callbacks and allow for as many condition "rows" as you want, giving you a scrollbar to see whichever group of 6 conditions you want to at a time.

So there's no way with the SQL Wizard to execute a query with more than 6 conditions-- that variable in the script just has to agree with the number of condition "rows" in the SUDialog.  When I update the example with the new XTable, that "MaxCondNum" variable will disappear.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

0 Kudos
Message 36 of 40
(1,733 Views)

Brad

Thanks for the explanation.  I however, came across something else that I need an explanation on.  It involves the filesizes of data imported using the SQL Wizard as compared to the SQL Reader plugin.  When I used the SQL Wizard to import a large file, I then saved it in a .tdm format for later use.  I do the same using the SQL Reader plugin and also save it as a .tdm.  I noticed that file was fairly large (larger than I would have expected when using the SQL Wizard).  I thought the .tdm was the diadem configuration file while the .tdx file being the actual data.  Therefore, I decided to try a simpler, smaller query to load in using both the SQL Wizard and SQL Reader plugin as a test.  The results to import  (i.e. shippingstorage_#141) from the database I uploaded to your ftp site are as follows

using SQL Wizard: .tdm size = 804kb, .tdx size=5175kb
using SQL Reader: .tdm size = 804kb, .tdx size=2261kb

It appears the larger the dataset the larger the difference in filesizes.  For example, a LTOE query the .tdx file differs from 71MB using SQL Reader to over 163MB using SQL Wizard.  The .tdm files are similar in size so I expected the .tdx to also be similar in size.  Everything appears to be identical with the exception of the filesizes.  The SQL WIZARD appears to always load my data and quicker so if that is the drawback then I can live with that.

Jim

0 Kudos
Message 37 of 40
(1,732 Views)

Hi Jim,

That looks like the difference between saving your numbers as DBLs and saving them as SGLs.  I realized after you mentioned this, that I had made no provisions for "datatyping" the resulting DIAdem channels that the SQL query results are loaded into.  All (non-String) data channels in the DIAdem Data Portal are stored as REAL64 / DOUBLE binary values, so initially the values loaded into DIAdem are all DBLs.  But as of DIAdem 10.1, we now have the ability to "tag" data channels with a "target data type" stored in the "ChnDataStyle(i)" array variable, which is then used when that channel is saved out to a TDM or TDMS data file.

In my experiments here, the below edited version of the SQL Wizard.SUD now tags the loaded data channels so that when they are saved out to TDM file, they are saved with the same binary data type that they had in the data base.  I believe this will remove the difference you have been observing.

Let me know,
Brad Turpin
DIAdem Product Support Engineer
National Instruments

0 Kudos
Message 38 of 40
(1,704 Views)

Brad

That appears to have solved the issue.  I am curious though, is there a  way to change the format of the data once it is loaded in - for example say I wanted to change the type from int32 to double. Can that be done after the import prior to saving as a .tdm or are you stuck with the original format (I dont need to do this but if I did at least I would know whether it was possible)

Thanks again for your help.

Jim

0 Kudos
Message 39 of 40
(1,683 Views)

Hi Jim,

Yes, this is possible (though only in DIAdem 10.1 or later).  Actually, this is what the newest SQL Wizard is doing.  The ArrayToChannels() function buffers in the data from ADO as string, date/time, or double channels in the Data Portal, then for non-double numeric columns it changes the channel property which governs the TDM output data type to the same data type as that column was in the data base.

Note that in DIAdem 10.1 there is also the ChnQuantize() function, with which you can specify an arbitrary target bit width, and DIAdem will automatically determine the target data type and scaling parameters.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

0 Kudos
Message 40 of 40
(1,663 Views)