Database connectivity package is flawed

[mikeporter]

No it's not an Oracle thing -- what good would any database be that didn't return data in some sort of deterministic way. You don't get to be the 2nd largest software company in the world delivering code that's totally useless (And no, I'm not going to go for any cheap Micro$oft jokes! I'll leave that as an 'exercise for the reader').

Now if he's concerned about some operational "quirk" of the database connectivity toolkit that is certainly something to have a conversation about, but that has nothing to do with relational databases in general...

Mike...

[patrickthebold]

I don't know much about data bases but it seems to me what he wants is the row numbers and column numbers to be hidden from the user.  For example if I want to store everyone's phone number in a database it doesn't matter that the phone numbers are in column 2, but that there is a field for "phone numbers."  Similar to how different languages handle enumerated types.  In C you can use the underlying integer for an enumerated type but it java you are not allowed.  The original poster wants something more like the java way, since there is no meaning behind the row and column numbers.  It's not really a bug so much as a preference, though there is reasoning behind it.

[smercurio_fc]

Mike, that was indeed my whole point, though not stated as clearly. The queries, as you also pointed out, return the data in a deterministic way, but it almost seems as if the poster is saying they shouldn't. In fact, his statement "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...". I'm not sure what mechanism is trying to be used here to get data other than queries.

My question about the Oracle thing was whether it returned a "SELECT *" query differently than, say, SQL Server would. Doesn't make any sense to me that it would, but then I haven't used Oracle.

Message Edited by smercurio_fc on 07-16-2007 03:07 PM

[smercurio_fc]

To patrickthebold: Well, any query won't return row numbers, and at the application level I'm not sure you can even use ordinal position in a TSQL statement for querying a database. If one really needs to know ordinal positions of columns the stored procedure sp_columns in SQL Server will return a table that lists the ordinal position of each column in the table specified as an argument to sp_columns.

[Nasir]

Hi:

To add to Bill's concerns, we ran into other critical issue when using this tools in 2000-2003.  Lot of time, the database connection (Oracle or Postgresss) would not close completely.  That wiould cause database lockdown.  After digging inside, we found NI uses Microsoft ADO Object (Active X) at the lower level.  So, for advanced user it may be a solution.  However, in that case you have to know what you're upto.

On Mike's comments, simple SQL statement like SELECT, can be complex when there is embeded statemet.  I mean multiple query in a SELECT statement.  In that case, you have to know expected data output.  In our case, we used raw ASCII and reformated to our desired ouput.

Nasir 

[mikeporter]

Yes a select statement can get complex, but at the heart there will always be a list of things that you are selecting, plus if you are using ADO you can fetch the name of each column in the recordset.

Concerning the ADO comments, I would assert that learning to use ADO is not really too hard - at least those parts of it that you will be using 99% of the time. Plus it has excellent documentation.

Mike...

[BillMe]

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

I don't see the flaw: The columns are returned in the order in which they were specified in the SELECT statement, e.g., "SELECT A, B, C FROM MYTABLE" returns A in column 0, B in column 1 and C in column 2.

[wiebe@CARYA]

Hi,

This was one of the first things I made after installing the LabSQL toolkit! That was years ago, and I did expect it to be included in LabSQL by now. It only returns the table names. You could use this vi (with open and close connection from the LabSQL toolkit), and use the connectivity toolkit for everything else.

Regards,

Wiebe.

Message Edited by wiebe@CARYA on 07-17-2007 04:18 AM

[mikeporter]

Of course this excellent example begs the question: If this is all it really takes to do a query - and a query is the only things that's half-way complicated in ADO - why bother with the connectivity toolkit at all...

Mike...

Message Edited by mikeporter on 07-17-2007 09:51 AM

[Wiebe@CARYA]

I agree. But it might save him from rewriting all the code he has.


I switched to LabSQL years ago because the connectivity toolkit was limited, and could not be changed. I ran into problems with types like uchar and meno (as I recall) that where not recognised by the toolkit, although it's a simple string. After removing a few leaks (and posting them to LabSQL's creator) it was, and still is, very usable. So I'm still using it with great pleasure. I don't know if the connectivity toolkit has evolved since...


Regards,


Wiebe.