test/jdbc/Gateway/GwStmt10.java

   1 //update tuple with SET and WHERE clause having params (UPDATE t1 SET f2=NULL, f6=NULL WHERE f1=?AND f9=?)
   2 import java.sql.*;
   3 /**
   4  *
   5  * @author bijaya
   6  */
   7 public class GwStmt10 {
   8  public static void main(String[] arg)
   9     {
  10         try
  11         {
  12             Class.forName("csql.jdbc.JdbcSqlDriver");
  13             Connection con=DriverManager.getConnection("jdbc:csql","root","manager");
  14             Statement cStmt=con.createStatement();
  15             PreparedStatement stmt=null,selStmt=null;
  16             stmt=con.prepareStatement("INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?);");
  17             int ret=0;
  18             for(int i=0;i<10;i++)
  19             {
  20                 stmt.setInt(1,i);
  21                 stmt.setShort(2,(short)(i+1));
  22 //                stmt.setByte(3,(byte)(i+2));
  23                 stmt.setLong(3,(long)i);
  24                 stmt.setFloat(4,(float)1000+i);
  25                 stmt.setString(5, String.valueOf("CSQLCACHE"+i));
  26                 stmt.setDate(6,Date.valueOf("2008-03-21"));
  27                 stmt.setTime(7,Time.valueOf("18:00:00"));
  28                 stmt.setTimestamp(8,Timestamp.valueOf("2008-03-21 18:00:00"));
  29                 ret=stmt.executeUpdate();
  30                 if(ret!=1) break;
  31             }
  32             stmt.close();
  33             con.commit();
  34             selStmt=con.prepareStatement("Select * from t1");
  35             ResultSet rs=null;
  36             rs=selStmt.executeQuery();
  37             while(rs.next())
  38             {
  39                 System.out.println("Tuple value is " + rs.getInt(1) + " "+
  40                                                         rs.getShort(2) + " "+
  41                                                         rs.getLong(3) + " "+
  42                                                         rs.getFloat(4) + " "+
  43                                                         rs.getString(5) + " "+
  44                                                         rs.getDate(6) + " "+
  45                                                         rs.getTime(7) + " "+
  46                                                         rs.getTimestamp(8) + " "
  47                                                         );
  48
  49             }
  50             rs.close();
  51             con.commit();
  52             ret=0;
  53             stmt=con.prepareStatement("UPDATE t1 SET f2=NULL, f5=NULL WHERE f1=?AND f9=?;");
  54             for(int i=0;i<10;i++)
  55             {
  56                 //stmt.setShort(1,(short)null);
  57                 //stmt.setTime(2,null);
  58                 stmt.setInt(1,3);
  59                 stmt.setTimestamp(2,Timestamp.valueOf("2008-03-21 18:00:00"));
  60                 ret=stmt.executeUpdate();
  61                 if(ret!=1) break;
  62             }
  63             stmt.close();
  64             con.commit();
  65             selStmt=con.prepareStatement("Select * from t1");
  66             rs=null;
  67             rs=selStmt.executeQuery();
  68             System.out.println();
  69             while(rs.next())
  70             {
  71                 System.out.print("Tuple value is " + rs.getInt(1)+ " ");
  72                 if(rs.wasNull()) System.out.print(" NULL");
  73                 System.out.print(rs.getShort(2) + " ");
  74                 if(rs.wasNull()) System.out.print(" NULL");
  75 //                System.out.print(rs.getByte(3)  + " ");
  76 //                if(rs.wasNull()) System.out.print(" NULL");
  77                 System.out.print(rs.getLong(3)  + " ");
  78                 if(rs.wasNull()) System.out.print(" NULL");
  79                 System.out.print(rs.getFloat(4) + " ");
  80                 if(rs.wasNull()) System.out.print(" NULL");
  81                 System.out.print(rs.getString(5) + " ");
  82                 if(rs.wasNull()) System.out.print(" NULL");
  83                 System.out.print(rs.getDate(6) + " ");
  84                 if(rs.wasNull()) System.out.print(" NULL");
  85                 System.out.print(rs.getTime(7)  + " ");
  86                 if(rs.wasNull()) System.out.print(" NULL");
  87                 System.out.println(rs.getTimestamp(8) + " ");
  88                 if(rs.wasNull()) System.out.println(" NULL");
  89             }
  90             rs.close();
  91             con.commit();
  92
  93             con.close();
  94        }catch(Exception e) {
  95             System.out.println("Exception in Test: "+e);
  96             e.printStackTrace();
  97         }
  98     }
  99
 100 }