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 DATALENGTH(string) String length in bytes LENGTH(string)
7 LEFT(string, num) Return num leftmost characters LEFT(string, num)
8 LEN(string) String length in characters CHAR_LENGTH(string)
9 LOWER(string) Convert string to lowercase LOWER(string)
10 LTRIM(string) Remove the leading blanks LTRIM(string)
11 PATINDEX(pattern, string) Get position of pattern in string UDF required
12 REPLICATE(string, num) Replicate string num times REPEAT(string, num)
13 REVERSE(string) Reverse string REVERSE(string)
14 RIGHT(string, num) Return num rightmost characters RIGHT(string, num)
15 RTRIM(string) Remove the trailing blanks RTRIM(string)
16 SPACE(num) Return string of num spaces SPACE(num)
17 STR_REPLACE(string, from, to) Replace substring with specified value REPLACE(string, from, to)
18 STUFF(str, start, len, val) Replace substring with val INSERT(str, start, len, val)
19 SUBSTRING(string, start, len) Get a substring of string SUBSTRING(string, start, len)
20 UPPER(string) Convert string to uppercase UPPER(string)

Datetime functions:

Sybase ASE MariaDB
1 CURRENT_BIGDATETIME() Get the current datetime (microseconds) NOW(6)
2 CURRENT_BIGTIME() Get the current time (microseconds) CURTIME(6)
3 CURRENT_DATE() Get the current date (year, month, day) CURRENT_DATE()
4 CURRENT_TIME() Get the current time (milliseconds) CURRENT_TIME(3)
5 DATEADD(unit, num, datetime) Add an interval to datetime TIMESTAMPADD(unit, num, datetime)
6 DATEDIFF(unit, dt1, dt2) Difference between 2 datetimes (dt2 - dt1) TIMESTAMPDIFF(unit, dt1, dt2)
7 DATENAME(unit, datetime) Extract unit from datetime YEAR(), MONTH(), DAY() etc.
8 DATEPART(unit, datetime) Extract unit from datetime YEAR(), MONTH(), DAY() etc.
9 DAY(datetime) Extract day from datetime DAY(datetime)
10 GETDATE() Get the current datetime (milliseconds) NOW(3)
11 GETUTCDATE() Get the current UTC datetime (milliseconds) UTC_TIMESTAMP(3)
12 MONTH(datetime) Extract month from datetime MONTH(datetime)
13 YEAR(datetime) Extract year from datetime YEAR(datetime)

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)
4 CONVERT(DATETIME, string, style) Convert string to DATETIME STR_TO_DATE(string, format)
5 HEXTOBIGINT(string) Convert hex string to BIGINT CAST(CONV(string,16,10) AS UNSIGNED)
6 HEXTOINT(string) Convert hex string to INT CAST(CONV(string,16,10) AS UNSIGNED)
7 INTTOHEX(num) Convert INT to 8-byte 0-padded
hex string (without 0x prefix)
LPAD(HEX(num), 8, '0')

CASE and NULL functions:

Sybase ASE MariaDB
1 COALESCE(exp, exp2, …) Get first non-NULL expression COALESCE(exp, exp2, …)
2 ISNULL(exp, exp2) Return exp2 if exp is NULL IFNULL(exp, exp2)
3 NULLIF(exp, exp2) Return NULL if exp=exp2, otherwise return exp NULLIF(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)
7 COS(num) Get the cosine COS(num)
8 COT(num) Get the cotangent COT(num)
9 EXP(num) Get e raised to the power of num EXP(num)
10 FLOOR(num) Get the largest INT less than or equal to num FLOOR(num)
11 LOG(num) Get the natural logarithm of num LOG(num)
12 LOG10(num) Get the base 10 logarithm of num LOG10(num)
13 PI() Get the pi constant PI()
14 POWER(num, power) Raise num to power POWER(num, power)
15 RADIANS(degree) Convert degree to radians RADIANS(degree)
16 RAND([seed]) Get random float within 0 and 1.0 RAND([seed])
17 ROUND(num [,dec]) Round num to dec digits ROUND(num [,dec])
18 SIGN(num) Return -1 for negative and 1 for positive num SIGN(num)
19 SIN(num) Get the sine SIN(num)
20 SQUARE(num) Get the square of num POWER(num, 2)
21 SQRT(num) Get the square root of num SQRT(num)
22 TAN(num) Get the tangent TAN(num)

System functions:

Sybase ASE MariaDB
1 ASEHOSTNAME() Get the server host name @@HOSTNAME
2 DB_NAME() Get the database name DATABASE()
3 HASH(expr, 'MD5' | 'SHA1') Hash the expr value MD5(expr) SHA1(expr)
4 NEWID() Generate a GUID value UUID()
5 SUSER_NAME() Get the user name CURRENT_USER()
6 USER | USER_NAME() USER()

Other functions:

Sybase ASE MariaDB
1 SOUNDEX(string) Get four-character soundex code for string SOUNDEX(string)

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