Sybase SQL Anywhere to MariaDB Migration

SQLines provides tools and services to help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from SAP SQL Anywhere, Sybase SQL Anywhere, Sybase Adaptive Server Anywhere (Sybase ASA) to MariaDB.

We also help convert embedded SQL statements in C/C++ (ODBC), C#, Java, PowerBuilder, VB/VB.NET, ASP/ASP.NET, Perl, PHP, Python, Linux shell and other applications.

  • SQLines Data - Data transfer, schema migration and validation tool
  • SQLines SQL Converter - SQL scripts assessment and conversion tool

Databases:

  • Sybase SQL Anywhere 17, 16, 12, 11, 10, 9 and 5
  • MariaDB 10.x and 5.x

Migration Reference

Built-in SQL Functions

Converting built-in SQL functions from Sybase SQL Anywhere to MariaDB.

String functions:

Sybase ASA MariaDB
1 CONVERT(CHAR[(len)], exp) Convert to string CONVERT(exp, CHAR[(len)]) Different parameter
order
CONVERT(VARCHAR[(len)], exp) VARCHAR cannot be used
2 LOCATE(str, substr [,pos]) Get position of substring,
pos can be negative
LOCATE(substr, str [,pos]) Different parameter
order, no negative pos
INSTR(str, substr) The same parameter order
3 REPEAT(string, n) Repeat string n times REPEAT(string, n)
4 REPLICATE(string, n) Repeat string n times REPEAT(string, n)
5 UCASE(string) Uppercase string UCASE(string)

Datetime functions:

Sybase ASA MariaDB
1 DATEADD(year, n, exp) Add n years TIMESTAMPADD(year, n, exp)
DATEADD(quarter, n, exp) Add n quarters TIMESTAMPADD(quarter, n, exp)
DATEADD(month, n, exp) Add n months TIMESTAMPADD(month, n, exp)
DATEADD(week, n, exp) Add n weeks TIMESTAMPADD(week, n, exp)
DATEADD(day, n, exp) Add n days TIMESTAMPADD(day, n, exp)
DATEADD(dayofyear, n, exp) Add n days TIMESTAMPADD(day, n, exp)
DATEADD(hour, n, exp) Add n hours TIMESTAMPADD(hour, n, exp)
DATEADD(minute, n, exp) Add n minutes TIMESTAMPADD(minute, n, exp)
DATEADD(second, n, exp) Add n seconds TIMESTAMPADD(second, n, exp)
DATEADD(millisecond, n, exp) Add n milliseconds TIMESTAMPADD(millisecond, n, exp)
DATEADD(microsecond, n, exp) Add n microseconds TIMESTAMPADD(microsecond, n, exp)
2 DATENAME(year | yy, exp) Year (1-9999) YEAR(exp)
DATENAME(Quarter | qq, exp) Quarter (1-4) QUARTER(exp)
DATENAME(Month | mm, exp) Month (January - December) MONTHNAME(exp)
DATENAME(Week | wk, exp) Week (1-54), begins on Sunday WEEK(exp)
DATENAME(Day | dd, exp) Day of month (1-31) DAY(exp)
DATENAME(Dayofyear | dy, exp) Day of year (1-366) DAYOFYEAR(exp)
DATENAME(Weekday | dw, exp) Day of week (Monday-Sunday) DAYNAME(exp)
DATENAME(Calyearofweek | cyr, exp) Year of week start YEAR(exp)
DATENAME(Calweekofyear | cwk, exp) Week (1-53) WEEK(exp)
DATENAME(Caldayofweek | cdw, exp) Day of week (1-7), Monday = 1 DAYOFWEEK(exp)
DATENAME(Hour | hh, exp) Hour (0-23) HOUR(exp)
DATENAME(Minute | mi, exp) Minute (0-59) MINUTE(exp)
DATENAME(Second | ss, exp) Second (0-59) SECOND(exp)
DATENAME(Millisecond | ms, exp) Millisecond (0-999) MICROSECOND(exp)/1000
DATENAME(Microsecond | mcs, exp) Microsecond (0-999999) MICROSECOND(exp)
DATENAME(TZOffset | tz, exp) Time zone
3 DAYS(dt1, dt2) Datetime difference in days TIMESTAMPDIFF(DAY, dt1, dt2)
4 MONTHS(dt1, dt2) Datetime difference in months TIMESTAMPDIFF(MONTH, dt1, dt2)
5 SECONDS(dt1, dt2) Datetime difference in seconds TIMESTAMPDIFF(SECOND, dt1, dt2)

CREATE FUNCTION Statement

Converting user-defined SQL functions from Sybase ASA to MariaDB:

Sybase SQL Anywhere MariaDB
1 CREATE FUNCTION name CREATE FUNCTION name
2 (IN param datatype(length) DEFAULT default, …) (param datatype(length), …) Default is not supported
3 RETURNS datatype(length) RETURNS datatype(length)

Transact-SQL Statements

Converting T-SQL statements from Sybase ASA to MariaDB:

Sybase SQL Anywhere MariaDB
1 DECLARE name EXCEPTION
FOR SQLSTATE [VALUE] val
User-defined exception DECLARE name CONDITION
FOR SQLSTATE [VALUE] val
SQLSTATE values are different

Exception block:

Sybase SQL Anywhere MariaDB
1 BEGIN
declarations
statements
EXCEPTION
WHEN condition THEN exception_stmts
END
BEGIN
declarations
DECLARE EXIT HANDLER
FOR condition BEGIN exception_stmts END
statements
END
EXIT HANDLER must be defined
after variable declarations

Error Codes

Converting error codes from Sybase ASA to MariaDB:

Sybase SQL Anywhere MariaDB
1 SQLSTATE '52003' Name of the column could not be found SQLSTATE '42S22' Unknown column '%s' in '%s'