Overview
SELECT
INSERT
UPDATE
DELETE
MOVE
DML Returning
CURSOR-FETCH
SCROLLABLE CURSOR
Sample Code

Overview

APRE is similar to ORACLE-style precompiler(PRO*C).

Maybe, you may easily find a few differences from ALTIBASE HDB and ORACLE.

SELECT

...
char out_name[40];
int  out_age;
char in_var[40];
...
EXEC SQL
  SELECT name, age
  INTO :out_name, :out_age
  FROM employee
  WHERE ename = :in_var;

SELECT statement can get only one record as a result. If you want to get multiple records, you have to use host variable arrays into "INTO" clauses.
If you use a single host variable, you may return an error as follows.

?
SQLCODE = -331880
sqlca.sqlerrm.sqlerrmc = Returns too many rows
APRE can use a host-variable into "SELECT target" clauses.

?
char alias[10];
char name[40];
int salary;
int rate;
...
SELECT name || CAST (:alias as CHAR(10)),
       salary * CAST (:rate as INTEGER)
INTO   :name,
       :salray
FROM employee;
If SELECT can not find a target-record, you can see an error as follows.

?
SQLCODE = 100
sqlca.sqlerrm.sqlerrmc = Not found data

INSERT

EXEC SQL INSERT INTO table (C1, C2, C3)
VALUES (:c1, :c2, :c3);
 
EXEC SQL INSERT INTO table (c1, c2, c3) SELECT c1, c2, c3 FROM src_table;

If a table has an unique constraints, you can see an error as follows.

?
SQLCODE = -69720
sqlca.sqlerrm.sqlerrmc = The row already exists in a unique index.
If you try to insert a null value into column having not null constraint, you can encounter an error as follows.

?
SQLCODE = -200820
sqlca.sqlerrm.sqlerrmc = Unable to insert(or update) NULL into NOT NULL column.

UPDATE

EXEC SQL UPDATE table SET col1 = :in_var1

WHERE name = :in_name;

If UPDATE can not find a target-record, you can see an error as follows.

?
SQLCODE = 100
sqlca.sqlerrm.sqlerrmc = Not found data

DELETE

EXEC SQL DELETE FROM table WHERE name = :in_name;

If DELETE can not find a target-record, you can encounter an error as follows.

?
SQLCODE = 100
sqlca.sqlerrm.sqlerrmc = Not found data

MOVE

Move-statement is supported only by ALTIBASE HDB.
This functionality is commonly used for moving a records between tables from memory table to disk table vice versa.

// Table A : memory table
// Table B : disk table
EXEC SQL MOVE INTO table_b FROM table_a
WHERE data < '20120101';

Icon

You can locate some tables into memory for performance.

but, Physical memory has a limitation of space.
Therefore, When you need to move a records of memory-table to disk-table, you can use move-statement.

DML Returning

ORACLE can return a record changed after INSERT, UPDATE DML or before DELETE DML.
But, ALTIBASE HDB doesn't support this functionality unlike ORACLE.
This functionality will be applied in ALTIBASE HDB V7.

CURSOR-FETCH

You can manipulate a Cursor as below.

DECLARE CURSOR

?
EXEC SQL DECLARE cursor_name CURSOR FOR
SELECT ename, sal
FROM employee
WHERE dept_no = :in_dept_no;
OPEN CURSOR

?
EXEC SQL OPEN cursor_name;

FETCH CURSOR

EXEC SQL DECLARE cursor_name CURSOR FOR
SELECT ename, sal
FROM employee
WHERE dept_no = :in_dept_no;
...
EXEC SQL OPEN cursor_name ;
...
while (1)
{
  EXEC SQL FETCH cursor_name INTO :ename, :sal;
  if (SQLCODE == SQL_NO_DATA)
      break;
}

CLOSE CURSOR

?
EXEC SQL OPEN cursor_name ;
...
while (1)
{
  EXEC SQL FETCH cursor_name INTO :ename, :sal;
  if (SQLCODE == SQL_NO_DATA)
      break;
}
EXEC SQL CLOSE cursor_name;

The cursor-name needs to be unique in your application.
You have to check an error on "DECLARE" and "OPEN" clauses.
After you don't check an error on "DECLARE" phase, The error happens at "OPEN" phase.
But, As you are not sure of the reason why error generated, you have to check an error at "DECLARE" phase.

?
SQLCODE = -1
sqlca.sqlerrm.sqlerrmc = The cursor must be declared for open. (Name: cursor1)

SCROLLABLE CURSOR

ALTIBASE HDB doesn't support a scrollable-cursor.

Also, this functionality will be applied in the ALTIBASE HDB V7(Updatable Scrollable Cursor).

Sample Code

# include <stdio.h>
 
main()
{
  EXEC SQL BEGIN DECLARE SECTION;
    char usr[20];
    char pwd[20];
    char opt[200];
 
    char ename[40+1];
    int  sal;
  EXEC SQL END DECLARE SECTION;
 
  // CONNECT TO DB
  sprintf (usr, "sys");
  sprintf (pwd, "manager");
  sprintf (opt, "DSN=127.0.0.1;PORT_NO=20300;CONNTYPE=1;NLS_USE=MS949");
 
  EXEC SQL CONNECT :usr IDENTIFIED BY :pwd USING  :opt;
  if (sqlca.sqlcode != SQL_SUCCESS)
  {
      printf("ERROR_CONNECT_DB] %d, %s\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
      exit(-1);
  }
 
 
  // CREATE TABLE
  EXEC SQL CREATE TABLE employee (name CHAR(40), sal INTEGER);
  if (sqlca.sqlcode != SQL_SUCCESS)
  {
      printf("ERROR_CREATE_TABLE] %d, %s\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
      exit(-1);
  }
 
 
  // INSERT a record
  sprintf(ename, "Andy Park");
  sal = 1000000;
  EXEC SQL INSERT INTO employee VALUES (:ename, :sal);
  if (sqlca.sqlcode != SQL_SUCCESS)
  {
      printf("ERROR_INSERT_TABLE] %d, %s\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
      exit(-1);
  }
 
  // CURSOR ~ FETCH a record
  sprintf(ename, "Andy Park");
 
  EXEC SQL DECLARE CURSOR1 CURSOR FOR
  SELECT name, sal
  FROM employee
  WHERE name = :ename;
  if (sqlca.sqlcode != SQL_SUCCESS)
  {
      printf("ERROR_DECLARE_TABLE] %d, %s\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
      exit(-1);
  }
 
  EXEC SQL OPEN CURSOR1;
  if (sqlca.sqlcode != SQL_SUCCESS)
  {
      printf("ERROR_OPEN_TABLE] %d, %s\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
      exit(-1);
  }
 
  while (1)
  {
    EXEC SQL FETCH CURSOR1 INTO :ename, :sal;
    if (sqlca.sqlcode != SQL_SUCCESS && sqlca.sqlcode != SQL_NO_DATA)
    {
       printf("ERROR_FETCH_TABLE] %d, %s\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
       exit(-1);
    } else if (sqlca.sqlcode == SQL_NO_DATA) break;
  }
  EXEC SQL CLOSE CURSOR1;
 
  // DROP TABLE
  EXEC SQL DROP TABLE employee ;
  if (sqlca.sqlcode != SQL_SUCCESS)
  {
      printf("ERROR_DROP_TABLE] %d, %s\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
      exit(-1);
  }
 
}

Basic SQL Statement