Connection 8 22 UniSQLConnection / / 9 3 UniSQL OID SET

Transcription

1

2 Connection 8 22 UniSQLConnection / / 9 3 UniSQL OID SET GLO GLO GLO 27 1

3 1 JDBC UniSQL DBMS( UniSQL) JDBC API UniSQL JDBC UniSQL UniSQL 11 UniSQL JDBC 111 JDBC 10 UniSQL/X Realese 41 UniCAS 41 JDK JDBC 20 UniSQL/X Realese 43 UniCAS 42 JDK UniSQL JDBC UniCAS CLASSPATH UniCAS UniSQL Pooling UniSQL JDBC UniCAS 121 UniCAS UniCAS Easy-Manager Terminal 1211 Easy-Manager UniCAS JDBC UniCAS 1 CAS Type ON, 2 CAS Type 2

4 Application Server Easy-Manager CAS Type 1 EasyManager 2 EasyManager Application Server Broker Level Broker Add 1 ~ 3 3 3

5 1 4 Broker Add Step1 Broker Name : Broker AS Type : CAS APPL_ROOT : CAS AS Minimum : AS Maximum : Broker Port : JDBC Connection Description : 4

6 2 5 Broker Add Step2 Advanced option : 1 Description : 3 6 Broker Add Step3 Report : 5

7 1211 Terminal UniCAS UniCAS monitor ( 7 ) JDBC CAS type broker, CAS broker Application Server Terninal CAS type 7 broker and current job information CAS $UNICAS/conf/unicasconf broker ( 8 ) broker SERVICE ON, Application Server 1 MIN_NUM_APPL_SERVER 1 6

8 8 specify the parameters of unicasconf file UniCAS Installation Guide 5~ CLASSPATH CLASSPATH UniSQL JDBC ( UniCAS Installation Guide 17~18 ) Ex) csh - setenv CLASSPATH ${CLASSPATH:${HOME/jdbc/unisqljdbc202jar 7

9 2 UniSQL SQL UniSQL RDBMS JDBC UniSQL Connection 21 Connection Connection Connection DriverManagergetConnection( jdbc : unisql : IP : PORT :::, ID, PASSWORD ); IP : IP PORT : UniCAS Type ID : DBA PUBLIC 2 ID PUBLIC PASSWORD : PASSWORD 22 UniSQLConnection UniSQL JDBC Connection UniSQLConnection GLO getnewinstance API UniSQLConnection UniSQLConnection (UniSQLConnection)DriverManagergetConnection( jdbc:unisql:ip : PORT :::, ID, PASSWORD ); 23 RDBMS import javasql*; public class selectdata { public static void main(string[] args) throws Exception { 8

10 Connection null; Statement stmt = null; ResultSet rs = null; // UniSQL DB Connect ClassforName("unisqljdbcdriverUniSQLDriver"); // 1 DriverManagergetConnection("jdbc:unisql: :43300:demodb:::","",""); // 2 String sql = "select name, country, cost, checkout_time, number_of_restaurants from hotel"; stmt = conncreatestatement(); rs = stmtexecutequery(sql); while(rsnext()) { String name = rsgetstring("name"); String country = rsgetstring("country"); String cost = rsgetstring("cost"); String checkout_time = rsgetstring("checkout_time"); String number_of_restaurants = rsgetstring("number_of_restaurants"); Systemoutprintln("name ==> " + name); Systemoutprintln("country ==> " + country); Systemoutprintln("cost ==> " + cost); Systemoutprintln("checkout_time ==> " + checkout_time); Systemoutprintln("number_of_restaurants ==> " + number_of_restaurants); Systemoutprintln("\n=========================================\n"); rsclose(); stmtclose(); connclose(); catch ( SQLException e ) { Systemerrprintln(egetMessage()); catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); 1 : UniSQL JDBC 2 : Connection sample ID PUBLIC user Connection user 2 : Connection / /,, RDBMS 9

11 import javasql*; public class insertdata { public static void main(string[] args) throws Exception { Connection null; Statement stmt = null; // UniSQL DB Connect ClassforName("unisqljdbcdriverUniSQLDriver"); DriverManagergetConnection("jdbc:unisql: :43300:demodb:::","",""); // connection autocommit (default autocommit ) connsetautocommit(false); // 1 String sql = "insert into test_class(cur_date) values (SYS_DATE)"; stmt = conncreatestatement(); stmtexecuteupdate(sql); Systemoutprintln(" "); stmtclose(); conncommit(); catch ( SQLException e ) { Systemerrprintln(egetMessage()); catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); 1 : true autocommit false (3212 ) 10

12 3 UniSQL 31 OID JDBC OID OID, OID DB user_info class car attribute car class, composition CREATE CLASS car ( name char(40), spec string, color char(20) ) CREATE CLASS user_info ( name char(20), addr string, car car ) 311 OID Connection OID string UniSQLOID 3111 OID attribute OID user_info car OID OID name, spec, color import javasql*; import unisqlsql*; // 1 public class selectoid2 { public static void main(string[] args) throws Exception { Connection null; Statement stmt = null; ResultSet rs = null; ResultSet urs = null; // UniSQL DB Connect ClassforName("unisqljdbcdriverUniSQLDriver"); DriverManagergetConnection("jdbc:unisql: :43300:demodb:::","",""); 11

13 connsetautocommit(false); // 2 // car attribute select // OID String sql = "select name, addr, car from user_info"; stmt = conncreatestatement(); rs = stmtexecutequery(sql); // OID attribute array String [] attr = {"name", "spec", "color"; // 3 while(rsnext()) { String user_name = rsgetstring("name"); String user_addr = rsgetstring("addr"); // select OID UniSQLOID UniSQLOID user_car = (UniSQLOID)rsgetObject("car"); // 4 // OID attribute // array, ResultSet urs = user_cargetvalues(attr); // 5 ursnext(); String car_name = ursgetstring("name"); String car_spec = ursgetstring("spec"); String car_color = ursgetstring("color"); Systemoutprintln(" : " + user_name); Systemoutprintln(" : " + user_addr); Systemoutprintln(" : " + car_name); Systemoutprintln(" : " + car_spec); Systemoutprintln(" : " + car_color); Systemoutprintln("\n==================================================\n"); ursclose(); rsclose(); stmtclose(); conncommit(); catch ( SQLException e ) { Systemerrprintln(egetMessage()); catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); user user 1 : OID unisqlsql* import 12

14 2 : ResultSet close() ResultSet ResultSet autocommit close() ResultSet autocommit false 3 : attr OID attribute array 4 : user_info class, user OID OID UniSQLOID getobject 5 : getobject OID API getvalues ResultSet getvalues(attr) : attr String array attribute attribute null attribute (ex getvalues(null) 3212 ) 3112 String OID String, OID String String OID UniSQLOID import javasql*; import unisqlsql*; import unisqljdbcdriver*; // 1 public class selectoid3 { public static void main(string[] args) throws Exception { UniSQLConnection null; ResultSet urs = null; // OID String String Car_OID_S = "@ "; // 2 ClassforName("unisqljdbcdriverUniSQLDriver"); (UniSQLConnection)DriverManagergetConnection("jdbc:unisql: :43300:demodb:::","", ""); // 3 // OID string UniSQLOID UniSQLOID Car_OID = UniSQLOIDgetNewInstance(conn, Car_OID_S); String [] attr = {"name", "spec", "color"; // 4 // OID ResultSet urs = Car_OIDgetValues(attr); ursnext(); String name = ursgetstring("name"); 13

15 String spec = ursgetstring("spec"); String color = ursgetstring("color"); Systemoutprintln("car_name ==> " + name); Systemoutprintln("car_spec ==> " + spec); Systemoutprintln("car_color ==> " + color); Systemoutprintln("\n=========================================\n"); ursclose(); catch ( SQLException e ) { Systemerrprintln(egetMessage()); catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); (servlet, JSP ) OID hidden OID string UniSQLOID 2 : OID string OID Car_OID_S ( ) 4 : string OID UniSQLOID getnewinstance 3 : getnewinstance connection, connection UniSQLConnection 1 : UniSQLConnection unisqljdbcdriver* import 312 OID OID OID OID 3121 OID car, OID user_info user import javasql*; import unisqlsql*; import unisqljdbcdriver*; // 1 public class insertoid { 14

16 public static void main(string[] args) throws Exception { Connection null; UniSQLStatement ustmt = null; UniSQLPreparedStatement upstmt = null; // UniSQL DB Connect ClassforName("unisqljdbcdriverUniSQLDriver"); DriverManagergetConnection("jdbc:unisql: :43300:demodb:::","",""); connsetautocommit(false); String sql = "insert into car values (' ','2000 cc',' ')"; // executeinsert UniSQLStatement ustmt = (UniSQLStatement)conncreateStatement(); // 2 // executeinsert insert insert insert instance OID UniSQLOID car_oid = ustmtexecuteinsert(sql); // 3 Systemoutprintln(" "); // OID class UniSQLPreparedStatement // //? OID sql = "insert into user_info(name, addr, car) values (' ', ' ',?)"; upstmt = (UniSQLPreparedStatement)connprepareStatement(sql); upstmtsetoid(1,car_oid); // 6 upstmtexecuteupdate(); Systemoutprintln("user "); ustmtclose(); upstmtclose(); conncommit(); // 5 // 4 catch ( SQLException e ) { Systemerrprintln(egetMessage()); catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); class, OID class car class, OID user_info class user OID 3 : car class OID executeinsert executeinsert insert, OID 15

17 2 : executeinsert Statement UniSQLStatement, unisqljdbcdriver* import ( 1 ) 4 : OID user_info class UniSQLPreparedStatement ( 5 ) OID? setoid ( 6 ) UniSQLPreparedStatement unisqljdbcdriver* import ( 1 ) 313 RDBMS UniSQL OID 3131 OID user_info class car OID OID car class import javasql*; import unisqlsql*; import unisqljdbcdriver*; public class deleteoid { public static void main(string[] args) throws Exception { Connection null; Statement stmt = null; ResultSet rs = null; // UniSQL DB Connect ClassforName("unisqljdbcdriverUniSQLDriver"); DriverManagergetConnection("jdbc:unisql: :43300:demodb:::","",""); String sql = "select car from user_info where name = ' '"; stmt = conncreatestatement(); rs = stmtexecutequery(sql); rsnext(); // OID UniSQLOID user_car = (UniSQLOID)rsgetObject("car"); // OID OID user_carremove(); // 1 Systemoutprintln(" "); rsclose(); stmtclose(); conncommit(); 16

18 catch ( SQLException e ) { Systemerrprintln(egetMessage()); catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); user car OID OID 1 : remove() car user_info class car attribute null ( ) 314 OID OID 2 JDBC API 3141 UPDATE OBJECT UPDATE OBJECT OID import javasql*; import unisqlsql*; import unisqljdbcdriver*; public class updateoid { public static void main(string[] args) throws Exception { Connection null; Statement stmt = null; UniSQLPreparedStatement upstmt = null; ResultSet rs = null; // UniSQL DB Connect ClassforName("unisqljdbcdriverUniSQLDriver"); DriverManagergetConnection("jdbc:unisql: :43300:demodb:::","",""); connsetautocommit(false); String sql = "select car from user_info where name = ' '"; 17

19 stmt = conncreatestatement(); rs = stmtexecutequery(sql); UniSQLOID user_car = null; // ' OID rsnext(); user_car = (UniSQLOID)rsgetObject("car"); // OID UPDATE sql = "update object? set name = 'SM5', spec ='2000cc, ABS, DUAL AIRBAG', color = ' '"; // 1 upstmt = (UniSQLPreparedStatement)connprepareStatement(sql); upstmtsetoid(1,user_car); upstmtexecuteupdate(); // 2 rsclose(); upstmtclose(); stmtclose(); conncommit(); catch ( SQLException e ) { Systemerrprintln(egetMessage()); catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); 1 : OID update object OID 2 : update object OID 3121 UniSQLPreparedStatement 3142 OID import javasql*; import unisqlsql*; 3141 UniSQL JDBC API public class updateoid2 { public static void main(string[] args) throws Exception { Connection null; Statement stmt = null; ResultSet rs = null; 18

20 // UniSQL DB Connect ClassforName("unisqljdbcdriverUniSQLDriver"); DriverManagergetConnection("jdbc:unisql: :43300:demodb:::","",""); // ' OID String sql = "select car from user_info where name = ' '"; stmt = conncreatestatement(); rs = stmtexecutequery(sql); UniSQLOID user_car = null; rsnext(); user_car = (UniSQLOID)rsgetObject("car"); // OID UPDATE String [] attr = { "name", "spec", "color"; // 1 Object [] val = { "SM525V", "2500cc LSD ABS", " "; // 2 user_carsetvalues(attr, val); rsclose(); stmtclose(); conncommit(); catch ( SQLException e ) { Systemerrprintln(egetMessage()); catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); // : update attribute array 2 : attr array array 3 : setvalues 32 SET JDBC SET SET DB user_info2 class car attribute car2 class car2 class spec attribute string CREATE CLASS car2 ( name char(40), spec set_of(string) ) CREATE CLASS user_info2 ( 19

21 name char(20), addr string, car set_of(car2) ) 321 SET derived table Collection SET 3211 derived table SET import javasql*; derived table SET public class selectset1 { public static void main(string[] args) throws Exception { Connection null; Statement stmt = null; ResultSet rs = null; // UniSQL DB Connect ClassforName("unisqljdbcdriverUniSQLDriver"); DriverManagergetConnection("jdbc:unisql: :43300:demodb:::","",""); // SET derived table String sql = "select name, addr, car2name from user_info2, table(car) as t(car2)"; stmt = conncreatestatement(); rs = stmtexecutequery(sql); while(rsnext()) { String user_name = rsgetstring("name"); String user_addr = rsgetstring("addr"); String car_name = rsgetstring("car2name"); Systemoutprintln(" : " + user_name); Systemoutprintln(" : " + user_addr); Systemoutprintln(" : " + car_name); Systemoutprintln("\n================================\n"); rsclose(); stmtclose(); catch ( SQLException e ) { Systemerrprintln(egetMessage()); catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); // 1 20

22 derived table SET car attribute derived table 1 : car attribute t derived table car2 attribute (3212 ) SM5 XG 3212 Collection SET Collection 3211 SET 3211 import javasql*; import unisqljdbcdriver*; import unisqlsql*; public class selectset2 { public static void main(string[] args) throws Exception { Connection null; Statement stmt = null; UniSQLResultSet rs = null; UniSQLResultSet urs = null; ClassforName("unisqljdbcdriverUniSQLDriver"); DriverManagergetConnection("jdbc:unisql: :43300:demodb:::","",""); connsetautocommit(false); // 1 String sql = "select name, addr, car from user_info2"; stmt = conncreatestatement(); rs = (UniSQLResultSet)stmtexecuteQuery(sql); while(rsnext()) { String user_name = rsgetstring("name"); String user_addr = rsgetstring("addr"); // SET getcollection // OID SET UniSQLOID array 21

23 UniSQLOID[] car = (UniSQLOID[])rsgetCollection("car"); Systemoutprintln(" : " + user_name); Systemoutprintln(" : " + user_addr); // 2 // array for(int i=0; i<carlength; i++) { // getvalues null attribute urs = (UniSQLResultSet)car[i]getValues(null); // 3 ursnext(); String car_name = ursgetstring("name"); Systemoutprintln(" : " + car_name); Systemoutprintln("\n================================\n"); ursclose(); rsclose(); stmtclose(); conncommit(); catch ( SQLException e ) { Systemerrprintln(egetMessage()); catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); derived table Collection SET 1 : ResultSet close() ResultSet ResultSet autocommit close() ResultSet autocommit false 2 : SET, getcollection array 3 : array for 1 UniSQLOID getvalues getvalues attribute null null class attribute (3111 ) (3221 ) SM5 XG 22

24 322 SET API API OID SET import javasql*; insert OID { SET public class insertset { public static void main(string[] args) throws Exception { Connection null; Statement stmt = null; // UniSQL DB Connect ClassforName("unisqljdbcdriverUniSQLDriver"); DriverManagergetConnection("jdbc:unisql: :43300:demodb:::","",""); // SET { // SET // car2 OID user_info2 SET String sql = "insert into user_info2 values (" + "' ', ' 679-4', { insert into car2 values (' ',{'2000cc',' ')," + "insert into car2 values ('SM5',{'2500cc',' '))"; // 1 stmt = conncreatestatement(); stmtexecuteupdate(sql); Systemoutprintln(" "); conncommit(); stmtclose(); catch ( SQLException e ) { Systemerrprintln(egetMessage()); catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); 23

25 SET { insert into car2(name, spec) values( SM5, { V6 2500cc, DUAL AIRBACK, ABS ) set_of(string) set_of(car2) { OID, OID, OID OID car2 class OID insert 323 SET API SET / 3231 SET import javasql*; SET public class updateset { public static void main(string[] args) throws Exception { Connection null; Statement stmt = null; ClassforName("unisqljdbcdriverUniSQLDriver"); DriverManagergetConnection("jdbc:unisql: :43300:demodb:::","",""); // SET String sql = "update car2 set spec = spec - {' ' + {' stmt = conncreatestatement(); stmtexecuteupdate(sql); Systemoutprintln(" "); stmtclose(); conncommit(); catch ( SQLException e ) { Systemerrprintln(egetMessage()); catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); ' where name = ' '"; 1 : spec attribute // 1 24

26 3232 API SET SET API import javasql*; import unisqljdbcdriver*; import unisqlsql*; public class updateset2 { public static void main(string[] args) throws Exception { Connection null; Statement stmt = null; UniSQLResultSet rs = null; ClassforName("unisqljdbcdriverUniSQLDriver"); DriverManagergetConnection("jdbc:unisql: :43300:demodb:::","",""); String sql = "select car from user_info2 where name = ' '"; stmt = conncreatestatement(); rs = (UniSQLResultSet)stmtexecuteQuery(sql); while(rsnext()) { UniSQLOID[] car = (UniSQLOID[])rsgetCollection("car"); // API SET for(int i=0; i<carlength; i++) { car[i]addtoset("spec"," "); car[i]removefromset("spec"," "); // 1 rsclose(); stmtclose(); conncommit(); catch ( SQLException e ) { Systemerrprintln(egetMessage()); catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); 1 : 44 SET OID addtoset(attribute, ) / removefromset(attribute, ) 25

27 33 GLO GLO GLO LO, FBO 2 LO, FBO GLO class GLO class class Create class file_object under glo GLO LO 331 GLO InputStream file_object class import javasql*; import javaio*; import unisqlsql*; import unisqljdbcdriver*; public class insertlo { public static void main(string[] args) throws Exception { UniSQLConnection null; FileInputStream inputfile = null; // UniSQL DB Connect ClassforName("unisqljdbcdriverUniSQLDriver"); (UniSQLConnection)DriverManagergetConnection("jdbc:unisql: :43300:demodb:::","",""); 1 // file InputStream inputfile = new FileInputStream("/home/unisql/file/testtxt"); // file_object class UniSQLOID glo_oid = conngetnewglo("file_object", inputfile); inputfileclose(); conncommit(); connclose(); 2 3 catch ( SQLException e ) { Systemerrprintln(egetMessage()); 26

28 catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); catch ( SQLException e ) { Systemerrprintln(egetMessage()); 1 GLO UniSQLConnection 2 GLO InputStream 3 getnewglo() API getnewglo( class, InputStream) : InputStream class UniSQLOID 332 GLO GLO GLO import javasql*; import javaio*; import unisqlsql*; import unisqljdbcdriver*; public class selectglo { public static void main(string[] args) throws Exception { UniSQLConnection null; FileOutputStream outputfile = null; // UniSQL DB Connect ClassforName("unisqljdbcdriverUniSQLDriver"); (UniSQLConnection)DriverManagergetConnection("jdbc:unisql: :52500:shindb:::","",""); 1 // GLO OID String sql = "select file_object from file_object"; 27

29 Statement stmt = conncreatestatement(); ResultSet rs = stmtexecutequery(sql); rsnext(); UniSQLOID glo_oid = (UniSQLOID)rsgetObject("file_object"); 2 // loadglo GLO outputfile = new FileOutputStream("/home/unisql/file/tmp000"); glo_oidloadglo(outputfile); 4 3 outputfileclose(); rsclose(); conncommit(); connclose(); catch ( SQLException e ) { Systemerrprintln(egetMessage()); catch ( Exception e ) { Systemerrprintln(egetMessage()); finally { if ( conn!= null ) connclose(); catch ( SQLException e ) { Systemerrprintln(egetMessage()); 1 UniSQLConnection 2 OID 3 OutPutStream 4 loadglo() API GLO loadglo(outputstream) : GLO OutPutSteeam 28