Release Notes - SQLines SQL Converter

Changes in SQLines SQL Converter tool.

• Version 3.3.77
• Version 3.3.73
• Version 3.3.71
• Version 3.3.70

• Generating function that RETURNS TRIGGER when converting CREATE TRIGGER (Oracle to PostgreSQL)
• Converting RAISE_APPLICATION_ERROR to RAISE EXCEPTION (Oracle to PostgreSQL)
• Converting GETDATE() + 1.1 (decimal expressions) to INTERVAL expressions (SQL Server to MySQL, MariaDB, SingleStore)
• Converting SELECT statements returning result sets in stored procedures to DECLARE CURSOR WITH RETURN and OPEN cursor statements (SQL Server, Sybase, MySQL to DB2)
• Recognizing pattern IF EXISTS (SELECT * FROM sysobjects WHERE … OBJECTPROPERTY(id, N'ISPROCEDURE') = 1) DROP PROCEDURE (from SQL Server)
• Recognizing pattern IF EXISTS (SELECT * FROM sysobjects WHERE … OBJECTPROPERTY(id, N'ISVIEW') = 1) DROP VIEW (from SQL Server)
• Adding explicit casting when converting '0' < 1 string-number comparison (Oracle to PostgreSQL)
• Adding Azure Synapse as target database
• Adding code snippets for non-ASCII identifiers in the assessment report
• Converting RPAD function (Oracle, MySQL, PostgreSQL, Netezza to SQL Server, Synapse)
• Converting PRIMARY KEY to PRIMARY KEY NONCLUSTERED NOT ENFORCED (to Synapse)
• Converting UNIQUE constraint to UNIQUE NOT ENFORCED (to Synapse)
• Removing schema name in DROP TRIGGER and CREATE TRIGGER statements (to PostgreSQL)
• Adding table name to DROP TRIGGER (to PostgreSQL)
• Converting SQLCODE to SQLSTATE (Oracle to PostgreSQL)
• Converting conditional predicate INSERTING to TG_OP = 'INSERT' in CREATE TRIGGER (Oracle to PostgreSQL)
• Converting DATETIME2(p) to TIMESTAMP(p) (SQL Server to Snowflake)
• Parsing and commenting ORGANIZATION EXTERNAL clause in CREATE TABLE (from Oracle)
• Commenting GLOBAL TEMPORARY in CREATE TABLE (to SQL Server)
• Moving NOT NULL constraint from ALTER TABLE … MODIFY (column NOT NULL ENABLE) to CREATE TABLE statement (Oracle to SQL Server)
• Renaming primary constraint name if it is the same as an index name defined on the table (to SQL Server)
• Changing the data type of the foreign key column to match the data type of the primary key column (to SQL Server)
• Adding NOT NULL constraints explicitly for primary key columns (Oracle to SQL Server)
• Bug fix - Parsing declaration block in CREATE TRIGGER (Oracle to PostgreSQL)
• Bug fix - Not specifying INTERVAL DAY literal precision when used in PL/SQL code (to Oracle)
• Bug fix - Converting DATEADD(HH, datetime) function (SQL Server, Sybase to Oracle, PostgreSQL)
• Bug fix - Removing function name after END function if CREATE FUNCTION has the qualified name schema.function (Oracle to PostgreSQL)
• Bug fix - Handling UTF-8 0xC2A0 sequence (non-breaking space) in the source scripts as it broke the SQL parser
• Bug fix - Converting :NEW.column and :OLD.column references to NEW.column and OLD.column in CREATE TRIGGER (Oracle to PostgreSQL)
• Bug fix - Adding PERFORM for procedure call within a trigger (Oracle to PostgreSQL)
• Bug fix - Converting enclosed [schema]] name (SQL Server to Snowflake)
• Bug fix - Removing schema name from index when the schema name mapping is applied (to SQL Server, MariaDB, MySQL)
• Bug fix - Parsing USING INDEX for primary key columns (from Oracle)

• Dynamic SQL conversion referenced in EXEC statements (from SQL Server, Sybase)
• Moving in-place declarations to DECLARE section of BEGIN-END block (to Oracle)
• Converting BEGIN CATCH to EXCEPTION WHEN OTHERS block (SQL Server to SingleStore)
• Converting RAISERROR function to RAISE USER_EXCEPTION (SQL Server to SingleStore)
• Convert FORMAT function to DATE_FORMAT (SQL Server to MySQL, MariaDB, SingleStore)
• Commenting SET XACT_ABORT ON statement in stored procedures (SQL Server to Singlestore)
• Commenting SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED | COMMITTED in stored procedures (SQL Server to Singlestore)
• Converting EXEC sp_name params procedure call into sp_name(params) (SQL Server, Sybase to Oracle)
• Parsing FORWARD_ONLY and STATIC cursor options in DECLARE CURSOR statement (from SQL Server)
• Converting pattern IF EXISTS (SELECT * FROM sys.objects … ) DROP TRIGGER (from SQL Server)
• Converting patterns IF EXISTS (SELECT * FROM sysobjects … ) DROP … (from SQL Server)
• Converting SQL*Plus EXECUTE to CALL statement with named parameters (Oracle to PostgreSQL)
• Parsing ALTER TABLE ADD column for adding multiple columns in single statement (from Oracle)
• Parsing FOREIGN KEY name (columns) syntax (from DB2 z/OS)
• Allowing to define empty prefix for -tsql_parameters_prefix option by specifying ”” or '' value
• Commenting COLLATE name clause for expressions (from SQL Server)
• Bug fix - Not adding FROM dual when converting SELECT @var=expr to v_var := expr (SQL Server, Sybase to Oracle)
• Bug fix - Removing constraint name for inline named NOT NULL constraints in CREATE TABLE (Oracle to MySQL)
• Bug fix - Converting DECLARE @tab AS TABLE to CREATE GLOBAL TEMPORARY TABLE (SQL Server to Oracle)
• Bug fix - Replace comma (,) with semicolon (;) when converting SELECT @var = exp, @var2 = exp2, … to var := exp; @var2 = exp2 (SQL Server, Sybase to Oracle, PostgreSQL)
• Bug fix - Parsing CROSS APPLY join when AS keyword is not specified before the alias (from any database supporting CROSS APPLY)
• Bug fix - DATEADD(dd, num, exp) conversion - replace DD with DAY in interval expression (SQL Server, Sybase to Oracle)
• Bug fix - Parsing ELSE clause when converting IF @@ERROR <> 0 to EXCEPTION block (SQL Server to Oracle)
• Bug fix - Removing DEALLOCATE cursor statement if semicolon (;) follows CLOSE cursor (from SQL Server)
• Bug fix - Parsing WITH (NOLOCK) for multi-table FROM without table aliases (from SQL Server)
• Bug fix - Converting #tmp.column identifiers referencing temporary tables (from SQL Server, Sybase)
• Bug fix - Converting enclosed table alias [alias] (from SQL Server, Sybase)
• Bug fix - Adding semicolon (;) when converting END CATCH to END; (SQL Server to SingleStore)
• Bug fix - Converting enclosed identifiers for temporary tables [#tmp] (from SQL Server)
• Bug fix - Parsing SELECT * INTO table FROM table2 for non-temporary tables (from SQL Server, Sybase)

• -ident option to transform the identifiers using the specified template
• Converting INSTR function with 4 parameters to CHARINDEX if the 4th parameter has value of 1 (i.e. to find the first occurrence) (Oracle to SQL Server)
• Outer join syntax (+) conversion for more than 2 tables (from Oracle)
• Data type mapping (sqlines_dtmap.txt) for various combinations of length and scale for numeric data types
• Reporting syntax errors with code snippets in sqlines_report.html file when -a assessment option is specified
• Adding OR REPLACE when converting CREATE PROCEDURE statement (to SingleStore)
• Removing IN, commenting OUT parameter specifiers for stored procedures (MySQL to SingleStore)
• Removing LANGUAGE SQL, NOT DETERMINISTIC, READS SQL DATA and COMMENT clauses when converting stored procedures and user-defined functions (from MySQL)
• Moving declarations before BEGIN-END block (MySQL to SingleStore)
• Converting USE db_name to SET SCHEMA name (SQL Server to DB2)
• Removing GO statement from CREATE PROCEDURE (SQL Server to DB2)
• Converting IDENTITY property to GENERATED ALWAYS AS IDENTITY (SQL Server, Sybase to DB2)
• Removing ASC, DESC column sorting from PRIMARY KEY constraints (SQL Server to DB2)
• Parsing SELECT … FROM … WHERE … INTO variables clause (INTO at the end of statement) (from MySQL)
• Converting SELECT statements returning result sets to ECHO SELECT (SQL Server, MySQL to SingleStore)

• -remd option to remove database name in 3-part identifiers (from SQL Server, Sybase)
• Commenting foreign key constraints in CREATE TABLE (to SingleStore)
• Converting _utf8mb4'string' string literals to _utf8'string' (MySQL to SingleStore)
• COMMENT ON TABLE conversion (to SQL Server)
• Bug fix: INSTR to dbo.INSTR4 UDF conversion (Oracle to SQL Server)
• Bug fix: FOR i IN k..m range loop (from Oracle)
• Bug fix: -smap schema mapping option
• Bug fix: ALTER TABLE name ADD col type - removing GO (from SQL Server)
• Bitwise & (AND) operator to BITAND function (SQL Server to Oracle)
• Bug fix: DROP FUNCTION statement parser
• IF EXISTS (SELECT * FROM sys.objects WHERE type = 'FN' …) DROP FUNCTION pattern recognition (from SQL Server)
• Bug fix: NVARCHAR(MAX) to NCLOB conversion (SQL Server to Oracle)
• Bug fix: Removing size for NVARCHAR(n) in parameters for procedures and functions (to Oracle)
• Bug fix: Parser CREATE FUNCTION … RETURNS @tab TABLE (cols) column definitions (from SQL Server)
• Removing WITH ENCRYPTION, SCHEMABINDING options in CREATE FUNCTION (from SQL Server)
• Bug fix: Parser rules fix - OPEN keyword cannot be an column alias (from SQL Server)
• Removing OPTION (MAXRECURSION num) query hint (from SQL Server)
• Parsing ENUM data type (from MySQL)
• Parsing SET data type (from MySQL)
• Parsing FULLTEXT index in CREATE TABLE (from MySQL)