9. Developing Altibase Applications#

This chapter will provide an overview of the process of authoring client applications that access Altibase.

Writing Client Application Programs#

Developing Altibase applications using the Altibase application program interface (API) for Altibase CLI, JDBC, ODBC, the C/C++ precompiler.

This chapter will briefly introduce the process of authoring client applications for use with Altibase.

For more detailed information about writing client applications, please refer to the CLI User's Manual, ODBC User's Manual, Precompiler User's Manual, and the API User's Manual.

Applications Using Altibase CLI#

This section explains how to write client applications using Altibase CLI. Altibase CLI is an API that can be used in an environment where Altibase is operated in the client-server structure. For more detailed information, please refer to the CLI User's Manual.

Header Files and Libraries#

To develop a program using the Altibase CLI, you need the header files and library files in the "include" and "lib" subdirectories of the Altibase installation home directory as follows:

$ALTIBASE_HOME/include/sqlcli.h
$ALTIBASE_HOME/lib/libodbccli.a

Makefile#

In order to compile the Altibase CLI source code in your program, the following must be included in the Makefile:

include $(ALTIBASE_HOME)/install/altibase_env.mk

This file includes links to library paths and libraries that are needed at compile time, as well as instructions for making object files. Please refer to the sample Makefile in $ALTIBASE_HOME/sample/SQLCLI

Example#

include $(ALTIBASE_HOME)/install/altibase_env.mk

SRCS=
OBJS=$(SRCS:.cpp=.$(OBJEXT))

BINS=demo_ex1

all: $(BINS)

demo_ex1: demo_ex1.$(OBJEXT)
    $(LD) $(LFLAGS) $(LDOUT)demo_ex1$(BINEXT) demo_ex1.$(OBJEXT) $(LIBOPT)odbccli$(LIBAFT) $(LIBS)

Multi-threaded Programming#

When developing a multi-threaded program, you should be aware of the following:

Must allocate environment handles and connection handles for each thread.

Writing Application#

The following code shows how to connect to and disconnect from an Altibase server in a program that uses Altibase CLI:

Altibase CLI Code Sample#

/* test.cpp */

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>
#include <sys/time.h>
#include <unistd.h>
#include <sqlcli.h>

void sbigint_bigint(int cnt);  
void slong_integer(int cnt);
void char_char(int cnt);
void char_number(int cnt);
void double_double(int cnt);
void prepare();
void execute();
void usage();
long logMsec(const char *astr);
void conn(char *port, char *conntype);

#define MSG_LEN 1024  

SQLHENV   env;  // Handle for the environment.
SQLHDBC   con;  // Handle for the connection.
SQLHSTMT  hstmt;  // Handle for a statement.
SQLHSTMT  bstmt;  // Handle for a statement.
int          errNo;
short        msgLength;
char         errMsg[MSG_LEN];
SQLRETURN rc;

/* Main Program - Usage output when the number of arguments is less than 5 */
int main(int ac, char **av)  
{
    if (ac < 5) 
    {
        usage();
    }

    conn(av[2], av[3]);

    switch(atoi(av[1]))
    {
    case 1:
        logMsec(" BIGINT - START TIME : ");
        sbigint_bigint(atoi(av[4]));
        logMsec(" BIGINT - END TIME : ");
        break;
    case 2:
        logMsec(" INTEGER - START TIME : ");
        slong_integer(atoi(av[4]));
        logMsec(" INTEGER - END TIME : ");
        break;
    case 3:
        logMsec(" CHAR - START TIME : ");
        char_char(atoi(av[4]));
        logMsec(" CHAR - END TIME : ");
        break;
    case 4:
        logMsec(" NUMBER - START TIME : ");
        char_number(atoi(av[4]));
        logMsec(" NUMBER - END TIME : ");
        break;
    case 5:
        logMsec(" DOUBLE - START TIME : ");
        double_double(atoi(av[4]));
        logMsec(" DOUBLE - END TIME : ");
        break;
    }
}

/* print the usage of the program */
void usage()
{
    printf("Usage: ./test <program_no> <port_no> <conntype> <cnt>\n");
    printf("\tprogram_no : 1  => \t SBIGINT-BIGINT\n");
    printf("\tprogram_no : 2  => \t SLONG-INTEGER\n");
    printf("\tprogram_no : 3  => \t CHAR-CHAR\n");
    printf("\tprogram_no : 4  => \t CHAR-NUMERIC\n");
    printf("\tprogram_no : 5  => \t DOUBLE-DOUBLE\n");
    exit(1);
}

/* Check the starting time and the ending time of the program */
long logMsec(const char *astr)
{
    struct timeval  tv;
    struct tm       *ctm;

    gettimeofday(&tv,NULL);
    ctm = localtime(&(tv.tv_sec));

    fprintf(stderr, "%s [%.02d:%.02d:%.02d]\n", astr, ctm->tm_hour, 
ctm->tm_min, ctm->tm_sec);

    return tv.tv_usec;
}

/* Altibase connection statement */
void conn(char *port, char *conntype)
{
    char connStr[200];
    char query[200];

    if (SQL_ERROR == SQLAllocEnv(&env))
    {
        fprintf(stderr, "SQLAllocEnv error!!\n"); 
//Memory allocation for the environment.
        return;
    }

    if (SQL_ERROR == SQLAllocConnect(env, &con)) 
// Memory allocation for a connection
    {
        fprintf(stderr, "SQLAllocConnect error!!\n");
        SQLFreeEnv(env);
        return;
    }

    sprintf((char*)connStr, "DSN=127.0.0.1;PORT_NO=%s;UID=SYS;PWD=MANAGER;CONNTYPE=%s", port, conntype);

/* Connection creation */
    if (SQL_ERROR == SQLDriverConnect(con, NULL, (char*)connStr,
SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT))
    {
        if (SQL_SUCCESS == SQLError(env, con, NULL, NULL, &errNo, (char*)errMsg, MSG_LEN, &msgLength)) 
        {
            fprintf(stderr, " rCM_-%d : %s\n", errNo, errMsg);
        }
        SQLFreeConnect(con);  
        SQLFreeEnv(env);
        return;
    }
/* Not automatically reflected upon execution of each SQL statement */
    SQLSetConnectAttr(con, SQL_ATTR_AUTOCOMMIT, 
(void*)SQL_AUTOCOMMIT_OFF, 0);
    if (rc == SQL_ERROR)              
    {                                 
        if (SQL_SUCCESS == SQLError(env, con, NULL, NULL, &errNo, (char*)errMsg, MSG_LEN, &msgLength))
        {
            fprintf(stderr, "[%d : %s]\n", errNo, errMsg);
        }
    }

    hstmt = bstmt = SQL_NULL_HSTMT;  

    SQLAllocStmt(con, &hstmt);
    SQLAllocStmt(con, &bstmt);

/* Executing the DDL statement directly and output the message in the defined format into a file. */
    strcpy(query, "drop table t1");   
    rc = SQLExecDirect(hstmt, (char*)query, SQL_NTS);  
    if (rc == SQL_ERROR)
    {
        if (SQL_SUCCESS == SQLError(env, con, hstmt, NULL, &errNo, (char*)errMsg, MSG_LEN, &msgLength))
        {
            fprintf(stderr, "[%d : %s]\n", errNo, errMsg); 
}
    }

/* Directly executes DDL statements and when an error occurs, outputs the message to a file according to the specified format */
    strcpy(query, "create table t1(i1 number(6))");
    rc = SQLExecDirect(hstmt, (char*)query, SQL_NTS);
    if (rc == SQL_ERROR)
    {
        if (SQL_SUCCESS == SQLError(env, con, hstmt, NULL, &errNo, (char*)errMsg, MSG_LEN, &msgLength))
        {
            fprintf(stderr, "[%d : %s]\n", errNo, errMsg); 
}
    }
}

/* Preparing a SQL statement */
void prepare()   
{
    char query[100];

    strcpy(query, "insert into t1 values(?)");

    rc = SQLPrepare(bstmt, (char*)query, SQL_NTS);
    if (rc == SQL_ERROR)              
    {                                 
        if (SQL_SUCCESS == SQLError(env, con, bstmt, NULL, &errNo, (char*)errMsg, MSG_LEN, &msgLength))
        {
            fprintf(stderr, "[%d : %s]\n", errNo, errMsg);
        }
    }
}

/* Executing a prepared SQL statement */
void execute()  
{
    rc = SQLExecute(bstmt);
    if (rc == SQL_ERROR)              
    {                                 
        if (SQL_SUCCESS == SQLError(env, con, bstmt, NULL, &errNo, (char*)errMsg, MSG_LEN, &msgLength))
        {
            fprintf(stderr, "[%d : %s]\n", errNo, errMsg);
        }
    }
}

void sbigint_bigint(int cnt)
{
    int i;
    long long i1;
    char tmp[100];
    int len = SQL_NTS;

    prepare();

/* Binding parameters */
    SQLBindParameter(bstmt, 1, SQL_PARAM_INPUT, SQL_C_SBIGINT, 
SQL_BIGINT, 0, 0, (void*)&i1, 0, &len);

    for(i=0; i<cnt; i++)
    {
        sprintf(tmp, "%d", i);
        i1 = atol(tmp);
        execute();
    }

/* Process COMMIT transaction. */
    rc = SQLTransact(NULL, con, SQL_COMMIT); 
}

void slong_integer(int cnt)
{
    int i;
    int i1;
    char tmp[100];
    int len = SQL_NTS;

    prepare();

/* Connect parameter to SQL statement */
    SQLBindParameter(bstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, 
SQL_INTEGER, 0, 0, (void*)&i1, 0, &len);

    for(i=0; i<cnt; i++)
    {
        sprintf(tmp, "%d", i);
        i1 = atoi(tmp);
        execute();
    }

/* COMMIT a transaction. */
    SQLTransact(NULL, con, SQL_COMMIT);
}

void char_char(int cnt)
{
    int  i;
    char i1[100];
    char tmp[100];
    int len = SQL_NTS;

    prepare();

/* Connect parameter to SQL statement */
    SQLBindParameter(bstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, 
SQL_CHAR, sizeof(i1)-1, 0, (void*)i1, sizeof(i1), &len);

    for(i=0; i<cnt; i++)
    {
        sprintf(tmp, "%d", i);
        strcpy(i1, tmp);
        execute();
    }

/* COMMIT a transaction */
    SQLTransact(NULL, con, SQL_COMMIT);
}

void char_number(int cnt)
{
    int  i;
    char i1[100];
    char tmp[100];
    int len = SQL_NTS;

    prepare();

    SQLBindParameter(bstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, 
SQL_NUMERIC, sizeof(i1)-1, 0, (void*)i1, sizeof(i1), 
&len);

    for(i=0; i<cnt; i++)
    {
        sprintf(tmp, "%d", i);
        strcpy(i1, tmp);
        execute();
    }

/* COMMIT a transaction. */
    SQLTransact(NULL, con, SQL_COMMIT);
}

void double_double(int cnt)
{
    int  i;
    double i1;
    char tmp[100];
    int len = SQL_NTS;

    prepare();

    SQLBindParameter(bstmt, 1, SQL_PARAM_INPUT, SQL_C_DOUBLE, 
SQL_DOUBLE, 0, 0, (void*)&i1, 0, &len);

    for(i=0; i<cnt; i++)
    {
        sprintf(tmp, "%d", i);
        i1 = atof(tmp);
        execute();
    }

/* COMMIT a transaction. */
    SQLTransact(NULL, con, SQL_COMMIT);
}

Execution Results#

$ make test
$ ./test 3 20300 1 100

BIGINT - START TIME :  [16:43:48]
BIGINT - END TIME :  [16:43:49]

Applications Using JDBC#

The following describes how to create a client application that uses the JDBC driver of Altibase. For more information about the JDBC driver of Altibase, please refer to the JDBC User's Manual.

JDBC Driver#

By default, Altibase provides the JDBC driver file, altibase.jar in the $ALTIBASE_HOME/lib directory.

To establish a connection with the Altibase server, first, load the driver and try to connect to the URL. The URL format supported by Altibase JDBC driver is as follows:

jdbc:Altibase://hostname:portnum/databasename

When loading the JDBC driver, register the JDBC driver with the following code in the program and use it:

Class.forName("Altibase.jdbc.driver.AltibaseDriver")

Usually, the URL is provided and an attempt to connect to the URL is made as follows: (if the id for logging into the databas is "SYS" and the password is "MANAGER")

String url = "jdbc:Altibase://127.0.0.1:20300/mydb";
Connection con = DriverManager.getConnection(url, "SYS", "MANAGER");

CLASSPATH#

The Altibase.jar file must be included in the CLASSPATH for JDBC application execution:

$ export CLASSPATH=$ALTIBASE_HOME/lib/Altibase.jar:$CLASSPATH

Writing Applications#

This sample program code shows how to connect to and disconnect from a database using JDBC APIs of Altibase.

JDBC Code Sample#

/* SimpleSQL.java */
import java.util.Properties;
import java.sql.*;

class SimpleSQL
{
    public static void main(String args[]) {

        Properties        sProps   = new Properties();
        Connection        sCon     = null;
        Statement         sStmt    = null;
        PreparedStatement sPreStmt = null;
        ResultSet         sRS;

        if ( args.length == 0 )
        {
            System.err.println("Usage : java class_name port_no");
           System.exit(-1);
        }

        String sPort     = args[0];
        String sURL      = "jdbc:Altibase://127.0.0.1:" + sPort + "/mydb";
        String sUser     = "SYS";
        String sPassword = "MANAGER";

        sProps.put( "user",     sUser);
        sProps.put( "password", sPassword);
        // sProps.put( "encoding", sEncoding );

        /* Deploy Altibase's JDBC Driver  */
        try
        {
            Class.forName("Altibase.jdbc.driver.AltibaseDriver");
        }
        catch ( Exception e )
        {
            System.out.println("Can't register Altibase Driver");
            System.out.println( "ERROR MESSAGE : " + e.getMessage() );
            System.exit(-1);
        }

        /* Initialize environment */
        try
        {
            sCon = DriverManager.getConnection( sURL, sProps );
            sStmt = sCon.createStatement();
        }
        catch ( Exception e )
        {
            System.out.println( "ERROR MESSAGE : " + e.getMessage() );
            e.printStackTrace();
        }

        try
        {
            sStmt.execute( "DROP TABLE TEST_EMP_TBL" );
        }
        catch ( SQLException e )
        {
        }

        try
        {
           sStmt.execute( "CREATE TABLE TEST_EMP_TBL " +
                           "( EMP_FIRST VARCHAR(20), " +
                           "EMP_LAST VARCHAR(20), " +
                           "EMP_NO INTEGER )" );

            sPreStmt = sCon.prepareStatement( "INSERT INTO TEST_EMP_TBL " +
                                              "VALUES( ?, ?, ? )" );

            sPreStmt.setString( 1, "Susan" );
            sPreStmt.setString( 2, "Davenport" );
            sPreStmt.setInt(    3, 2 );
            sPreStmt.execute();

            sPreStmt.setString( 1, "Ken" );
            sPreStmt.setString( 2, "Kobain" );
            sPreStmt.setInt(    3, 3 );
            sPreStmt.execute();

            sPreStmt.setString( 1, "Aaron" );
            sPreStmt.setString( 2, "Foster" );
            sPreStmt.setInt(    3, 4 );
            sPreStmt.execute();

            sPreStmt.setString( 1, "Farhad" );
            sPreStmt.setString( 2, "Ghorbani" );
            sPreStmt.setInt(    3, 5 );
            sPreStmt.execute();

            sPreStmt.setString( 1, "Ryu" );
            sPreStmt.setString( 2, "Momoi" );
            sPreStmt.setInt(    3, 6 );
            sPreStmt.execute();

            sRS = sStmt.executeQuery( "SELECT EMP_FIRST, EMP_LAST," +
                                      " EMP_NO FROM TEST_EMP_TBL " );

            /* Fetch all data */
            while( sRS.next() )
            {
                System.out.println( "  EmpName : " + sRS.getString(1) +
                                   " " + sRS.getString(2) );
                System.out.println( "  EmpNO   : " + sRS.getInt(3) );
            }

            /* Finalize process */
            sStmt.close();
            sPreStmt.close();
            sCon.close();
        }
        catch ( SQLException e )
        {
            System.out.println( "ERROR CODE    : " + e.getErrorCode() );
            System.out.println( "ERROR MESSAGE : " + e.getMessage() );
            e.printStackTrace();
        }
    }
}

Execution Results#

$ javac SimpleSQL.java      
$ java SimpleSQL 20300
  EmpName : Susan Davenport
  EmpNO   : 2
  EmpName : Ken Kobain
  EmpNO   : 3
  EmpName : Aaron Foster
  EmpNO   : 4
  EmpName : Farhad Ghorbani
  EmpNO   : 5
  EmpName : Ryu Momoi
  EmpNO   : 6

Applications Written Using the C/C++ Precompiler#

Altibase C/C++ precompiler converts source code that contains embedded SQL statements to run-time library calls, and creates a new source program that can be compiled in the host language. For more detailed information, please refer to the Precompiler User's Manual.

Environment Settings#

The following environment settings must be made in order to compile and link files precompiled using the C/C++ precompiler.

Header File#

The necessary header file is ulpLibInterface.h, and is located in $ALTIBASE_HOME/include/.

To compile a precompiled programs, the following compiler option must be used:

-I$ALTIBASE_HOME/include

Library#

The necessary library files are libapre.a and libodbccli.a, which are located in the $ALTIBASE_HOME/lib directory.

To link the precompiled application program, the following options must be used:

-L$ALTIBASE_HOME/lib -lapre -lodbccli -lpthread

Precompiling#

The C/C++ precompiler converts code that was written in C or C++ and contained embedded SQL statements to a C or C++ application.

The input file, which contains the code written in C or C++, has the .sc filename extension, and the output file has the .c or .cpp filename extension. While the default filename extension of the output file is .c, the user can set this freely as desired.

Precompiling embedded C/C++ programs#

The following example shows the use of various options when precompiling:

$ apre -h
=====================================================================
APRE (Altibase Precompiler) C/C++ Precompiler HELP Screen
=====================================================================
Usage  :  apre [<options>] <filename>

-h               : Display this help information.
-t <c|cpp>       : Specify the file extension for the output file.
                   c   - File extension is '.c' (default)
                   cpp - File extension is '.cpp'
-o <output_path> : Specify the directory path for the output file.
                   (default : current directory)
-mt              : When precompiling a multithreaded application,
                   this option must be specified.
-I<include_path> : Specify the directory paths for files included using APRE C/C++.
                   (default : current directory)
-parse <none|partial|full>
                 : Control which non-SQL code is parsed.
-D<define_name>  : Use to define a preprocessor symbol.
-v               : Output the version of APRE.
-n               : Specify when CHAR variables are not null-padded.
-unsafe_null     : Specify to suppress errors when NULL values are fetched
                   and indicator variables are not used.
-align           : Specify when using alignment in AIX.
-spill <values>  : Specify the register allocation spill area size.
-keyword         : Display all reserved keywords.
-debug <macro|symbol>
                 : Use for debugging.
                   macro   - Display macro table.
                   symbol  - Display symbol table.
-nchar_var <variable_name_list>
                 : Process the specified variables using
                   the Altibase national character set.
-nchar_utf16     : Set client nchar encoding to UTF-16.
-lines           : Add #line directives to the generated code.
-silent          : No display Copyright

======================================================================

Multi-threaded Programming#

The C/C++ Precompiler supports multi-threaded programs. Learn how to use the built-in SQL statements in multi-threaded programming:

In precompiling, the judgment of the multi-thread program should be provided to the precompiler as an option (-mt).
Each thread must have its own connection. In other words, multiple threads cannot share a single connection.
Only one connection with a connection name (i.e. default connection) is allowed in a program.
Must specify the connection name to be used in the embedded SQL statement.

Writing Applications#

Apre C/C++ Code Sample#

/******************************************************************
 * SAMPLE : DELETE
 *          1. Using scalar host variables
 *          2. Reference  : array host variables - arrays1.sc
 ******************************************************************/

int main()
{
    /* declare host variables */
    EXEC SQL BEGIN DECLARE SECTION;
    char usr[10];
    char pwd[10];
    char conn_opt[1024];

    /* scalar type */
    int      s_eno;
    short    s_dno;
    EXEC SQL END DECLARE SECTION;

    printf("<DELETE>\n");

    /* set username */
    strcpy(usr, "SYS");
    /* set password */
    strcpy(pwd, "MANAGER");
    /* set various options */
    strcpy(conn_opt, "Server=127.0.0.1"); /* Port=20300 */

    /* connect to altibase server */
    EXEC SQL CONNECT :usr IDENTIFIED BY :pwd USING :conn_opt;
    /* check sqlca.sqlcode */
    if (sqlca.sqlcode != SQL_SUCCESS)
    {
        printf("Error : [%d] %s\n\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
        exit(1);
    }

    /* use scalar host variables */

    s_eno = 5;
    s_dno = 1000;

    EXEC SQL DELETE FROM EMPLOYEES
                    WHERE ENO > :s_eno AND DNO > :s_dno AND EMP_JOB LIKE 'P%';

    printf("------------------------------------------------------------------\n");
    printf("[Scalar Host Variables]\n");
    printf("------------------------------------------------------------------\n");

    /* check sqlca.sqlcode */
    if (sqlca.sqlcode == SQL_SUCCESS)
    {
        /* sqlca.sqlerrd[2] holds the rows-processed(deleted) count */
        printf("%d rows deleted\n\n", sqlca.sqlerrd[2]);
    }
    else
    {
        printf("Error : [%d] %s\n\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
    }

    /* disconnect */
    EXEC SQL DISCONNECT;
    /* check sqlca.sqlcode */
    if (sqlca.sqlcode != SQL_SUCCESS)
    {
        printf("Error : [%d] %s\n\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
    }
}

Execution Result#

$ make delete

$ ./delete
<DELETE>
------------------------------------------------------------------
[Scalar Host Variables]
------------------------------------------------------------------
3 rows deleted

Administrator

Reference

Developer

Tools

External Tools

9. Developing Altibase Applications#

Writing Client Application Programs#

Applications Using Altibase CLI#

Header Files and Libraries#

Makefile#

Example#

Multi-threaded Programming#

Writing Application#

Altibase CLI Code Sample#

Execution Results#

Applications Using JDBC#

JDBC Driver#

CLASSPATH#

Writing Applications#

JDBC Code Sample#

Execution Results#

Applications Written Using the C/C++ Precompiler#

Environment Settings#

Header File#

Library#

Precompiling#

Precompiling embedded C/C++ programs#

Multi-threaded Programming#

Writing Applications#

Apre C/C++ Code Sample#

Execution Result#