Extended stored procedures Sybase IQ now supports extended stored procedures for sending electronic mail using the Microsoft Messaging API standard (MAPI) or the Internet standard Simple Mail Transfer Protocol (SMTP). These system procedures are implemented as extended stored procedures: each procedure calls a function in an external DLL.
For more information, see Chapter 7, “System Procedures and Functions,” in Adaptive Server Anywhere SQL Reference.
EXECUTE IMMEDIATE supports queries that return result sets The EXECUTE IMMEDIATE statement can return a result set when you specify the WITH RESULT SET ON clause. This new feature allows more dynamic construction of statements inside stored procedures. The default setting is WITH RESULT SET OFF.
EXECUTE IMMEDIATE allows more flexible escape character processing A new option WITH ESCAPES OFF allows escape character processing to be suppressed. This feature makes it easier to construct dynamic statements that include file paths.
EXECUTE IMMEDIATE allows more control over identifier delimiters A new option WITH QUOTES overrides the current setting of the QUOTED_IDENTIFIER option.
For more information, see “EXECUTE IMMEDIATE statement [ESQL] [SP]” and “QUOTED_IDENTIFIER option [TSQL]” in Sybase IQ Reference Manual.
SET statement can be used to assign variable values You can now assign values to variables using the SET statement in Transact-SQL procedures.
Source format preserved for stored procedures The source format, including spaces and line breaks, is now stored in the database as a comment. This comment is used for procedure profiling.
MESSAGE statement enhancements A FOR CONNECTION clause has been added to the MESSAGE statement.
Also, a DEBUG ONLY clause has been added to the MESSAGE statement. When the DEBUG_MESSAGES option is set to ON, debugging messages appear for all stored procedures and triggers that contain a MESSAGE statement that includes the DEBUG ONLY clause.
For more information, see MESSAGE statement and DEBUG_MESSAGES option in Sybase IQ Reference Manual.
Extended support for variables in SQL statements Several statements have been made more flexible by permitting variables as well as constants in some locations. This is especially useful in stored procedures and batches, where variables can be declared and used. It provides functionality previously only available, in more cumbersome form, in EXECUTE IMMEDIATE.
The following statements have this extended support for variables:
The TOP clause of the SELECT statement can now reference integer variables as well as constants.
BACKUP statement archive-device.
RESTORE statement db-file, archive-device, and dbspace-name.
LOAD TABLE statement filename.
For more information, see Chapter 6, “SQL Statements” in Sybase IQ Reference Manual.
sa_make_object system procedure This system procedure can be used in a SQL script to ensure that a skeletal instance of an object exists before executing an ALTER statement which provides the actual definition.
For more information, see “sa_make_object system procedure” in Adaptive Server Anywhere SQL Reference.
