| A typical sequence of ODBC operations | Using Windows COM Objects | |
All operations to be carried out on a data source are given as SQL statements. SQL is the standard database query language, and ODBC supports a subset of the complete language. For more information about SQL see any relevant documentation provided with your SQL system. For more information on it's implementation in ODBC see the ODBC SDK Programmer's Reference manual.
A handy way of developing the correct syntax for your own SQL statements is by creating a Query in Microsoft Access and viewing the resulting SQL command (View - SQL). The Access SQL is a slightly different dialect to ODBC SQL, but in the main all you need to do is change the use of double quotes " to single quotes ' in the resulting statement.
Note that the @ODBCconnectMode 0 command can be used to ensure that ODBC dialogs are always silenced, including error reporting.
Examples
When using XpertRule's ODBC @commands you can issue quite complex command lines using variables and concatenated text.
Like this one command line example (which may wrap over more than one line. Note that double quote characters are shown in red to clarify their use. All other quote characters are single quotes.
@ODBCexecSQL DBhandle,'SELECT Age FROM Main_Table WHERE Name = '''+SelectedName+''''
In this example SelectedName is a variable. In this example you can also see the use of three single quotes, to get the result of one single quote into the actual end result command string!
The following example would include the contents of the variables MyStringVar and MyNumVar, and also uses square bracket delimiters in place of double quotes (typical for SQL to MS-Access databases).
='SELECT ['+MyStringVar+'] FROM Main_TableWHERE Number = '''+MyNumVar+''''
Note that if the field names contained certain characters, such as spaces, then square brackets must be put around them to enable the SQL to be interpreted correctly. Another tip is that dates need to be enclosed by hash # characters.
Errors in such syntax can become very difficult to debug. Especially as the ODBC error messages will often not be very explicit. To simplify such fault finding in your ODBC commands, you could build up the ODBC command into a single string (e.g. mycommands) and issue it like this:
@ODBCexecSQL mycommands
The advantage is that you can add @Debug commands like this:
@Debug mycommands
and see the string displayed just as it 'looks' to ODBC. This should make syntax errors much easier to spot and correct.
Note that you can also disable ODBC system errors by using the @ODBCconnectMode command.
String Guidelines
String_literal = '{Char|''}' The double single quote, that is, two single quotes next to each other, is replaced by a single quote.
String_Char = #nnn can be used to add an ASCII character number nnn.
String_Var = VariableName
String Expression = String_Literal | String_Char | String_Var { + | & Literal_Text | Single_Char | String_Var }
Examples
@Assign Str = 'Hello to ' + #39 + nameVar + #39 + 'from John'
could give the result - Hello to 'Fred' from John
@Assign Str = 'That''s the way to do it'
gives the result - That's the way to do it