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.
Posts
