Appendix A. tbESQL/C 프로그램 예제

DDL과 PSM, 데이터베이스의 이름, 대용량 객체형을 사용하여 tbESQL/C 프로그램의 예제를 설명합니다.

DDL과 PSM

다음은 DDL과 PSM을 사용하여 tbESQL/C 프로그램을 작성한 예입니다.

void Test_call1(void)
{
    char *conn_str = "tibero/tmax"; 
    int num;
    int fact;
    
    EXEC SQL CONNECT :conn_str;
    
    EXEC SQL CREATE OR REPLACE FUNCTION fact(n IN INTEGER) RETURN INTEGER IS 
    BEGIN
        IF (n <= 0) then return 1; 
        ELSE return n * fact(n - 1); 
        END IF;
    END fact;
    END-EXEC;
    
    num = 5;
    
    EXEC SQL CALL fact(:num) INTO :fact; 
    
    EXEC SQL COMMIT WORK RELEASE;
}

void Test_call2(void)
{
    char *conn_str = "tibero/tmax"; 
    char x[10];
    int y;
    
    EXEC SQL CONNECT :conn_str; 
    
    EXEC SQL DROP TABLE T1;
    EXEC SQL CREATE TABLE T1(x VARCHAR(10));
    EXEC SQL CREATE OR REPLACE PROCEDURE test_proc(x IN VARCHAR, y IN OUT NUMBER) IS 
    
        BEGIN
            INSERT INTO T1 VALUES(x); 
            y := 2;
        END;
    END-EXEC;
    
    memset(x, 0, sizeof(x));
    strcpy(x, "TIBERO");
    
    EXEC SQL CALL test_proc(:x, :y); 
    
    memset(x, 0, sizeof(x));
    
    EXEC SQL SELECT x INTO :x FROM T1; /* x값은 "TIBERO "가 됩니다 */
    
    EXEC SQL COMMIT WORK RELEASE;
}

데이터베이스의 이름

다음은 데이터베이스의 이름을 사용하여 tbESQL/C 프로그램을 작성한 예입니다.

void Test_named_database(void)
{
    VARCHAR branch[5], postal[10], region[10]; 
    
    char *user_pass = "tibero/tmax";
    
    EXEC SQL WHENEVER SQLERROR
        DO printf("file: %s, line: %d, error: %d\n",
                __FILE__, __LINE__, sqlca.sqlcode); 
                
    EXEC SQL DECLARE C1 DATABASE;
    
    EXEC SQL CONNECT :user_pass AT C1;
    
    EXEC SQL at C1 DECLARE C1 CURSOR FOR
                select * from branch order by branch_cd; 
                
    EXEC SQL OPEN C1;
    
    memset(branch.arr, 0, 5);
    memset(postal.arr, 0, 10);
    memset(region.arr, ' ', 10);
    
    EXEC SQL FETCH C1 INTO :branch, :postal, :region; 
    
    memset(branch.arr, 0, 5);
    memset(postal.arr, 0, 10); 
    memset(region.arr, ' ', 10);
    
    EXEC SQL FETCH C1 INTO :branch, :postal, :region; 
    
    EXEC SQL CLOSE C1;
    
    EXEC SQL AT C1 COMMIT;
}

대용량 객체형

다음은 대용량 객체형을 사용하여 tbESQL/C 프로그램을 작성한 예입니다.

ClobLocator, BlobLocator의 사용을 위해서는 esql_lob.h 헤더 파일을 명시해야 합니다.

#include <esql_lob.h> 

#define MAXBUFLEN 5000

char *conn_str = "tibero/tmax";

void Test_clob_write(void)
{
    ClobLocator *clob;
    unsigned int clob_len, write_amt, offset; 
    unsigned char buffer[MAXBUFLEN];
    int i;
    
    EXEC SQL CONNECT :conn_str; 
    
    EXEC SQL DROP TABLE CLOB_TABLE; 
    
    EXEC SQL WHENEVER SQLERROR
        do printf("error: %d!!!\n", sqlca.sqlcode);
        
    EXEC SQL WHENEVER NOTFOUND
        do printf("notfound!!!\n");
        
    EXEC SQL CREATE TABLE CLOB_TABLE (num_col number, clob_col clob); 
    
    EXEC SQL INSERT INTO CLOB_TABLE VALUES (1, empty_clob());
    EXEC SQL ALLOCATE :clob;
    
    EXEC SQL SELECT clob_col INTO :clob FROM CLOB_TABLE WHERE num_col = 1; 
    
    EXEC SQL LOB DESCRIBE :clob GET LENGTH INTO :clob_len;
    
    write_amt = MAXBUFLEN; 
    offset = clob_len + 1;
    
    for (i = 0; i < MAXBUFLEN; i += 25) {
        strcpy((char *)(buffer + i), "abcdefghijklmnopqrstuvwxy");
    }
    
    EXEC SQL LOB WRITE ONE :write_amt FROM :buffer 
        WITH LENGTH :write_amt INTO :clob AT :offset;
        
    EXEC SQL FREE :clob;
    
    EXEC SQL COMMIT RELEASE;
}

void Test_clob_read(void)
{
    ClobLocator *clob;
    unsigned int clob_len, read_amt; 
    unsigned char buffer[26];
    
    memset(buffer, 0, sizeof(buffer)); 
    
    EXEC SQL CONNECT :conn_str;
    
    EXEC SQL ALLOCATE :clob;
    
    EXEC SQL SELECT clob_col INTO :clob FROM CLOB_TABLE WHERE num_col = 1; 
    
    EXEC SQL LOB DESCRIBE :clob GET LENGTH INTO :clob_len;
    
    read_amt = 25;
    
    EXEC SQL LOB READ :read_amt FROM :clob 
        INTO :buffer WITH LENGTH :read_amt;
        
    EXEC SQL FREE :clob;
    
    EXEC SQL COMMIT RELEASE;
}

void Test_blob_write(void)
{
    BlobLocator *blob;
    unsigned int blob_len, offset; 
    unsigned char buffer[MAXBUFLEN * 2]; 
    FILE *fp;
    
    memset(buffer, 0, sizeof(buffer));
    
    EXEC SQL CONNECT :conn_str;

    EXEC SQL WHENEVER SQLERROR CONTINUE;

    EXEC SQL DROP TABLE BLOB_TABLE;

    EXEC SQL WHENEVER SQLERROR
        do printf("error: %d!!!\n", sqlca.sqlcode); 
        
    EXEC SQL WHENEVER NOTFOUND
        do printf("notfound!!!\n");
        
    EXEC SQL CREATE TABLE BLOB_TABLE (blob_col blob); 
    
    EXEC SQL ALLOCATE :blob;
    
    EXEC SQL INSERT INTO BLOB_TABLE VALUES (empty_blob())
        RETURNING blob_col INTO :blob;
        
    fp = fopen((const char *)"./test_blob.jpeg", (const char *)"r"); 
    fseek(fp, 0L, SEEK_END);
    blob_len = (unsigned int)ftell(fp);
    
    fseek(fp, 0L, SEEK_SET);
    fread((void *)buffer, (size_t)blob_len, (size_t)1, fp); 
    offset = 1;
    
    EXEC SQL LOB WRITE ONE :blob_len FROM :buffer 
        WITH LENGTH :blob_len INTO :blob AT :offset;
        
    fclose(fp);
    
    EXEC SQL FREE :blob;
    
    EXEC SQL COMMIT RELEASE;
}
void Test_blob_read(void)
{
    ClobLocator *blob;
    unsigned int blob_len, read_amt; 
    unsigned char org_buffer[MAXBUFLEN * 2]; 
    unsigned char buffer[MAXBUFLEN * 2]; 
    FILE *fp;

    memset(buffer, 0, sizeof(org_buffer));
    memset(buffer, 0, sizeof(buffer));
    
    EXEC SQL CONNECT :conn_str;

    EXEC SQL ALLOCATE :blob;

    EXEC SQL SELECT blob_col INTO :blob FROM BLOB_TABLE;

    EXEC SQL LOB DESCRIBE :blob GET LENGTH INTO :blob_len;

    read_amt = MAXBUFLEN * 2;

    EXEC SQL LOB READ :blob_len FROM :blob 
        INTO :buffer WITH LENGTH :read_amt;

    fp = fopen((const char *)"TESTCASE/pc_success/test_blob.jpeg", 
               (const char *)"r");
    fread((void *)org_buffer, (size_t)blob_len, (size_t)1, fp); 
    
    fclose(fp);
    
    EXEC SQL FREE :blob;

    EXEC SQL COMMIT RELEASE;
}