How do I ignore the differences in DBMSs when reading data from a DB?

[iCat]

My boss wants me to create a text parser that parses the below sentence and executes an SQL query.

'get table field1,field2,field3,...,fieldN [where SQL conditional statements]'

This corresponds to 'select field1, field2, field3, ..., fieldN from table where blahblah'.

Since different DBMSs have different formats for timestamp, that sentence has to be changed for each DBMS.

One way to ignore the differences is to make a complex text anlayzer that converts a certain type of timestamp string to one that fits each DBMS.

But it looks ridiculous to adopt such a solution when there might be an elegant solution.

LabVIEW DB Toolkit is not capable of concealing the differences.

Does anybody have a better idea than I do?

[Brandon-L]

How many DBMS are you dealing with?

I am not aware of a elegant solution with the LabVIEW DB Toolkit.

[Mark_Ramsdale]

Hi,

If each DB has a different timestamp format you would probably do best to have a case structure based on the DB type.  Each case will parse and reconstruct the datestamp to fit the required format.

There are different ways to format the timestamp from LabVIEW, look at the timestamp conversion functions.  This might help with the parsing, by putting the LabVIEW timestamp in a "better" text format.

[PhillipBrooks · Accepted solution]

I remember having an issue like this when working with an older version of an Oracle server. I was able alter the session by setting 'NLS_TIMESTAMP_FORMAT'.

This page might be of interest; I found this when searching for 'NLS_TIMESTAMP_FORMAT'

Database Portability: Date and Timestamp Columns

(PS The Format Into String and Scan From String functions work nicely with the correct date format specifiers. See the link below in my signature for ISO 8601 timestamp/string conversions)