18.4.4 Coding examples using the native interface

Organization of this subsection
(1) Data insertion and retrieval
(2) Data insertion and single-row retrieval
(3) CALL statement execution
(4) Update using a cursor

(1) Data insertion and retrieval

A coding example (sample1.sqlj) of data insertion and retrieval follows:

import java.sql.*;
import JP.co.Hitachi.soft.HiRDB.pdjpp.runtime.*;
//Declare iterator (cursor)
#sql iterator Pos(int,HiRDBCHAR(10),HiRDBNCHAR(5),HiRDBDECIMAL(10,5));

public class sample1{
 public static void main(String args[]){

   //Connect and create table
   try{
     #sql{CONNECT};    //Refer to client environment variable and connect
     #sql{CREATE TABLE SAMPLE1(c1 int,c2 char(10),c3 nchar(5),c4 decimal(10,5))};
   }catch(SQLException e){System.out.println(e.getMessage());};

   //Insert data
   try{
     int InInt = 100;
     HiRDBCHAR InChar = new HiRDBCHAR("CHAR");
     HiRDBNCHAR InNchar = new HiRDBNCHAR("NCHAR");
     HiRDBDECIMAL InDecimal = new HiRDBDECIMAL("12345.678");

     #sql{INSERT INTO SAMPLE1 VALUES(:InInt,:InChar,:InNchar,:InDecimal)};
     #sql{COMMIT};
   }catch(SQLException e){System.out.println(e.getMessage());};

   //Retrieve data (FETCH)
   try{
     Pos sampleCur = null;
     int OutInt = 0;
     HiRDBCHAR OutChar = null;
     HiRDBNCHAR OutNchar = null;
     HiRDBDECIMAL OutDecimal = null;

     #sql sampleCur  = {SELECT * FROM SAMPLE1};
     while(true){
       #sql {FETCH :sampleCur INTO :OutInt ,:OutChar ,:OutNchar ,:OutDecimal };
       if(sampleCur.endFetch()) break;
         System.out.println("c1="+ OutInt +" c2="+ OutChar.getString() +
            " c3="+ OutNchar.getString() + " c4="+ OutDecimal.getString());
     }
   }catch(SQLException e){System.out.println(e.getMessage());};
   try{#sql{DISCONNECT};}catch(SQLException e){System.out.println(e.getMessage());}
 }
}

(2) Data insertion and single-row retrieval

A coding example (sample2.sqlj) of data insertion and single-row retrieval follows:

import java.sql.*;
import JP.co.Hitachi.soft.HiRDB.pdjpp.runtime.*;
//Iterator (cursor) declaration
#sql iterator Pos(int,HiRDBCHAR(10),HiRDBNCHAR(5),HiRDBDECIMAL(10,5));

public class sample1{
 public static void main(String args[]){

   //Connection and table creation
   try{
     #sql{CONNECT};    //Refer to the client environment variables and connect.
     #sql{CREATE TABLE SAMPLE1(c1 int,c2 char(10),c3 nchar(5),c4 decimal(10,5))};
   }catch(SQLException e){System.out.println(e.getMessage());};

   //Insert data
   try{
     int InInt = 100;
     HiRDBCHAR InChar = new HiRDBCHAR("CHAR");
     HiRDBNCHAR InNchar = new HiRDBNCHAR("NCHAR");
     HiRDBDECIMAL InDecimal = new HiRDBDECIMAL("12345.678");

     #sql{INSERT INTO SAMPLE1 VALUES(:InInt,:InChar,:InNchar,:InDecimal)};
     #sql{COMMIT};
   }catch(SQLException e){System.out.println(e.getMessage());};

   //Retrieve data (single-row retrieval)
   try{
     //Declare output variables
     int OutInt = 0;
     HiRDBCHAR OutChar = new HiRDBCHAR(10);
     HiRDBNCHAR OutNchar = new HiRDBNCHAR(5);
     HiRDBDECIMAL OutDecimal = new HiRDBDECIMAL(10,5);

     #sql {SELECT * INTO :OutInt,:OutChar,:OutNchar,:OutDecimal FROM SAMPLE1};
     System.out.println("c1="+ OutInt +" c2="+ OutChar.getString() +
       " c3="+ OutNchar.getString() + " c4="+ OutDecimal.getString());
   }catch(SQLException e){System.out.println(e.getMessage());};
   try{#sql{DISCONNECT};}catch(SQLException e){System.out.println(e.getMessage());}
 }
}

(3) CALL statement execution

A coding example (sample3.sqlj) of CALL statement execution follows:

import java.sql.*;
import JP.co.Hitachi.soft.HiRDB.pdjpp.runtime.*;

public class sample3{
 public static void main(String args[]){

   Integer PInteger1 = new Integer(99);
   Integer PInteger2 = new Integer(100);
   Integer PInteger3 = new Integer(101);try{
     #sql {CONNECT};
   }catch(SQLException e){System.out.println(e.getMessage());}

   try{
     #sql {DROP PROCEDURE PROCSQLJ};
     #sql {DROP TABLE PROCTABLE};
   }catch(SQLException e1){}

   try{
     #sql {CREATE TABLE PROCTABLE(c1 int, c2 int)};
     #sql {CREATE PROCEDURE PROC1(in p1 int,out p2 int,inout p3 int)
       begin
       insert into PROCTABLE values(p1,p3);
       select * into p2,p3 from PROCTABLE;
       end};
     #sql {COMMIT};
   }catch(SQLException e){System.out.println(e.getMessage());}

   try{
     #sql {CALL PROC1(in :PInteger1 ,out :PInteger2 ,inout :PInteger3 )};
   }catch(SQLException e){System.out.println(e.getMessage());}

   System.out.println("IN parameter PInteger1 = " + PInteger1 );
   System.out.println("OUT parameter PInteger2 = " + PInteger2 );
   System.out.println("INOUT parameter PInteger3 = " + PInteger3 );

   try{#sql {DISCONNECT};}catch(SQLException e){System.out.println(e.getMessage());}
 }
}

(4) Update using a cursor

A coding example (sample4.sqlj) of update using a cursor follows:

import java.sql.*;
import JP.co.Hitachi.soft.HiRDB.pdjpp.runtime.*;
#sql iterator iterP implements JP.co.Hitachi.soft.HiRDB.pdjpp.runtime.ForUpdate(short);

public class sample4{
 public static void main(String args[]){
   iterP positer = null;
   iterP positer2 = null;
   short indata;
   short indata2 = 0;
   short indata3 = 999;
   try{
     #sql {CONNECT};
     #sql {DROP TABLE CURTABLE};
   }catch(SQLException e){System.out.println(e.getMessage());}

   //Create table
   try{#sql {CREATE TABLE CURTABLE(c1 smallint)};
   }catch(SQLException e){System.out.println(e.getMessage());}
   //Insert data
   for(short i = 0;i < 5;i++){
     indata = i;
     try{#sql{INSERT INTO CURTABLE VALUES(:indata)};}catch(SQLException e){}
   }
   //Execute SELECT and update using cursor
   try{
     #sql positer = {SELECT * FROM CURTABLE};
   }catch(SQLException e){}
   try{
     while(true){
       #sql {FETCH :positer INTO :indata2};
       if(positer.endFetch()) break;
       System.out.println(indata2);
       #sql { UPDATE CURTABLE SET C1=:indata3 WHERE CURRENT OF :positer };
     }
   }catch(SQLException e){e.getMessage();}

   //Check update results
   try{#sql positer2 = {SELECT * FROM CURTABLE};}catch(SQLException e){}
   try{
     while(true){
       #sql {FETCH :positer2 INTO :indata2};
       if(positer2.endFetch()) break;
       System.out.println(indata2);
     }
   }catch(SQLException e){System.out.println(e.getMessage());}
   try{#sql{DISCONNECT};}catch(SQLException e){}
 }
}