Sybase ASE to MariaDB Migration

SQLines open source tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Sybase Adaptive Server Enterprise (Sybase ASE) to MariaDB.

  • SQLines Data - Schema, data migration and validation tool
  • SQLines SQL Converter - SQL scripts conversion tool

Databases:

  • Sybase Adaptive Server Enterprise 16.x, 15.x, 12.x and 11.x
  • MariaDB 10.x and 5.x

Migration Reference

SQL Language Elements

Converting SQL language elements:

Sybase ASE MariaDB
1 string + string2 + ... String concatenation, NULL is treated as '' CONCAT(string, string2, ...) Result is NULL
if any string is NULL

Data Types

Converting data types:

Sybase ASE MariaDB
1 BIGINT 64-bit integer BIGINT
2 DATE Date (year, month and day) DATE
3 DATETIME Date and time with 1/300 seconds fraction DATETIME(3)
4 SMALLDATETIME Date and time with accuracy to minute DATETIME

Built-in SQL Functions

String functions:

Sybase ASE MariaDB
1 ASCII(string) Get ASCII value of left-most character ASCII(string)
2 CHAR(code) Get character from its ASCII code CHAR(code)
3 CHAR_LENGTH(string) String length in characters CHAR_LENGTH(string)
4 CHARINDEX(substr, string [,pos]) Get position of substr in string LOCATE(substr, string [,pos])
5 CONVERT(VARCHAR(len), string) Get a substring of string CAST(string AS CHAR(len))
6 LEN(string) String length in characters CHAR_LENGTH(string)
7 SUBSTRING(string, start, len) Get a substring of string SUBSTRING(string, start, len)

Conversion functions:

Sybase ASE MariaDB
1 BIGINTTOHEX(num) Convert BIGINT to 16-byte 0-padded
hex string (without 0x prefix)
LPAD(HEX(num), 16, '0')
2 BINTOSTR(exp) Convert a binary value to hex string HEX(exp)
3 CONVERT(CHAR, datetime, style) Convert datetime to string DATE_FORMAT(datetime, format)

CASE and NULL functions:

Sybase ASE MariaDB
1 COALESCE(exp, exp2, …) Get first non-NULL expression COALESCE(exp, exp2, …)

Math functions:

Sybase ASE MariaDB
1 ABS(num) Get the absolute value ABS(num)
2 ACOS(num) Get the arc cosine ACOS(num)
3 ASIN(num) Get the arc sine ASIN(num)
4 ATAN(num) Get the arc tangent ATAN(num)
5 ATN2(num) Get the arc tangent of 2 values ATAN2(num)
6 CEILING(num) Get the smallest INT greater than or equal to num CEILING(num)

System functions:

Sybase ASE MariaDB
1 ASEHOSTNAME() Get the server host name @@HOSTNAME

CREATE TABLE Statement

CREATE TABLE statement keywords and clauses:

Sybase ASE MariaDB
1 IDENTITY Identity column AUTO_INCREMENT Integer column and primary key is required in CREATE TABLE
2 ENCRYPT Encrypted column Keyword removed

UPDATE Statement

UPDATE statement keywords and clauses:

Sybase ASE MariaDB
1 UPDATE t1 SET … FROM t1, t2, … WHERE … UPDATE FROM syntax UPDATE t1, t2, … SET … WHERE …

CREATE PROCEDURE Statement

Converting Transact-SQL stored procedures:

Sybase ASE MariaDB
1 CREATE PROCEDURE name CREATE PROCEDURE name
2 @param datatype [= default] [OUTPUT] IN | OUT | INOUT p_param datatype no defaults
3 Optional () for procedure parameters () required
4 AS before the procedure body Removed
5 BEGIN END is optional for the procedure body BEGIN END required
6 Optional statement delimiter ; is added after each statement
7 GO Specified by DELIMITER command

For further information, see Conversion of Transact-SQL Statements.

Transact-SQL Statements

Variable declaration and assignment:

Sybase ASE MariaDB
1 DECLARE in any order, can be placed
before/after DML/DDL statements
Must be before DML/DDL, cursors must be declared
after variables, handlers after cursors
2 DECLARE @var type DECLARE v_var type
3 DECLARE @v1 type1, @v2 type2, … DECLARE v_v1 type1; DECLARE v_v2 type2, DECLARE …
4 DECLARE cur CURSOR FOR select_stmt DECLARE cur CURSOR FOR select_stmt
5 SET @var = expression SET v_var = expression
6 SELECT @var = expression SET v_var = expression

Flow-of-control statements:

Sybase ASE MariaDB
1 IF condition BEGIN … END IF statement IF condition THEN … END IF;
2 IF … ELSE IF … IF ELSE IF statement IF … ELSEIF …
3 WHILE condition BEGIN stmts END Conditional loop WHILE condition DO stmts END WHILE;
4 RETURN RETURN from a stored procedure LEAVE sp_lbl

Cursor operations:

Sybase ASE MariaDB
1 @@sqlstatus = 0 Fetch was successful not_found = 0 with condition handler for NOT FOUND
@@sqlstatus = 2 No more data in the result set not_found = 1

Executing stored procedures:

Sybase ASE MariaDB
1 EXEC sp_name @par1, @par2 [OUTPUT], … Executing procedure with parameters CALL sp_name(par1, par2, …)

Temporary tables:

Sybase ASE MariaDB
1 CREATE TABLE tempdb..name Create a temporary table CREATE TEMPORARY TABLE name

Transaction statements:

Sybase ASE MariaDB
1 BEGIN TRANSACTION Begin a transaction START TRANSACTION
2 COMMIT TRANSACTION | TRAN Commit transaction COMMIT

Other issues:

Sybase ASE MariaDB
1 BEGIN … END Code block BEGIN … END; semicolon (;) is required