Example Oracle TimesTen Connected programming

We would like to develope Oracle Timesten. We should understand ODBC programming and Oracle Timesten Architecture.
Anyway Let me show example to connect Oracle Timesten.

sample odbc file:
[ttmb_demo3]
Driver=/oracle/product/TimesTen/ttmb/lib/libtten.so
DataStore=/oradata/TimesTen/ttmb_demo3
DatabaseCharacterSet=TH8TISASCII
OracleId=TESTDB
PermSize=1000
TempSize=1000
UID=timesten_tmp
Authenticate=1
OraclePWD=password
TempWarnThreshold=85
PermWarnThreshold=85

[cc1]
TTC_SERVER=timeten01
TTC_SERVER_DSN=ttmb_demo3
UID=timesten_tmp
PWDCrypt=9629dc02d3c811e6368b4820e364a0d8f1a470cd
Authenticate=1

sample table:
Command> desc TEMP_DATA5;
Table TIMESTEN_TMP.TEMP_DATA5:
Columns:
*ID NUMBER NOT NULL
DATA VARCHAR2 (400) NOT INLINE
REMARK VARCHAR2 (255) NOT INLINE
1 table found.

Example Data>>>
< 41001, TEST, <NULL> >
< 41002, TEST, <NULL> >
< 41003, TEST, <NULL> >
< 41004, TEST, <NULL> >
< 41005, TEST, <NULL> >
< 41006, TEST, <NULL> >
< 41007, TEST, <NULL> >
< 41008, TEST, <NULL> >
< 41009, TEST, <NULL> >
< 41010, TEST, <NULL> >

We would like to query "select * from TIMESTEN_TMP.TEMP_DATA5" by C program.
"test.c" file >>>
#include <sqlunix.h>
#include <sql.h>
#include <sqltypes.h>
#include <sqlext.h>
#include <stdio.h>
#include <ctype.h>
#include <stdlib.h>
#include <limits.h>
#include <errno.h>
#include <time.h>

#define MAXCOLS 3
#define COL_LEN_MAX 100
#define DATA_SIZE 400
#define REMARK_SIZE 255
void print_err(HDBC hdbc, HSTMT hstmt, SQLHENV henv);
int main()
{
SQLHENV henv = SQL_NULL_HENV;
/* Environment handle */
SQLHDBC hdbc = SQL_NULL_HDBC;
/* Connection handle */
SQLHSTMT hstmt;
SQLRETURN rc;
int i;
SQLSMALLINT numCols;
SQLCHAR colname[32];
SQLSMALLINT colnamelen, coltype, scale, nullable;
SQLULEN collen [MAXCOLS];
SQLLEN outlen [MAXCOLS];
SQLCHAR* data [MAXCOLS];
UCHAR Sdata[DATA_SIZE];
SDWORD Sid;
UCHAR Sremark[REMARK_SIZE];
SQLLEN Cid,Cdata,Cremark;
rc = SQLAllocEnv(&henv);
rc = SQLAllocConnect(henv, &hdbc);
rc = SQLConnect(hdbc, "ttmb_demo3", SQL_NTS,
"timesten_tmp", SQL_NTS,
"timesten", SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
print_err(hdbc, SQL_NULL_HSTMT, henv );
/* other declarations, and program set-up here */
hstmt = SQL_NULL_HSTMT;
rc = SQLAllocStmt(hdbc, &hstmt);
/* Prepare the SELECT statement */
rc = SQLPrepare(hstmt,
(SQLCHAR*) "SELECT * FROM TIMESTEN_TMP.TEMP_DATA5",
SQL_NTS);
/* Determine number of columns in result rows */
rc = SQLNumResultCols(hstmt, &numCols);
/* Describe and bind the columns */
for (i = 0; i < numCols; i++) {
rc = SQLDescribeCol(hstmt,
(SQLSMALLINT) (i + 1),
colname,(SQLSMALLINT)sizeof(colname), &colnamelen, &coltype, &collen[i],
&scale, &nullable);
/* ... */
data[i] = (UCHAR*) malloc (collen[i] +1);
rc = SQLBindCol(hstmt, (SQLSMALLINT) (i + 1),
SQL_C_CHAR, data[i],
COL_LEN_MAX, &outlen[i]);
/* ... */
// printf ("%d\n",rc);

}
/* Execute the SELECT statement */
rc = SQLExecute(hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
print_err(hdbc, hstmt, henv );
/* ... */
/* Fetch the rows */
if (numCols > 0) {
SQLBindCol(hstmt, 1, SQL_C_SLONG, &Sid, 0, &Cid);
SQLBindCol(hstmt, 2, SQL_C_CHAR, &Sdata, DATA_SIZE, &Cdata);
SQLBindCol(hstmt, 3, SQL_C_CHAR, &Sremark, REMARK_SIZE, &Cremark);
while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) {
printf(" %d,%s,%s\n ", Sid,Sdata,Sremark);
/* ... "Process" the result row */
} /* end of for-loop */
if (rc != SQL_NO_DATA_FOUND)
fprintf(stderr,
"Unable to fetch the next row\n");
/* Close the cursor associated with the SELECT statement */
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
}
SQLFreeStmt(hstmt, SQL_DROP);
SQLDisconnect(hdbc);
SQLFreeConnect(hdbc);
SQLFreeEnv(henv);
printf ("DisConnected\n");
}

void print_err(HDBC hdbc, HSTMT hstmt, SQLHENV henv){
SQLFreeStmt(hstmt, SQL_DROP);
SQLDisconnect(hdbc);
SQLFreeConnect(hdbc);
SQLFreeEnv(henv);
printf ("Error\n");
exit (0);
}
>>>>>>>>>>>>>>>>>>>>>

Example "Makefile" file >>>
CC = gcc
INSTDIR = /oracle/product/TimesTen/ttmb
CFLAGS = -I${INSTDIR}/include
LIBS = -L${INSTDIR}/lib -ltten
LIBSDEBUG = -L${INSTDIR}/lib -lttenD
LIBSCS = -L${INSTDIR}/lib -lttclient
PROGS = test
all: $(PROGS)
clean:
rm -f $(PROGS) *.o
test:test.o
$(CC) -o test test.o $(LIBS)
>>>>>>>>>>>>>>>>>>>>>

$ make
gcc -I/oracle/product/TimesTen/ttmb/include -c -o test.o test.c
$ ls
Makefile test test.c test.o
$ ./test
41001,TEST,
41002,TEST,
41003,TEST,
41004,TEST,
41005,TEST,
41006,TEST,
41007,TEST,
41008,TEST,
41009,TEST,
41010,TEST,
DisConnected
********************************************************************

We would like to query "select * from TIMESTEN_TMP.TEMP_DATA5" by JAVA program.- export CLASSPATH
$ export CLASSPATH=.:/oracle/product/TimesTen/ttmb/lib/ttjdbc5.jar
- sample java program.
"TestTimesTen.java" file >>>
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.*;
public class TestTimesTen {
public static void main(String[] args) {
try {
Class.forName("com.timesten.jdbc.TimesTenDriver");
String URL = "jdbc:timesten:client:DSN=cc1;TCP_PORT=17003";
Connection _Connection = DriverManager.getConnection(URL);
Statement stmt = _Connection.createStatement();
ResultSet rs = stmt.executeQuery(" SELECT * FROM TIMESTEN_TMP.TEMP_DATA5 ");
while (rs.next()){
System.out.println( rs.getInt(1)+"," + rs.getString(2));
}
System.out.println("Test Completed");
}
catch (SQLException ex) {
ex.printStackTrace();
}
catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
}
}
>>>>>>>>>>>>>>>>>>>>>

$ /home/timesten/jdk1.6.0_03/bin/javac TestTimesTen.java
$ /home/timesten/jdk1.6.0_03/bin/java TestTimesTen
41001,TEST
41002,TEST
41003,TEST
41004,TEST
41005,TEST
41006,TEST
41007,TEST
41008,TEST
41009,TEST
41010,TEST

"TestTimesTen2.java" file (direct connect)>>>
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.*;
public class TestTimesTen3 {
public static void main(String[] args) {
try {
Class.forName("com.timesten.jdbc.TimesTenDriver");
String URL = "jdbc:timesten:DSN=ttmb_demo3;PWDCrypt=9629dc02d3c811e6368b4820e364a0d8f1a470cd";
Connection _Connection = DriverManager.getConnection(URL);
Statement stmt = _Connection.createStatement();
ResultSet rs = stmt.executeQuery(" SELECT * FROM TIMESTEN_TMP.TEMP_DATA5 ");
while (rs.next()){
System.out.println( rs.getInt(1)+"," + rs.getString(2));
}
System.out.println("Test Completed");
}
catch (SQLException ex) {
ex.printStackTrace();
}
catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
}
}
>>>>>>>>>>>>>>>>>>>>>

$ /home/timesten/jdk1.6.0_03/bin/javac TestTimesTen2.java
$ /home/timesten/jdk1.6.0_03/bin/java TestTimesTen2
41001,TEST
41002,TEST
41003,TEST
41004,TEST
41005,TEST
41006,TEST
41007,TEST
41008,TEST
41009,TEST
41010,TEST
********************************************************************

We would like to query "select * from TIMESTEN_TMP.TEMP_DATA5" by PERL program.- install DBD-TimesTen module
- sample Perl program
"test.pl" file >>>
>>>>>>>>>>>>>>>>>>>>>
#!/usr/bin/perl -w
use DBI qw(:sql_types);
use DBD::TimesTen qw(:sql_isolation_options);
my $dbh = DBI->connect('DBI:TimesTen:DSN=cc1;TCP_PORT=17003', undef,undef
)
or die $DBI::errstr;
my $sql = qq{ SELECT * FROM TIMESTEN_TMP.TEMP_DATA5 };
my $sth = $dbh->prepare( $sql );
$sth->execute();
while ( my @row = $sth->fetchrow_array ) {
print "$row[0],$row[1]\n";
}
$sth->finish();
$dbh->disconnect();
>>>>>>>>>>>>>>>>>>>>>
$ perl test.pl
41001,TEST
41002,TEST
41003,TEST
41004,TEST
41005,TEST
41006,TEST
41007,TEST
41008,TEST
41009,TEST
41010,TEST
********************************************************************

It's funny to develope some programs with Oracle Timesten.
Anyway We need to recognise about ODBC Programming.