Sybase ASE to MariaDB Migration

SQLines 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 SQL Converter - SQL Scripts Conversion and Assessment tool. Try Online
SQLines Data - Data Transfer, Schema Migration and Validation tool

Databases:

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

Migration Reference

SQL Language Elements

Converting SQL language elements:

	Sybase ASE		MariaDB
1	Single '' and double "" quotes	Identifiers and strings	Single '' and double "" quotes
2	string + string2 + ...	String concatenation, NULL is treated as ''	CONCAT(string, string2, ...)	Result is NULL if any string is NULL

Data Types

Numeric data types:

	Sybase ASE		MariaDB
1	BIGINT	64-bit integer	BIGINT
2	DECIMAL(p,s)	Fixed-point number	DECIMAL(p,s)
3	INT, INTEGER	32-bit integer	INT, INTEGER
4	NUMERIC(p,s)	Fixed-point number	NUMERIC(p,s)
5	SMALLINT	16-bit integer	SMALLINT
6	TINYINT	8-bit unsigned integer, 0 to 255	TINYINT UNSIGNED

Converting datetime data types:

	Sybase ASE		MariaDB
1	DATE	Date (year, month and day)	DATE
2	DATETIME	Date and time with 1/300 seconds fraction	DATETIME(3)
3	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 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()	Get the 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

Blocks:

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

Other Transact-SQL statements:

	Sybase ASE		MariaDB
1	PRINT 'text'	Print message	SELECT 'text' AS ''
			\! echo 'text';	In SQL script
			Commented inside a function

SQL Statements

Converting SQL statements:

	Sybase ASE		MariaDB
1	USE dbname	Change the database	USE dbname

System Procedures

Converting system procedures:

	Sybase ASE		MariaDB
1	sp_addgroup name	Create group	CREATE ROLE name
2	sp_adduser name, name_in_db, group_name	Create user	CREATE USER name IDENTIFIED BY pwd; GRANT group_name TO name

SQLines Data

SQLines SQL Converter

Migration to MariaDB

Migration to MySQL

Migration to PostgreSQL

Migration to SQL Server

Migration to Oracle

SQL in Applications