Database connectivity package is flawed

[will_t]

Hello,

I have noticed a fundamental flaw in the database connectivity package that has been bothering me very much.

Any of the VIs that return a "database variant" return a 2 dimensional array representing the table that is the result of the given query.

This concept is flawed because it makes assumptions about the ordinal positions of rows and columns. In relational databases it doesn't make any sense to attempt to cast a table into an array. That would be analogous to converting an unordered set to a matrix.

Relational databases do not have anything in their specifications that say anything about the logical ordering of rows and columns. A table is simply an unordered set. Of course in practice they do have a physical ordinal position depending on the database implementation and it seems that the connectivity package uses this ordinal position to convert the table result into an array. This effectively makes the query nodes unusuable as based on relational database theory the order of the results are undefined. This issue isn't as problematic withe the ordering of rows since they typically have a primary key or collation. However this is problematic with columns. There is nothing gauranteeing me that the logical ordering of the columns is going to follow some kind of rule because there is none. They are by definition unordred yet the query VIs assign them order. This is a major concern as I'm developing an application that will be used for a long time and this brings into question the future reliability of the code when future maintainers attempt to debug a problem stemming from this when a change in the database is made.

My basic question is if the engineers of this product are aware of this issue and if it will be resolved in a patch or future version. I honestly think it should be patched it. However, that is up to NI of course.

Thank you for your time,

[patrickthebold]

Does LabVIEW have unordered sets?

[Dennis_Knutson]

I am far from being a relational database expert but how else would you want to display the results of a query? I use either LabSQL or the database functions in TestStand and the results are displayed the same as when I use the Query Analyzer in SQL Server. It seems to me that the query you write applies a logical ordering or rows and columns to a view that the user has selected.

[Wiebe@CARYA]

If you don't want your data as a variant with 2d array, how do you want it? You say you don't like how it is, but not how you would want it.


Regards,


Wiebe.

[will_t]

A simple solution would be having the query also output column name to ordinal mappings. For example it could return an array of strings where index 0 represents the position of column ordinal 0 and its name. Then you can index that and problem solved. Not the most elegant solution; but, simple and effective.

Like I said earlier the rows aren't really an issue because they will be ordered by the collation rules defined for the table.

It's not that I have a problem with converting it to a 2d array in principle (some relational algebra purists would). However, there needs to be some information available to determine ordinal mappings at the very least so that the order isn't undefined.

Message Edited by will_t on 07-16-2007 11:07 AM

[Dennis_Knutson]

I don't know if there is a function in the database toolkit to get the column names since I don't use it. You might want to look and see if such a function is included. I'm not sure it would be appropriate to include those in the 2D array of values though, and I doubt that the toolkit would ever be modified to do this. It would just break too many existing programs. I could conceive where it might be modified to return colum names as a separate item however. For product improvement suggetstions, NI has a page at http://digital.ni.com/applications/psc.nsf/default?OpenForm&temp1=&node=.

In the meantime, you could also look at some of the VIs that Mike Porter has posted that use low level ADO functions such as the ones at http://forums.ni.com/ni/board/message?board.id=170&message.id=256401#M256401. The column names are returned and used as the column headers in a LabVIEW table.

[tst]

I'm also far from an expert on DBs, and I find getting back a 2D array is satisfactory. Are you saying that each row in the result of query can have a different structure?

In any case, the DB toolkit was designed to make things simple for people who do not have experience with DBs, not to be a high level DB manipulation tool. Its main advantage is that it parses clusters automatically, so that you can build clusters which map to tables, but you can do that yourself using some OpenG VIs.

If you really want, you can go into the Rec Get Value From Field, output the Name property and build your 1D array of column names, but my advice (like Dennis) is that if you understand a lot about DBs, don't use the toolkit and use something which is more low level.

[will_t]

I wasn't aware there were lower level toolkits available. I'll give them a shot. Thanks for the links.

[mikeporter]

I have a lot of experience with databases and I'm not sure what in the world you are talking about. While it's true that rows in a table have no inherent order, this is why in SQL you have the ORDER BY clause in SQL. However, columns in a table most definitely have an order - and its defined when the table is created. Likewise, the data in a query also has a well-defined column order. The columns typically occur in the order that they are listed in the query. If the wild card character "*" as in:

SELECT * from MYTABLE

The columns occur in the order defined in the table definition.

Mike...

[smercurio_fc]

I just saw this thread, and I have to echo Mike's comment. I've also done quite a lot of work with relational database in SQL Servers and I have no idea what you're talking about either. As Mike indicated, if using SELECT *, the column order will be as the table is defined. The column order can be explicitly specified in the query by using SELECT col1, col2, col3, etc. I don't usually pay attention to row order since it doesn't usually matter since the data is being used in applications which control the sort at the application layer, rather than the data layer. This wouldn't be an Oracle thing, would it?