Altibase Precompiler (APRE*C/C++) Guide#
Overview#
This document is for developers who develop DBMS application programs using the ALTIBASE Precompiler APRE*C/C++. This document describes the most suitable development method using the Altibase, and reference is also made to source conversion from other DBMS by referring to various sections introduced in this document. For more detailed information for developing, please refer to the manual.
It is recommended to refer to the following documents in advance:
- Altibase Replication Configuration Guide
- Altibase Replication Constraints Guide
- APRE*C/C++ New Features & Upgrade Guide
- Altibase Precompiler (APRE*C/C++) Makefile
The test environment of this document is as follows.
- ALTIBASE: Altibase 6 or later
- OS: Linux (2.6.32-504.el6.x86_64)
Info
For errors and improvements related to this document, please contact the technical support portal or technical support center.
- Technical support portal: http://support.altibase.com/en
- Technical support center: +82-2-2082-1114
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 for ALTIBASE Development#
When developing with the Altibase Precompiler, it describes a few things to be considered in advance. In this chapter, we will refer to the development of the program by first mentioning performance, threads, and so on.
Considerations for Performance#
Developers should consider at least two things with regard to performance.
-
Minimization of Prepare/Execute or Direct-Execute method
Form of performance Simple Insert Performance (2.4GHz / 8EA) Prepare/Execute each time 0.0008.43sec (Processing time per case) Prepare once/Execute each time 0.0005.55sec (Processing time per case) Since the above performance comparison is for a very simple insert, it should be premised that the effect of improving performance may vary depending on the query complexity, load, and equipment at the time of application. However, if the communication cost due to each DBMS and Prepare is reduced, the application server can be very effective in a structure that is separated from the DBMS server. In other words, it is expected to reduce the cost of 1 prepare communication from the communication cost of prepare 1 and execute 1.
-
Check the SQL statement execution plan (PLAN)
Altibase can check the execution plan with the following command.
iSQL> ALTER SESSION SET EXPLAIN PLAN = on;The prompt "iSQL" refers to executed in the SQL statement execution utility provided by Altibase. This utility is provided in $ALTIBASE_HOME/bin/isql. (In addition to this, "Orange for Altibase" of Wear Valley, which is provided by ALTIBASE, can be used in the Windows environment.) When the query is executed in the query processor after executing the above command, the execution plan of the query is displayed.
iSQL> select * from t1 where a = 1; ------------------------------------------------------------ PROJECT (COLUMN_COUNT: 2, TUPLE_SIZE: 8 ) SCAN ( TABLE: T1,INDEX: __SYS_IDX_ID_102, ACCESS: 1, SELF_ID: 2 )The developer needs to tune the SQL statement in a form that reduces the cost of the number of accesses by checking at least what index (INDEX) the table accesses and how many times the data is fetched from the corresponding index. Since ALTIBASE's memory DB hardly performs disk I/O, it can perform a certain portion of fast performance even if a full scan is performed, but on the contrary, unnecessarily frequent access to memory causes frequent system calls, resulting in relatively high CPU load. In other words, it is necessary to cover the CPU cost as frequently as the process of loading the memory space into the cache, which is the space that the CPU can compute, occurs frequently. Therefore, it should be considered that reducing these costs with the appropriate index scans can maximize performance and minimize CPU load.
Considerations for Threads#
ALTIBASE does not guarantee inter-thread protection for connection objects. In other words, if a query is executed by accessing it simultaneously with one connection, an unknown error occurs. Therefore, when developing in the form of taking a connection object for each individual thread, or when multiple threads need to access one connection, the user must perform concurrency control on the connection object. If such concurrency control is not performed, the following errors may occur.
- Invalid request to process the SQL statement
- Communication link failure (EXEC->PREP)
- Communication link failure (PREP->EXEC)
- Communication link failure (EXEC->INVL)
- Connection does not exist
Considerations for Host Variables#
When specifying the size of a char type host variable when declaring a host variable in ALTIBASE, "+1byte" in consideration of null-padding must be added. In the precompiled option, +1 byte can be omitted with the "-n" option, but in this case, the user must control the clear length of the char type host variable.
Note
Assuming that there is a table declared as Table (C1 char(10)), the size of the host variable is char C1 [10 + 1]; It is declared in the form.
The indicator-related part that occurs when a NULL value is returned is also a case of a problem that occurs frequently. This is explained in the last part of the document, "Frequently Occurred Error Messages". "?" is used for parameter markers of host variables with all the ALTIBASE precompilers.
sprintf("delete from sample_table where column1 = ?");
Also, a colon (:) must be used before the host variable in the source.
Checking Errors#
The SQLCA object of ALTIBASE can be used without a separate declaration. It is recommended for Developers to insert code that performs error checking at all stages of using EXEC SQL statements. In the case of ALTIBASE, when using a cursor, if an error is not checked in the Cursor Prepare/Declare step, only an error indicating that the cursor has not been defined is checked at the time of opening the cursor. Please make sure to check for errors in the Cursor Prepare/Declare step that is omitted. The following error may occur during the open or fetch phase.
The statement does not exist
The cursor must be opened for fetch.
The cursor does not exist.
The variables provided by ALTIBASE for error checking are as follows. The error check does not matter which variable is selected, but it is recommended to use sqlca.sqlcode to check errors, and to output SQLCODE when printing the debugging log to check errors.
| Variable | Description |
|---|---|
| SQLCODE | Error codes defined internally by ALTIBASE (ex. -69720) |
| sqlca.sqlcode | Error code that can determine the success/failure of SQL |
| SQLSTATE | Error code of char* type according to CLI standard (ex. 08S01) |
- Example of application
/* open cursor */
EXEC SQL OPEN DEPT_CUR;
if (sqlca.sqlcode != SQL_SUCCESS)
{
fprintf (stdout, "DB_ERR: %d, %s\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
}
Since SQLCODE is all defined in $ALTIBASE_HOME/msg/manual.txt, refer to the relevant file, and some predefined values as below can also be used. If there is a case of conversion in another DBMS or a case that the user has separately redefined, it must be converted appropriately.
| Error | Code value | Description |
|---|---|---|
| SQL_ERROR | -1 | If there is an error in processing |
| SQL_SUCCESS | 0 | If processing is successful |
| SQL_SUCCESS_WITH_INFO | 1 | If processing is successful or if there is an error in the result |
| SES_DUPKEY_ERR | -69720 | In case of index duplication |
| SQL_NO_DATA | 100 | If there is no data |
| SQL_INVALID_HANDLE | -2 | If an internal object required for SQL processing is an error |
Commit Mode#
ALTIBASE basically operates in Auto-Commit mode unless the configuration is changed. That is, when a change transaction occurs, if the execution result is normal, it is automatically reflected in the DB. If the user wants to change this setting to NonAuto-Commit mode, change "AUTO_COMMIT = 1" defined in $ALTIBASE_HOME/conf/altibase.properties to "0" or execute the following command in the program.
EXEC SQL AUTOCOMMIT OFF;
After changing to NonAuto-Commit mode, explicit Commit/Rollback must be performed after all transactions are executed.
Starting from Altibase 5, it is not necessary to perform explicit Commit/Rollback for the SELECT statement. However, when handling LOBs, NonAuto-Commit mode must be accessed, and explicitly Commit/Rollback must be performed. When accessing through Auto-Commit, the following error occurs.
-
Error in change transaction (INSERT, UPDATE, DELETE)
Connection is in autocommit mode. One can not operate on LOB datas with autocommit mode on -
Error in query transaction (SELECT)
LobLocator can not span the transaction 34734145 // This number was expressed as a random number
Additionally, it should be considered that if an explicit Commit/Rollback is not performed after accessing the LOB, the lock is maintained by the corresponding transaction, which may cause a resource failure.
System Signal Handling#
Altibase client library is not safe when a system signal occurs. For example, if the network connection is terminated due to an external cause, an application program in progress may be forcibly terminated by receiving a SIGPIPE signal. To prevent this forced termination, the SIGPIPE signal must be handled by the user application. However, if a function in the Altibase client library is called while processing the SIGPIPE signal, this should not be called because the program may be stopped.
However, after signal processing is completed, it is possible to call a function in the Altibase client library.
-
Example of signal handling
int main() { ....... signal(SIGPIPE, SIG_IGN ); .... EXEC SQL CONNECT :user identified by :pwd; ..... EXEC SQL DISCONNECT; .... }
Other unsupported items#
- Dynamic Method 4 method is not supported
- Context syntax is not supported (refer to "Considerations for Threads")
- Procedure typeset, Ref-Cursor, and result set in the form of array cannot be retrieved with the precompiler
- It is not safe for the system signal occurrence. System signal processing should be handled by the user code
How to use APRE#
In this chapter, detailed explanations are given with the source of development examples. When converting other DBMS, convert it by referring to this document.
How to use APRE*C/C++#
Altibase Precompiler is called APRE*C/C++ and the executable file is "apre". The user should develop the source with ".sc" as the extension. The following is how to use it.
$ apre -mt -t cpp connect1.sc
-----------------------------------------------------------------
Altibase C/C++ Precompiler.
Release Version 8.1.0.0.2
Copyright 2000, ALTIBASE Corporation or its subsidiaries.
All Rights Reserved.
-----------------------------------------------------------------
$ ls connect1.cpp
When compiled normally, a file called sample.cpp is created in the corresponding directory, and the prompt returns without any error. For detailed options related to apre, refer to the "Altibase Precompiler (APRE*C/C++) Makefile".
If C++ needs a "*.c" file as a C compiler, precompile it with "-t c" instead of "-t cpp".
Basic Makefile#
The basic header file and library for Altibase development are located below.
| Classification | Location |
|---|---|
| Header file | $ALTIBASE_HOME/include |
| Library | $ALTIBASE_HOME/lib |
The part to be added in the library is "-lapre -lodbccli". With this reference, a simple Makefile that compiles connect1.sc can be written as follows.
%.cpp : %.sc
apre -unsafe_null -t cpp $<
connect1: connect1.cpp
g++ -Wl,-relax -L. -O3 -L${ALTIBASE_HOME}/lib -I${ALTIBASE_HOME}/include -o connect1 connect1.o -lapre -lodbccli -ldl -lpthread -lcrypt -lrt
In addition to the ALTIBASE library, a system library is required for compilation. For a list of system libraries required for each platform and an example of a makefile, use the list defined in the "LIBS" tag in the $ALTIBASE_HOME/install/altibase_env.mk file.
One more point to consider is that if the user uses a C compiler other than a C++ compiler, the ALTIBASE library is provided in C++, so the C++ system library must be linked for compatibility. For details, refer to the "Altibase Precompiler (APRE*C/C++) Makefile".
Host Variables#
When coding using SQL statements on the source, all variables used to control I/O for data to the DBMS are referred to as host variables. Prior to Altibase 5.3.3, all host variables could be used only those declared in the “EXEC SQL BEGIN DECLARE SECTION” and “EXEC SQL END DECLARE SECTION” clauses.
However, starting from version 5.3.3 or later, it is possible to recognize general variables as host variables through the apre option. (apre -parse full option)
- Example
char H_TODAY [20];
EXEC SQL SELECT TO_CHAR(SYSDATE, ‘yyyy-mm-dd’) INTO :H_TODAY FROM DUAL;
In the process of development, there may be a case where it is necessary to write a host variable in the Target clause of the SELECT statement. From Altibase 5, it is supported in the following form with an operator called CAST.
EXEC SQL BEGIN DECLARE SECTION;
char H_var1 [10];
char H_var2 [10];
int H_INT;
int H_Out;
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT CAST (:H_var1 AS CHAR(10)) INTO :H_Var2 FROM DUAL;
……
EXEC SQL SELECT CAST (:H_INT AS INTEGER) + Column1 INTO :H_Out FROM T1;
Runtime Error Handling#
ALTIBASE returns an error with the sqlca structure. The user can check the error code and error message with the following.
| Variable | Code value | Description |
|---|---|---|
| sqlca.sqlcode | SQL_ERROR (-1) | When an error occurs |
| SQL_SUCCESS (0) | When processed normally | |
| SQL_SUCCESS_WITH_INFO (1) | When it has been processed but the user confirmation is required | |
| SQL_NO_DATA (100) | When there is no target data | |
| sqlca.sqlerrm.sqlerrmc | Contain an error message | |
| sqlca.sqlerrd[2] | In the case of modified DML, it has the number of processed records, and in the case of the select statement, when the host variable is an array, it only has the number of arrays (not cumulative) of variables containing the currently fetched records. | |
| SQLCODE | Contain error codes defined in ALTIBASE | |
| SQLSTATE | Contain CLI standard error codes (ex) 08S01 |
Since the above code values are already defined in the source in the precompile stage, the developer does not need to redefine or include it in the header.
Connect/Disconnect#
ALTIBASE does not have the same configuration as a separate listener and is implemented in a form that directly connects to the thread inside the DBMS. Therefore, the connection is made by directly entering the IP and PORT number of the server to be connected. The following is a basic example code for connecting to ALTIBASE.
// Start of host variable declaration
EXEC SQL BEGIN DECLARE SECTION;
char usr[20];
char pwd[20];
char opt[200];
// End of host variable declaration
EXEC SQL END DECLARE SECTION;
main()
{
// Unique ID of DB user account
sprintf (usr, “sys”);
// Password of DB user account
sprintf (pwd, “manager”);
// Server IP and connection port
sprintf (opt, “DSN=127.0.0.1; PORT_NO=20300;CONNTYPE=1”);
// Connection trial
EXEC SQL CONNECT :usr IDENTIFIED BY :pwd USING :opt;
if (sqlca.sqlcode != 0)
{
Printf(“ConnectError:: %d %s\n”, SQLCODE, sqlca.sqlerrm.sqlerrmc);
}
}
Connection Type#
The type of connection method is the setting value of the CONNTYPE property, and one of three types can be selected: TCP/IP, unix domain, and IPC.
| CONNTYPE | Description |
|---|---|
| CONNTYPE=1 | Communication between the program and DBMS is performed in the TCP/IP method |
| CONNTYPE=2 | Communication between the program and DBMS is performed in the unix domain method |
| CONNTYPE=3 | Communication between the program and DBMS is performed in the IPC method |
CONNTYPE=2, 3 methods can be used only if the program is located in the same equipment as the DBMS server. Since both methods do not incur communication costs up to the hardware level, it is recommended if achieving faster performance than TCP/IP is required. When selecting the IPC connection method, the IPC_CHANNEL_COUNT in the ALTIBASE_HOME/conf/altibase.properties file must be adjusted to a value of 1 or larger. This setting can be used only by restarting the DB, and kernel settings related to IPC must be sufficiently adjusted in advance.
Connection Name#
It is possible to give an explicit name at the time of connection, but it is performed as follows.
EXEC SQL AT CONN1 CONNECT ~~
EXEC SQL AT :ConnName CONNECT ~~
It can be processed by saving it as an explicit name or host variable.
Connection Close#
The method of disconnecting the connection is as follows. If abnormal connection termination occurs, be sure to perform "EXEC SQL FREE" before attempting to reconnect.
EXEC SQL DISCONNECT;
EXEC SQL AT CONN1 DISCONNECT;
EXEC SQL AT :ConnName DISCONNECT;
Note
Please note that ALTIBASE DISCONNECT is operated in NonAuto-Commit mode, but it should be noted that the changed transaction normally processed immediately before DISCONNECT by the user is committed. (If the user does not want to reflect it, the user must explicitly perform rollback and then disconnect.)
Commit/Rollback#
Commit/Rollback can be executed as follows.
EXEC SQL COMMIT;
EXEC SQL ROLLBACK;
EXEC SQL AT CONN1 COMMIT;
EXEC SQL AT :ConnName COMMIT;
Example of executing DML#
EXEC SQL BEGIN DECLARE SECTION;
char H_today [20 + 1];
EXEC SQL END DECLARE SECTION;
main()
{
// DB connection (Refer to the example above.)
…
memset (H_today, 0x00, sizeof(H_today));
// Read Today's date from DB.
EXEC SQL SELECT TO_CHAR (sysdate, ‘yyyy-mm-dd’) INTO :H_today FROM Dual;
// Create table.
EXEC SQL CREATE TABLE TEST ( C1 CHAR(20));
// Insert the read data into the test table.
EXEC SQL INSERT INTO TEST (C1) VALUES (:H_today);
EXEC SQL INSERT INTO TEST VALUES (:H_today);
// Delete a date from the table.
EXEC SQL DELETE FROM T1 WHERE C1 like ‘2010%’;
EXEC SQL DELETE T1 WHERE C1 = ‘2010’;
}
In the case of ALTIBASE, the INTO clause of the SELECT clause cannot be omitted. For other transactional DML, the user can use SQL as it is.
Cursor#
A cursor is generally used as follows.
EXEC SQL BEGIN DECLARE SECTION;
int H_Condition;
int O_C1;
int O_C2;
EXEC SQL END DECLARE SECTION;
main()
{
// DB Connection
// Declare Cursor
EXEC SQL DECLARE CURSOR1 CURSOR FOR SELECT C1, C2 FROM TEST1 WHERE C1 >= :H_Condition;
// Open Cursor
EXEC SQL OPEN CURSOR1;
While (1)
{
// Read Cursor result
EXEC SQL FETCH CURSOR1 INTO :O_C1, :O_C2;
if (sqlca.sqlcode == SQL_NO_DATA)
break;
else if (sqlca.sqlcode != SQL_SUCCESS)
{
printf(“FetchError:: %d, %s\n”, SQLCODE, sqlca.sqlerrm.sqlerrmc);
break;
}
}
// Close cursor
EXEC SQL CLOSE CURSOR1;
}
FAC (Fetch Across Commit) Error#
In a non-autocommit environment, it is not recommended to fetch the cursor by record unit after opening the cursor and commit/rollback during fetching in the ANSI standard.
Altibase complies with ANSI standards, and errors occur in FAC by default.
However, starting from Altibase 6.3.1 or later, the FAC function is supported with the option of opening the cursor.
Example of code which the error occurs#
This shows an example of code that causes the FAC error.
while (1)
{
EXEC SQL FETCH CURSOR1 INTO :O_C1, :O_C2;
if (sqlca.sqlcode != 0)
{
printf(“FetchError::%d, %s\n”, SQLCODE, sqlca.sqlerrm.sqlerrmc);
break;
}
EXEC SQL UPDATE SET CHANGE_V = :O_C2 WHERE C1 = :O_C1;
if (sqlca.sqlcode != SQL_SUCCESS)
EXEC SQL ROLLBACK;
else
EXEC SQL COMMIT;
}
In the above code, an error check is performed at the point after the fetch, and the following errors may occur during the execution. (Based on Altibase 5.3)
$ ./SampleTest
FetchError: 100, Not found data
At this point, there are more actual records, but this error occurs because cursor1 that was previously opened at the time of committing has been internally terminated and there are no more result sets to fetch. (This error may be somewhat ambiguous to judge because it is the same as when there is no actual data, but this situation can be considered if such an error occurs below the expected number.)
Therefore, when a separate change transaction needs to be created in the process of fetching using a cursor, the session must be executed in auto-commit, or a separate connection object must be created and processed with the session.
FAC Error Message#
Depending on the version of Altibase, the FAC error message is displayed slightly differently.
| Version | Error Code | Error Message |
|---|---|---|
| Altibase 4.3.9 | ERR-4103C | Request of fetching data to an unprepared SQL statement. |
| Altibase 5.3.3 ~ 6.1.1 | 100 | |
| Altibase 6.3.1 or later | ERR-410D2 | Fetch out of sequence. |
Example of performing FAC by creating a separate connection#
This is an example of performing a FAC connection.
main()
{
// DB Connection
// Create connection for separate change transaction
EXEC SQL AT CONN1 CONNECT :usr IDENTIFIED BY :pwd USING :opt;
// Omit cursor mode
while (1)
{
EXEC SQL FETCH CURSOR1 INTO :O_C1, :O_C2;
// Specify the connection name created above at the time of change.
EXEC SQL AT CONN1 UPDATE t1 set change_v = :O_C2 where C1 = :O_C1;
// In commit / rollback, specify the connection name created above.
If (sqlca.sqlcode != SQL_SUCCESS)
EXEC SQL AT CONN1 ROLLBACK;
Else
EXEC SQL AT CONN1 COMMIT;
}
}
In the case of using a separate connection, the user must consider that the entire execution flow must be tied to one transaction. When using a separate connection object, some are committed due to an error during execution and some cannot be processed. If it is to be processed in the form of All or Nothing in one transaction, the commit/rollback statement must be used outside the fetch-loop.
Use "WITH HOLD" statement when declaring cursor#
Starting from Altibase 6.3.1 or later, if the cursor declared with "WITH HOLD" is opened, the cursor is not closed even after the transaction is completed after Commit/Rollback is executed. This is valid only when the session is in non-autocommit mode. For more detailed information, please refer to "ALTIBASE CLI User's Manual" and "ALTIBASE Precompiler Manual".
main()
{
.....
EXEC SQL AUTOCOMMIT OFF;
......
EXEC SQL DECLARE CURSOR1 CURSOR WITH HOLD FOR // When declaring a cursor, use the With hold option.
SELECT * FROM DEPARTMENT;
......
while(1)
{
EXEC SQL FETCH CURSOR1 INTO :O_C1, :O_C2;
......
EXEC SQL UPDATE t1 set change_v = :O_C2 where C1 = :O_C1;
// FAC error does not occur even if Commit/Rollback is executed.
If (sqlca.sqlcode != SQL_SUCCESS)
EXEC SQL ROLLBACK;
Else
EXEC SQL COMMIT;
......
}
...
}
Dynamic SQL#
Two examples of change transaction and inquiry transaction are described as follows.
Example of a change transaction#
EXEC SQL BEGIN DECLARE SECTION;
char sql_text [1024];
int H_var1;
int H_var2;
EXEC SQL END DECLARE SECTION;
main()
{
// DB Connection
sprintf (sql_text, “insert into test_table values (?, ?)”);
EXEC SQL PREPARE STMT1 FROM :sql_text;
if (sqlca.sqlcode != SQL_SUCCESS)
{
printf(“PrepareError:: %d, %s\n”, SQLCODE, sqlca.sqlerrm.sqlerrmc);
exit(-1);
}
for ( i=0; i<1000; i++)
{
EXEC SQL EXECUTE STMT1 USING :H_var1, :H_var2;
if (sqlca.sqlcode != SQL_SUCCESS)
{
printf(“PrepareError:: %d, %s\n”, SQLCODE, sqlca.sqlerrm.sqlerrmc);
exit(-1);
}
}
}
Example of a read transaction#
EXEC SQL BEGIN DECLARE SECTION;
char sql_text [1024];
int H_var1;
int H_var2;
EXEC SQL END DECLARE SECTION;
main()
{
// DB Connection
sprintf (sql_text, “select * from test_table where c1 >= ?”);
EXEC SQL PREPARE STMT1 FROM :sql_text;
if (sqlca.sqlcode != SQL_SUCCESS)
{
printf(“PrepareError:: %d, %s\n”,SQLCODE, sqlca.sqlerrm.sqlerrmc);
exit(-1);
}
EXEC SQL DECLARE CURSOR1 CURSOR FOR STMT1;
EXEC SQL OPEN CURSOR1 USING :H_var1;
// The following code is the same as cursor fetch.
This is a method of creating an SQL statement in a separate char* type variable and dynamically using the SQL statement when the table name is not specified or the column or search condition needs to be changed.
Starting from Altibase 5.1 or later, the execution plan executed by all sessions is internally shared in the area called Plan-Cache. However, if the query statement is changed each time like Dynamic SQL, the execution plan already created in Plan-Cache is not used, and the query performance is inevitably slower than that of static SQL statements because it operates in Prepare/Execute form again.
Call Function#
This describes an example of how to call a user-created or DBMS built-in function.
Example of calling a built-in function#
EXEC SQL BEGIN DECLARE SECTION;
char H_day [20];
EXEC SQL END DECLARE SECTION;
main()
{
// Example of using a function to retrieve a date
EXEC SQL SELECT TO_CHAR(SYSDATE,‘yyyy-mm-dd hh:mi:ss’) INTO :H_day from dual;
}
Example of calling a user-defined function#
// Function that adds the two input values and returns
Create or replace function user_func (a integer, b integer)
Return integer
As
Begin
Return a + b;
End;
/
---- calling source ----
main()
{
// Example of using user-generated function
EXEC SQL SELECT user_func (1, 2) INTO :H_value from dual;
}
It doesn't always have to be from dual, and it is fine if the user designates an arbitrary table as needed. (The user should be cautious about the function when char/varchar is returned in the return clause, the returned length must be specified. If not specified, only 1 byte is returned, so the following exception occurs in most functions: “Invalid length of the data type”)
Call Procedure#
Unlike other functions, procedures are executed with the separate calling method. Please note that functions can also be used in the same way.
// In the case of a function
EXEC SQL EXECUTE
BEGIN
:H_var := user_func (:in_param1, :in_param2);
END;
END-EXEC;
// In the case of a procedure, there is no return clause.
EXEC SQL EXECUTE
BEGIN
user_proc (:in_param1 in, :in_param2 out);
END;
END-EXEC;
WHENEVER Statement#
The WHENEVER statement of ALTIBASE provides such as do/goto/continue/stop. This statement must be declared before executing the embedded SQL statement. The following form is possible in the condition statement of the WHENEVER statement.
| Condition | Description |
|---|---|
| SQLERROR | When sqlca.sqlcode is SQL_ERROR |
| NOT FOUND | When sqlca.sqlcode is SQL_NO_DATA |
The processing part is possible as follows.
| Process | Description |
|---|---|
| DO [user-defined function] | Execute a user-defined function |
| CONTINUE | Ignore the error and continue |
| DO BREAK | Exit the loop. It is only valid within a loop. |
| DO CONTINUE | Move to the first position in the loop. This statement is only valid within loops. |
| GOTO [Label] | Execute GOTO to the designated by the user |
| STOP | Disconnect and close the program |
Examples are as follows.
EXEC SQL WHENEVER SQLERROR DO user_function();
EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL WHENEVER SQLERROR GOTO ERROR_ROUTINE;
EXEC SQL WHENEVER SQLERROR CONTINUE;
Session Failover function and Checking current connection#
In ALTIBASE, there isn't any way to actively return the status of the connection object, but the developer can detect that the connection has been terminated by checking the status of SQLSTATE after executing a query.
One of the possible methods is to put the following example source into a common function.
Example of Session Failover by User Code#
if ( memcmp (SQLSTATE, “08001”, 5) == 0 || memcmp (SQLSTATE, “08S01”, 5) == 0 || memcmp (SQLSTATE, “08003”, 5) == 0 )
{
// Connection is disconnected. Execute FREE, not DISCONNECT call.
EXEC SQL FREE;
EXEC SQL CONNECT ~~ // Opposite server
}
Starting from Altibase 5.3.3 or later, the Session FailOver function of the concept called CTF (Connection Time Failover) and STF (Service Time FailOver) are provided. CTF means FailOver tries to connect when the program first starts, and STF means FailOver occurs in the process of executing a query after the connection.
Since this function does not support transaction FailOver, the user must consider reprocessing the business logic after the Session FailOver. The example is as follows.
Example of using Altibase FailOver function#
Sprintf (opt, "DSN=127.0.0.1;"
"AlternateServers=(128.0.0.1:20300,128.0.0.2:20301);"
"ConnectionRetryCount=3;"
"ConnectionRetryDelay=10;"
"SessionFailOver=on;LoadBalance=off") ;
EXEC SQL CONNECT :usr IDENTIFIED BY :pwd USING :opt;
.................
EXEC SQL INSERT INTO T1 VALUES (:H_var1);
if (sqlca.sqlcode != SQL_SUCCESS)
{
// If this error appears, it means that FailOver has already been normally performed.!!
if (SQLCODE == EMBEDED_ALTIBASE_FAILOVER_SUCCESS)
{
printf(“FailOver Success\n”);
goto Retry_business_logic;
}
}
The meaning of each input item of the connection string is as follows.
| Option | Description |
|---|---|
| AlternateServers | The target servers to FailOver are listed in the form of IP:PORT in order based on comma (,). |
| ConnectionRetryCount | The number of connection attempts to one target server |
| ConnectionRetryDelay | After sleep for this interval (in seconds), retry the connection |
| SessionFailOver | Determine whether to use the SessionFailOver function during the service time. In other words, this specifies whether to automatically connect to the server in the AlternateServers list when an SQL error occurs. (on/off) |
| LoadBalance | When turned on, the server and AlternateServers list specified in the DSN are randomly selected at the time of initial connection. If turned off, the DSN is selected first, and then the connection is attempted in the order described in the AlternateServers list. |
If an error occurs at the time of cursor opening and needs to be reprocessed, the cursor must be reprocessed after executing the cursor until close release before reprocessing.
EXEC SQL CLOSE RELEASE CURSOR1;
APRE Sample Program#
This chapter briefly describes the sample program included in the ALTIBASE installation file.
Refer to various samples in $ALTIBASE_HOME/sample/APRE.
The source of the directory can be referenced as follows.
| Classification | Sample source name |
|---|---|
| DB connection sample | connect1.sc, connect2.sc |
| Host variable sample | arrays1.sc, arrays2.sc, pointer.sc, date.sc, varchar.sc, binary.sc |
| Cursor usage sample | cursor1.sc, cursor2.sc |
| Dynamic SQL sample | dynamic1.sc, dynamic2.sc, dynamic3.sc |
| Multi Connection sample | mc1.sc, mc2.sc |
| Thread development environment sample | mt1.sc, mt2.sc |
| DML specific usage sample | select.sc, insert.sc, delete.sc, update.sc |
| LOB usage sample | BLOB/blobSample.sc, CLOB/clobSample.sc |
| PSM/Function sample | psm1.sc, psm2.sc |
| FailOver sample | Fail-Over/FailOverSample.sc |
This document describes a sample of performing an update to a separate table in the cursor-fetch process by making two connections by applying sample sources in combination.
Testsample.sc
--------------------------------------------------------------------------
EXEC SQL BEGIN DECLARE SECTION;
char usr [20];
char pwd [20];
char opt [200];
char conn2 [20];
int H_C1;
int H_C2;
int i;
EXEC SQL END DECLARE SECTION;
void error_do(const char*step)
{
if (sqlca.sqlcode != 0)
{
printf ("Error: <%s> %d, %s\n", step, SQLCODE, sqlca.sqlerrm.sqlerrmc);
exit(-1);
} else {
printf ("%s success\n", step);
}
}
main()
{
// Connection information to connect
sprintf (usr, "sys");
sprintf (pwd, "manager");
sprintf (opt, "DSN=192.168.1.35;PORT_NO=27584;CONNTYPE=1");
// Execute DB connection
EXEC SQL CONNECT :usr IDENTIFIED BY :pwd USING :opt;
error_do("conn");
// Execute a separate connection for update execution in the cursor
sprintf (conn2, "update_con");
EXEC SQL AT :conn2 CONNECT :usr IDENTIFIED BY :pwd USING :opt;
error_do("conn2");
// Change to NonAutoCommit mode
EXEC SQL AT :conn2 ALTER SESSION SET AUTOCOMMIT = FALSE;
error_do ("alter session");
// Create two tables for testing and enter the data
EXEC SQL CREATE TABLE TABLE_A (C1 INTEGER, C2 INTEGER);
EXEC SQL CREATE TABLE TABLE_B (C1 INTEGER, C2 INTEGER);
for (i=0; i<10; i++)
{
EXEC SQL INSERT INTO TABLE_A VALUES (:i, :i);
error_do ("insert_table_a") (identifier: function is declared as error_do);
EXEC SQL INSERT INTO TABLE_B VALUES (:i, :i);
error_do("insert_table_b") (identifier: function is declared as error_do);
}
EXEC SQL AT :conn2 COMMIT;
// Declare cursor
EXEC SQL DECLARE CURSOR1 CURSOR FOR SELECT C1, C2 FROM TABLE_A;
error_do("declare");
// Open cursor
EXEC SQL OPEN CURSOR1;
error_do("open");
while (1)
{
// Update while fetching
EXEC SQL FETCH CURSOR1 INTO :H_C1, :H_C2;
error_do("fetch");
// Update
EXEC SQL AT :conn2 UPDATE TABLE_B SET C2 = C2 + :H_C2 WHERE C1 = :H_C1;
error_do("update");
}
// Close the used cursor
EXEC SQL CLOSE CURSOR1;
// Disconnect
EXEC SQL DISCONNECT;
// Commit the session that was performed the update and disconnect.
EXEC SQL AT :conn2 COMMIT;
EXEC SQL AT :conn2 DISCONNECT;
}
Frequently Occurring Error Messages#
This chapter describes and summarizes frequently occurring errors identified with SQLCODE and sqlca.sqlerrm.sqlerrmc. There can be two SQLCODEs for the same sqlca.sqlerrm.sqlerrmc. This is because it may be different depending on the location of the module in which the error occurred among the ALTIBASE internal modules. Since the contents of the error are the same, the corrective actions are the same as well.
Connection does not exist. (SQLCODE=-2)#
This is the case that the connection object to be used does not exist or disappears during the query processing process. Check whether the connection has been properly processed and in the case of a threaded program, check whether the connection name is properly indicated in the EXEC SQL AT clause. In some cases, the connection may have been disconnected from the DBMS server. (Check for timeout-related messages among the error messages.)
String data right truncated. (SQLCODE=1)#
This is the case when the length of the declared variable is small in the process of storing the value in the variable from the DB. Change the length of the DB variable as large as (data length + 1byte).
Invalid size of data to bind to a host variable [ Column ID = , Data Size = , Declared Size of Host Variable = ] (SQLCODE=-201144 or -266423)#
The actual data of (A+1)-th column has the size of B, but the length of the host variable has the size of C, so it occurs when the data value exceeds the length of the variable. Therefore, the length of the variable must be set larger than the length of the data. From time to time, even if the memory of the host variable is broken due to a program error. Therefore, it is recommended to check whether the host variable's memset or memory is broken. Or, in the case of a threaded program, it can also occur when concurrency control is not properly controlled with a single connection for multiple SQL statements.
Calculation stack overflow (SQLCODE=-135187 or -659475)#
This is the case when the size of the stack used in the query process is exceeded. Execute the SQL statement as follows before executing the query. The default is 1024.
EXEC SQL ALTER SESSION SET STACK SIZE = 4096;
Value overflow or Numeric value out of range (SQLCODE=-135184 or -659472 or -331890)#
This is the case when the data value used in the host variable is an input that is larger than the expression range of the column defined in the actual DB. Check the data value and change the column if necessary.
Conversion not applicable (SQLCODE=-135180 or -659468)#
This is the case when conversion between the data type of the column used in ALTIBASE or the type of host variable is not supported.
Table T1 (c1 char(10));
Insert into t1 values ('A') ;
(O) update t1 set c1 = case2(C1 = 1, 0, 1) ;
(X) update t1 set c1 = decode (C1=1, 0, 1); // Boolean type is not allowed in the parameter of decode.
Additionally, an error may occur when a host variable having a data type that cannot be converted is used in CLOB/BLOB handling
Invalid length of the data type (SQLCODE=-135181 or -659469)#
This is the case when data larger than the length that can be stored in the DB column is input.
Table T1 (c1 char(10));
(X) Insert into t1 values ('AAAAAAAAAAAAAAAAAAAAAAAAA');
Invalid literal (SQLCODE = -135185 or -659473)#
This error occurs when a string that cannot be converted to a numeric value is used with a host variable declared as a numeric type.
It can also occur when a built-in function like to_number() receives a non-numeric string as its input argument, where a numeric value is expected.
To resolve this issue, ensure that the data used matches the expected numeric type.
(X) select to_number ('A') from dual;
Invalid character value for cast specification (SQLCODE=-331893)#
This is the case when a string is contained in a numeric host variable and processed. Declare a host variable suitable for the column type.
Invalid cursor state. (SQLCODE=-331822) or Function sequence error (SQLCODE= -331796)#
This is the case when the order to be followed to use the cursor is not performed properly. Or, this error also occurs when a cursor that has already read all data or a cursor that has been closed and calls to fetch again.
The cursor must be opened for fetch (SQLCODE=-1) or The cursor does not exist (SQLCODE=-589857)#
To use a cursor normally, it must be processed with the order of PREPARE -> DECLARE -> OPEN -> FETCH -> CLOSE. This error occurs when an attempt is made to FETCH a cursor that is not normally DECLARE/OPEN.
Not enough insert values (SQLCODE=-200787)#
This error occurs when the number of columns specified in the INSERT statement and the number of host variables does not match.
The tablespace does not have enough free space (SQLCODE=-69685 or -69923)#
This is the case when there is no space due to the use of all the tablespace capacity during the transaction.
In this case, in the case of the disk tablespace, space must be secured by adding a data file, and in the case of a memory tablespace, available space must be secured by deleting or compacting unnecessary data.
Input literal is not long enough for date format. (SQLCODE=-135218)#
This is the case when the string entered into the date function is too short. Check if there are no errors in the entered data.
select TO_DATE('10123', 'yyyymmdd') from DUAL
Literals in the input do not match format string. (SQLCODE=-135224)#
This is the case when the date string entered in the date function and the date format do not match.
select TO_DATE ('2010123', ' yyyy-mmdd') from DUAL // Error in the yyyy-mm format
The transaction is already active. (SQLCODE=-266311 or -266312)#
Commit-mode can be adjusted for each session. In this process, this occurs when a transaction that has not been committed/rolled back exists and changes to Auto-Commit. Therefore, if an error occurs in the process of changing the properties of a session, first check whether a previously occurred transaction exists. Or, change the session properties after executing Commit/Rollback. Occasionally, in the case of implementing and using a thread program and a connection pool, it may occur when user management for the connection object (ie, when AutoCommit/NonAutoCommit is used in combination) is careless.
The row already exists in a unique index. (SQLCODE=-69720)#
This is the case when the value accessed or changed in the INSERT or UPDATE statement already exists in the primary key or unique index. In this case, check whether the corresponding data value already exists with an inquiry.
Unable to insert (or update) NULL into NOT NULL column. (SQLCODE=-200790)#
This is the case when the column is NOT NULL among the data values entered in the INSERT or UPDATE statement, and a NULL value is attempted to be inserted. Track data values to prevent NULL data from occurring, or change the NOT NULL Constraints of the column properly.
Indicator variable required but not supplied. (SQLCODE=-331841 or -594101 or 594103)#
This case is when the value contained in the host variable from the DB is NULL. This error is returned as SQL_SUCCESS_WITH_INFO. That is, it is an error that recognizes that the passed part is NULL. (There is no guarantee that the actual variable will contain any value.)
To solve this error, it can be solved by performing NVL processing on the column or using a separate int type indicator variable for host variables. Alternatively, the -unsafe_null option can be used among the apre options.
1) Compile with apre -unsafe_null.
2) SELECT NVL (Column,'N/A') from T1.
3) How to use indicator variable
EXEC SQL BEGIN DECLARE SECTION;
char H_var [20 + 1];
int H_indi;
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT Column INTO :H_var indicator :H_indi;
Client's query exceeded in the execution time limitation. (SQLCODE=-4164)#
If the time for executing a query exceeds the specified time, this error is returned for the query.
Check the execution plan of the query described in the document and take necessary tuning measures. Or, if it is due to the amount of data to be processed even though proper measures have already been taken, execute the following query.
EXEC SQL ALTER SESSION SET QUERY_TIMEOUT = 0;
If set to "0", execution continues regardless of the timeout setting for query processing. However, if this has been done, it is recommended that the user make a careful decision to change, as it can occupy a large amount of resources until the query is completed.
Communication link failure. (SQLCODE=-331843 or -331855)#
This is the case when the connection is disconnected due to a server or network error in the process of processing a query. In this case, first, check whether a log related to timeout is recorded in $ALTIBASE_HOME/trc/altibase_boot.log. Usually, two cases occur.
[Notify : Fetch Timeout] Session Closed by Server : Session ID = 44
[Notify : UTrans Timeout] Session Closed by Server : Session ID = 44
The fetch timeout means that the interval between the DBMS server and the program is limited to 60 seconds. If it exceeds this, the server forcibly disconnects the session.
The UTrans Timeout refers to a case where the interval of time that the user keeps without performing Commit/Rollback after a change transaction occurs is limited to 3600 seconds, and if it exceeds this, the session is disconnected. In both cases, the limit value can be changed as follows.
EXEC SQL ALTER SESSION SET FETCH_TIMEOUT = 0;
EXEC SQL ALTER SESSION SET UTRANS_TIMEOUT = 0;
If it is set to a value of "0", it operates so that the corresponding limit is not applied. However, it is recommended that, as in the case of QUERY_TIMEOUT, the developer must set the above problem at its source, since it will continue to occupy resources within the DBMS, and in some cases, failure can occur as long as the transaction continues.
Network problems such as L4, firewall timeout, etc. may cause the altibase_boot.log to be disconnected without errors. Therefore, the cause must be found with a separate PBT (Problem Tracking) process.
Considerations when converting from other DBMSs#
Among the contents of each part described above, the parts to be considered when converting from other DBMSs are summarized in a table.
| Classification | Example of using ALTIBASE and Explanation |
|---|---|
| CONNECT/DISCONNECT | EXEC SQL [AT :con_name] CONNECT :usr IDENTIFIED BY :pwd USING :opt; Refer to the Connect/Disconnect section above for details. |
| Function argument | void user_function (char *param1) { EXEC SQL BEGIN ARGUMENT SECTION; char *param1; EXEC SQL END ARGUMENT SECTION; } Or, void user_function (char *param1) { EXEC SQL BEGIN DECLARE SECTION; char *H_param1; EXEC SQL END DECLARE SECTION; H_param1 = param1; } |
| Error code | sqlca.sqlcode, SQLCODE, SQLSTATE, etc. can be used without a separate declaration, and the error code is different from other DBMSs, so the user must appropriately convert it. If the CLI standard code is used, it can be used interchangeably. Statements such as EXEC SQL INCLUDE sqlca.h; are not needed. |
| Host variable declaration | Starting from Altibase 5.3 or later, the host variable declaration clause can be omitted according to the apre option. Alternatively, the variable indicated in the declaration clause can be used as a host variable as before. EXEC SQL BEGIN DECLARE SECTION; char *H_param1; EXEC SQL END DECLARE SECTION; Starting from Altibase 5.3.3 or later, the initialization of host variables is supported. |
| Header file path | When specifying a header in the source, it can be done as follows. EXEC SQL INCLUDE "…../user_header.h"; Or, EXEC SQL OPTION (INCLUDE=/Absolute path/); EXEC SQL INCLUDE user_header.h; |
| Declaration of host variable in header file | #ifdef SESC_DECLARE ….. #endif It can be specified in the define clause as above. |
| Omit the into clause of SELECT | Not supported |
| SET TRANSACTION ~ statement | Not supported |