how to load ODBC data?

[Nick_Hewish]

I have just installed NI DIAdem 9.1 on Windows XP and am struggling with the inadequate documentation.
I have a local MySQL database server running, and have installed the MySQL ODBC driver.
I have defined an ODBC data source to provide access to my database.

Now how do I get DIAdem to use the ODBC connection?
There is no file, just a named ODBC source.

I am upset that this simple action is not described in the documentation. (Or I cannot find it.)

[Brad_Turpin]

Hi Nick,

DIAdem can interact with your data base in two ways.  You can open up the table and column hierarchy of your data base in the NAVIGATOR, which is nice because it shows you the whole data base and enables you to easily select which tables/columns you're interested in.  What's not so nice is that the only thing you can do at that point is to load entire columns from your data base into the Data Portal.  Most people have a data base for the particular reason that they want to query specific records out of selected columns, so it's rarely useful to load entire columns (though it makes for a good sales demo).

The second way that DIAdem can interact with your data base is through a VBScript which uses the "SQL_" ODBC functions.  If you launch the DIAdem Help system and go to the "Index" tab, then type "SQL_" in the keyword field and hit the "Enter" key on your keyboard, you will find a healthy list of built-in SQL commands to communicate with external data bases using ODBC.

I am including my own personal list of SQL examples as well,
Brad Turpin
DIAdem Product Support Engineer
National Instruments

[david_jenkinson]

Hi,

I am running the demo version of diadem 11.  We have a Mysql database on a remote server we access through a Mysql ODBC driver.  I have successfully added the GFSODBC.dll to the GPI extension, but how do I get my data to show up in the navigator?  I see nowhere to specify this, nor anywhere in the help file specifying.  Where do I configure the datafinder to access the database? 

Thanks

David J.

[Brad_Turpin]

Hi David,

The DataFinder only works with data files, so you will not be able to use the DataFinder to query your external MySQL data base.  As I stated in the above post to Nick, you can open a Data Storage in the NAVIGATOR to point to your external MySQL data base so that you see the entire data base table and column structure in the NAVIGATOR tree view.  This can be very helpful, but it will only enable you to load one or more ENTIRE columns from the data base, which is not likely to be what you want to do.

In order to query specific records out of an external SQL data base, you will have to run a VBScript.  The ZIP file in my answer to Nick above contains a number of example SQL applications.  The one that will likely be most interesting to you is the "SQL Wizard" application, which gives you a DataFinder-like interface to your external MySQL data base.  You will need to look at the VBScript file and enter in your data base DSN in order to point to your external MySQL data base.

Also, in the answer above to Nick I specifically mentioned what you had to do to find all the ODBC functions in the DIAdem Help system, so I'm surprised that you didn't find anything.  Perhaps you weren't looking for a programmatic answer.

Let me know how I can help you with those examples,

Brad Turpin
DIAdem Product Support Engineer
National Instruments

[david_jenkinson]

Yes I understand in the end, to be useful, I'll need to run VBscripts.  As a "sanity" check to see if I can even see my database from diadem, I was attempting to establish simple connectivity, and see my database in navigator.  From there I was going to implement scripting.  The open "data storage" detail you supplied in your reply was the missing bit of information, which is not contained in your earlier reply nor in the help file (that I can find).  Yes I can see the all the ODBC functions themselves in the help file fine, that was the easy part.

So in opening my database to see in navigator, I am assuming I go to settings/data stores.../create data store, click SQL,click OK.  From there I see a open data store box, with a drop down, which has no explicit "MySQL" option.  Am I going down the right path here?  I also see a file dialog, but I don't want to point to a specific database file, but a odbc connection, correct?  I have the ODBC driver for our database installed under the System DSN tab in the ODBC administrator, "Mysql ODBC 3.51 driver", which I can successfully connect.  We are using the ODBC connection in teststand currently, in which the "datalink/connection string expression" in the schema contains "Provider=MSDASQL.1".  I am not sure where this string came from, as I am inheriting the database portion of the project from a past employee.  

Seeing the database in navigator will provide some benefit, just in being able to see the columns and tables in building my vb script queries.  

[Brad_Turpin]

Hi David,

OK, now I understand, and yes this is an excellent way to get started.  It makes it a lot easier to program the VBScript when you can see the table and column structure in the NAVIGATOR.

I agree that it would be easier to have an explicit "MySQL" data base option, but the reality is that 95% of the time we connect to external data bases with "Connection Type" = "ADO Connection String".  Since you have a DSN associated with the ODBC profile for your MySQL data base, your ADO connection string is particularly simple:

"DSN=_____;UID=_____;PWD=_____;"

Where instead of the "_" characters you would actually type in the Data Source Name (DSN), the User ID (UID) and the Pasword (PWD) for your MySQL data base's ODBC profile.  If you don't need the User ID and Password your string gets even simper:

"DSN=_____;UID=;PWD=;"

Brad Turpin

DIAdem Product support Engineer
National Instruments

[david_jenkinson]

Hi Brad,

That did do the trick, I can see my database in navigator now. 

I'm now going to start checking out the sql wizard script you provided and I'll post any questions I might have in that process. 

Are there any other starter resources, other than the getting started/diadem script section in the help, that you'd recommend for someone starting on the scripting specific side of things, or any general pointers?   

Thanks

David J.

[Brad_Turpin]

Hi David,

The "DIAdem Hands-On Exercises" are a great way to learn about DIAdem in general, but they focus on interactive usage.  Exercise #3 does show you how to use the VBScript recorder, but that's about it for scripting in the Hands-On.

http://zone.ni.com/devzone/cda/epd/p/id/5393

I learned how to program with VBScript using this list of functions in the DIAdem Help:

DIAdem Help
    Programming Reference
        General Information on Scripts
             VBScript
                  Language Reference
                      VBScript Features

The thing I like so much about this Help page is that it groups the VBScript functions by category, say "Array manipulation" or "String handling".  You can also click on each function and get an excellent description of its use and parameterization and most importantly example code you can copy and paste to start from.

Brad Turpin

DIAdem Product Support Engineer
National Instruments

[david_jenkinson]

Ya I did walk through that whole excercise.  Was pretty good but very little on scripting.  I will look through that help file, looks like that will be enough I think to get me going.  Thanks

Dave