test/jdbc/network/Statement/TestScript7.java

   1
   2 //executequery with ins, upd, del statements which will throw exception
   3
   4 import java.sql.Connection;
   5 import java.sql.DriverManager;
   6 import java.sql.PreparedStatement;
   7 import java.sql.ResultSet;
   8 import java.sql.Statement;
   9
  10 /**
  11  *
  12  * @author bijaya
  13  */
  14 public class TestScript7 {
  15  public static void main(String[] args)
  16     {
  17        try
  18        {
  19            Class.forName("csql.jdbc.JdbcSqlDriver");
  20            Connection con = DriverManager.getConnection("jdbc:csql://localhost:5678", "root", "manager");
  21            Statement cStmt = con.createStatement();
  22            cStmt.execute("CREATE TABLE T1 (f1 integer, f2 char (20));");
  23            con.commit();
  24            PreparedStatement stmt = null, selStmt= null;
  25            stmt = con.prepareStatement("INSERT INTO T1 VALUES (?, ?);");
  26            for (int i =0 ; i< 10 ; i++) {
  27              stmt.setInt(1, i);
  28              stmt.setString(2, String.valueOf(i+100));
  29              stmt.executeQuery();
  30            }
  31            stmt.close();
  32            con.commit();
  33            selStmt = con.prepareStatement("SELECT * from T1 ;");
  34            ResultSet rs = null;
  35            rs = selStmt.executeQuery();
  36            while (rs.next())
  37            {
  38                System.out.println("Tuple value is " + rs.getInt(1)+ " "+ rs.getString(2));
  39            }
  40            rs.close();
  41
  42            int ret=0;
  43            stmt = con.prepareStatement("UPDATE T1 SET f2 = ? WHERE f1 = ?;");
  44            for (int i =0 ; i< 10 ; i +=2) {
  45              stmt.setString(1, String.valueOf(i+200));
  46              stmt.setInt(2, i);
  47              stmt.executeQuery();
  48
  49            }
  50            stmt.close();
  51            con.commit();
  52
  53            System.out.println("After update, listing tuples:");
  54
  55            rs = selStmt.executeQuery();
  56            while (rs.next())
  57            {
  58                System.out.println("Tuple value is " + rs.getInt(1)+ " "+ rs.getString(2));
  59            }
  60            rs.close();
  61
  62            con.commit();
  63            stmt = con.prepareStatement("DELETE FROM T1 WHERE f1 = ?;");
  64            for (int i =0 ; i< 10 ; i +=3) {
  65             stmt.setInt(1, i);
  66             stmt.executeQuery();
  67
  68            }
  69            stmt.close();
  70            con.commit();
  71
  72            System.out.println("After delete, listing tuples:");
  73
  74            rs = selStmt.executeQuery();
  75            while (rs.next())
  76            {
  77                System.out.println("Tuple value is " + rs.getInt(1)+ " "+ rs.getString(2));
  78            }
  79            rs.close();
  80
  81            con.commit();
  82            cStmt.execute("DROP TABLE T1 ;");
  83            con.close();
  84         }catch(Exception e) {
  85              System.out.println("Exception in Test: "+e);
  86              e.printStackTrace();
  87              System.exit(0);
  88         }
  89     }
  90
  91 }