Skip to content

MSSQL to ALTIBASE Conversion Guide#

Overview#

This document explains the considerations and methods for migrating from Microsoft SQL Server to ALTIBASE.

It targets SQL Server 2016 and Altibase 7.1 or later.

Info

For errors and improvements related to this document, please contact the technical support portal or technical support center.

Info

This document is provided for informational purposes only and is subject to change without prior notice. It may contain errors, and Altibase makes no express or implied warranties of any kind, including but not limited to warranties of merchantability or fitness for a particular purpose.

The development, release, and timing of any features or functionality described for Altibase products in this document are at the sole discretion of Altibase.

Altibase may hold patents, trademarks, copyrights, or other intellectual property rights related to the contents of this document.

Considerations When Converting SQL Server Schema#

The schema in SQL Server is a container for objects (a logical namespace).

In Altibase, schemas and users are not separated, so conversion must be done by mapping to a specific user unit.

Item

SQL Server

Altibase

Feature Schemas and users are separated (since SQL Server 2005) One user can access multiple schemas Permissions can be granted at the schema level Schema = User Schemas cannot be created separately; objects must be created per USER
Sample CREATE SCHEMA Sales AUTHORIZATION John; CREATE TABLE Sales.Customers (...); CREATE USER Sales IDENTIFIED BY salespwd; CREATE TABLE Sales.Customers (...);

When migrating SQL Server schemas to Altibase, replace schemas with "Schema = User" as shown in the table below, and common schemas like dbo should be replaced by creating separate users.

Item

SQL Server

Altibase

Remarks

Schema Structure Independent from user user = Schema When converting, SQL Server schemas must be mapped to Altibase users
Default Schema dbo Not supported Altibase does not have dbo schema, so it should be replaced by creating a specific user
Common Object Management Uses dbo Create separate user and grant permissions dbo.TableName format needs to be mapped to a specific user schema and granted permissions
Permission Grant Unit Can be at schema level Only at object level Schema-level permissions must be broken down into object-level permissions
Naming Conflict Handling Resolved by schema separation Resolved by user name Be careful that SQL Server schema names do not conflict with Altibase user names during conversion

Object Conversion#

This section describes the considerations when converting SQL Server objects to ALTIBASE.

DATATYPE#

This section explains how each DATATYPE in SQL Server tables is converted when migrating to Altibase.

Category SQL Server Altibase SQL Server Maximum ALTIBASE Maximum Remark
Character Types CHAR CHAR 8000 bytes
CHAR(MAX) : 2GB
32000 bytes CHAR(MAX) is converted to CLOB in ALTIBASE.
VARCHAR VARCHAR 8000 bytes
VARCHAR(MAX) : Max 2GB
32000 bytes VARCHAR(MAX) is converted to CLOB in ALTIBASE.
NCHAR NCHAR 8000 bytes
NCHAR(MAX) : 2GB
Max length: 16000 (UTF16)
Max length: 10666 (UTF8)
NCHAR(MAX) is converted to CLOB in ALTIBASE.
NVARCHAR NVARCHAR 8000 bytes
NVARCHAR(MAX) : 2GB
Max length: 16000 (UTF16)
Max length: 10666 (UTF8)
NVARCHAR(MAX) is converted to CLOB in ALTIBASE.
BINARY BYTE 8000 bytes 32000 bytes
VARBINARY BLOB 8000 bytes 2GB VARBINARY(MAX) is converted to BLOB in ALTIBASE.
IMAGE -> VARBINARY(MAX) BLOB 2GB 2GB Since SQL Server 2005,
it is recommended to use VARBINARY(MAX) as a replacement.
TEXT, NTEXT -> VARCHAR(MAX), NVARCHAR(MAX) CLOB TEXT : 65536 bytes
NTEXT : 2GB
2GB Since SQL Server 2005,
it is recommended to use VARCHAR(MAX) or NVARCHAR(MAX) as replacements.
Numeric Types BIGINT BIGINT
NUMERIC NUMERIC
BIT BIT
SMALLINT SMALLINT
TINYINT SMALLINT TINYINT : 1 byte SMALLINT : 2 bytes
REAL REAL
INT INTEGER
MONEY DECIMAL(p, s)
SMALLMONEY DECIMAL(p, s)
Date Types DATE DATE Stores date only. Format: YYYY-MM-DD
DATETIMEOFFSET Not Supported Store the date, time, and time zone. Format: YYYY-MM-DD hh:mm:ss [+|-]hh:mm
In Altibase, this can be replaced with DATE if the time zone is excluded.
DATETIME2 DATE Store the date and time. Format: YYYY-MM-DD hh:mm:ss
Altibase stores up to microseconds (6 decimal places).
SMALLDATETIME DATE Stores date and time.
Format: YYYY-MM-DD hh:mm:ss
DATETIME DATE Stores date and time (backward compatibility).
Format: YYYY-MM-DD hh:mm:ss [.nnn]
TIME DATE Store time only. Format: hh:mm:ss
SQL Server stores up to 100 nanoseconds (7 decimal places).
Altibase stores up to microseconds (6 decimal places).

FUNCTION & EXPRESSION#

Category SQL Server Altibase Remarks
Aggregate AVG AVG
CHECKSUM_AGG Not Supported
COUNT COUNT
COUNT_BIG Replace with COUNT
GROUPING GROUPING A function that distinguishes aggregation levels when using ROLLUP, CUBE, or GROUPING SETS.
GROUPING_ID GROUPING_ID Returns a numeric value corresponding to the GROUPING bit vector associated with the row.
MAX MAX
MIN MIN
STDEV STDDEV
STDEVP Not Supported
SUM SUM
VAR VARIANCE
Ranking DENSE_RANK DENSE_RANK
NTILE NTILE
RANK RANK
ROW_NUMBER ROW_NUMBER
Conversion CAST, CONVERT CAST The CONVERT function in Altibase provides different functionality
PARSE CAST The CONVERT function can be used to change the character set
TRY_CAST CAST Returns an error if casting fails
TRY_CONVERT Not Supported
TRY_PARSE Not Supported
Date CURRENT_TIMESTAMP CURRENT_TIMESTAMP
DATENAME DATENAME
DATEPART DATEPART
DAY DATEPART
DATEADD DATEADD In Altibase, date strings must be formatted using the TO_DATE() function
DATEDIFF DATEDIFF
DATEFROMPARTS TO_DATE
DATETIME2FROMPARTS TO_DATE
DATETIMEFROMPARTS TO_DATE
DATETIMEOFFSETFROMPARTS TO_DATE
EOMONTH LAST_DAY
GETDATE SYSDATE Conversion Type: datetime
GETUTCDATE Replace with unix_date
ISDATE Not Supported
SMALLDATETIMEFROMPARTS TO_DATE
SWITCHOFFSET Replace with CONV_TIMEZONE
SYSDATETIME SYSDATE Conversion Type: datetime2SWITCHOFFSET
SYSDATETIMEOFFSET Not Supported
SYSUTCDATETIME Replace with unix_date
TIMEFROMPARTS TO_DATE
TODATETIMEOFFSET Not Supported
YEAR Replace with DATEPART or EXTRACT
Logical CHOOSE Not Supported
IIF Replace with case2
Mathematical ABS ABS
ACOS ACOS
ASIN ASIN
ATAN ATAN
ATN2 ATN2
CEIL(n) CEIL(n)
COS COS
COT Not Supported
DEGREES Not Supported
EXP EXP
FLOOR FLOOR
LOG LOG LOG (m, n)
LOG10 LOG(10,m)
PI Not Supported
POWER POWER
RADIANS Not Supported
RAND RAND SQL Server: Returns a pseudo-random float value between 0 and 1.
Altibase: Returns a pseudo-random integer value between 0 and 2,147,483,647.
ROUND ROUND
SIGN SIGN
SIN SIN
SQRT SQRT
SQUARE Not Supported
TAN TAN
String ASCII ASCII
CHAR CHAR
CHARINDEX INSTR, POSITION
CONCAT CONCAT
DIFFERENCE Not Supported
LEFT SUBSTR, SUBSTRING SUBSTR (expr, start [, length])
Specify a positive value for Start
LEN LENGTH
LOWER LOWER
LTRIM LTRIM In Altibase, LTRIM(expr1 [, expr2]) is used.
RTRIM RTRIM In Altibase, RTRIM(expr1 [, expr2]) is used.
PATINDEX INSTR, POSITION
QUOTENAME Not Supported
REPLACE REPLACE
REPLICATE REPLICATE
REVERSE REVERSE_STR
RIGHT SUBSTR, SUBSTRING SUBSTR (expr, start [, length])
Specify a negative value for Start
PATINDEX INSTR, POSITION
SOUNDEX Not Supported
SPACE LPAD, RPAD
STR TO_CHAR
STUFF STUFF
SUBSTRING SUBSTRING
UNICODE Not Supported
UPPER UPPER

OBJECT#

Category SQL Server Altibase Remark
CONSTRAINT Supported Supported
TRIGGER Supported Supported
Multi Key-Index Supported Supported
VIEW Supported Supported
UPDATABLE VIEW Supported Supported In Altibase, if the WITH READ ONLY option is not specified when creating a VIEW, an updatable view is created.
SEQUENCE Supported Supported
STORED FUNCTION/PROCEDURE Supported Supported
SYNONYM Supported Supported
Temporary Table Supported Supported In Altibase, temporary tables are created and used within a Volatile TableSpace.
USER Supported Supported
REPLICATION Supported Supported

CREATE TABLESPACE#

In SQL Server, databases are managed as data storage units, whereas in Altibase, tablespaces are managed.
In Altibase, tablespaces are classified based on storage type into memory tablespaces and disk tablespaces, by creator into system tablespaces and user-defined tablespaces, and by stored content into dictionary tablespaces, undo tablespaces, temporary tablespaces, and data tablespaces.

Therefore, when converting to Altibase, use CREATE MEMORY DATA TABLESPACE or CREATE DISK DATA TABLESPACE depending on the type of data storage.

The following explains guidelines for mapping various options in the CREATE TABLESPACE statement when converting a SQL Server DATABASE to an Altibase tablespace.

CREATE TABLESPACE user_data DATAFILE '/tmp/tbs.user' SIZE 10M AUTOEXTEND ON NEXT 128M;
SQL Server Altibase Remark
FILENAME FILENAME
SIZE SIZE default 100MB
MAXSIZE MAXSIZE
FILEGROWTH AUTOEXTEND ON NEXT
FILESTREAM Not Supported Delete when generating the statement in Altibase
DEFAULT_FULLTEXT_LANGUAGE Not Supported Delete when generating the statement in Altibase
DEFAULT_LANGUAGE Not Supported Delete when generating the statement in Altibase
NESTED_TRIGGERS Not Supported Delete when generating the statement in Altibase
TRANSFORM_NOISE_WORDS Not Supported Delete when generating the statement in Altibase
TWO_DIGIT_YEAR_CUTOFF Not Supported Delete when generating the statement in Altibase
DB_CHAINING Not Supported Delete when generating the statement in Altibase
TRUSTWORTHY Not Supported Delete when generating the statement in Altibase

CREATE TABLE#

When converting a SQL Server TABLE to Altibase, various options used in the CREATE TABLE statement must be appropriately modified.
Altibase does not provide OBJECT TABLE or XMLType TABLE.
Altibase provides memory TABLE, so if the characteristics of the table to be converted fit a memory TABLE, it should be created specifying a memory TABLESPACE.

If a memory TABLE is created, the options used in the SQL Server CREATE TABLE statement cannot be used.
Refer to Altibase SQL manuals for the syntax to create memory TABLEs.

If converting a SQL Server TABLE to a DISK TABLE, the various options set in CREATE TABLE should be converted to Altibase-compatible options as follows.

1. Column definition

SQL Server Altibase Remark
FILESTREAM Not Supported
COLLATE Not Supported
CONSTRAINT CONSTRAINT In Altibase, you cannot specify an index name when defining a PRIMARY KEY or UNIQUE constraint
IDENTITY Not Supported
ROWGUIDCOL Not Supported

2. data type

SQL Server Altibase Remark
Precision, scale Precision, scale
max Not Supported
CONTENT Not Supported
DOCUMENT Not Supported
xml_schema_collection Not Supported

3. column constraint

SQL Server Altibase Remark
PRIMARY KEY PRIMARY KEY
NULL, NOT NULL NULL, NOT NULL
UNIQUE UNIQUE
CLUSTERED, NONCLUSTERED Not Supported
FOREIGN KEY REFERENCES FOREIGN KEY REFERENCES
partition_scheme_name PARTITION BY RANGE | HASH | LIST Specify the type of partitioned table when creating a table

4. computed column definition

Not Supported

5. table constraint

SQL Server Altibase Remark
PRIMARY KEY PRIMARY KEY
CLUSTERED, NONCLUSTERED Not Supported
FOREIGN KEY REFERENCES FOREIGN KEY REFERENCES
partition_scheme_name PARTITION BY RANGE | HASH | LIST Specify the type of partitioned table when creating a table

6. table_option

SQL Server Altibase Remark
DATA_COMPRESSION Replace with COMPRESS(column_name) Altibase provides column-level compression

7. index_option

SQL Server Altibase Remark
PAD_INDEX Not Supported
FILLFACTOR Not Supported
IGNORE_DUP_KEY Not Supported Altibase is in the OFF state
STATISTICS_NORECOMPUTE Not Supported Altibase is in the OFF state
ALLOW_ROW_LOCKS Not Supported Defined according to the Durability Level setting
ALLOW_PAGE_LOCKS Not Supported Defined according to the Durability Level setting
DATA_COMPRESSION Not Supported Index compression is not supported

CREATE USER#

In Altibase, database login and user are not created separately; a single user is created and managed.

SQL Server Altibase Remark
CREATE LOGIN <login_name> WITH PASSWORD = '<password>';

-- Users based on logins in master
CREATE USER user_name
    [
        { FOR | FROM } LOGIN login_name
    ]
    [ WITH DEFAULT_SCHEMA = schema_name ]
[ ; ]
CREATE USER <user_name> IDENTIFIED BY '<password>'
DEFAULT TABLESPACE = tablespace_name

CREATE INDEX#

Altibase provides only BTREE and RTREE indexes and does not support BITMAP, CLUSTER, REVERSE, or Global partitioned indexes.

The following explains how to convert the options used in the CREATE INDEX statement when migrating to Altibase.

SQL Server Altibase Remark
UNIQUE UNIQUE
CLUSTERED | NONCLUSTERED Not Supported
ASC | DESC ASC | DESC
INCLUDE Not Supported
filter_predicate Not Supported
partition_scheme_name PARTITION  ON Only local indexes are supported
ON filegroup_name Not Supported
table_or_view_name Table_name Views are not supported
PAD_INDEX Not Supported
FILLFACTOR Not Supported
SORT_IN_TEMPDB Not Supported
IGNORE_DUP_KEY Not Supported Altibase is in the ON state
STATISTICS_NORECOMPUTE Not Supported Altibase is in the OFF state
DROP_EXISTING Not Supported
ONLINE Not Supported Altibase is in the OFF state
ALLOW_ROW_LOCKS Not Supported Defined according to the Durability Level setting
ALLOW_PAGE_LOCKS Not Supported Defined according to the Durability Level setting
MAXDOP Not Supported Applied only at creation
DATA_COMPRESSION Replace with COMPRESS(column_name)

SQL Conversion#

This explains how to convert SQL statements from SQL Server to Altibase.

JOIN#

In addition to the joins below, Altibase also provides Semi Join and Anti Join.

SQL Server Altibase Remark
INNER JOIN INNER JOIN
LEFT OUTER JOIN LEFT OUTER JOIN
RIGHT OUTER JOIN RIGHT OUTER JOIN
FULL OUTER JOIN FULL OUTER JOIN
CROSS JOIN CROSS JOIN

Execution Query#

The query terminator in Altibase is “;”. Therefore, if you want to execute multiple queries at once, each query must be separated by the terminator “;”.

SQL Server Altibase Remark
GO ;
Commit;
default Autocommit

Temporary Table#

SQL Server Altibase Remark
CREATE TABLE #TempProcess CREATE TABLE TEMPORARY table_name (…) on commit (...) rows TABLESPACE volatile_tablespace_name

Control Statement#

SQL Server Altibase Remark
IF If (condition)
Else if (condition)
Else
End
If condition then
Elseif condition then
Else
End
While While (condition)
BEING
END
BREAK => Exit the while loop
While condition loop
End loop
Exit when condition => Exit the loop when the condition is met

Identity Attribute#

Altibase does not have an Identity property. Therefore, if needed, create a SEQUENCE and apply it as the DEFAULT value for the column.

For example:

SQL Server Altibase Remark
-- Apply auto-increment by 1 to the c1 column.
create table seq_test
(c1 int identity(1,1) not null
...
);
-- create a sequence
create sequence seq1 start with 1 increment by 1 nocache;

-- Apply the sequence as the default value.
create table seq_test
(c1 integer default seq1.nextval not null,
...
);

Procedure Conversion#

The syntax for creating and executing a PROCEDURE in Altibase differs from that of SQL Server.
This example demonstrates how to modify a PROCEDURE.

Parameter Declaration#

When converting the parameter declaration section from SQL Server to Altibase:
Remove the '@' symbol used in SQL Server parameter declarations.
Specify in, out, or in out depending on the nature of each parameter.
For functions, since they return a single value upon execution, the data type must be specified after RETURN.

SQL Server Altibase Remark
CREATE Procedure dbo.sp1
    @nTop INT --ignored
  , @nGroupCode INT = -2
  , @nObjectCode INT = -2
  , @nRCLS INT=1 – 1:Set , 0:Unset
  , @nLCRS INT=1 – 1:Set , 0:Unset
  , @nLCLS INT=1 – 1:Set , 0:Unset
  , @sStartDate VARCHAR(19) = '' – ignored
  , @sEndDate VARCHAR(19) = '' – ignored
  , @nSort INT = 0 – 0:Number of detections, 1:Number of attempts 2:DataSize
  , @sSignatureName VARCHAR(100) = ''
As
…
create or replace procedure sp1
(
    i_nTop IN INT --ignored
  , i_nGroupCode IN INT := -2
  , i_nObjectCode IN INT := -2
  , i_nRCLS IN INT:=1 – 1:Set , 0:Unset
  , i_nLCRS IN INT:=1 – 1:Set , 0:Unset
  , i_nLCLS IN INT:=1 – 1:Set , 0:Unset
  , i_sStartDate IN VARCHAR(19) := '' – ignored
  , i_sEndDate IN VARCHAR(19) := '' – ignored
  , i_nSort IN INT := 0 – 0:Number of detections, 1:Number of attempts 2:DataSize
  , i_sSignatureName IN VARCHAR(100) := ''
)
as
…

Variable Declaration#

In Altibase, variables are declared between AS and BEGIN.
Remove the @ symbol from variable declarations used in SQL Server.
Replace the delimiter , with ; when declaring variables.
Always end the last variable declaration with a ; to indicate the end.

SQL Server Altibase Remark
create or replace procedure sp1
(
  ……
)
as
DECLARE @sTotalQry VARCHAR(3000)
  , @sQry VARCHAR(3000)
  , @sFilterQry VARCHAR(1000)
  , @dStartDate DATETIME
  , @dEndDate DATETIME
  , @sSort VARCHAR(20)
  , @sSortSub VARCHAR(90)
  , @sTop VARCHAR(10)
  , @sTable VARCHAR(50)
  , @nSec INT
  , @sBaseDate VARCHAR(23)
create or replace procedure sp1
(
  ……
)
as
i_sTotalQry VARCHAR(3000);
i_sQry VARCHAR(3000);
i_sFilterQry VARCHAR(1000);
i_dStartDate DATE;
i_dEndDate DATE;
i_sSort VARCHAR(20);
i_sSortSub VARCHAR(90);
i_sTop VARCHAR(10);
i_sTable VARCHAR(50);
i_nSec INT;
i_sBaseDate VARCHAR(23);

Assignment#

In SQL Server, variables are assigned values using SET and the @ symbol.
Altibase supports two methods to modify this:

  1. Remove SET and the @ symbol, replace = with :=, replace the delimiter , with ;, and always end with a ; to indicate the end.
  2. Remove the @ symbol, replace the delimiter , with ;, and always end with a ; to indicate the end.
SQL Server Altibase Remark
SET @sTop = '1000'
SET @sBaseDate = CONVERT(VARCHAR, DATEADD(dd, -1, GETDATE()), 121)
i_sTop := '1000' ;
i_sBaseDate := to_char(DATEADD (SYSDATE, -1, 'DAY'), 'YYYY-MM-DD HH:MI:SS') ;

OR

Set i_sTop = '1000' ;
Set i_sBaseDate = to_char(DATEADD (SYSDATE, -1, 'DAY'), 'YYYY-MM-DD HH:MI:SS') ;

Flow Control (Control_flow_statement) - IF statement#

In Altibase, every statement must end with a ; to indicate the end.
An IF statement begins with IF (condition) THEN.
It ends with END IF to indicate the end of the IF block.

SQL Server Altibase Remark
IF (@nRCLS = 1) AND (@nLCRS = 1) AND (@nLCLS = 1) SET @sFilterQry = ''
ELSE IF (@nRCLS = 0) AND (@nLCRS = 0) AND (@nLCLS = 0) SET @sFilterQry = ''
IF (i_nRCLS = 1) AND (i_nLCRS = 1) AND (i_nLCLS = 1) THEN
    i_sFilterQry := '' ;
ELSIF (i_nRCLS = 0) AND (i_nLCRS = 0) AND (i_nLCLS = 0) THEN
    i_sFilterQry := '' ;
END IF;

SELECT clause#

In Altibase, every statement must end with a ; to indicate the end.
To send a SELECT result set to the client in Altibase, a REF CURSOR must first be defined as a database object.
The REF CURSOR should then be included as a parameter in the stored procedure.
When executing the corresponding query, use the OPEN command.
Remove the @ symbol used in SQL Server.

SQL Server Altibase Remark
……
SET @sQry = 'select * from test_tbl'
EXEC(@sQry)
……
CREATE TYPESET MY_TYPE
    AS
    TYPE MY_CUR IS REF CURSOR;
END;
/

create or replace procedure spTMSGetEventSignatureRankVariation
(
    ……
  , P1 OUT MY_TYPE.MY_CUR
)
As
    i_sQry := 'select * from test_tbl' ;
    OPEN P1 FOR i_sQry;
END;

Exception Handler#

In Altibase, every statement must end with a ; to indicate the end.
The Exception Handler in Altibase is used to handle specific exceptions when they occur.
Exception handling must always be written within a BEGIN ... END; block.
Specify either a system-defined or user-defined exception name; when that exception occurs, the corresponding statement will be executed.
If the specific exception is not handled, it will be caught by the OTHERS routine as a fallback.
To check if the number of affected records is zero, use the SQL%ROWCOUNT constant.
System-defined exceptions are included at the end of the file.

SQL Server Altibase Remark
IF @@ERROR <> 0
BEGIN
    IF @@ROWCOUNT = 0 BEGIN
        ROLLBACK
    END
END
BEGIN
    ……
    EXCEPTION WHEN NO_DATA_FOUND THEN
    rollback;
END;

OR

IF SQL%ROWCOUNT = 0 then
    ROLLBACK
END if;

SP Call#

In Altibase, every statement must end with a ; to indicate the end.
In SQL Server, a stored procedure is called using exec sp_name without parentheses around the parameters.
In Altibase, the stored procedure is executed by specifying the procedure name followed by its parameters in parentheses.
Remove the @ symbol used in SQL Server.

SQL Server Altibase Remark
IF @ID IS NOT NULL BEGIN
EXEC AddCustomer 'David', 'Seoul';
또는
EXEC AddCustomer @Name = 'David', @City = 'Seoul';
IF ID IS NOT NULL THEN
EXEC AddCustomer('David', 'Seoul');

Functions#

In Altibase, every statement must end with a ; to indicate the end.
Usage may vary by function, and function names may differ.
Remove the @ symbol used in SQL Server functions.

SQL Server Altibase Remark
1. LEFT, RIGHT function
    LEFT(@ID_NUMBER,6)
    RIGHT(@ID_NUMBER,7)
2. ISNULL(V1, 0)
3. CASE sample WHEN '0' THEN B.sample1+B.package ELSE ISNULL(D.sample2,0) END
4. Convert
    CONVERT(VARCHAR(10),,120) <= B.dStartDate TO_CHAR(CASE2(i_dStartDate1=NULL,SYSDATE),'YYYY-MM-DD');
5. LEN
6. RTRIM(LTRIM())
7. DATEADD(mi, -20, GETDATE())
8. CEILING
9. Remainder operator % (a % b )
10. charIndex(‘aaa’, ‘aaabbbcccddd’)
11. String concatenation + : (‘alti’ + ‘base’)
1. Replace with SubStr
    SUBSTR(ID_NUMBER,1,6)
    SUBSTR(ID_NUMBER,8,7)
2. NVL(V1, 0)
3. CASE2(sample = '0', B.sample1 || B.package,D.sample2=NULL,0,)
4. Convert
    CONVERT(VARCHAR(23), dStartDate, 121) => TO_CHAR(dStartDate, 'YYYY-MM-DD HH:MI:SS.FF3');
    CONVERT(DATETIME, sEndDate) => TO_DATE(sEndDate, 'YYYY-MM-DD HH:MI:SS');
5. LENGTH
6. TRIM()
7. DATEADD(sysdate, -20, 'MINUTE');
8. CEIL
9. MOD(a, b)
10. INSTR(‘aaabbbcccddd’, ‘aaa’)
11. || (‘alti’ || ’base’)

ETC#

In Altibase, every statement must end with a ; to indicate the end.
Remove the @ symbol used in SQL Server.

| SQL Server | Altibase | Remark | | --- | --- | | UPDATE STATISTICS index_name | Not required | | with (nolock) | Not required (remove) | | Use of keywords like order, level | Do not use keywords like order, level (reserved in Altibase) |

When using a DB Link in Altibase, use REMOTE_TABLE(dblink_name, query) for SELECT statements, and REMOTE_EXECUTE_IMMEDIATE(dblink_name, query) for DML statements.
For more details about ALTIBASE DB LINK, refer to the Altibase 8.1 Online Manual (Database Link User’s Manual).

JOIN Update#

In Altibase, there are two methods to perform a join update:

1. Use JOIN UPDATE - Each table must have a Primary Key or a Unique Key.

  1. Use an update with MERGE JOIN
    • Merge into A using ( SELECT … )
      WHEN matched then
      Update …

Using Cursor#

Let's look at how to convert a SQL Server cursor to Altibase using an example.

SQL Server Altibase Remark
declare security_cursor cursor for
    select fldID from tblDept where fldParentID=@fldID
open security_cursor
fetch next from security_cursor into @fldID
while @@fetch_status = 0
begin
    exec sr_GetSubDeptID_Str @fldID, @DeptID OUTPUT
    declare @sql as varchar(8000)
    begin
        exec(@sql)
    end
    fetch next from security_cursor into @fldID
end
close security_cursor
deallocate security_cursor
-- Here is an example of an Altibase stored procedure that
  calculates the number of employees and the total salary by department.

CREATE OR REPLACE PROCEDURE ForCursor_Test
AS
BEGIN
    DECLARE CURSOR dept_sum IS
    SELECT b.dname, COUNT(a.empno) cnt, SUM(a.sal) salary
    FROM emp a, dept b
    WHERE a.deptno = b.deptno
    GROUP BY b.dname;

    –- Execute the cursor using a FOR loop.
    BEGIN
        FOR emp_list IN dept_sum LOOP
            println('dept name : ' || emp_list.dname);
            println ('employee count : ' || emp_list.cnt);
            println('total salary : ' || emp_list.salary);
        END LOOP;
    END;
END;
/

Exception Code#

Refer to the Error Message Reference manual.

Exception Name Error Code (integer) Error Code (hexadecimal) Error Section
"CURSOR_ALREADY_OPEN" 201062 31166 qpERR_ABORT_QSX_CURSOR_ALREADY_OPEN
"DUP_VAL_ON_INDEX" 201063 31167 qpERR_ABORT_QSX_DUP_VAL_ON_INDEX
"INVALID_CURSOR" 201064 31168 qpERR_ABORT_QSX_INVALID_CURSOR
"INVALID_NUMBER" 201065 31169 qpERR_ABORT_QSX_INVALID_NUMBER
"NO_DATA_FOUND" 201066 3116A qpERR_ABORT_QSX_NO_DATA_FOUND
"PROGRAM_ERROR" 201067 3116B qpERR_ABORT_QSX_PROGRAM_ERROR
"STORAGE_ERROR" 201068 3116C qpERR_ABORT_QSX_STORAGE_ERROR
"TIMEOUT_ON_RESOURCE" 201069 3116D qpERR_ABORT_QSX_TIMEOUT_ON_RESOURCE
"TOO_MANY_ROWS" 201070 3116E qpERR_ABORT_QSX_TOO_MANY_ROWS
"VALUE_ERROR" 201071 3116F qpERR_ABORT_QSX_VALUE_ERROR
"ZERO_DIVIDE" 201072 31170 qpERR_ABORT_QSX_ZERO_DIVIDE
"INVALID_PATH" 201237 31215 qpERR_ABORT_QSX_FILE_INVALID_PATH
"INVALID_MODE" 201235 31213 qpERR_ABORT_QSX_INVALID_FILEOPEN_MODE
"INVALID_FILEHANDLE" 201238 31216 qpERR_ABORT_QSX_FILE_INVALID_FILEHANDLE
"INVALID_OPERATION" 201239 31217 qpERR_ABORT_QSX_FILE_INVALID_OPERATION
"READ_ERROR" 201242 3121A qpERR_ABORT_QSX_FILE_READ_ERROR
"WRITE_ERROR" 201243 3121B qpERR_ABORT_QSX_FILE_WRITE_ERROR
"ACCESS_DENIED" 201236 31214 qpERR_ABORT_QSX_DIRECTORY_ACCESS_DENIED
"DELETE_FAILED" 201240 31218 qpERR_ABORT_QSX_FILE_DELETE_FAILED
"RENAME_FAILED" 201241 31219 qpERR_ABORT_QSX_FILE_RENAME_FAILED