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 APRE(SES)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 version 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)

Ex) 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 version 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;

  // Start of host variable declaration
  EXEC SQL BEGIN  DECLARE SECTION;      
  char   usr[20];
  char   pwd[20];
  char   opt[200];
  // End of host variable delcaration
  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 conenction 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);
  }
 
}

EXEC SQL AT CONN1 CONNECT ~~
 
EXEC SQL AT :ConnName CONNECT ~~

EXEC SQL DISCONNECT;
 
EXEC SQL AT CONN1 DISCONNECT;
 
EXEC SQL AT :ConnName DISCONNECT;

In a non-autocommit environment, it is not recommending 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 version 6.3.1 or later, the FAC function is supported with the option of opening the cursor.

Example of code which the error occurs

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 version 5.3)

shell>./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.

Example of performing FAC by creating a separate connection

main()
{
    // DB Connection
 
    // Create conneciton 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 version 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;
        ......
    }
   ...
}

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);
        }
    }
 
}

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.

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;
}

// 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;
}

// 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;

EXEC SQL WHENEVER SQLERROR DO user_function();
EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL WHENEVER SQLERROR GOTO ERROR_ROUTINE;
EXEC SQL WHENVER SQLERROR CONTINUE;

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
}

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;
   }
}