int main()
{
DbRetVal rv = OK;
AbsSqlConnection *con = createConnection();
rv = con->connect("root","manager");
if(rv!=OK)return 1;
AbsSqlStatement *stmt = createStatement();
stmt->setConnection(con);
char statement[200];
strcpy(statement,"CREATE TABLE T1(F1 INT,F2 CHAR(20));");
int rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; delete con; return 1; }
printf("Prepare the Statement\n");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; delete con; return 2; }
printf("Execute the Statement\n");
stmt->free();
printf("free all the resources\n");
//again prepare
strcpy(statement,"DROP TABLE T1;");
rv = stmt->prepare(statement);
if(rv!=OK) {
printf("Test script failed\n");
delete stmt; delete con; return 3;
}
printf("Again prepare passed\n");
//Drop the table
strcpy(statement,"DROP TABLE T1;");
rv = stmt->prepare(statement);
rv = stmt->execute(rows);
if(rv==OK)printf("Table dropped successfully\n");
stmt->free(); delete stmt; delete con;
return 0;
}
int main()
{
AbsSqlConnection *conn = createConnection();
DbRetVal rv = conn->connect("root", "manager");
if (rv != OK) { printf("Error during connection %d\n", rv); return 1; }
AbsSqlStatement *stmt = createStatement();
stmt->setConnection(conn);
char statement[1024]="create table t1 (f1 int, f2 char(196), primary key(f1));";
int ret = executeDdlQuery(conn, stmt, statement);
if (ret != 0) { return 1; }
pthread_t thr[2];
int *status1, *status2;
pthread_create (&thr[0], NULL, &runTest1, NULL);
pthread_create (&thr[1], NULL, &runTest2, NULL);
printf("All threads started\n");
pthread_join(thr[0], (void**)&status1);
pthread_join(thr[1], (void**)&status2);
strcpy(statement, "drop table t1;");
ret = executeDdlQuery(conn, stmt, statement);
if (ret != 0) { return 1; }
conn->disconnect();
stmt->free(); delete stmt; delete conn;
if (*status1 != 0 || *status2 != 0) return 1;
if (p1RetVal) { delete p1RetVal; p1RetVal = NULL; }
if (p2RetVal) { delete p2RetVal; p2RetVal = NULL; }
return 0;
}
void* runTest1(void *message)
{
AbsSqlConnection *conn = createConnection();
DbRetVal rv = conn->connect("root", "manager");
if (rv != OK) { printf("Error during connection %d\n", rv); return NULL; }
AbsSqlStatement *stmt = createStatement();
stmt->setConnection(conn);
# ifdef RDUNCOM
rv = conn->beginTrans(READ_UNCOMMITTED);
# elif defined RDRPT
rv = conn->beginTrans(READ_REPEATABLE);
# else
rv = conn->beginTrans(READ_COMMITTED);
#endif
if (rv != OK) return NULL;
printf("Thread and pid is %d %lu\n", os::getpid(), os::getthrid());
p1RetVal = new int();
*p1RetVal = 0;
rv = select(stmt, 100, true);
if (rv != OK) { printf("Test Failed:first thread failed to select\n"); *p1RetVal = 1; }
selectDone = 1;
while (deleteDone != 1) {::sleep(1); }
printf("P2 deleted says P1 and P1 is selecting now\n");
rv = select(stmt, 100, false);
if (rv == OK) { printf("Test Failed:first thread read succeeded \n"); *p1RetVal = 1; }
printf ("P1 second select returned with rv = %d\n", rv);
conn->commit();
rv = conn->disconnect();
stmt->free(); delete stmt; delete conn;
printf("conn closed %d for Thread and pid is %d %lu\n", rv, os::getpid(), os::getthrid());
pthread_exit(p1RetVal);
}
int main()
{
DbRetVal rv = OK;
AbsSqlConnection *con = createConnection();
rv = con->connect("root","manager");
if(rv !=OK)return 1;
printf("Connection opened\n");
AbsSqlStatement *stmt = createStatement();
stmt->setConnection(con);
char statement[200];
strcpy(statement,"CREATE TABLE t1(f1 INT);");
int rows;
rv = con->disconnect();
if(rv!=OK)return 2;
printf("Connection closed\n");
rv = stmt->prepare(statement);
if(rv==OK)
{
delete stmt;
delete con;
printf("Test script failed\n");
return 3;
}
printf("Test script passed\n");
delete stmt;
delete con;
return 0;
}
void* runTest2(void *message)
{
AbsSqlConnection *conn = createConnection();
DbRetVal rv = conn->connect("root", "manager");
if (rv != OK) { printf("Error during connection %d\n", rv); return NULL; }
AbsSqlStatement *stmt = createStatement();
stmt->setConnection(conn);
# ifdef RDUNCOM
rv = conn->beginTrans(READ_UNCOMMITTED);
# elif defined RDRPT
rv = conn->beginTrans(READ_REPEATABLE);
# else
rv = conn->beginTrans(READ_COMMITTED);
# endif
if (rv != OK) return NULL;
printf("Thread and pid is %d %lu\n", os::getpid(), os::getthrid());
p2RetVal = new int();
*p2RetVal = 0;
::sleep(2);
rv = insert(stmt, 100, false);
if (rv == OK) { printf("Test Failed:second thread inserted\n"); *p2RetVal = 1; }
rv = conn->commit();
rv = conn->disconnect();
stmt->free(); delete stmt; delete conn;
printf("conn closed %d for Thread and pid is %d %lu\n", rv, os::getpid(), os::getthrid());
pthread_exit(p2RetVal);
}
/* {{{ MySQL_Connection::getSchema() -I- */
sql::SQLString
MySQL_Connection::getSchema()
{
CPP_ENTER_WL(intern->logger, "MySQL_Connection::getSchema");
checkClosed();
boost::scoped_ptr< sql::Statement > stmt(createStatement());
boost::scoped_ptr< sql::ResultSet > rset(stmt->executeQuery("SELECT DATABASE()")); //SELECT SCHEMA()
rset->next();
return rset->getString(1);
}
/* {{{ MySQL_Connection::setSchema() -I- (not part of JDBC) */
void
MySQL_Connection::setSchema(const sql::SQLString& catalog)
{
CPP_ENTER_WL(intern->logger, "MySQL_Connection::setCatalog");
checkClosed();
sql::SQLString sql("USE `");
sql.append(catalog).append("`");
boost::scoped_ptr< sql::Statement > stmt(createStatement());
stmt->execute(sql);
}
/* {{{ MySQL_Connection::rollback() -I- */
void
MySQL_Connection::rollback(Savepoint * savepoint)
{
CPP_ENTER_WL(intern->logger, "MySQL_Connection::rollback");
checkClosed();
if (getAutoCommit()) {
throw sql::InvalidArgumentException("The connection is in autoCommit mode");
}
sql::SQLString sql("ROLLBACK TO SAVEPOINT ");
sql.append(savepoint->getSavepointName());
boost::scoped_ptr< sql::Statement > stmt(createStatement());
stmt->execute(sql);
}
/* {{{ MySQL_Connection::releaseSavepoint() -I- */
void
MySQL_Connection::releaseSavepoint(Savepoint * savepoint)
{
CPP_ENTER_WL(intern->logger, "MySQL_Connection::releaseSavepoint");
checkClosed();
if (proxy->get_server_version() < 50001) {
throw sql::MethodNotImplementedException("releaseSavepoint not available in this server version");
}
if (getAutoCommit()) {
throw sql::InvalidArgumentException("The connection is in autoCommit mode");
}
sql::SQLString sql("RELEASE SAVEPOINT ");
sql.append(savepoint->getSavepointName());
boost::scoped_ptr<sql::Statement> stmt(createStatement());
stmt->execute(sql);
}
int main()
{
DbRetVal rv = OK;
AbsSqlConnection *con = createConnection();
rv = con->connect("root","manager");
if(rv!=OK)return 1;
AbsSqlStatement *stmt = createStatement();
stmt->setConnection(con);
char statement[200];
strcpy(statement,"CREATE TABLE T1(F1 INT,F2 INT);");
int rows = 0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; delete con; return 2; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; delete con; return 3; }
stmt->free();
printf("Table T1 created\n");
strcpy(statement,"DESCRIBE T1;");
rv = stmt->prepare(statement);
char colName[IDENTIFIER_LENGTH];
int sqlType=0;
int length=0;
int isNull=0;
stmt->bindField(4, colName);
stmt->bindField(5, &sqlType);
stmt->bindField(7, &length);
stmt->bindField(11, &isNull);
int i=0;
rv = stmt->execute(rows);
if (rv != OK) { printError(rv, "execute failed"); return rv; }
while (stmt->fetch(rv) != NULL) {
printf("cnt = %d\n", i++);
}
stmt->free();
strcpy(statement,"DROP TABLE T1;");
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; delete con; return 8; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; delete con; return 9; }
printf("Table dropped\n");
stmt->free(); delete stmt; delete con;
return 0;
}
int main()
{
DbRetVal rv = OK;
AbsSqlConnection *con = createConnection();
rv = con->connect("root","manager");
if(rv !=OK)return 1;
printf("Connection opened\n");
AbsSqlStatement *stmt = createStatement();
stmt->setConnection(con);
char statement[200];
strcpy(statement,"CREATE TABLE t1(f1 INT,f2 CHAR(20)); ");
if(strcmp(getenv("DSN"),"db2")==0){
strcpy(statement,"CREATE TABLE t1(f1 INT,f2 CHAR(20))");
}
int rows=0;
rv = stmt->prepare(statement);
if(rv!=OK)
{
delete stmt;
delete con;
return 2;
}
printf("prepared successfully\n");
rv = con->disconnect();
if(rv!=OK)return 3;
printf("Disconnect successfully\n");
rv = stmt->execute(rows);
if(rv == OK)
{
printf("Test script failed \n");
delete stmt;
return 4;
}
stmt->free();
printf("Test script passed\n");
delete stmt;
delete con;
return 0;
}
/* {{{ MySQL_Connection::setSavepoint() -I- */
sql::Savepoint *
MySQL_Connection::setSavepoint(const sql::SQLString& name)
{
CPP_ENTER_WL(intern->logger, "MySQL_Connection::setSavepoint");
checkClosed();
if (getAutoCommit()) {
throw sql::InvalidArgumentException("The connection is in autoCommit mode");
}
if (!name.length()) {
throw sql::InvalidArgumentException("Savepoint name cannot be empty string");
}
sql::SQLString sql("SAVEPOINT ");
sql.append(name);
boost::scoped_ptr< sql::Statement > stmt(createStatement());
stmt->execute(sql);
return new MySQL_Savepoint(name);
}
void* runTest2(void *message)
{
AbsSqlConnection *conn = createConnection();
DbRetVal rv = conn->connect("root", "manager");
if (rv != OK) { printf("Error during connection %d\n", rv); return NULL; }
AbsSqlStatement *stmt = createStatement();
stmt->setConnection(conn);
# ifdef RDUNCOM
rv = conn->beginTrans(READ_UNCOMMITTED);
# elif defined RDRPT
rv = conn->beginTrans(READ_REPEATABLE);
# else
rv = conn->beginTrans(READ_COMMITTED);
#endif
if (rv != OK) return NULL;
printf("Thread and pid is %d %lu\n", os::getpid(), os::getthrid());
while (selectDone != 1) {::sleep(1); }
p2RetVal = new int();
*p2RetVal = 0;
rv = remove(stmt, 100, true);
# ifdef RDUNCOM
if (rv != OK) {
printf("Test Failed:second thread failed to remove.\n");
# elif defined RDRPT
if (rv == OK) {
printf("Test Failed:second thread selects.\n");
# else
if (rv != OK) {
printf("Test Failed:second thread failed to remove\n");
#endif
*p2RetVal = 1;
}
printf("P2 deleted\n");
deleteDone =1;
sleep(5);
rv = conn->commit();
printf("P2 committed\n");
rv = conn->disconnect();
stmt->free(); delete stmt; delete conn;
printf("conn closed %d for Thread and pid is %d %lu\n", rv, os::getpid(), os::getthrid());
pthread_exit(p2RetVal);
}
/// ATM only supports (key, value) inserts
void asyncInsert(const std::string &table,
const int64_t &key,
const std::string &value) {
if(!connected_) {
LOG(FATAL)
<< "asyncInsert failed, establish connection to cassandra first";
} else if(futures_.size() > kMaxNumQueuedFutures) {
VLOG(1) << "asyncInsert, queue has reached capacity, draining...";
wait();
}
const auto tableName = keyspace_ + "." + table;
const auto query("INSERT INTO " + tableName + " (key, value) "
+ "VALUES (?, ?);");
auto statement = createStatement(cass_statement_new(query.c_str(), 2));
cass_statement_bind_int64(statement.get(), 0, (cass_int64_t)key);
cass_statement_bind_string(statement.get(), 1, value.data());
futures_.emplace(cass_session_execute(session_.get(), statement.get()),
cass_future_free);
}
void MyTblPlnrQPrjList::initStatements() {
stmtInsertRec = createStatement("INSERT INTO TblPlnrQPrjList (jref, jnum, ref, Title, ixVState) VALUES (?,?,?,?,?)", false);
stmtUpdateRec = createStatement("UPDATE TblPlnrQPrjList SET jref = ?, jnum = ?, ref = ?, Title = ?, ixVState = ? WHERE qref = ?", false);
stmtRemoveRecByQref = createStatement("DELETE FROM TblPlnrQPrjList WHERE qref = ?", false);
stmtRemoveRstByJref = createStatement("DELETE FROM TblPlnrQPrjList WHERE jref = ?", false);
};
void MyTblPlnrJMUserState::initStatements() {
stmtInsertRec = createStatement("INSERT INTO TblPlnrJMUserState (refPlnrMUser, x1Startd, ixVState) VALUES (?,?,?)", false);
stmtUpdateRec = createStatement("UPDATE TblPlnrJMUserState SET refPlnrMUser = ?, x1Startd = ?, ixVState = ? WHERE ref = ?", false);
stmtRemoveRecByRef = createStatement("DELETE FROM TblPlnrJMUserState WHERE ref = ?", false);
};
int main()
{
DbRetVal rv = OK;
AbsSqlConnection *con = createConnection();
rv = con->connect("root","manager");
if(rv!=OK)return 1;
AbsSqlStatement *stmt = createStatement();
stmt->setConnection(con);
char statement[200];
if(strcmp(getenv("DSN"),"oracle")==0)
strcpy(statement,"CREATE TABLE t1(f1 number(9),f2 number(4),f3 CHAR(30),f4 FLOAT,f5 FLOAT,f6 DATE,f8 TIMESTAMP,f9 number(9),f10 number(18));");
else if(strcmp(getenv("DSN"),"psql")==0)
strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30),f4 FLOAT4,f5 FLOAT4,f6 DATE,f7 TIME,f8 TIMESTAMP,f9 INT,f10 BIGINT);");
else if(strcmp(getenv("DSN"),"sybase")==0)
strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30),f4 REAL,f5 REAL,f6 DATE,f7 TIME,f8 DATETIME,f9 INT,f10 BIGINT);");
else if(strcmp(getenv("DSN"),"db2")==0)
strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30),f4 REAL,f5 REAL,f6 DATE,f7 TIME,f8 TIMESTAMP,f9 INT,f10 BIGINT)");
else
strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30),f4 FLOAT,f5 FLOAT,f6 DATE,f7 TIME,f8 TIMESTAMP,f9 INT,f10 BIGINT);");
int rows = 0;
rv = stmt->prepare(statement);
if(rv!=OK)
{
delete stmt;
delete con;
return 2;
}
rv = stmt->execute(rows);
if(rv!=OK)
{
delete stmt;
delete con;
return 3;
}
con->commit();
stmt->free();
printf("Table T1 created\n");
// insert into table
if(strcmp(getenv("DSN"),"oracle")==0){
strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?);");
}
else if(strcmp(getenv("DSN"),"db2")==0){
strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?)");
}
else{
strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?);");
}
int f1var = 0;
short int f2var = 1;
char f3var[35]="jitendra";
float f4var = 5.5;
float f5var = 10.50;
Date f6var;
f6var.set(2007,01,21);
Time f7var;
f7var.set(12,29,30);
TimeStamp f8var;
f8var.setDate(2007,01,21);
f8var.setTime(12,29,30);
int f9var = 20;
long long f10var = 12000;
rv = stmt->prepare(statement);
if(rv!=OK)
{
delete stmt;
delete con;
return 4;
}
if(strcmp(getenv("DSN"),"psql")==0){
con->rollback();
}
int count=0;
for(int i=0;i<10;i++)
{
rv = con->beginTrans();
if(rv!=OK) break;
f1var=i;
stmt->setIntParam(1,f1var);
stmt->setShortParam(2,f2var);
stmt->setStringParam(3,f3var);
stmt->setFloatParam(4,f4var);
stmt->setFloatParam(5,f5var);
stmt->setDateParam(6,f6var);
if(strcmp(getenv("DSN"),"oracle")==0){
stmt->setTimeStampParam(7,f8var);
stmt->setIntParam(8,f9var);
stmt->setLongLongParam(9,f10var);
}else{
stmt->setTimeParam(7,f7var);
stmt->setTimeStampParam(8,f8var);
stmt->setIntParam(9,f9var);
stmt->setLongLongParam(10,f10var);
}
rv = stmt->execute(rows);
if(rv!=OK)break;
rv = con->commit();
if(rv!=OK)break;
count++;
}
printf("Total rows inserted %d\n",count);
stmt->free();
//*********************************************************
// delete records from the T1
if(strcmp(getenv("DSN"),"oracle")==0)
strcpy(statement,"DELETE FROM t1 WHERE F10=? AND F9=? AND F8=? AND F6=? AND F5=? AND F4=? AND F3=? AND F2=? AND F1=?;");
else if(strcmp(getenv("DSN"),"sybase")==0)
strcpy(statement,"DELETE FROM t1 WHERE f10=? AND f9=? AND f8=? AND f7=? AND f6=? AND f5=? AND f4=? AND f3=? AND f2=? AND f1=?;");
else if(strcmp(getenv("DSN"),"db2")==0)
strcpy(statement,"DELETE FROM t1 WHERE f10=? AND f9=? AND f8=? AND f7=? AND f6=? AND f5=? AND f4=? AND f3=? AND f2=? AND f1=?");
else
strcpy(statement,"DELETE FROM t1 WHERE F10=? AND F9=? AND F8=? AND F7=? AND F6=? AND F5=? AND F4=? AND F3=? AND F2=? AND F1=?;");
rv = stmt->prepare(statement);
if(rv!=OK){delete stmt;delete con;return -1;}
if(strcmp(getenv("DSN"),"psql")==0){
con->rollback();
}
/* stmt->bindField(1,&f10var);
stmt->bindField(2,&f9var);
stmt->bindField(3,&f8var);
stmt->bindField(4,&f7var);
stmt->bindField(5,&f6var);
stmt->bindField(6,&f5var);
stmt->bindField(7,&f4var);
stmt->bindField(8,f3var);
stmt->bindField(9,&f2var);
stmt->bindField(10,&f1var);*/
short int f2var1=1;
char f3var1[20] ="jitendra";
float f4var1 = 5.5;
float f5var1 = 10.50;
Date f6var1;
f6var1.set(2007,1,21);
Time f7var1;
f7var1.set(12,29,30);
TimeStamp f8var1;
f8var1.setDate(2007,1,21);
f8var1.setTime(12,29,30);
int f9var1=20;
long long f10var1=12000;
count=0;
for(int i=5;i<10;i++)
{
rv = con->beginTrans();
if(rv!=OK)break;
stmt->setLongLongParam(1,f10var1);
stmt->setIntParam(2,f9var1);
stmt->setTimeStampParam(3,f8var1);
if(strcmp(getenv("DSN"),"oracle")==0){
stmt->setDateParam(4,f6var1);
stmt->setFloatParam(5,f5var1);
stmt->setFloatParam(6,f4var1);
stmt->setStringParam(7,f3var1);
stmt->setShortParam(8,f2var1);
stmt->setIntParam(9,i);
}else{
stmt->setTimeParam(4,f7var1);
stmt->setDateParam(5,f6var1);
stmt->setFloatParam(6,f5var1);
stmt->setFloatParam(7,f4var1);
stmt->setStringParam(8,f3var1);
stmt->setShortParam(9,f2var1);
stmt->setIntParam(10,i);
}
rv = stmt->execute(rows);
if(rv!=OK)break;
rv = con->commit();
if(rv!=OK)break;
count++;
}
stmt->free();
printf("Total row deleted=%d\n",count);
//**********************************************************
// SELECT * FROM T1;
strcpy(statement,"SELECT * FROM t1;");
if(strcmp(getenv("DSN"),"db2")==0){
strcpy(statement,"SELECT * FROM t1");
}
rv = stmt->prepare(statement);
if(rv!=OK)
{
delete stmt;
delete con;
return 5;
}
stmt->bindField(1,&f1var);
stmt->bindField(2,&f2var);
stmt->bindField(3,f3var);
stmt->bindField(4,&f4var);
stmt->bindField(5,&f5var);
stmt->bindField(6,&f6var);
if(strcmp(getenv("DSN"),"oracle")==0){
stmt->bindField(7,&f8var);
stmt->bindField(8,&f9var);
stmt->bindField(9,&f10var);
}else{
stmt->bindField(7,&f7var);
stmt->bindField(8,&f8var);
stmt->bindField(9,&f9var);
stmt->bindField(10,&f10var);
}
count=0;
rv = con->beginTrans();
if(rv!=OK)return 6;
stmt->execute(rows);
while(stmt->fetch() !=NULL)
{
if(strcmp(getenv("DSN"),"oracle")==0)
printf("F1=%d | F2=%hd | F3=%s | F4=%f | F5=%f | DATE=%d-%d-%d | TIMESTAMP=%d-%d-%d %d:%d:%d | F9=%d | F10=%lld \n",f1var,f2var,f3var,f4var,f5var,f6var.year(),f6var.month(),f6var.dayOfMonth(),f8var.year(),f8var.month(),f8var.dayOfMonth(),f8var.hours(),f8var.minutes(),f8var.seconds(),f9var,f10var);
else
printf("F1=%d | F2=%hd | F3=%s | F4=%f | F5=%f | DATE=%d-%d-%d | TIME=%d:%d:%d | TIMESTAMP=%d-%d-%d %d:%d:%d | F9=%d | F10=%lld \n",f1var,f2var,f3var,f4var,f5var,f6var.year(),f6var.month(),f6var.dayOfMonth(),f7var.hours(),f7var.minutes(),f7var.seconds(),f8var.year(),f8var.month(),f8var.dayOfMonth(),f8var.hours(),f8var.minutes(),f8var.seconds(),f9var,f10var);
count++;
}
stmt->close();
rv = con->commit();
if(rv !=OK)
{
delete stmt;
delete con;
return 7;
}
printf("Total row fetched=%d\n",count);
stmt->free();
strcpy(statement,"DROP TABLE t1;");
if(strcmp(getenv("DSN"),"db2")==0){
strcpy(statement,"DROP TABLE t1");
}
rv = stmt->prepare(statement);
if(rv!=OK)
{
delete stmt;
delete con;
return 8;
}
rv = stmt->execute(rows);
if(rv!=OK)
{
delete stmt;
delete con;
return 9;
}
stmt->free();
printf("Table dropped\n");
con->commit();
delete stmt;
delete con;
return 0;
}
int main()
{
DbRetVal rv = OK;
AbsSqlConnection *con = createConnection();
rv = con->connect("root","manager");
if(rv!=OK)return 1;
AbsSqlStatement *stmt = createStatement();
stmt->setConnection(con);
char statement[200];
if(strcmp(getenv("DSN"),"oracle")==0)
strcpy(statement,"CREATE TABLE t1(f1 number(9),f2 number(4),f3 CHAR(30) DEFAULT 'LAKSHYA',f4 FLOAT,f5 FLOAT,f6 DATE,f8 TIMESTAMP,f9 number(9),f10 number(18));");
else if(strcmp(getenv("DSN"),"psql")==0)
strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30) DEFAULT 'LAKSHYA',f4 FLOAT4,f5 FLOAT4,f6 DATE,f7 TIME,f8 TIMESTAMP,f9 INT,f10 BIGINT);");
else if(strcmp(getenv("DSN"),"sybase")==0)
strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30) DEFAULT 'LAKSHYA',f4 REAL,f5 REAL,f6 DATE,f7 TIME,f8 DATETIME,f9 INT,f10 BIGINT);");
else if(strcmp(getenv("DSN"),"db2")==0)
strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30) DEFAULT 'LAKSHYA',f4 REAL,f5 REAL,f6 DATE,f7 TIME,f8 TIMESTAMP,f9 INT,f10 BIGINT)");
else
strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30) DEFAULT 'LAKSHYA',f4 FLOAT,f5 FLOAT,f6 DATE,f7 TIME,f8 TIMESTAMP,f9 INT,f10 BIGINT);");
int rows=0;
rv = stmt->prepare(statement);
if(rv!=OK)
{
delete stmt;
delete con;
return 2;
}
rv = stmt->execute(rows);
if(rv!=OK)
{
delete stmt;
delete con;
return 3;
}
stmt->free();
con->commit();
printf("'t1' Table created\n");
// insert records
if(strcmp(getenv("DSN"),"oracle")==0)
strcpy(statement,"INSERT INTO t1(f1,f2,f4,f5,f6,f8,f9,f10)VALUES(?,?,?,?,?,?,?,?);");
else if(strcmp(getenv("DSN"),"db2")==0)
strcpy(statement,"INSERT INTO t1(f1,f2,f4,f5,f6,f7,f8,f9,f10)VALUES(?,?,?,?,?,?,?,?,?)");
else
strcpy(statement,"INSERT INTO t1(f1,f2,f4,f5,f6,f7,f8,f9,f10)VALUES(?,?,?,?,?,?,?,?,?);");
int f1var = 99;
short int f2var = 1;
// char f3var[20] = "prabakaran";
float f4var = 5.5;
float f5var = 10.50;
Date f6var;
f6var.set(2008,1,21);
Time f7var;
f7var.set(12,29,30);
TimeStamp f8var;
f8var.setDate(2008,1,21);
f8var.setTime(12,29,30);
int f9var = 20;
long long f10var = 12000;
rv = stmt->prepare(statement);
if(rv!=OK)
{
delete stmt;
delete con;
return 4;
}
int count=0;
for(int i=0;i<2;i++)
{
f1var = f1var+i;
for(int j=0;j<5;j++)
{
rv = con->beginTrans();
if(rv!=OK)break;
stmt->setIntParam(1,f1var);
stmt->setShortParam(2,f2var);
//stmt->setStringParam(3,f3var);
stmt->setFloatParam(3,f4var);
stmt->setFloatParam(4,f5var);
stmt->setDateParam(5,f6var);
if(strcmp(getenv("DSN"),"oracle")==0){
stmt->setTimeStampParam(6,f8var);
stmt->setIntParam(7,f9var);
stmt->setLongLongParam(8,f10var);
}else{
stmt->setTimeParam(6,f7var);
stmt->setTimeStampParam(7,f8var);
stmt->setIntParam(8,f9var);
stmt->setLongLongParam(9,f10var);
}
rv = stmt->execute(rows);
if(rv!=OK)break;
rv = con->commit();
if(rv!=OK)break;
count++;
}
}
printf("%d Rows Inserted\n",count);
stmt->free();
//*******************************************
strcpy(statement,"SELECT f8,f10,f2,f6,f3 FROM t1 WHERE f1=100;");
if(strcmp(getenv("DSN"),"db2")==0)
strcpy(statement,"SELECT f8,f10,f2,f6,f3 FROM t1 WHERE f1=100");
rv = stmt->prepare(statement);
int nop;
nop = stmt->noOfProjFields();
if(nop!=5)return 1;
printf("Total number of projection fields in the select statement=%d\n",nop);
// getParamFldInfo()
FieldInfo *field = new FieldInfo();
char fldName[20];
DataType type;
size_t length;
int offset;
char defaultValueBuf[10];
bool isNull;
bool isDefault;
if(rv !=OK)
{
delete stmt;
delete con;
return 5;
}
char f3var[32]="jitu";
stmt->bindField(1,&f8var);
stmt->bindField(2,&f10var);
stmt->bindField(3,&f2var);
stmt->bindField(4,&f6var);
stmt->bindField(5,f3var);
count = 0;
rv = con->beginTrans();
if(rv!=OK)return 6;
stmt->execute(rows);
while(stmt->fetch()!=NULL)
{
printf("F1=%d | F2=%hd | F3=%s | F4=%f | F5=%f | DATE=%d-%d-%d | TIME=%d:%d:%d | TIMESTAMP=%d-%d-%d %d:%d:%d | f9=%d | f10=%lld \n",f1var,f2var,f3var,f4var,f5var,f6var.year(),f6var.month(),f6var.dayOfMonth(),f7var.hours(),f7var.minutes(),f7var.seconds(),f8var.year(),f8var.month(),f8var.dayOfMonth(),f8var.hours(),f8var.minutes(),f8var.seconds(),f9var,f10var);
count++;
}
stmt->close();
rv = con->commit();
if(rv!=OK)
{
delete stmt;
delete con;
return 6;
}
printf("Total row fetched=%d\n",count);
stmt->free();
strcpy(statement,"DROP TABLE t1;");
if(strcmp(getenv("DSN"),"db2")==0)
strcpy(statement,"DROP TABLE t1");
rv = stmt->prepare(statement);
if(rv!=OK)
{
delete stmt;
delete con;
return 7;
}
rv = stmt->execute(rows);
if(rv!=OK)
{
delete stmt;
delete con;
return 8;
}
stmt->free();
con->commit();
printf("Table dropped\n");
delete stmt;
delete con;
return 0;
}
int main()
{
DbRetVal rv = OK;
AbsSqlConnection *con = createConnection();
rv = con->connect("root","manager");
if(rv!=OK)return 1;
AbsSqlStatement *stmt = createStatement();
stmt->setConnection(con);
char statement[200];
strcpy(statement,"CREATE TABLE t1(f1 INT,f2 CHAR(20));");
if(strcmp(getenv("DSN"),"db2")==0)
strcpy(statement,"CREATE TABLE t1(f1 INT,f2 CHAR(20))");
int rows=0;
rv = stmt->prepare(statement);
if(rv!=OK)
{
delete stmt;
delete con;
return 1;
}
rv = stmt->execute(rows);
if(rv!=OK)
{
delete stmt;
delete con;
return 2;
}
stmt->free();
printf("Table created\n");
con->commit();
// insert records
strcpy(statement,"INSERT INTO t1 VALUES(?,?);");
if(strcmp(getenv("DSN"),"db2")==0)
strcpy(statement,"INSERT INTO t1 VALUES(?,?)");
int f1var = 1;
char f2var[20] = "lakshya";
rv = stmt->prepare(statement);
if(rv!=OK)
{
delete stmt;
delete con;
return 3;
}
int count=0;
for(int i=0;i<2;i++)
{
for(int j=0;j<5;j++)
{
f1var = j;
rv = con->beginTrans();
if(rv!=OK)break;
stmt->setIntParam(1,f1var);
stmt->setStringParam(2,f2var);
rv = stmt->execute(rows);
if(rv!=OK)break;
rv = con->commit();
if(rv!=OK)break;
count++;
}
printf("%d rows inserted\n",count);
}
stmt->free();
//*******************************************
strcpy(statement,"SELECT * FROM t1;");
if(strcmp(getenv("DSN"),"db2")==0)
strcpy(statement,"SELECT * FROM t1");
/*rv = stmt->prepare(statement);
if(rv!=OK)
{
delete stmt;
delete con;
return 1;
} */
stmt->bindField(1,&f1var);
stmt->bindField(2,f2var);
void *ret;
count = 0;
rv = con->beginTrans();
if(rv!=OK)return 6;
stmt->execute(rows);
while(1)
{
ret = ((char*)stmt->fetch());
if(ret ==NULL)
{
printf("fetch failed without prepare\n");
strcpy(statement,"DROP TABLE t1;");
if(strcmp(getenv("DSN"),"db2")==0)
strcpy(statement,"DROP TABLE t1;");
rv = stmt->prepare(statement);
rv = stmt->execute(rows);
if(rv==OK){printf("Table dropped successfully\n");}
stmt->free();
con->commit();
delete stmt;
delete con;
return 0;
}
printf("Test script failed\n");
}
delete stmt;
delete con;
return 8;
}
void MyTblFmncRMFileMSample::initStatements() {
stmtInsertRec = createStatement("INSERT INTO TblFmncRMFileMSample (refFmncMFile, refFmncMSample) VALUES (?,?)", false);
stmtUpdateRec = createStatement("UPDATE TblFmncRMFileMSample SET refFmncMFile = ?, refFmncMSample = ? WHERE ref = ?", false);
stmtRemoveRecByRef = createStatement("DELETE FROM TblFmncRMFileMSample WHERE ref = ?", false);
};
void MyTblPlnrQDtpAPar::initStatements() {
stmtInsertRec = createStatement("INSERT INTO TblPlnrQDtpAPar (jref, jnum, ref, x2SrefKKey, refPlnrMFile, Val) VALUES (?,?,?,?,?,?)", false);
stmtUpdateRec = createStatement("UPDATE TblPlnrQDtpAPar SET jref = ?, jnum = ?, ref = ?, x2SrefKKey = ?, refPlnrMFile = ?, Val = ? WHERE qref = ?", false);
stmtRemoveRecByQref = createStatement("DELETE FROM TblPlnrQDtpAPar WHERE qref = ?", false);
stmtRemoveRstByJref = createStatement("DELETE FROM TblPlnrQDtpAPar WHERE jref = ?", false);
};
void MyTblFmncAMToolChar::initStatements() {
stmtInsertRec = createStatement("INSERT INTO TblFmncAMToolChar (refFmncMTool, x1OsrefFmncKToolchar, Val, Comment) VALUES (?,?,?,?)", false);
stmtUpdateRec = createStatement("UPDATE TblFmncAMToolChar SET refFmncMTool = ?, x1OsrefFmncKToolchar = ?, Val = ?, Comment = ? WHERE ref = ?", false);
stmtRemoveRecByRef = createStatement("DELETE FROM TblFmncAMToolChar WHERE ref = ?", false);
};
void MyTblFmncJMRunState::initStatements() {
stmtInsertRec = createStatement("INSERT INTO TblFmncJMRunState (refFmncMRun, x1Start, ixVState) VALUES (?,?,?)", false);
stmtUpdateRec = createStatement("UPDATE TblFmncJMRunState SET refFmncMRun = ?, x1Start = ?, ixVState = ? WHERE ref = ?", false);
stmtRemoveRecByRef = createStatement("DELETE FROM TblFmncJMRunState WHERE ref = ?", false);
};
int main()
{
DbRetVal rv = OK;
AbsSqlConnection *con = createConnection();
rv = con->connect("root","manager");
if(rv !=OK) {
delete con;
return 1;
}
printf("Connection opened\n");
AbsSqlStatement *stmt = createStatement();
stmt->setConnection(con);
//Creating Table
char statement[400];
strcpy(statement,"CREATE TABLE pkeyTINY(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f1));");
int rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 2; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 3; }
stmt->free();
strcpy(statement,"CREATE TABLE fkeyTINY(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f1) references pkeyTINY(f1));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 4; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 5; }
stmt->free();
rv = stmt->prepare("DROP TABLE pkeyTINY;");
rv = stmt->execute(rows);
if(rv==OK) { delete stmt; con->disconnect(); delete con; return 6; }
printf("Pkey table pkeyTINY can not be dropped withouting dropping Fkey Table fkeyTINY\n");
stmt->free();
strcpy(statement,"CREATE TABLE pkeySMALL(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f2));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 7; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 8; }
stmt->free();
strcpy(statement,"CREATE TABLE fkeySMALL(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f2) references pkeySMALL(f2));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 9; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 10; }
stmt->free();
rv = stmt->prepare("DROP TABLE pkeySMALL;");
rv = stmt->execute(rows);
if(rv==OK) { delete stmt; con->disconnect(); delete con; return 11; }
printf("Pkey table pkeySMALL can not be dropped withouting dropping Fkey Table fkeySMALL\n");
stmt->free();
strcpy(statement,"CREATE TABLE pkeyINT(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f3));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 12; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 13; }
stmt->free();
strcpy(statement,"CREATE TABLE fkeyINT(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f3) references pkeyINT(f3));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 14; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 15; }
stmt->free();
rv = stmt->prepare("DROP TABLE pkeyINT;");
rv = stmt->execute(rows);
if(rv==OK) { delete stmt; con->disconnect(); delete con; return 16; }
printf("Pkey table pkeyINT can not be dropped withouting dropping Fkey Table fkeyINT\n");
stmt->free();
strcpy(statement,"CREATE TABLE pkeyBIG(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f4));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 17; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 18; }
stmt->free();
strcpy(statement,"CREATE TABLE fkeyBIG(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f4) references pkeyBIG(f4));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 19; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 20; }
stmt->free();
rv = stmt->prepare("DROP TABLE pkeyBIG;");
rv = stmt->execute(rows);
if(rv==OK) { delete stmt; con->disconnect(); delete con; return 21; }
printf("Pkey table pkeyBIG can not be dropped withouting dropping Fkey Table fkeyBIG\n");
stmt->free();
strcpy(statement,"CREATE TABLE pkeyCHAR(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f5));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 22; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 23; }
stmt->free();
strcpy(statement,"CREATE TABLE fkeyCHAR(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f5) references pkeyCHAR(f5));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 24; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 25; }
stmt->free();
rv = stmt->prepare("DROP TABLE pkeyCHAR;");
rv = stmt->execute(rows);
if(rv==OK) { delete stmt; con->disconnect(); delete con; return 26; }
printf("Pkey table pkeyCHAR can not be dropped withouting dropping Fkey Table fkeyCHAR\n");
stmt->free();
strcpy(statement,"CREATE TABLE pkeyVARCHAR(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f6));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 27; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 28; }
stmt->free();
strcpy(statement,"CREATE TABLE fkeyVARCHAR(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f6) references pkeyVARCHAR(f6));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 29; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 30; }
stmt->free();
rv = stmt->prepare("DROP TABLE pkeyVARCHAR;");
rv = stmt->execute(rows);
if(rv==OK) { delete stmt; con->disconnect(); delete con; return 31; }
printf("Pkey table pkeyVARCHAR can not be dropped withouting dropping Fkey Table fkeyVARCHAR\n");
stmt->free();
strcpy(statement,"CREATE TABLE pkeyDATE(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f9));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 32; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 33; }
stmt->free();
strcpy(statement,"CREATE TABLE fkeyDATE(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f9) references pkeyDATE(f9));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 34; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 35; }
stmt->free();
rv = stmt->prepare("DROP TABLE pkeyDATE;");
rv = stmt->execute(rows);
if(rv==OK) { delete stmt; con->disconnect(); delete con; return 36; }
printf("Pkey table pkeyDATE can not be dropped withouting dropping Fkey Table fkeyDATE\n");
stmt->free();
strcpy(statement,"CREATE TABLE pkeyTIME(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, primary key(f10));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 37; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 38; }
stmt->free();
strcpy(statement,"CREATE TABLE fkeyTIME(f1 TINYINT NOT NULL,f2 SMALLINT NOT NULL,f3 INT NOT NULL,f4 BIGINT NOT NULL,f5 CHAR(20) NOT NULL,f6 VARCHAR(30) NOT NULL,f7 FLOAT NOT NULL,f8 DOUBLE NOT NULL,f9 DATE NOT NULL,f10 TIME NOT NULL,f11 TIMESTAMP NOT NULL, foreign key(f10) references pkeyTIME(f10));");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 39; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 40; }
stmt->free();
rv = stmt->prepare("DROP TABLE pkeyTIME;");
rv = stmt->execute(rows);
if(rv==OK) { delete stmt; con->disconnect(); delete con; return 41; }
printf("Pkey table pkeyTIME can not be dropped withouting dropping Fkey Table fkeyTIME\n");
stmt->free();
// Show all tables
strcpy(statement,"GETALLTABLES;");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 42; }
stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 43; }
printf("\t TABLES\n");
printf("\t--------\n");
while(stmt->next() !=NULL) {
printf("\t%s\n",stmt->getFieldValuePtr(2)); //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
}
stmt->free();
//Droping tables
rv = stmt->prepare("DROP TABLE fkeyTINY;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 44; }
printf("fkeyTINY Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE pkeyTINY;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 45; }
printf("pkeyTINY Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE fkeySMALL;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 46; }
printf("fkeySMALL Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE pkeySMALL;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 47; }
printf("pkeySMALL Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE fkeyINT;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 48; }
printf("fkeyINT Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE pkeyINT;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 49; }
printf("pkeyINT Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE fkeyBIG;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 50; }
printf("fkeyBIG Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE pkeyBIG;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 51; }
printf("pkeyBIG Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE fkeyCHAR;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 52; }
printf("fkeyCHAR Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE pkeyCHAR;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 53; }
printf("pkeyCHAR Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE fkeyVARCHAR;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 54; }
printf("fkeyVARCHAR Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE pkeyVARCHAR;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 55; }
printf("pkeyVARCHAR Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE fkeyDATE;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 56; }
printf("fkeyDATE Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE pkeyDATE;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 57; }
printf("pkeyDATE Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE fkeyTIME;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 58; }
printf("fkeyTIME Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE pkeyTIME;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 59; }
printf("pkeyTIME Table Dropped\n");
stmt->free();
// Show all tables
strcpy(statement,"GETALLTABLES;");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 60; }
stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 61; }
while(stmt->next() !=NULL) {
printf("Table Name is %s\n",stmt->getFieldValuePtr(2)); //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
}
stmt->free();
con->disconnect();
printf("Connection Closed\n");
delete stmt; delete con;
return 0;
}
void MyTblFmncQStpRef1NFile::initStatements() {
stmtInsertRec = createStatement("INSERT INTO TblFmncQStpRef1NFile (jref, jnum, ref) VALUES (?,?,?)", false);
stmtUpdateRec = createStatement("UPDATE TblFmncQStpRef1NFile SET jref = ?, jnum = ?, ref = ? WHERE qref = ?", false);
stmtRemoveRecByQref = createStatement("DELETE FROM TblFmncQStpRef1NFile WHERE qref = ?", false);
stmtRemoveRstByJref = createStatement("DELETE FROM TblFmncQStpRef1NFile WHERE jref = ?", false);
};
int main()
{
DbRetVal rv = OK;
AbsSqlConnection *con = createConnection();
rv = con->connect("root","manager");
if(rv !=OK) {
delete con;
return 1;
}
printf("Connection opened\n");
AbsSqlStatement *stmt = createStatement();
stmt->setConnection(con);
//Creating Table
char statement[400];
strcpy(statement,"CREATE TABLE MASTER(f1 TINYINT ,f2 SMALLINT ,f3 INT ,f4 BIGINT ,f5 CHAR(20) ,f6 VARCHAR(30) ,f7 FLOAT ,f8 DOUBLE ,f9 DATE ,f10 TIME ,f11 TIMESTAMP);");
int rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 2; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 3; }
printf("Table MASTER Created\n");
stmt->free();
strcpy(statement,"CREATE TABLE COPYMASTER AS SELECT * FROM MASTER;");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 4; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 5; }
printf("Table COPYMASTER Created\n");
stmt->free();
strcpy(statement,"CREATE TABLE COPYMASTER_FIELDLEVEL AS SELECT f2,f5,f1,f9,f11,f10,f6,f4,f7,f3 FROM MASTER;");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 6; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 7; }
printf("Table COPYMASTER_FIELDLEVEL Created\n");
stmt->free();
strcpy(statement,"CREATE TABLE COPYMASTER_DUPLICATEFIELD AS SELECT f2,f5,f2 FROM MASTER;");
rows=0;
rv = stmt->prepare(statement);
if(rv==OK) { delete stmt; con->disconnect(); delete con; return 8; }
printf("Table COPYMASTER_DUPLICATEFIELD can not be Created because of duplicate fields\n");
// Show all tables
strcpy(statement,"GETALLTABLES;");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 9; }
stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 10; }
printf("\t TABLES\n");
printf("\t--------\n");
while(stmt->next() !=NULL) {
printf("\t%s\n",stmt->getFieldValuePtr(2)); //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
}
stmt->free();
//Droping tables
rv = stmt->prepare("DROP TABLE MASTER;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 11; }
printf("MASTER Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE COPYMASTER;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 12; }
printf("COPYMASTER Table Dropped\n");
stmt->free();
rv = stmt->prepare("DROP TABLE COPYMASTER_FIELDLEVEL;");
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 13; }
printf("COPYMASTER_FIELDLEVEL Table Dropped\n");
stmt->free();
// Show all tables
strcpy(statement,"GETALLTABLES;");
rows=0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 14; }
stmt->execute(rows);
if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 15; }
while(stmt->next() !=NULL) {
printf("Table Name is %s\n",stmt->getFieldValuePtr(2)); //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
}
stmt->free();
con->disconnect();
printf("Connection Closed\n");
delete stmt; delete con;
return 0;
}
int main()
{
DbRetVal rv = OK;
AbsSqlConnection *con= createConnection();
rv = con->connect("root","manager");
if(rv!=OK)return 1;
AbsSqlStatement *stmt = createStatement();
stmt->setConnection(con);
char statement[200];
strcpy(statement,"CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 INT,F10 BIGINT, F11 BINARY(4));");
int rows = 0;
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; delete con; return 1; }
rv = stmt->execute(rows);
if(rv!=OK) { delete stmt; delete con; return 2; }
stmt->free();
printf("Table created\n");
// insert into table
strcpy(statement,"INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?,?,?);");
int f1var = 100;
short int f2var = 10;
char f3var[32]="jitendra";
float f4var = 5.5;
float f5var = 10.50;
Date f6var;
f6var.set(2007,01,21);
Time f7var;
f7var.set(12,29,30);
TimeStamp f8var;
f8var.setDate(2007,01,21);
f8var.setTime(12,29,30);
int f9var = 20;
long long f10var = 12000;
char f11var[8]="23fe";
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; delete con; return 3; }
int count=0;
for(int i=0;i<10;i++) {
rv = con->beginTrans();
if (rv!=OK) break;
stmt->setIntParam(1,f1var);
stmt->setShortParam(2,f2var);
stmt->setStringParam(3,f3var);
stmt->setFloatParam(4,f4var);
stmt->setFloatParam(5,f5var);
stmt->setDateParam(6,f6var);
stmt->setTimeParam(7,f7var);
stmt->setTimeStampParam(8,f8var);
stmt->setIntParam(9,f9var);
stmt->setLongLongParam(10,f10var);
stmt->setBinaryParam(11,f11var, 8);
rv = stmt->execute(rows);
if(rv!=OK)break;
rv = con->commit();
if (rv != OK) break;
count++;
}
printf("Total rows inserted %d\n",count);
stmt->free();
//**********************************************************
// SELECT * FROM T1;
strcpy(statement,"SELECT * FROM T1 WHERE F1=? AND F2=? AND F3=? AND F4=? AND F5=? AND F6=? AND F7=? AND F8=? AND F9=? AND F10=? AND F11=?;");
rv = stmt->prepare(statement);
if(rv!=OK) { delete stmt; delete con; return 4; }
stmt->bindField(1,&f1var);
stmt->bindField(2,&f2var);
stmt->bindField(3,f3var);
stmt->bindField(4,&f4var);
stmt->bindField(5,&f5var);
stmt->bindField(6,&f6var);
stmt->bindField(7,&f7var);
stmt->bindField(8,&f8var);
stmt->bindField(9,&f9var);
stmt->bindField(10,&f10var);
stmt->bindField(11,f11var);
int f1var1=100;
short int f2var1=10;
char f3var1[20]="jitendra";
float f4var1=5.5;
float f5var1=10.50;
Date f6var1;
f6var1.set(2007,1,21);
Time f7var1;
f7var1.set(12,29,30);
TimeStamp f8var1;
f8var1.setDate(2007,1,21);
f8var1.setTime(12,29,30);
int f9var1=20;
long long f10var1=12000;
char f11var1[8]="23fe";
count=0;
rv = con->beginTrans();
if(rv!=OK)return 5;
stmt->setIntParam(1,f1var1);
stmt->setShortParam(2,f2var1);
stmt->setStringParam(3,f3var1);
stmt->setFloatParam(4,f4var1);
stmt->setFloatParam(5,f5var1);
stmt->setDateParam(6,f6var1);
stmt->setTimeParam(7,f7var1);
stmt->setTimeStampParam(8,f8var1);
stmt->setIntParam(9,f9var1);
stmt->setLongLongParam(10,f10var1);
stmt->setBinaryParam(11,f11var1, 8);
stmt->execute(rows);
while(stmt->fetch()!=NULL) {
printf("F1=%d | F2=%hd | F3=%s | F4=%f | F5=%f | DATE=%d-%d-%d | TIME=%d:%d:%d | TIMESTAMP=%d-%d-%d %d:%d:%d | F9=%d | F10=%lld",f1var,f2var,f3var,f4var,f5var,f6var.year(),f6var.month(),f6var.dayOfMonth(),f7var.hours(),f7var.minutes(),f7var.seconds(),f8var.year(),f8var.month(),f8var.dayOfMonth(),f8var.hours(),f8var.minutes(),f8var.seconds(),f9var,f10var);
printf(" | ");
AllDataType::printVal(f11var,typeBinary,4);
printf("\n");
count++;
}
stmt->close();
rv = con->commit();
if(rv!=OK)return 6;
stmt->free();
printf("%d Tuples fetched\n",count);
strcpy(statement,"DROP TABLE T1;");
rv = stmt->prepare(statement);
if(rv!=OK){delete stmt; delete con; return -1;}
rv = stmt->execute(rows);
if(rv!=OK){delete stmt;delete con;return -1;}
printf("Table dropped\n");
stmt->free(); delete stmt; delete con;
return 0;
}
void MyTblPlnrQStkList::initStatements() {
stmtInsertRec = createStatement("INSERT INTO TblPlnrQStkList (jref, jnum, ref, sref, ixVBasetype, dsnRefPlnrMDesign, ixVModtype, ixVFlrAligntype, ixVCeilAligntype) VALUES (?,?,?,?,?,?,?,?,?)", false);
stmtUpdateRec = createStatement("UPDATE TblPlnrQStkList SET jref = ?, jnum = ?, ref = ?, sref = ?, ixVBasetype = ?, dsnRefPlnrMDesign = ?, ixVModtype = ?, ixVFlrAligntype = ?, ixVCeilAligntype = ? WHERE qref = ?", false);
stmtRemoveRecByQref = createStatement("DELETE FROM TblPlnrQStkList WHERE qref = ?", false);
stmtRemoveRstByJref = createStatement("DELETE FROM TblPlnrQStkList WHERE jref = ?", false);
};
void MyTblFmncQUsgMNUser::initStatements() {
stmtInsertRec = createStatement("INSERT INTO TblFmncQUsgMNUser (jref, jnum, mref, ref, ixFmncVUserlevel) VALUES (?,?,?,?,?)", false);
stmtUpdateRec = createStatement("UPDATE TblFmncQUsgMNUser SET jref = ?, jnum = ?, mref = ?, ref = ?, ixFmncVUserlevel = ? WHERE qref = ?", false);
stmtRemoveRecByQref = createStatement("DELETE FROM TblFmncQUsgMNUser WHERE qref = ?", false);
stmtRemoveRstByJref = createStatement("DELETE FROM TblFmncQUsgMNUser WHERE jref = ?", false);
};
std::shared_ptr<ODBCLib::CODBCStatement> ODBCLib::CODBCSession::createStatement(const wchar_t* statement) {
std::shared_ptr<ODBCLib::CODBCStatement> result(createStatement());
result->prepare(statement);
return result;
}
