database interface

[eirik]

We are producing for the car industry and the car industry is demanding heavy logging from the first component to delivery and we have to secure, that our logging values are available the next 15 years. It's our responsibility to achieve the demands!  We are going to have a central tailored logging database for our product, because

• Serial-number generation by database functions are easier and  safer. And there is only one place for serial number generation
• There is only one server that has to be to administer
• Process results are better and easier to compare

But the database interface in TS is relatively simple and makes the development with Teststand 4.1 unnecessary complicated. There are serveral problems:

1. In a database like PostgreSQL a function will be called with the expression SELECT function(). Teststand  interprets this like an error! I have to write SELECT function FROM function() if I want get back a value.
2. If I'm calling a function with no return value with SELECT function() and only execute this expression (no fetch), I'm getting an error. But the same function that is returning a simple integer is working. 
3. If I'm working with locale, all numbers with type real will be formated with a comma and that is not allowed in SQL numbers!
4. Building a SQL-String in Teststand is ugly. The parameters will put into the string like this: "SELECT *  FROM ... WHERE " + var=" + Str(parameter);
   Before in V3.5 I wrote always "'" + parameter + "'" but this isnt't looking nice too.
5. Most of the error messages are cryptic.

Teststand 4.1 is a great product and I'm very satisfied so long, but this part isn't very funny. So my proposals are: 

The hole database functionality should be overhauled:

• Typechecking for parameters and results
• Localizing all parameters and results
• We need a SQL-builder with possibility for debugging
• We need a certified database connectivity to databases like SQL-Server, Oracle, MySQL, PostgreSQL, IBM-DB, maybe also to file based databases like Access or MSQL.

•  I'm missing exception handling for example in the case that the database connection is lost.  

Maybe the hole concept based on low level SQL commands isn't really the right answer. I'm open for a discussion and expecting no fast solution. Maybe a version 5.0 will have a better database functionality?

[JettR]

Hello Eirik,

Thank you for the feedback on TestStand's Database interface.  I wanted to address some of your concerns.  Also, I am assuming from your forum post you are using the TestStand Database step types. 

The TestStand Database step types implement the OLE-DB standard interface for communicating with databases, which is defined by Microsoft.  The syntax for calling a database function by using a "SELECT function()" command is not supported by the OLE-DB interface, thus that syntax is not supported by TestStand.  You might want to try "SELECT * FROM function()" or using the "Stored Procedure" Command Type in the Open SQL Statement step type (see the picture below).

 

As for the localization, the Str() function uses the station options setting for localization to format any numbers.  You can change this Station Option by going to Configure » Station Options and selecting the Localization tab.  You can also call the Str() function and pass the third parameter a '3' to override the station option setting.  When you pass a '3' as the third parameter to Str(), you will ensure that Str() will not localize any number regardless of the Station Options.

Lastly, the TestStand Database step types are there to give you a simple interface to databases.  If you need something more complex, there is no substitute for creating code in an external programming environments that interacts with your database.  These other programming environments will give you much more control over the database operations.  Then you can just call these code modules from TestStand.

Message Edited by JettR on 09-03-2008 03:16 PM