Rules for Using SQL Statements
This section summarizes the rules for using SQL statements. The following table shows the SQL naming conventions used in ADOCE.
Summary
|
Fields and indexes
|
Tables
|
| Length of characters |
1<=n<=64 |
1<=n<=31 |
| Alphanumeric |
Yes |
Yes |
| Control characters (ASCII values 0 through 31) |
No |
No |
| Disallowed |
`. ! ‘ [ ] " * ? |
`. ! ‘ [ ] " * ? |
| Explicitly allowed |
No |
_ - + = , ; @ # $ |
| Leading or trailing white space or periods |
No |
No |
| Interior spaces |
Yes |
Yes |
| Interior periods |
No |
No |
The following criteria apply to SQL statements:
- White space consists of the characters space, tab, newline, and linefeed.
- Runs of white space are treated as a single white-space character in all cases except within strings enclosed by quotation marks.
- White space is ignored except within strings enclosed by quotation marks or when the white space serves as a separator.
- Quotation marks around object names are required when names contain elements other than alphanumeric characters and/or the underscore.
- An SQL statement can end in a trailing semicolon, which is ignored.
- An SQL statement can be no longer than 2,048 characters.
- You can enclose strings with either double or single quotation marks. There is no escape mechanism for actual quotation marks within strings. You can use square brackets to enclose object names but not strings.
- Quotation marks must be used to enclose table or field names that are not alphanumeric text. The exception is the underscore character, which can be used without enclosing quotation marks.
- Statements are case sensitive when assigning data to a field.
- SQL is always in English and dates are interpreted according to the current user’s locale. Use the “yyyy/mm/dd” format to provide generic locale-independent dates. SQL keywords are not localized.
- Table names and field names must contain a leading alphanumeric character.
- An empty SQL string generates the DB_E_NOCOMMAND error value.
- A table name cannot contain periods, exclamation points, accent graves, brackets, leading spaces, or control characters. The name has a maximum length of 31 characters.
Other rules and limits of the SQL control include the following:
- Constant literals are limited by memory.
- Tables are limited to 255 columns.
- Query results are limited to 64k.
- Table and procedure names are limited to less than 31 characters.
- Field names can be up to 64 characters.
- Up to three fields can be sorted in an ORDER BY clause.