String delimiters

The ANSI string delimiter character is the single quote ('string'). Double quotes are used to delimit database object names ("object-name").

Example: WHERE "tabname"."colname" = 'string'

Informix^® allows double quotes as string delimiters, but SQL SERVER doesn't. This is important, since many BDL programs use that character to delimit the strings in SQL commands.

Note: This problem concerns only double quotes within SQL statements. Double quotes used in BDL string expressions are not subject of SQL compatibility problems.

National character strings:

With SQL SERVER, all UNICODE strings must be prefaced with an N character:

UPDATE cust SET cust_name =N'矇閬頝' WHERE cust_id=123

If you don't specify the N prefix, SQL SERVER will convert the characters from the current system locale to the database locale. If the string is prefixed with N, the server can recognize a UNICODE string and use it as is to insert into NCHAR or NVARCHAR columns.

Solution

The SQL SERVER database interface can automatically replace all double quotes by single quotes.

Escaped string delimiters can be used inside strings like the following:

'This is a single quote: '''
'This is a single quote: \''
"This is a double quote: """
"This is a double quote: \""

Important: Database object names cannot be delimited by double quotes because the database interface cannot determine the difference between a database object name and a quoted string !

For example, if the program executes the SQL statement:

WHERE "tabname"."colname" = "string"

replacing all double quotes by single quotes would produce:

WHERE 'tabname'.'colname' = 'string'

This would produce an error since 'tabname'.'colname' is not allowed by ORACLE.

Although double quotes are replaced automatically in SQL statements, you should use only single quotes to enforce portability.

National character strings

When using the SNC database driver, all string literals of an SQL statement are automatically changed to get the N prefix. Thus, you don't need to add the N prefix by hand in all of your programs. This solution makes by the way your Genero code portable to other databases.

With the SNC database driver, character string data is converted from the current Genero BDL locale to Wide Char (Unicode UCS-2), before is it used in an ODBC call such as SQLPrepareW or SQLBindParameter(SQL_C_WCHAR). When fetching character data, the SNC database driver converts from Wide Char to the current Genero BDL locale. The current Genero BDL locale is defined by LANG, and if LANG is not defined, the default is the ANSI Code Page of the Windows™ operating system. See CHARACTER data types for more details.

When using the FTM (FreeTDS) or the ESM (EasySoft) database driver on UNIX™, string literals get the N prefix if the current locale is a multibyte encoding like BIG5, EUC-JP or UTF-8. If the current locale is a single-byte encoding like ISO-8859-1, no prefix will be added to the string literals.