Release Notes - SQLines SQL Converter

Changes in SQLines SQL Converter tool.

Version 3.3.77

  • 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)

Version 3.3.73

  • 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)

Version 3.3.71

  • -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)

Version 3.3.70

  • -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)