Release Notes - SQLines Data

Changes in SQLines Data tool.

• Version 3.7.7
• Version 3.7.3
• Version 3.7.1
• Version 3.7.0
• Previous Releases

In progress. Please contact us at support@sqlines.com for updates, improvements and new features.

• Renaming columns named ctid to ctid_ to prevent “ERROR: column name “ctid” conflicts with a system column name” (to PostgreSQL)
• Added -mariadb_load_data_duplicates and -mysql_load_data_duplicates options handle duplicate rows (based on PRIMARY KEY or UNIQUE constraints)
• Enclosing constraint names containing special characters with double quotes (to Oracle, PostgreSQL)
• Fixing the transfer of large negative SMALLINT data type values (from IBM DB2, Sybase ASE)
• Mapping the XML data type to the XML data type (was TEXT) (IBM DB2, SQL Server to PostgreSQL)
• Skipping system-generated indexes automatically created on XML columns (pureXML feature) with INDEXTYPE XPTH (XML path index) and XRGN (XML region index) in SYSCAT.INDEXES (from IBM DB2)
• Fixing the transfer of large XML columns (from IBM DB2)
• Added the -pg_set_session_replication_role option to allow running SET session_replication_role = 'replica' to disable foreign key checks for each transfer session when loading into an existing schema with referential constraints (to PostgreSQL)
• Added the -pg_truncate=CASCADE option to generate TRUNCATE tab CASCADE statements when running a truncate operation for tables. This helps prevent the error “Cannot truncate a table referenced in a foreign key constraint” (to PostgreSQL)
• Added the -load_existing_columns_order=target to load data into existing tables using the list and order defined by the target table columns
• Transferring constraints from IBM DB2 for z/OS
• Extracting ON DELETE clause for foreign keys (from Oracle)
• Read/write optimization for data transfer sessions

Released on May 28, 2025.

• Enabling Sybase SQL Anywhere connector (-sd=asa) on Linux
• Transferring data between PostgreSQL databases
• Transferring TIMESTAMP WITH LOCAL TIME ZONE data type data (from Oracle)
• Transferring HIERARCHYID data type data (SQL Server to MariaDB, MySQL)
• Improving LOB transfer performance (from Oracle, SQL Server)
• Converting DEFAULT NULL NOT NULL to NOT NULL column definition (Oracle to MySQL, MariaDB)

Released on April 17, 2025.

• Transferring CHECK constraints (MySQL to MariaDB)
• When converting BIT data type to BOOLEAN, change the default values 0 and 1 to FALSE and TRUE to resolve “ERROR: column ”<col>” is of type boolean but default expression is of type integer (SQL Server, Sybase ASE, Sybase ASA to PostgreSQL)
• Resolving duplicate index names (Sybase ASA to PostgreSQL)
• Resolving CHAR/VARCHAR data transfer issues for UTF8 client character set (from Sybase ASE)
• Transferring BOOLEAN data type data (from IBM DB2)
• Converting a foreign key column's data type to BIGINT if it references a primary key column defined as NUMERIC with AUTOINCREMENT to prevent ERROR: foreign key constraint ”<name>” cannot be implemented, DETAIL: Key columns ”<col>” and ”<col>” are of incompatible types: numeric and bigint. (Sybase ASA to PostgreSQL)
• Transferring datetime values with fractional seconds more than 6 digits, and resolving “ORA-01830: date format picture ends before converting entire input string” error (to Oracle)
• Fixing encoding issues when transferring BLOB data (IBM DB2 to PostgreSQL)
• Converting the UNIQUEIDENTIFIER data type to the UUID data type (SQL Server, Sybase ASA to PostgreSQL)
• Converting BIT data type to TINYINT data type by default (was CHAR(1) data type) (SQL Server to MySQL)
• Adding the -sql_conn_string option to specify additional connection string options i.e. Encrypt=Yes;TrustServerCertificate=Yes; (SQL Server)
• Resolving transfer issues for VARCHAR(MAX) and VARBINARY(MAX) data larger than 1 MB (from SQL Server)

Released on March 01, 2025.

• Transferring JSON data type data (MySQL to PostgreSQL)
• Converting FLOAT and DOUBLE data type defined on AUTO_INCREMENT columns to BIGINT (from MySQL)
• Validating LOB data when -batch_max_rows > 1 (Oracle to MySQL)
• Not transferring disabled indexes (ALTER INDEX idx ON tab DISABLE) (from SQL Server)
• Using 65001 codepage (UTF8) by default when importing data using the BCP utility (to SQL Server)
• Transferring CLOB data in UTF-8 encoding (Oracle to SQL Server)
• Extracting column defaults (from SQL Server)
• Enclosing column default values in parentheses for TEXT and BLOB columns preventing “ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column can't have a default value” (to MySQL)
• Setting the correct length in characters for VARCHAR columns for databases in UTF-8 character set (MySQL to MySQL)
• Transferring foreign keys (Sybase SQL Anywhere to PostgreSQL)
• Converting NUMERIC(10,0) to BIGINT for autoincrement columns (Sybase SQL Anywhere to PostgreSQL, MariaDB, MySQL)
• Increasing internal buffer to read DOUBLE values (was 22, now 31 characters) (from MySQL)
• In data validation mode read constraints even if -constraints=false to enable primary and unique keys to sort data for comparison
• Adding -sybase_asa_charset option to specify the client character set (Sybase SQL Anywhere)
• Automatically resolving “ERROR: deadlock detected. DETAIL: Process waits for AccessExclusiveLock on object” errors by restarting rollbacked DDL statements (to PostgreSQL)
• Converting TINYINT (0-255) to TINYINT UNSIGNED (Sybase ASE to MariaDB, MySQL)
• Transferring REAL data type data (from Sybase ASE)

Released on January 11, 2025.

• Fixing data validation for NVARCHAR (Unicode UCS2) and VARCHAR (Unicode UTF-8) data types (SQL Server to MySQL, MariaDB)
• Fixing data type mapping option for VARCHAR, NVARCHAR and NCHAR data types (from SQL Server)
• Transferring IMAGE data type data (Sybase ASE to PostgreSQL, MySQL, MariaDB)
• Transferring IMAGE data type data (SQL Server to MySQL, MariaDB)
• Fixing the query to extract indexes from user schemas (from SQL Server)
• Transferring BIT data type values (MySQL to PostgreSQL)
• Applying the -pg_zero_char_replace option when validating data transfer to mask differences caused by this data transformation (to PostgreSQL)

Released on October 30, 2024.

• Extracting IDENTITY next values for tables (from Sybase ASE)
• Validating BIT values 0 and 1 with BOOLEAN values 'f' and 't' (Sybase ASE to PostgreSQL)
• Fixing validation of TEXT data (large objects) caused by different row batch size issues (Sybase ASE to PostgreSQL)
• Validating string representation of values like .0 and 0.0, .7 and 0.7 as equal for fixed point data types (DECIMAL i.e.)
• Transferring DEFAULT '' (empty string) column properties (from MySQL)
• Fixed global data type mapping configuration for VARCHAR data type (from Informix)
• Transferring data from BINARY_FLOAT data type (from Oracle)
• Extracting user-defined types in XML data representation by default (from Oracle)
• Connecting with user SYS automatically applying mode SYSDBA (Oracle)
• In Sybase ASE NULL values always go first when ORDER BY is used, so add NULLS FIRST clause when sorting data for row content validation with Oracle, PostgreSQL (Sybase ASE to Oracle, PostgreSQL)
• Distinguishing CHAR and VARCHAR types (from Sybase ASE)
• Resolving “ORA-12899: value too large for column” error (SQL Server to Oracle)
• Extracting IDENTITY next value and increment for tables (from SQL Server)
• Fixing data transfer and validation for TEXT data exceeding 1 MB (PostgreSQL)
• Supporting transfer of INTERVAL YEAR TO YEAR data type (from Informix)

Releasd on September 17, 2024.

• Added -sql_unicode_char option to extract CHAR and VARCHAR data in Unicode (from SQL Server)
• Fixing transfer size calculation for LOBs (Oracle to Oracle)
• Added support for UTF-16 surrogate pairs (complementary characters) transfer to UTF-8 databases (to PostgreSQL, MySQL)
• Resolving issues with INT, SMALLINT, and TIMESTAMP extract to Stdout (standard output)
• Supporting any ODBC data source as target database
• Transferring GEOGRAPHY data (from SQL Server)
• Supporting wildcards *, *.*, schema.* or *.table for object to denote all objects in a single mapping in -cmapf column mapping option
• Automatically resolving “Deadlock found when trying to get lock; try restarting transaction” errors by restarting rollbacked statements (to MySQL and MariaDB)
• Validating string representation of values like 0 and 0.00 as equal for fixed point data types (DECIMAL i.e.)
• Added -deadlock_restart_attempts option to specify the number of restart attempts for deadlocked DDL statements (to SQL Server, MySQL and MariaDB)
• Transferring LONGTEXT data (from MySQL)
• Transferring non-unique indexes (from Sybase ASE)

Released on July 04, 2024.

• Supporting transferring up to 32 columns per index (was 8) (from Sybase ASE)
• Added PostgreSQL as source to SQLines Data UI
• Fixing length indicator issues with DB2 11 64-bit CLI driver for array inserts (Oracle to IBM DB2)
• Fixing transfer of NUMBER(n,0) data where n < 10 (Oracle to IBM DB2)
• Enabling validation of data content in rows (IBM DB2, ODBC)
• Enclosing identifiers containing special characters with double quotes (to IBM DB2)
• Supporting BLOB data type in CREATE TABLE (to IBM DB2)
• Fixing mapping of the schema name for referenced table in foreign keys (from Oracle)
• Transferring DATE data (Oracle to IBM DB2)
• -char_to_varchar_length option to define the start length for CHAR(n) columns to be converted to VARCHAR(n). It helps dealing with SQL Server ”Exceeds the maximum allowable table row size of 8060 bytes” errors
• Transferring tables exceeding 1024 columns using SPARSE columns (to SQL Server)
• Automatically detect PostgreSQL client libraries location if pgAdmin 4 is installed (Windows)
• In SQLines Data UI show Failed status if there is an error during Open Cursor
• Handling “Invalid Descriptor Index” errors for NTEXT columns (from SQL Server)
• Handling special characters (blanks i.e.) in table names for ALTER TABLE statements (from SQL Server)
• -pg_zero_char_replace option to replace characters with zero codes in character strings (to PostgreSQL)

Released on June 12, 2024.

• Preserving case-sensitive identifiers (enclosed with double quotes) (Oracle to PostgreSQL)
• Validating negative decimal numbers (like -.30 and -0.30)
• Fixing applying -batch_max_rows option (Sybase ASE)
• Excluding columns generated for function-based indexes to prevent errors “Name 'sybfi2_1' is reserved for internal use by Adaptive Server. Please use a different name” (from Sybase ASE)
• Fixing syntax error reading list of tables from QSYS2.TABLES (from DB2 iSeries, DB2/400)
• Enabling ODBC (unixODBC) data source on Linux
• Adding IMMEDIATE clause for TRUNCATE TABLE statement (to IBM DB2)
• Supporting CLOB data type in CREATE TABLE (to IBM DB2)
• Extracting more than 32KB from TEXT data type (from Sybase ASE)
• Writing CLOB and BLOB data (Oracle to IBM DB2)
• Hide misleading “ORA-01403: no data found” error message when reading the database schema (from Oracle)
• Transferring data from RAW data type (Oracle to IBM DB2)

Released on February 29, 2024.

• Enclosing columns with [] during data validation process (to SQL Server)
• Refactoring data validation (Sybase SQL Anywhere)
• Fixing applying -batch_max_rows option (Sybase SQL Anywhere)
• Converting DECIMAL(65,s) to DECIMAL(38,s) due to data type range limitations (MySQL to SQL Server)
• Transferring indexes (from MySQL)
• Transferring foreign keys (from MySQL)
• Reading IBM DB2 iSeries catalog view QSYS2.TABLES to get the table list using ODBC driver
• Adding ODBC data source to SQLines Data UI
• Transferring BIT(n) data type data (MySQL to SQL Server)
• Implementing case-insensitive table name mapping (-tmapf option)
• Using hex encoding internally when transferring binary data using COPY command (IBM DB2, Informix, Sybase ASA to PostgreSQL)
• Fixing transferring LONG BINARY data larger 3 MB (from Sybase SQL Anywhere)
• Transferring column DEFAULT values and AUTO_INCREMENT properties including next value (from MySQL)
• Fixing global data type mapping sqlines_dtmap.txt for DATETIME and INT data types (from MySQL)
• Added -identity_start option to specify the start value for identity columns (by default, it is taken as the next value for the corresponding source table)
• Applying column mapping and escaping for ALTER TABLE … DEFAULT statements (to Oracle, SQL Server, PostgreSQL)
• Validating BIT values 0 and 1 with BOOLEAN values 'f' and 't' (Sybase ASA to PostgreSQL)
• Ordering rows using numeric, datetime and string columns for tables without unique keys or indexes (from Sybase ASA)
• Fixing data validation for BIGINT data type (PostgreSQL)
• Added the option -datetime_year_lte_to_null to convert DATETIME values to NULL if their year is less or equal to the specified value (from MySQL)
• Transferring data of IMAGE data type (Sybase ASE to Oracle)
• Fixing data validation for NUMERIC and DECIMAL datatypes comparing string representation like .50 and 0.5 (from Sybase ASA)
• If table transfer failed set SET IDENTITY_INSERT table1 OFF explicitly to prevent errors: “IDENTITY_INSERT is already ON for table 'table1'. Cannot perform SET operation for table 'table2'” for tables with identity columns (to SQL Server)
• Moving VARCHAR(max), NVARCHAR(max), IMAGE and TEXT columns to the end of SELECT list to prevent “Invalid Descriptor Index” errors (from SQL Server)

Released on January 09, 2024.

• Transferring data from INTERVAL HOUR TO FRACTION(n) type to INTERVAL HOUR TO SECOND(n) (Informix to PostgreSQL)
• Migrating primary key and unique constraints (from MySQL)
• VARCHAR and NUMERIC data transfer (invalid datatype in OCI call error) issue (PostgreSQL to Oracle)
• Validating tables with primary key or unique constraints (MySQL to PostgreSQL)
• Reading TEXT values more than 4,000 bytes (from PostgreSQL)
• Validation query for tables with reserved words as columns (PostgreSQL)
• Applying -batch_max_rows option for Informix API (Informix)
• Fixing closing cursor and freeing resources when -batch_max_rows=1 (PostgreSQL)
• Added -sybase_ident_quotes option to specify how to enclose column identifiers in Sybase Adaptive Server Enterprise (Sybase ASE)
• Handling and reporting error ”[SQL Anywhere]Database server connection limit exceeded” (10 connections for Personal Server) (from Sybase SQL Anywhere)
• Handling multiple AUTOINCREMENT columns per table (leaving the first one) (Sybase SQL Anywhere to SQL Server)
• Fixing transfer of tables with column names containing blanks (Sybase SQL Anywhere to SQL Server)
• Updated version of built-in SQL converter (used to convert DEFAULT expressions i.e.)
• Fixing ODBC connection issues
• When dropping existing foreign key constraints enclose the constraint name with [] (to SQL Server)
• Updated the list of reserved key words (to SQL Server)
• Skipping creating defaults and constraints for columns of global temporary tables (from Sybase SQL Anywhere)
• Enclose index names with [] (to SQL Server)
• Enclose index names with `` (to MySQL, MariaDB)
• Handling errors “Transaction (Process ID num) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.” when creating constraints (to SQL Server)
• Handling errors “Introducing FOREIGN KEY constraint 'fk_name' on table 'table_name' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.” (to SQL Server)
• Fixing data validation including DATE NOT NULL and DATETIME YEAR TO FRACTION NOT NULL to ORDER BY column list if there are no primary key or unique constraints (from Informix)

Released on September 12, 2023.

• -column_name_quotes and -object_name_quotes to specify quote characters for identifiers (columns, schemas, tables etc.) in generated SQL scripts
• Remove disabled Terminate button from UI as this functionality is currently not supported
• Migrating column comments from Sybase SQL Anywhere
• Extended trace information when reading BLOB values from Informix
• Bug fix - Extracting AUTOINCREMENT columns from Sybase SQL Anywhere if max_identity is 0 in syscolumn catalog view
• Bug fix - Transferring DATETIME data on a Linux client (from SQL Server)

Released on June 27, 2023.

• Bug fix - Enclosing column names containing special characters (, -, (, ) and so on) with double quotes (to Oracle)
• Bug fix - Enclosing column names starting with a digit with double quotes (to Oracle)
• Bug fix - Fixing error “another command is already in progress” when reading data from PostgreSQL after fetching table with 0 rows or when a fetch error happens (releasing query result resources)
• Bug fix - Logging PostgreSQL libpq library use and load errors
• Bug fix - Transferring Oracle DATE (was mapped to DOUBLE)
• Bug fix - Oracle NLS_LANG environment variable setting
• Bug fix - IBM DB2 DB2CODEPAGE environment variable setting

Released on May 28, 2023.

• Migrating DATETIMEOFFSET data type (from SQL Server)
• Using DATETIME YEAR TO FRACTION and INT8 columns for sorting data for validation if there are no primary key or unique columns (from Informix)
• Allow specifying the target size for global data type mapping in sqlines_dtmap.txt
• Migrating TEXT data type to VARCHAR(MAX) (MySQL to SQL Server)
• Supporting ODBC Driver for SQL Server on Windows as SQL Server Native Client is deprecated
• Adding -sql_lib and -sql_driver options to specify SQL Server client libraries and driver for Windows and Linux
• Reducing memory consumption when validating data for PostgreSQL
• Bug fix - Informix driver for Linux also returns fractional part of DATETIME YEAR TO FRACTION(n) as zero when binding as SQL_TIMESTAMP_STRUCT (from Informix)
• Bug fix - DATETIMEOFFSET data type mapping in sqlines_dtmap.txt file (from SQL Server)
• Bug fix - Truncating table.dump.txt on restart on Linux
• Bug fix - Show error message if COPY FROM STDIN initialization failed (to PostgreSQL)
• Bug fix - Data type missed in CREATE TABLE for a column with NUMBER data type defined as a computed column (GENERATED AS expression) (from Oracle)

Released on March 12, 2023.

• Bug fix - Migrating fractional part of DATETIME YEAR TO FRACTION(n) (Informix to PostgreSQL)
• Bug fix - Creating dump data file (table.dump.txt) and validation difference file (table_diff.txt) in the directory defined by -out option (they were always created in the current directory)
• Bug fix - Fixing crash when extracting data from Oracle rule types (DBMS_RULE i.e. SYS.RE$NV_NODE)
• Bug fix - Dumping DATETIME value to table_diff.txt file (from Informix)
• Bug fix - DATETIME and TIMESTAMP data types validation (Informix to PostgreSQL)
• Using one-based index for rows when dumping row differences to table_diff.txt file during data validation

Released on February 07, 2023.

• Extracting Oracle SDO_GEOMETRY spatial data in WKT Spatial format (Well-Known Text)
• Added -dlog option to specify name and location for executed DDL statements (default is sqldata_ddl.sql)
• Bug fix - Skip creating constraints, sequences, defaults and indexes in Truncate mode (-cmd=truncate)
• SQLData UI - Initializing connection windows with last active connection settings (server name, user name, database etc.)
• Extending sqldata.trc metrics for rows validation mode
• Bug fix - Fixing SQLData UI crash when performing data validation on tables with large number of columns (validation SELECT query more than 2048 bytes)
• Bug fix - Transferring VARCHAR data (MariaDB/MySQL to MariaDB/MySQL)