Interactive SQL Behavior Changes

Changes to Interactive SQL behavior were introduced in this release.

• READ statement encoding algorithm changed – When running the READ statement in Interactive SQL, the encoding that is used to read the data is now determined in the following order:
  1. The encoding specified by the ENCODING clause (if this clause is specified).
  2. The encoding specified by the byte order mark in the file (if byte order mark is specified).
  3. The default TEXT format encoding (if the format encoding is specified).
  4. The default encoding for the platform you are running on. On English Windows computers, the default encoding is 1252.
• Changes to the default encoding for Windows – The following change applies to running Interactive SQL as a console application (with no windowed user interface) on Windows computers where the ANSI and OEM encodings differ.

  1. Previously, when running Interactive SQL as a console application, the INPUT and READ statements assumed that the file was encoded using the OEM encoding (cp437, for example) in the absence of an explicit ENCODING clause. Similarly, the OUTPUT statement would output the file using the OEM encoding.

     Now, when running Interactive SQL as a console application, the INPUT and READ statements assume that the file is encoded using the ANSI encoding (cp1252, for example). Similarly, the OUTPUT statement outputs the file using the ANSI encoding.

     From the command prompt, to process a file that uses the OEM encoding, you must specify the encoding explicitly. For example:

     dbisql READ ENCODING 'cp437' myfile.sql
     

  2. Previously, when running Interactive SQL as a console application, results written to and read from the command prompt used the ANSI encoding (cp1252, for example), which could cause extended characters to be displayed incorrectly.

     Now, when running Interactive SQL as a console application, results written to and read from the command prompt use the OEM encoding (cp437, for example).

• Change to the CLEAR statement, Clear menu item, and the Esc key – Now, the CLEAR statement closes any open result sets and leaves the contents of the SQL Statements pane unchanged.

  Also, the Edit > Close Results menu item, which is equivalent to executing a CLEAR statement, closes any open results sets and leaves the contents of the SQL Statements pane unchanged.

  The Edit > Clear menu item which used to clear the contents of the SQL Statements pane has been removed. As a result, the keyboard shortcut for the Clear menu item, the Esc key, has also been removed. Now by default, pressing the Esc key has no affect.

  However, you can set the Esc key to clear the SQL Statements pane and close any opened result sets. Choose Tools > Options > Compatibility and select Pressing The Esc Key Clears SQL Statements And Closes Result Sets.

• -codepage option removed – If you want Interactive SQL to read a file with a specific code page, use the ENCODING clause of the OUTPUT, or READ statements. The -codepage option has been removed from the software.
• Change to SET OPTION statement [Interactive SQL] – Previously, if you used the SET OPTION statement to set an option and didn't specify a value, the option was set to Off. Now, if the option value is omitted, the specified option is set to its default value. This change affects the following options: auto_commit, auto_refetch, bell, commit_on_exit, and echo.
• Change to OUTPUT statement – When outputting results to a TEXT file, you can use the WITH COLUMN NAMES clause to insert the column names at the beginning of the file.
• READ keyword no longer required – When you run Interactive SQL from the command prompt, the READ keyword is optional when specifying a .sql file to run. If the .sql file requires parameters, specify the parameters after the file name.
  For example, these commands are equivalent:

  With the READ keyword	Without the READ keyword
  READ file.sql parm1	file.sql parm1

• Improvements to Microsoft Excel ODBC driver support – The following list describes changes related to exporting data via the Microsoft Excel ODBC driver:

  • Previously you could not export data that was stored as a CHAR, LONG VARCHAR, NCHAR, NVARCHAR, or LONG NVARCHAR data types.

    Now when you export data that is stored as a CHAR, LONG VARCHAR, NCHAR, NVARCHAR, or LONG NVARCHAR data types from a SQL Anywhere database using the Microsoft Excel ODBC driver, the data is stored as VARCHAR (the closest type supported by the Excel driver).

    The Microsoft Excel ODBC driver supports text column widths up to 255 characters.

  • You can export data that is stored as REAL, FLOAT, and BIGINT data types.
  • Data stored as MONEY and SMALLMONEY data types are exported to the CURRENCY data type. Otherwise numerical data is exported as numbers.
  • You can use the Export Wizard to export tables.
• Installation Changes – Interactive SQL is no longer an option in a custom installation. The installer installs Interactive SQL by default.