LabVIEW
Possible DateTime bug in Database Toolkit
Solved!
We've had issues with old code using "home cooked" sql-statements. The solution was supposed to use more of the build in functions for increased safetly and stability. Now those build in functions need a workaround which basically puts us back to square one.
Yes, i can easily build an SQL-statement myself. I can also write a memory manager by myself. I shouldn't have to.
/Y
Actually with SQL statments it is by far not as easy. SQL Server does not use the exact same SQL syntax as for instance MySQL or even worse Oracle. So if someone implements an SQL interface he has a few choices. Implement the least common denominator, which for SQL syntax is really very little, or go for a specific database backend. SQL Server would be the obvious choice here, but that leaves out all the people that want to use Access since it is part of their Office package or since they happen to have it around for whatever other reason. Also some argument that any closed source DB backend is a complete nogo, so their choice of MySQL is also punished. Then there are the corporate users who work with SAP or whatever else based on Oracle backends, or with Oracle directly. This is where the really big money is as far as DBs are concerned, so it would be another logical choice, although in the case of LabVIEW applications, most IT departements rather would give an arm and a leg then letting some solutions not being developed in their choice of environment connect to their databases.
So the choice in the case of the Database toolkit was mostly to support the most common denominator, leaving some more exotic database backends out in the cold. It works for all major database backends, but doesn't support 100% of the features of any single backend. It also means that the fractional seconds had to be scraped to allow support for databases that can't represent fractional seconds. And they do exist.
Of course you don't like it, and that is your right, but I feel for me it's not worth the energy to get upset about these things. If it is important for me I make a specific adaption of a VI for my specific database and otherwise I just leave it as it is.
In this specific case I have to admit that I don't even use the Database Toolkit. I have long ago written my own ODBC based database interface library and incorportated over the time many features into it. And I have used it with SQL Server, Oracle, Access and even MySQL and there is no way around it, if you want to make use of more advanced database features then you have to write backend specific functions in many cases. I tried to put a lot of things into the intermediate DLL to make it work seemless, but in many cases this ends up into several feature flags that must be enabled for a specific backend but cause troubles for another backend.
I'm using the solution i posted earlier, i have a DB-cluster in which Booleans are integers and DateTimes are strings and a DateTime->String function. It gives the right results in the DB.
The boring side effect is that i cant read from DB directly with my target cluster but need to read from this DBcluster and then wire each element with conversion as needed to my target cluster.
/Y
