topic Re: Extracting one column from Database in LabVIEW

Extracting one column from Database

Pablop — Fri, 29 Jun 2007 23:54:30 GMT

I'm just begging my forray in to using the Database Connectivity tools and I have a couple of queastions (the first of many most likely) that haven't been answered by going through the DB Tools Manual and this forum.

See attached JPEG. I want to extract the first colum of the table because I will use this to populate a selection number ring. I assume from the help file that the "columns" input feature of the Select Data VI will help me do this, but I'm at a loss to know what its looking for. I tried the column name and the column number, that didn't work.

Second question, what is the best method for handling inserting data into a table if it has the Autonumber field as column 1.I want that number to be provided by the Access DB, and I need to skip it it. Its probably tied to the same as above as I notice that the insert VI has an input feature for cloumns as well.

Thanks

Re: Extracting one column from Database

mikeporter — Sat, 30 Jun 2007 01:46:20 GMT

The first thing I would recommend is to not waste your time with the Database Connectivity Toolkit. I make this recommendation because the DCT adds a lot of complexity with no real advantage. Most of the problem I see involve (as in your case) trying to get the toolkit to do something that should be very easy. The attached file is something I put together a while ago to show how easy it is to communicate with Access databases. However, I don't use this code for deliverable applications because it doesn't incorporate any subVIs. It's point is to demonstrate the inherent simplicity of the process.

Over the weekend I'll post the VIs I use for connecting to databases. Fetching a single column should be as simple as writing a query that returns the column you need. With my drivers it is. Likewise, inserting data into the database should simply be a matter of executing a command that lists the data to insert into the columns that you choose to populate. Again, with my drivers its simple like it should be.

The structural difference between my drivers and the DCT is that the latter forms a very "thick" layer overlaying the database. The complexity of this layer hides the native database interface substituting it's own proprietary interface. This is a problem because if you have trouble it's not enough to find someone that knows databases. You have to find someone who is familiar with this toolkit.

By contrast, the alternative drivers are a very "thin" layer overlaying the native ADO support from Windows. Hence if you have a problem you can get help from anyone that knows ADO, SQL or the specific database that you are using. Let's face it, there are a lot more people who know databases, SQL and ADO than there are people who know the database connectivity toolkit.

Finally because these alternative drivers are more direct, they are also much more efficient.

Mike...

Re: Extracting one column from Database

mikeporter — Sun, 01 Jul 2007 19:43:30 GMT

Here are the drivers that I promised. Please let me know about anything you find in the documentation that you don't understand or doesn't sound right.

Mike...

Re: Extracting one column from Database

rolfk — Mon, 02 Jul 2007 09:13:14 GMT

@mikeporter wrote:

By contrast, the alternative drivers are a very "thin" layer overlaying the native ADO support from Windows. Hence if you have a problem you can get help from anyone that knows ADO, SQL or the specific database that you are using. Let's face it, there are a lot more people who know databases, SQL and ADO than there are people who know the database connectivity toolkit.

Finally because these alternative drivers are more direct, they are also much more efficient.

Hmm, this is a bit of a bold statement. The thickest part of accessing databases with ADO is in the ADO layer itself. What the DCT adds to this is in comparison not so thick. It could be that the DCT does some things a bit more complicated than would be strictly necessary to support various use cases, but in general the performance of a database query is rather in the method used than in the interface. For instance using querysets for the OP problem will most likely be the most efficient way to get the data, and the DCT does use them by default if I'm not mistaken.
I could however be wrong here as I haven't used the DCT myself yet, just looked over it, since I use my own ODBC based database access interface for platform independance reasons. ODBC can work on Linux and MacOS too while ADO/DAO is ActiveX based and for that reason Windows only.

And no, ODBC does not have to be slower if you know what you want to do with the database and have an interface that translates well between the ODBC C API and LabVIEW.

Rolf Kalbermatter

Message Edited by rolfk on 07-02-2007 11:14 AM

Re: Extracting one column from Database

mikeporter — Mon, 02 Jul 2007 11:55:37 GMT

I don't see what is so bold about it - more like common sense. Consider that there are literally hundreds of web sites run by consultants, user groups and vendors where people can go with questions and problems concerning ADO, SQL and databases. Unfortunately if you're using the DCT none of these sites are of any use to you because you have the DCT's proprietary interface hiding the software and interfaces that are really doing the work.

I agree with you that ADO is a very think layer - which is largely why I am so reluctant to add another thick layer on top of it. Especially so when it's a layer that seems to add problems and few benefits. Until you have troubleshot a problem of trying to get the DCT to do something that should be rather simple, you can't really appreciate how thick it is itself.

You are also absolutely correct about ODBC not necessarily being slow. Over the past decade the interface (or perhaps the available drivers) has improved considerably making possible things like operating with BLOBs. In addition, as you point out ODBC is now a cross platform solution.

Mike..

Re: Extracting one column from Database

rolfk — Mon, 02 Jul 2007 13:06:03 GMT

Actually ODBC has been cross platform for many years . unixODBC and libiodbc do exist for quite some time and have worked fairly well even 5 years ago. MacOS ODBC access was another issue, as Apple originally supported it through MacODBC but later abandoned this, probably for licensing cost and support reasons. Now with MacOS X being able to use unixODBC it is also supported.

Speed of ODBC is not only a driver issue but also an issue how you implement the API especially with LabVIEW, since you do not want to go through to many hoops with binary-string-binary data conversions. This is also the area where ODBC access gets difficult since even the best API implementation can not succeed to reduce this overhead if a particular driver does not support certain binary data types. And with any given database interface it is actually a trial and error process if a certain datatype can be directly used.

Rolf Kalbermatter

Message Edited by rolfk on 07-02-2007 03:13 PM

Re: Extracting one column from Database

Pablop — Thu, 05 Jul 2007 20:43:08 GMT

Hello Mike,

These VI's look great, thanks, I've already started to use them (and I never did get a response on exactly how to use the DCT).

Just so I'm understanding this correctly: The zipped up Databse ICS files seem to be examples of how you would access a database without either the DCT or your subsequently poseted VI's? Was that the purpose of posting it, to show how it would be done otherwise?

With regard to the set of VI's you ove posted, I may never need all of them, but thanks! I'm using the Open, Close and Create and Read record set rather straighforwardly to load data from my sample database. Which VI would you primarily use to insert, udate and delete data? The Execute SQL VI?

Re: Extracting one column from Database

mikeporter — Thu, 05 Jul 2007 21:37:02 GMT

@Pablop wrote:
Hello Mike,

These VI's look great, thanks, I've already started to use them (and I never did get a response on exactly how to use the DCT).

Just so I'm understanding this correctly: The zipped up Databse ICS files seem to be examples of how you would access a database without either the DCT or your subsequently poseted VI's? Was that the purpose of posting it, to show how it would be done otherwise?

Not really. You want to use well-structured drivers for deliverable code. The purpose of those VIs is simply to expose the "bare bones" of the process to illustrate that the process is basically pretty simple. I have talked to a lot of people who feel that there is some profound mystery involved in connecting to databases. I wanted to show that there isn't.

With regard to the set of VI's you ove posted, I may never need all of them, but thanks! I'm using the Open, Close and Create and Read record set rather straighforwardly to load data from my sample database. Which VI would you primarily use to insert, update and delete data? The Execute SQL VI?

Yes, the Execute VI is for any sort of action that doesn't return data. In addition to inserting, updating and deleting data you can also use it to send SQL DDL (data definition language) statements that modify the structure of the database. This category would include such things as creating tables, adding columns, altering user permissions, etc.

Re: Extracting one column from Database

tst — Fri, 06 Jul 2007 07:30:28 GMT

Mike, can you backsave those to 7.1\7.0?
I only had a chance to look at them shortly and I would like to do it in a better environment where I only have 7.x.

Re: Extracting one column from Database

mikeporter — Sat, 07 Jul 2007 23:01:08 GMT

Here it is... Enjoy...

Mike...

Re: Extracting one column from Database

tst — Sun, 08 Jul 2007 09:40:19 GMT

Excellent!

P.S. Are you coming to NIWeek?

Re: Extracting one column from Database

mikeporter — Sun, 08 Jul 2007 21:16:43 GMT

Unfortunately not. Can't put the finances together this year

Mike...

Re: Extracting one column from Database

kkronline — Fri, 13 Jul 2007 07:49:54 GMT

Mike,

please send the VIs for LV7.1.

Thanks in advance.

Karthik

Re: Extracting one column from Database

tst — Fri, 13 Jul 2007 08:19:23 GMT

@kkronline wrote:

Mike,

please send the VIs for LV7.1.

Look at post #10 and then give Mike a lot of stars.

Entering large data stream to Database

Pablop — Mon, 27 Aug 2007 22:47:03 GMT

Hey Mike (or any interested users out there!)

I'm banging away on my main application and have made use of your DB tools, mostly just the basic ones. They work great. But I was wondering about your advice on writing a large amount of data into the DB. It goes something like this:

1) The operator hits the test buttons
2) 80 unique pieces of data are generated, mostly numeric, a few boolean, some string. My program shoves them all into a cluster (with clsuters in it) and passes it to a Write DB VI.
3) I want all of these items to be entered in a single DB row. Date/Time Stamp, Part Number, Serial Number and then all of the other stuff

Seems like the only way I know how to accomplish this is to write an enormous INSERT statement explicitly using the name of each field and then use the Execute SQL VI. Is that about it for options?

Re: Entering large data stream to Database

tst — Tue, 28 Aug 2007 06:36:48 GMT

You can try doing something like the attached VI (I believe this is based on code originally written by Jean-Pierre Drolet), but you should note that this is not tested code, it was just a quick example.

You will need the OpenG data tools package to do the variant cluster parsing.

Re: Entering large data stream to Database

Pablop — Tue, 28 Aug 2007 15:56:30 GMT

I'm not able to open the file, I must not have the correct tool set.

Re: Entering large data stream to Database

tst — Tue, 28 Aug 2007 18:30:12 GMT

Follow the instructions at the link and then download the data tools package (you will also need the string package).

If you want another approach, you can try modifying Ben's example here.

Re: Extracting one column from Database

m_min — Wed, 14 May 2008 08:17:16 GMT

Mike,

Your database drivers are totally awesome... Thanks so much for your contribution..

Regards,
min

Re: Extracting one column from Database

msimpson — Fri, 11 Jul 2008 20:09:22 GMT

I may be to late, but can you backsave these to v 6i? They'd help me out quite alot.

Thanks,

Mark