Overview

This provides sample code to test the basic usage of the WITH clause using a cursor through tbESQL/C.

Method

Test Code

Create TEST Table & Insert Data

CREATE TABLE EMPLOYEE (
       emp_id               NUMBER(7) NOT NULL CONSTRAINT emp_id_pk PRIMARY KEY,
       emp_name             VARCHAR(10) NULL,
       hiredate             DATE NULL,
       salary               NUMBER(8,2) NULL,
       comm                 NUMBER(8,2) NULL,
       dept_id              NUMBER(2) NULL
);
insert into employee values(2001043,'STEVE',to_date('2004-01-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),2500,1000,10);
insert into employee values(2003122,'PAUL',to_date('2004-02-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),3000,1000,10);
insert into employee values(2004001,'MICHAEL',to_date('2004-01-02 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),4000,500,10); 
insert into employee values(2004098,'DAVID',to_date('2004-03-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),3000,100,10); 
insert into employee values(2005287,'JOHN',to_date('2004-01-10 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),5000,400,10); 
insert into employee values(2006121,'LINCOLN',to_date('2003-07-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),2800,2000,10); 
insert into employee values(2007112,'FERNANDO',to_date('2003-08-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),3400,1500,10); 
insert into employee values(2006246,'WARDEN',to_date('2004-11-21 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),6000,2100,10); 
insert into employee values(2001397,'JANE',to_date('2003-11-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),2000,'',10); 
insert into employee values(1999235,'THEODORE',to_date('2004-01-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),3500,'',20); 
insert into employee values(1998232,'KAREN',to_date('2004-11-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),4000,1000,20);
insert into employee values(1998234,'BILL',to_date('2004-05-05 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),2900,'',20);
insert into employee values(2003465,'SANDRA',to_date('2004-06-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),3800,2000,20);
insert into employee values(2005853,'CHARLES',to_date('2004-08-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),4000,'',20);
insert into employee values(2001267,'WAYNE',to_date('2004-11-12 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),3400,1300,30);
insert into employee values(2002658,'JAMES',to_date('2004-12-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'),4400,1500,30);
commit;

Sample Code [sample.tbc]

#include <stdio.h>
#include <sqlca.h>
#include <stdlib.h>
#include <string.h>

int TIBERO_CONNECT();

int main()
{
    if (TIBERO_CONNECT() != 0)
        exit(1);

    return 0;
}

/** db connect **/
int TIBERO_CONNECT()
{
    EXEC SQL BEGIN DECLARE SECTION;
        VARCHAR username[128];
        VARCHAR password[32];
        VARCHAR EMP_NAME[24];
    EXEC SQL END DECLARE SECTION;

    int sqlcode = 0;

    printf("\n\n 01_CONNECT ");
    printf("\n\n username : ");

    fgets((char *)username.arr, sizeof(username.arr), stdin);
    username.arr[strlen((char *)username.arr) - 1] = '\0';
    username.len = (unsigned short)strlen((char *)username.arr);

    printf(" password : ");
    fgets((char *)password.arr, sizeof(password.arr), stdin);
    password.arr[strlen((char *)password.arr) - 1] = '\0';
    password.len = (unsigned short)strlen((char *)password.arr);

    EXEC SQL WHENEVER SQLERROR GOTO connect_error;

    EXEC SQL CONNECT :username IDENTIFIED BY :password;

    printf("\n\n Connected to Tibero as user %s.\n", username.arr);

    EXEC SQL DECLARE dual_cursor CURSOR FOR
        WITH employees AS (SELECT * FROM Employee)
        SELECT EMP_NAME FROM employees WHERE SALARY < 3000
        UNION ALL
        SELECT EMP_NAME FROM employees WHERE DEPT_ID = 10;

    EXEC SQL OPEN dual_cursor;
    EXEC SQL WHENEVER NOT FOUND DO break;

    printf("\n Sample Test Query\n");

    while (1) {
        EXEC SQL FETCH dual_cursor INTO :EMP_NAME;

        EMP_NAME.arr[EMP_NAME.len] = '\0';
        printf("\n EMP_NAME = %s", EMP_NAME.arr);

        if (sqlca.sqlcode == 0)
            sqlcode = 0;
        else
            sqlcode = -1;
    }

    if (sqlcode == 0)
        printf("\n\n\n Operation Success!\n");
    else
        printf("Operation Fail sqlerrd: [%d]\n", sqlca.sqlerrd[2]);

    EXEC SQL CLOSE dual_cursor;

    return 0;

connect_error:
    fprintf(stderr, "Cannot connect to Tibero as user %s\n", username.arr);
    return -1;
}

Test Procedure

1. Precompile
$ tbpc sample.tbc

2. Compile&Link
cc -o sample sample.c -L$TB_HOME/client/lib -ltbertl -ltbcli -lpthread -lm -I$TB_HOME/client/include

3. Result
$ ./sample


 01_CONNECT 

 username : tibero
 password : tmax


 Connected to Tibero as user tibero1.

 Sample Test Query

 EMP_NAME = STEVE
 EMP_NAME = LINCOLN
 EMP_NAME = JANE
 EMP_NAME = BILL
 EMP_NAME = STEVE
 EMP_NAME = PAUL
 EMP_NAME = MICHAEL
 EMP_NAME = DAVID
 EMP_NAME = JOHN
 EMP_NAME = LINCOLN
 EMP_NAME = FERNANDO
 EMP_NAME = WARDEN
 EMP_NAME = JANE


 Operation Success!