Extracting one column from Database

[Pablop]

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

[mikeporter]

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...

[mikeporter]

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...

[rolfk]

---

@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

[mikeporter]

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..

[rolfk]

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

[Pablop]

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?

[mikeporter]

---

@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.

---

[tst]

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.

[mikeporter]

Here it is... Enjoy...

Mike...