Using CitectSCADA > Exchanging Data with Other Applications > Using ODBC drivers > Getting the correct syntax with ODBC

Getting the correct syntax with ODBC

The ODBC syntax for SQLs varies from the Access syntax in some ways. A good way to get the syntax correct and view the resulting Recordset is to use the query designer in Microsoft Query then copy the SQL text from it into Cicode. Because MS Query uses ODBC, any syntax that works in it will work when called via ODBC from Cicode. MS Query can also be used to confirm that the DSN is correct.

MS Query tends to create SQL text that is more complex than necessary. In particular it includes the path with the file name which is not necessary because the path is already defined in the DSN entry. It is considered bad practice to hard code file paths. MS Query also tends to prefix column (field) names with the table names to avoid any chance of ambiguity. Again this is not always necessary and it is desirable to keep the SQL text as brief as possible in your code.

The SQL statement text generated by the query designer can be pasted into Execute SQL window (under the File menu of MS Query), any surplus text removed and the SQL statement tested until the simplest syntax that works can be found. There is provision to save the SQL text if necessary. The final version of the SQL statement can be used with confidence in Cicode.

See Also