From 14e8a06661044a1bf9165198df66acf7a317f543 Mon Sep 17 00:00:00 2001 From: prabatuty Date: Sat, 15 Dec 2007 05:14:07 +0000 Subject: [PATCH] Adding examples for isql, jdbc, sqlapi --- examples/isql/README | 70 ++++++++++++++++++++++++++++++++ examples/isql/create.sql | 3 ++ examples/isql/delete.sql | 7 ++++ examples/isql/drop.sql | 4 ++ examples/isql/insert.sql | 7 ++++ examples/isql/select.sql | 5 +++ examples/jdbc/Makefile | 7 ++++ examples/jdbc/README | 46 +++++++++++++++++++++ examples/jdbc/jdbcexample.java | 88 +++++++++++++++++++++++++++++++++++++++++ examples/sqlapi/Makefile | 37 +++++++++++++++++ examples/sqlapi/README | 48 ++++++++++++++++++++++ examples/sqlapi/sqlapiexample.c | 85 +++++++++++++++++++++++++++++++++++++++ 12 files changed, 407 insertions(+) create mode 100644 examples/isql/README create mode 100644 examples/isql/create.sql create mode 100644 examples/isql/delete.sql create mode 100644 examples/isql/drop.sql create mode 100644 examples/isql/insert.sql create mode 100644 examples/isql/select.sql create mode 100644 examples/jdbc/Makefile create mode 100644 examples/jdbc/README create mode 100644 examples/jdbc/jdbcexample.java create mode 100644 examples/sqlapi/Makefile create mode 100644 examples/sqlapi/README create mode 100644 examples/sqlapi/sqlapiexample.c diff --git a/examples/isql/README b/examples/isql/README new file mode 100644 index 00000000..9d157100 --- /dev/null +++ b/examples/isql/README @@ -0,0 +1,70 @@ +----------------------------------README--------------------------------------- +This contains an example program that demonstates usage of csql tool. + +What it does? +------------- +A) create.sql +It creates table t1 with two fields f1 integer (primary key) and f2 string. + +B) insert.sql +Inserts 5 rows into the table t1 + +C) select.sql +select value of f2 from table t1 for tuple with f1 value 3 +select all the tuples from table t1 + +D) delete.sql +delete all rows from table t1 with f1 value 3 +delete all rows from table t1; + +E) Drop the table +drop the table object + +How to run +---------- +1. Set the CSQL_CONFIG_FILE environment variable to absolute path of csql.conf file. + $export CSQL_CONFIG_FILE=/tmp/csql.conf +2. Set the LD_LIBRARY_PATH environment variable to csql library path + $export LD_LIBRARY_PATH=$CSQL_INSTALL_ROOT/lib:$LD_LIBRARY_PATH + +3. Run the csql tool with appropriate sql script as input + $csql -u root -p manager -s create.sql + +Output of example: +------------------ +A) create.sql +echo create table t1 (f1 integer, f2 char (20), primary key(f1)); +Statement Executed + +B) insert.sql +Statement Executed: Rows Affected = 1 +Statement Executed: Rows Affected = 1 +Statement Executed: Rows Affected = 1 +Statement Executed: Rows Affected = 1 +Statement Executed: Rows Affected = 1 + +C)select.sql +echo select f2 from t1 where f1 =3; +--------------------------------------------------------- + f2 +--------------------------------------------------------- + Baskar + +echo select * from t1; +--------------------------------------------------------- + f1 f2 +--------------------------------------------------------- + 1 Hari + 2 Mani + 3 Baskar + 4 Babu + 5 Raghu + +D)delete.sql +echo delete from t1 where f1 =3; +Statement Executed: Rows Affected = 1 +echo delete from t1; +Statement Executed: Rows Affected = 4 + +E)drop.sql +Statement Executed diff --git a/examples/isql/create.sql b/examples/isql/create.sql new file mode 100644 index 00000000..666c565e --- /dev/null +++ b/examples/isql/create.sql @@ -0,0 +1,3 @@ +-- create example; +echo create table t1 (f1 integer, f2 char (20), primary key(f1)); +create table t1 (f1 integer, f2 char (20), primary key(f1)); diff --git a/examples/isql/delete.sql b/examples/isql/delete.sql new file mode 100644 index 00000000..d52d39e0 --- /dev/null +++ b/examples/isql/delete.sql @@ -0,0 +1,7 @@ +-- delete example; +echo delete from t1 where f1 =3; +delete from t1 where f1 = 3; + +echo delete from t1; +delete from t1; +commit; diff --git a/examples/isql/drop.sql b/examples/isql/drop.sql new file mode 100644 index 00000000..b3d22e95 --- /dev/null +++ b/examples/isql/drop.sql @@ -0,0 +1,4 @@ +-- drop example + +echo drop table t1; +drop table t1; diff --git a/examples/isql/insert.sql b/examples/isql/insert.sql new file mode 100644 index 00000000..4ffe51f6 --- /dev/null +++ b/examples/isql/insert.sql @@ -0,0 +1,7 @@ +-- insert example; +insert into t1 (f1, f2) values (1, 'Hari'); +insert into t1 values (2, 'Mani'); +insert into t1 values (3, 'Baskar'); +insert into t1 values (4, 'Babu'); +insert into t1 values (5, 'Raghu'); +commit; diff --git a/examples/isql/select.sql b/examples/isql/select.sql new file mode 100644 index 00000000..1c7e182a --- /dev/null +++ b/examples/isql/select.sql @@ -0,0 +1,5 @@ +-- select example ; +echo select f2 from t1 where f1 =3; +select f2 from t1 where f1 = 3; +echo select * from t1; +select * from t1; diff --git a/examples/jdbc/Makefile b/examples/jdbc/Makefile new file mode 100644 index 00000000..6418da31 --- /dev/null +++ b/examples/jdbc/Makefile @@ -0,0 +1,7 @@ +OSNAME = $(shell uname -s) +JAVAC = javac +TARGETS = jdbcexample +jdbcexample: jdbcexample.java + javac jdbcexample.java +clean: + rm -rf *.class diff --git a/examples/jdbc/README b/examples/jdbc/README new file mode 100644 index 00000000..d91b790d --- /dev/null +++ b/examples/jdbc/README @@ -0,0 +1,46 @@ +----------------------------------README--------------------------------------- +This contains an example program that demonstates usage of jdbc interface. + +What it does? +------------- +It creates table T1 with two fields f1 integer and f2 string. +Creates index on f1 field of T1 table. +Inserts 10 rows into the table T1, 1 insert per transaction +Update all rows where f1 field have even numbers +Delete all rows where f1 value is 0,3,6,9 +Select rows with f1 value 0 to 9 and displays +Drop the table + +How to compile +-------------- +1. Make a copy of this directory in a writable area +2. Edit the Makefile and set the JAVAC variable to point to the javac + compiler on your system. +3. Use the make command to compile + $make + +How to run +---------- +1. Set the CSQL_CONFIG_FILE environment variable to absolute path of csql.conf file. + $export CSQL_CONFIG_FILE=/tmp/csql.conf +2. Set the LD_LIBRARY_PATH environment variable to csql library path + $export LD_LIBRARY_PATH=$CSQL_INSTALL_ROOT/lib:$LD_LIBRARY_PATH + +3. Run the class file created by make + $java jdbcexample + +Output of example: +------------------ +Table t1 created +Index created on T1 (f1) +Total Rows inserted 10 +Total Rows updated 5 +Total Rows deleted 4 +Tuple value is 1 101 +Tuple value is 2 202 +Tuple value is 4 204 +Tuple value is 5 105 +Tuple value is 7 107 +Tuple value is 8 208 +Total Rows selected 6 +Dropped table T1 diff --git a/examples/jdbc/jdbcexample.java b/examples/jdbc/jdbcexample.java new file mode 100644 index 00000000..e3393834 --- /dev/null +++ b/examples/jdbc/jdbcexample.java @@ -0,0 +1,88 @@ +import java.sql.*; +public class jdbcexample +{ + public static void main(String[] args) + { + try + { + Class.forName("csql.jdbc.JdbcSqlDriver"); + Connection con = DriverManager.getConnection("jdbc:csql", "root", "manager"); + Statement cStmt = con.createStatement(); + cStmt.execute("CREATE TABLE T1 (f1 integer, f2 char (20));"); + System.out.println("Table t1 created"); + cStmt.execute("CREATE INDEX IDX ON T1 ( f1);"); + System.out.println("Index created on T1 (f1) "); + cStmt.close(); + con.commit(); + + PreparedStatement stmt = null, selStmt= null; + stmt = con.prepareStatement("INSERT INTO T1 (f1, f2) VALUES (?, ?);"); + int count =0; + int ret =0; + for (int i =0 ; i< 10 ; i++) { + stmt.setInt(1, i); + stmt.setString(2, String.valueOf(i+100)); + ret = stmt.executeUpdate(); + if (ret != 1) break; //error + count++; + } + stmt.close(); + con.commit(); + System.out.println("Total Rows inserted " + count); + + count =0; + stmt = con.prepareStatement("UPDATE T1 SET f2 = ? WHERE f1 = ?;"); + for (int i =0 ; i< 10 ; i +=2) { + stmt.setString(1, String.valueOf(i+200)); + stmt.setInt(2, i); + ret = stmt.executeUpdate(); + if (ret != 1) break; //error + count++; + } + stmt.close(); + con.commit(); + System.out.println("Total Rows updated " + count); + + count =0; + stmt = con.prepareStatement("DELETE FROM T1 WHERE f1 = ?;"); + for (int i =0 ; i< 10 ; i +=3) { + stmt.setInt(1, i); + ret = stmt.executeUpdate(); + if (ret != 1) break; //error + count++; + } + stmt.close(); + con.commit(); + System.out.println("Total Rows deleted " + count); + + count =0; + selStmt = con.prepareStatement("SELECT * from T1 where f1 = ?;"); + ResultSet rs = null; + for (int i =0 ; i< 10 ; i++) { + selStmt.setInt(1, i); + rs = selStmt.executeQuery(); + while (rs.next()) + { + System.out.println("Tuple value is " + rs.getInt(1)+ " "+ rs.getString(2)); + count++; + } + rs.close(); + } + selStmt.close(); + con.commit(); + System.out.println("Total Rows selected " + count); + + cStmt.execute("DROP TABLE T1;"); + System.out.println("Dropped table T1"); + cStmt.close(); + + con.close(); + } + catch(Exception e) { + System.out.println("Exception in Test: "+e); + e.printStackTrace(); + } + + } +} + diff --git a/examples/sqlapi/Makefile b/examples/sqlapi/Makefile new file mode 100644 index 00000000..aca2d5ad --- /dev/null +++ b/examples/sqlapi/Makefile @@ -0,0 +1,37 @@ +OSNAME = $(shell uname -s) +PlatForm=supported + +ifeq ($(OSNAME), SunOS) +CPlus = CC +CPlusFlags = -O2 -w -mt -xarch=v8 +SYSLIBS=-ldl -lnsl -lsocket -lposix4 +else +ifeq ($(OSNAME), Linux) +CPlus = g++ +CPlusFlags = -O2 -w +SYSLIBS= -rdynamic -lrt -lpthread -lcrypt +else +PlatForm=notsupported +all: + echo "CSQL is not supported on $(OSNAME) platform" +endif +endif + +INCL= -I$(CSQL_INSTALL_ROOT)/include +LIBS= -L$(CSQL_INSTALL_ROOT)/lib -lcsqlsql -lcsql + +TARGETS = \ + sqlapiexample + +ifeq ($(PlatForm), supported) +all: $(TARGETS) +endif + +.c.o:.c + $(CPlus) $(CPlusFlags) $(INCL) -c -o $@ $< + +sqlapiexample: sqlapiexample.o + $(CPlus) $(CPlusFlags) -o $@ $< $(LIBS) $(SYSLIBS) + +clean: + rm -f *.o sqlapiexample diff --git a/examples/sqlapi/README b/examples/sqlapi/README new file mode 100644 index 00000000..a3828388 --- /dev/null +++ b/examples/sqlapi/README @@ -0,0 +1,48 @@ +----------------------------------README--------------------------------------- +This contains an example program that demonstates usage of sqlapi interface. + +What it does? +------------- +It creates table t1 with two fields f1 integer and f2 string. +Creates index on f1 field of t1 table. +Inserts 100 rows into the table t1, 1 insert per transaction +select rows with f1 value 1 to 10 and displays +Drop the table + +How to compile +-------------- +1. Make a copy of this directory in a writable area +2. Edit the Makefile and set the CPlus variable to point to the C++ + compiler on your system. +3. Set the CSQL_INSTALL_ROOT environment variable to point to your installation + directory or change INCL and LIB variable in the Makefile to point to + installation directory +4. Use the make command to compile + $make + +How to run +---------- +1. Set the CSQL_CONFIG_FILE environment variable to absolute path of csql.conf file. + $export CSQL_CONFIG_FILE=/tmp/csql.conf +2. Set the LD_LIBRARY_PATH environment variable to csql library path + $export LD_LIBRARY_PATH=$CSQL_INSTALL_ROOT/lib:$LD_LIBRARY_PATH + +3. Run the executable created by make + $./sqlapiexample + +Output of example: +------------------ +Table t1 created +Index created on t1(f1) +Total Rows Inserted 100 +Row value is 0 Gopika_0 +Row value is 1 Gopika_1 +Row value is 2 Gopika_2 +Row value is 3 Gopika_3 +Row value is 4 Gopika_4 +Row value is 5 Gopika_5 +Row value is 6 Gopika_6 +Row value is 7 Gopika_7 +Row value is 8 Gopika_8 +Row value is 9 Gopika_9 +Table dropped diff --git a/examples/sqlapi/sqlapiexample.c b/examples/sqlapi/sqlapiexample.c new file mode 100644 index 00000000..0fab91b3 --- /dev/null +++ b/examples/sqlapi/sqlapiexample.c @@ -0,0 +1,85 @@ +#include +int main() +{ + DbRetVal rv = OK; + SqlConnection *con = new SqlConnection(); + rv = con->connect("root", "manager"); + if (rv != OK) return 1; + SqlStatement *stmt = new SqlStatement(); + stmt->setConnection(con); + char statement[1024]; + strcpy(statement, "CREATE TABLE t1 (f1 int, f2 char(20), primary key (f1));"); + 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 -1; } + stmt->free(); + printf("Table t1 created\n"); + + strcpy(statement, "CREATE INDEX t1idx on t1 (f1);"); + 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; } + stmt->free(); + printf("Index created on t1(f1) \n"); + + strcpy(statement, "INSERT INTO t1 (f1, f2) VALUES (?, ?);"); + int id1 =10; + char name[20]; + strcpy(name, "Rithish"); + rv = stmt->prepare(statement); + if (rv != OK) {delete stmt; delete con; return -1; } + int count =0; + for (int i = 0 ; i < 100 ; i++) + { + rv = con->beginTrans(); + if (rv != OK) break; + id1 = i; + stmt->setIntParam(1, id1); + sprintf(name, "Gopika_%d", id1); + stmt->setStringParam(2, name); + 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(); + + strcpy(statement, "SELECT * FROM t1 where f1 = ?;"); + rv = stmt->prepare(statement); + if (rv != OK) {delete stmt; delete con; return -1; } + stmt->bindField(1, &id1); + stmt->bindField(2, name); + for (int i = 0 ; i < 10 ; i++) + { + rv = con->beginTrans(); + if (rv != OK) break; + stmt->setIntParam(1, i); + stmt->execute(rows); + while (stmt->fetch() != NULL) { + printf("Row value is %d %s\n", id1, name); + } + stmt->close(); + rv = con->commit(); + if (rv != OK) break; + } + stmt->free(); + + + 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; } + stmt->free(); + printf("Table dropped\n"); + + + delete stmt; + delete con; + return 0; +} -- 2.11.4.GIT