The source file:
#include <ocicpp.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <string>
#include <sys/time.h>
#include <map>
#include <iostream.h>
using std::string;
using namespace OCICPP;
void printHead(Cursor &);
void printRow(Cursor &);
void help(Connection &);
void connect(Connection &);
void transStart(Connection &);
void transCommit(Connection &);
void transRollback(Connection &);
void version(Connection &);
void test_hash_cap(Connection &);
void exit(Connection &);
void test_blob(Connection &);
void test_bfile(Connection &);
void test_aqm(Connection &);
void test_rowid(Connection &);
void test_refcur(Connection &);
void test_ntable(Connection &);
void test_perfomance(Connection &);
void test_prefetch(Connection &);
void createBFile(Connection &);
void serverVersion(Connection &);
void test_huge_insert(Connection &);
typedef void (*func_type) (Connection &);
map<string,func_type> commands_map;
map<string,string> help_map;
void init_command_map() {
commands_map["connect"]=connect;
commands_map["exit"]=exit;
commands_map["version"]=version;
commands_map["help"]=help;
commands_map["transStart"]=transStart;
commands_map["transCommit"]=transCommit;
commands_map["transRollback"]=transRollback;
commands_map["test_hash_cap"]=test_hash_cap;
commands_map["test_blob"]=test_blob;
commands_map["test_aqm"]=test_aqm;
commands_map["test_rowid"]=test_rowid;
commands_map["test_refcur"]=test_refcur;
commands_map["test_ntable"]=test_ntable;
commands_map["test_perf"]=test_perfomance;
commands_map["test_prefetch"]=test_prefetch;
commands_map["test_bfile"]=test_bfile;
commands_map["createBFile"]=createBFile;
commands_map["serverVersion"]=serverVersion;
commands_map["test_huge_insert"]=test_huge_insert;
}
void init_help_map() {
help_map["connect"]="connecting to database prompts for tnsname , user and password";
help_map["exit"]="exit's demo";
help_map["version"]="Prints ocicpplib version";
help_map["help"]="Prints this help";
help_map["transStart"]="Starts new serializable transaction";
help_map["transCommit"]="Commits current transaction";
help_map["transRollback"]="Rollbacks current transaction";
help_map["test_hash_cap"]="Shows use of hash capabilities";
help_map["test_blob"]="Shows use of lob capabilities.";
help_map["test_aqm"]="Shows use of bind calls.Uses as code example.";
help_map["test_rowid"]="Shows use of rowid.Uses as code example";
help_map["test_refcur"]="Shows use of refcursor.Uses as code example";
help_map["test_ntable"]="Shows use of nested tables.Uses as code example";
help_map["test_bfile"]="Shows use of BFile selected from table";
help_map["createBFile"]="Shows use of BFiles specified by directory and name";
help_map["serverVersion"]="Print server version";
}
int main(int argv,char **argc) {
db::init();
Connection con;
// Cursor cur;
string sql;
init_command_map();
init_help_map();
help(con);
connect(con);
while(1) {
cout<<">";
getline(cin,sql);
try {
map<string,func_type>::const_iterator iter=commands_map.find(sql);
if(iter!=commands_map.end()) {
(iter->second)(con);
} else if(sql=="") {
help(con);
} else {
cout<<"Constructing cursor"<<endl;
Cursor cur;
cout<<"Dropping it"<<endl;
cur.drop();
cout<<"Executing query"<<endl;
con.execQuery(sql,cur);
cout<<"executed ok"<<endl;
printHead(cur);
while(cur.fetch()) {
printRow(cur);
}
}
} catch(OraError er) {
cout << er.message<<endl;
}
}
}
void connect(Connection &con) {
string tns,user,passwd;
cout << "connect string:";
getline(cin,tns);
cout<<"user:";
getline(cin,user);
cout<<"password:";
getline(cin,passwd);
cout<<"Connecting..."<<endl;
con.drop();
db::connect(tns.c_str(),user.c_str(),passwd.c_str(),con);
cout<<"connected."<<endl;
}
void transStart(Connection &con) {
con.transStart();
}
void transCommit(Connection &con) {
con.transCommit();
}
void transRollback(Connection &con) {
con.transRollback();
}
void version(Connection &con) {
version();
}
void test_hash_cap(Connection &con) {
Cursor cur;
string sql,cell;
cout << "sql:";
getline(cin,sql);
cout << "enter '<' when finish playing"<<endl;
con.execQuery(sql,cur);
printHead(cur);
if(!cur.fetch()) return;
cout<<"fetched ok"<<endl;
while(1) {
string field;
cout<<"Field to print or '.' to next row:";
cin>>field;
if(field==".") {
if(!cur.fetch()) break;
} else if(field=="<") {
break;
} else {
try {
cur.getStr(field,cell);
cout<<cell<<endl;
} catch(OraError &er) {
if(er.errtype()==OCICPPERROR) {
cout<<er.message<<endl;
continue;
} else throw;
}
}
}
}
void exit(Connection &con) {
try {
con.drop();
} catch(OraError er) {
cout << er.message<<endl;
exit(1);
}
exit(0);
}
void test_bfile(Connection &con) {
Cursor cur;
string sql,field,mode,dir,pos,len;
cout << "sql: ";
getline(cin,sql);
con.execQuery(sql,cur);
if(!cur.fetch()) {
cout << "No more rows"<<endl;
return;
}
while(1) {
cout<< "field to process or . for next row:";
getline(cin,field);
if(field==".") {
if(!cur.fetch()) {
cout << "No more rows "<<endl;
return;
}
} else if(field=="") {
return;
} else {
BFile bfile;
unsigned actread;
if(cur.getColType(field)==SQLT_FILE) {
cur.getFILE(field,bfile);
bfile.open();
} else {
return;
}
while(1) {
cout << "mode(r/s/l/o) Enter to exit:";
getline(cin,mode);
if(mode=="s") {
cout<<"Direction(SET/CUR/END:";
getline(cin,dir);
cout<<"Position:";
getline(cin,pos);
if(dir=="SET") bfile.seek(atoi(pos.c_str()),SET);
else if(dir=="CUR") bfile.seek(atoi(pos.c_str()),CUR);
else bfile.seek(atoi(pos.c_str()),END);
} else if(mode=="o") {
cout<<"Offset:"<<bfile.tell()<<endl;
} else if(mode=="l") {
cout << "Len: "<<bfile.getLen()<<endl;
} else if(mode=="r") {
char buf[256];
while((actread=bfile.read(buf,254))) {
buf[actread]='\0';
cout << buf;
}
cout << endl;
} else break;
}
bfile.close();
}
}
}
void test_blob(Connection &con) {
Cursor cur;
string sql,field,mode,blob,dir,pos,len;
cout << "sql: ";
getline(cin,sql);
con.execQuery(sql,cur);
if(!cur.fetch()) {
cout << "No more rows"<<endl;
return;
}
while(1) {
cout<< "field to process or . for next row:";
getline(cin,field);
if(field==".") {
if(!cur.fetch()) {
cout << "No more rows "<<endl;
return;
}
} else if(field=="") {
return;
} else {
Lob lob;
unsigned actread;
if(cur.getColType(field)==SQLT_BLOB) cur.getBLOB(field,lob);
else if(cur.getColType(field)==SQLT_CLOB) cur.getCLOB(field,lob);
while(1) {
cout << "mode(r/w/s/t/l/o) Enter to exit:";
getline(cin,mode);
if(mode=="s") {
cout<<"Direction(SET/CUR/END:";
getline(cin,dir);
cout<<"Position:";
getline(cin,pos);
if(dir=="SET") lob.seek(atoi(pos.c_str()),SET);
else if(dir=="CUR") lob.seek(atoi(pos.c_str()),CUR);
else lob.seek(atoi(pos.c_str()),END);
} else if(mode=="o") {
cout<<"Offset:"<<lob.tell()<<endl;
} else if(mode=="t") {
cout << "new len:";
getline(cin,len);
lob.trunc(atoi(len.c_str()));
} else if(mode=="l") {
cout << "Len: "<<lob.getLen()<<endl;
} else if(mode=="r") {
char buf[256];
while((actread=lob.read(buf,254))) {
buf[actread]='\0';
cout << buf;
}
cout << endl;
} else if(mode=="w") {
cout<<"data:";
getline(cin,blob);
lob.write(blob.c_str(),strlen(blob.c_str()));
} else break;
}
}
}
}
void test_aqm(Connection &con) {
string sql,param,val;
short isNull;
map<string,string> params;
Cursor cur;
cout<<"sql to run:";
getline(cin,sql);
con.prepare(sql,cur);
cout<<"Statement prepared"<<endl;
do {
cout<<"parameter name:";
getline(cin,param);
if(param=="") break;
cout<<"value:";
getline(cin,val);
params[param]=val;
cur.bind(param,val,&isNull);
} while(1);
cur.execute();
printHead(cur);
while(cur.fetch()) {
printRow(cur);
}
}
void test_rowid(Connection &con) {
string sql="select rowid,id,var_col from ociex where id<5 for update";
string id,upSql;
Cursor cur1,cur2;
RowID rowid;
short isNull;
con.execQuery(sql,cur1);
while(cur1.fetch()) {
cur1.getRowID("ROWID",rowid);
cur1.getStr("ID",id);
upSql="update ociex set var_col='testing rowid id="+id+"' where rowid=:rid";
cout<<upSql<<endl;
con.prepare(upSql,cur2);
cout<<"Statement for cur2 prepared"<<endl;
cur2.bind(":rid",rowid,&isNull);
cout<<"binded ok"<<endl;
cur2.execute();
cout<<"executed ok"<<endl;
}
}
void test_refcur(Connection &con) {
Cursor cur1,cur2;
string sql="
begin
OPEN :cursor1 FOR SELECT * FROM ociex;
end;";
con.prepare(sql,cur1);
cur1.bind(":cursor1",cur2);
cur1.execute();
cur2.execute();
printHead(cur2);
while(cur2.fetch()) {
printRow(cur2);
}
}
void test_ntable(Connection &con) {
Cursor cur1,cur2,cur3;
/*
string sql="select p.poster_id,CURSOR(select pp.play_id,t.text prod_name
from t_play pp, t_text t
where t.object_type_id(+)=12
and t.text_type_id(+)=23
and t.object_id(+)=pp.play_id
and t.language_id(+)=1
and pp.poster_id=p.poster_id) ddd from t_poster p";
*/
/*
string sql="select CURSOR(select pp.play_id,t.text prod_name
from t_play pp, t_text t
where t.object_type_id(+)=12
and t.text_type_id(+)=23
and t.object_id(+)=pp.play_id
and t.language_id(+)=1
) ddd from dual";
*/
string sql="begin PLAY.get_select(:cur); end;";
con.prepare(sql,cur3);
cur3.bind(":cur",cur1);
cur3.execute();
cur1.execute();
// con.execQuery(sql,cur3);
printHead(cur1);
while(cur1.fetch()) {
cur1.getCursor(1,cur2);
cur2.execute();
printHead(cur2);
while(cur2.fetch()) {
printRow(cur2);
}
cur2.drop();
}
}
void test_perfomance(Connection &con) {
timeval start,end;
Cursor cur;
string sql;
unsigned prefetch;
double diff,overall=0;
int nrows=0;
cout << "sql:";
getline(cin,sql);
cout << "rows to prefetch:";
cin >> prefetch;
gettimeofday(&start,0);
con.prepare(sql,cur);
gettimeofday(&end,0);
diff=(end.tv_sec-start.tv_sec)+(end.tv_usec-start.tv_usec)/1000000.0;
overall+=diff;
cout << "Prepared in " << diff <<endl;
start=end;
cur.execute();
gettimeofday(&end,0);
diff=(end.tv_sec-start.tv_sec)+((double)(end.tv_usec-start.tv_usec))/1000000;
overall+=diff;
cout << "Executed in " << diff <<endl;
start=end;
while(cur.fetch()) {
/* dummy while */
nrows++;
}
gettimeofday(&end,0);
diff=(end.tv_sec-start.tv_sec)+((double)(end.tv_usec-start.tv_usec))/1000000;
overall+=diff;
cout << "Fetched " << nrows << " rows in " << diff << "seconds. Overall "<<overall << endl;
}
void createBFile(Connection &con) {
string dir,file;
string direction,pos;
unsigned actread;
BFile bfile;
cout << "dir:";
cin >> dir;
cout << "file name:";
cin >> file;
con.createBFile(dir,file,bfile);
bfile.open();
while(1) {
string c; // command
cout << "(r/s/t/l/q) >";
cin >> c;
if(c=="r") {
char buf[256];
while((actread=bfile.read(buf,254))) {
buf[actread]='\0';
cout << buf;
}
} else if(c=="s") {
cout<<"Direction(SET/CUR/END:";
getline(cin,direction);
cout<<"Position:";
getline(cin,pos);
if(direction=="SET") bfile.seek(atoi(pos.c_str()),SET);
else if(direction=="CUR") bfile.seek(atoi(pos.c_str()),CUR);
else bfile.seek(atoi(pos.c_str()),END);
} else if(c=="t") {
cout<<"Offset:"<<bfile.tell()<<endl;
} else if(c=="l") {
cout << "Len: "<<bfile.getLen()<<endl;
} else if(c=="q") {
break;
}
}
bfile.close();
}
void test_prefetch(Connection &con) {
timeval start,end;
Cursor cur;
string sql;
int prefetch,nrows=0;
double diff,overall=0;
cout << "sql:";
getline(cin,sql);
cout << "Prefetch:";
cin >> prefetch;
gettimeofday(&start,0);
con.execQuery(sql,cur,prefetch);
gettimeofday(&end,0);
diff=(end.tv_sec-start.tv_sec)+(end.tv_usec-start.tv_usec)/1000000.0;
overall+=diff;
cout << "executed in " << diff <<endl;
start=end;
while(cur.fetch()) {
/* dummy while */
nrows++;
cerr << cur.getStr(0) << endl;
}
gettimeofday(&end,0);
diff=(end.tv_sec-start.tv_sec)+((double)(end.tv_usec-start.tv_usec))/1000000;
overall+=diff;
cout << "Fetched " << nrows << " rows in " << diff << "seconds. Overall "<<overall << endl;
}
void serverVersion(Connection &con) {
cout << con.serverVersion() << endl;
}
void test_huge_insert(Connection &con) {
int i;
std::string sql;
try {
for(i=0;i<1000000000;i++) {
char buf[256];
sprintf(buf,"%d",i);
sql="insert into test1(id,f1,f2) values(";
sql+=buf;
sql+=",'f1=";
sql+=buf;
sql+="','f2=-";
sql+=buf;
sql+="')";
cout << sql << endl;
con.execUpdate(sql);
}
} catch(OraError er) {
cerr << "catch error:" << er.message << " i=" << i << endl;
}
}
void printHead(Cursor &cur) {
string attrName;
for(int i=0;i<cur.getNCols();i++) {
cur.getColName(i,attrName);
if(!i) cout<<"|";
cout<<attrName<<"("<<cur.getColSize(i)<<")|";
}
cout << endl;
}
void printRow(Cursor &cur) {
string cell;
int nCols=cur.getNCols();
for(int i=0;i<nCols;i++) {
cur.getStr(i,cell);
if(!i) cout<<"|";
cout<<cell<<"|";
}
cout << endl;
}
void help(Connection &con) {
map<string,string>::const_iterator ci=help_map.begin();
cout << "Here's a list of available commands:"<<endl;
while(ci!=help_map.end()) {
cout << ci->first << " " << ci->second << endl;
*ci++;
}
cout << "For details please visit docs's page at http://ocicpplib.sourceforge.net/docs.html" <<endl;
}
1.2.4-20010128 written by Dimitri van Heesch,
© 1997-2001