Different databases use different SQL dialects, making it necessary for C1Query SQL generation to adjust its syntax rules to the database requirements. Even with the same database, you may need different formats depending on the current settings in your database.
Properties of a special object, C1Query.SQLInfo determine various parameters affecting the syntax of generated SQL queries, for various aspects of the SQL language that are database-dependent. C1Query fills these properties with default values suiting the database from which the schema was imported, for three OLE DB providers: Microsoft OLE DB providers for SQL Server, Access and Oracle. For other databases and OLE DB providers, it is the responsibility of the developer to set the appropriate values of these properties in code. See SQL Syntax Properties.
Another source of dependencies is representing literal (constant) values for different data types. Databases use different syntax for date, time and number values in SQL. As with general SQL syntax rules, C1Query tries to resolve this issue automatically for the three popular OLE DB providers previously mentioned. But it may be necessary for the developer to intervene, if they use a different OLE DB provider, or have special settings in their database.
C1Query provides two levels of control over literal values in SQL. In most cases, you can use SQLInfo.FormatDate/FormatFloat/FormatTime properties. These are properties of the C1Query.SQLInfo object. Set these properties in code if their default values do not satisfy your database requirements. Their values are valid format strings as in the Visual Basic Format function. There are also two additional values:
|
Default |
Values are formatted using the default Windows rules (as in Visual Basic function CStr), according to the current regional settings. |
|
US Default |
Values are formatted as in regional setting “English (United States)”. |
 |
Note: FormatDate/FormatFloat/FormatTime properties have effect only in Visual Basic application. When using C1Query in containers other than Visual Basic, you need to use the FormatLiteral event. |
The FormatLiteral event gives developers full control over generating literal strings in the resulting SQL statement. If FormatDate/FormatFloat/FormatTime properties do not solve your formatting problems, you can use the FormatLiteral event to specify the text for every literal in the generated SQL statement dynamically. This event is triggered for every constant value in the user query. The Value argument gives you the value that needs to be formatted. If you set the Text argument before exiting the event procedure, the specified string is used for the formatted literal value in the resulting SQL statement.