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.

  Code Block
  none none
  SQLCODE = -331880 sqlca.sqlerrm.sqlerrmc = Returns too many rows

• APRE can use a host-variable into "SELECT target" clauses.

  Code Block
  c c
  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.

  Code Block
  none none
  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.

  Code Block
  none none
  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.

  Code Block
  none none
  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.

  Code Block
  none none
  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.

  Code Block
  none none
  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';

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

  Code Block
  c c
  EXEC SQL DECLARE cursor_name CURSOR FOR SELECT ename, sal FROM employee WHERE dept_no = :in_dept_no;

• OPEN CURSOR

  Code Block
  c c
  EXEC SQL OPEN cursor_name;

• FETCH CURSOR

  Code Block
  c c
  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

  Code Block
  c c
  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.

  Code Block
  none none
  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);
  }

}