|
1
21
22 package org.apache.derbyTesting.functionTests.tests.derbynet;
23
24 import java.sql.Connection  ;
25 import java.sql.DriverManager ;
26 import java.sql.PreparedStatement ;
27 import java.sql.Statement ;
28 import java.sql.ResultSet ;
29 import java.math.BigDecimal ;
30 import java.sql.Date ;
31 import java.sql.Time ;
32 import java.sql.Timestamp ;
33 import java.sql.SQLException ;
34 import java.io.ByteArrayInputStream ;
35 import java.io.InputStreamReader ;
36 import org.apache.derbyTesting.functionTests.util.TestUtil;
37 import org.apache.derby.tools.ij;
38
39
42
43 public class csPrepStmt
44 {
45 private static Connection conn = null;
46
47 private static String [] testObjects = {"table t1", "table tab1", "table t2", "table bigtab", "table tstab",
49 "table Numeric_Tab", "table lobCheckOne", "table lobCheckTwo"};
50
51 public static void main (String args[])
52 {
53 try
54 {
55 System.out.println("csPrepStmt Test Starts");
56 ij.getPropertyArg(args);
59 conn = ij.startJBMS();
60
61 if (conn == null)
62 {
63 System.out.println("conn didn't work");
64 return;
65 }
66
67 Statement cleanstmt = conn.createStatement();
68 TestUtil.cleanUpTest(cleanstmt, testObjects);
69
70 PreparedStatement ps;
71 ResultSet rs;
72 boolean hasResultSet;
73 int uc;
74
75 System.out.println("executeUpdate() without parameters");
77 ps = conn.prepareStatement("create table t1(c1 int, c2 int, c3 int)");
78 uc = ps.executeUpdate();
79 System.out.println("Update count is: " + uc);
80
81 System.out.println("executeUpdate() with parameters");
83 ps = conn.prepareStatement("insert into t1 values (?, 5, ?)");
84 ps.setInt(1, 99);
85 ps.setInt(2, 9);
86 uc = ps.executeUpdate();
87 System.out.println("Update count is: " + uc);
88
89 System.out.println("execute() with parameters, no result set returned");
91 ps = conn.prepareStatement("insert into t1 values (2, 6, ?), (?, 5, 8)");
92 ps.setInt(1, 10);
93 ps.setInt(2, 7);
94 hasResultSet = ps.execute();
95 while (hasResultSet)
96 {
97 rs = ps.getResultSet();
98 while (rs.next())
99 System.out.println("ERROR: should not get here!");
100 hasResultSet = ps.getMoreResults();
101 }
102 uc = ps.getUpdateCount();
103 if (uc != -1)
104 System.out.println("Update count is: " + uc);
105
106 System.out.println("executQuery() without parameters");
108 ps = conn.prepareStatement("select * from t1");
109 rs = ps.executeQuery();
110 while (rs.next())
111 System.out.println("got row: "+" "+rs.getInt(1)+" "+rs.getInt(2)+" "+rs.getInt(3));
112 System.out.println("end of rows");
113
114 System.out.println("executQuery() with parameters");
116 ps = conn.prepareStatement("select * from t1 where c2 = ?");
117 ps.setInt(1, 5);
118 rs = ps.executeQuery();
119 while (rs.next())
120 System.out.println("got row: "+" "+rs.getInt(1)+" "+rs.getInt(2)+" "+rs.getInt(3));
121 System.out.println("end of rows");
122
123 System.out.println("execute() with parameters with result set returned");
125 ps = conn.prepareStatement("select * from t1 where c2 = ?");
126 ps.setInt(1, 5);
127 hasResultSet = ps.execute();
128 while (hasResultSet)
129 {
130 rs = ps.getResultSet();
131 while (rs.next())
132 System.out.println("got row: "+" "+rs.getInt(1)+" "+rs.getInt(2)+" "+rs.getInt(3));
133 hasResultSet = ps.getMoreResults();
134 }
135 System.out.println("end of rows");
136 uc = ps.getUpdateCount();
137 if (uc != -1)
138 System.out.println("Update count is: " + uc);
139
140 System.out.println("test different data types for input parameters of a Prepared Statement");
142 ps = conn.prepareStatement("create table t2(ti smallint, si smallint,i int, bi bigint, r real, f float, d double precision, n5_2 numeric(5,2), dec10_3 decimal(10,3), ch20 char(20),vc varchar(20), lvc long varchar,b20 char(23) for bit data, vb varchar(23) for bit data, lvb long varchar for bit data, dt date, tm time, ts timestamp not null)");
143 uc = ps.executeUpdate();
144 System.out.println("Update count is: " + uc);
145
146 byte[] ba = new byte[] {0x00,0x1,0x2,0x3,0x4,0x5,0x6,0x7,0x8,0x9,0xa,0xb,0xc,
148 0xd,0xe,0xf,0x10,0x11,0x12,0x13 };
149
150 ps = conn.prepareStatement("insert into t2 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? , ?)");
151 ps.setByte(1, (byte) 1);
152 ps.setShort(2, (short) 2);
153 ps.setInt(3, 3);
154 ps.setLong(4, 4);
155 ps.setFloat(5, (float) 5.0);
156 ps.setDouble(6, 6.0);
157 ps.setDouble(7, 7.0);
158 ps.setBigDecimal(8, new BigDecimal ("88.88"));
159 ps.setBigDecimal(9, new BigDecimal ("99.1"));
160 ps.setString(10, "column11string");
161 byte[] c11ba = new String ("column11vcstring").getBytes("UTF-8");
162 int len = c11ba.length;
163 ps.setAsciiStream(11, new ByteArrayInputStream (c11ba), len);
164 byte[] c12ba = new String ("column12lvcstring").getBytes("UTF-8");
165 len = c12ba.length;
166 ps.setCharacterStream(12, new InputStreamReader (new ByteArrayInputStream (c12ba),"UTF-8"),len);
167 ps.setBytes(13,ba);
168 ps.setBinaryStream(14, new ByteArrayInputStream (ba), ba.length);
169 ps.setBytes(15,ba);
170 ps.setDate(16, Date.valueOf("2002-04-12"));
171 ps.setTime(17, Time.valueOf("11:44:30"));
172 ps.setTimestamp(18, Timestamp.valueOf("2002-04-12 11:44:30.000000000"));
173 uc = ps.executeUpdate();
174 System.out.println("Update count is: " + uc);
175
176 System.out.println("test setObject on different data types for input parameters of a Prepared Statement");
179 ps = conn.prepareStatement("insert into t2 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? , ?)");
180 ps.setObject(1, new Byte ((byte) 1));
181 ps.setObject(2, new Integer ( 2));
182 ps.setObject(3, new Integer (3));
183 ps.setObject(4, new Long (4));
184 ps.setObject(5, new Float (5.0));
185 ps.setObject(6, new Double (6.0));
186 ps.setObject(7, new Double (7.0));
187 ps.setObject(8, new BigDecimal ("88.88"));
188 ps.setObject(9, new BigDecimal ("99.1"));
189 ps.setObject(10, "column10string");
190 ps.setObject(11, "column11vcstring");
191 ps.setObject(12, "column12lvcstring");
192 ps.setObject(13,ba);
193 ps.setObject(14,ba);
194 ps.setObject(15,ba);
195 ps.setObject(16, Date.valueOf("2002-04-12"));
196 ps.setObject(17, Time.valueOf("11:44:30"));
197 ps.setObject(18, Timestamp.valueOf("2002-04-12 11:44:30.000000000"));
198 uc = ps.executeUpdate();
199 System.out.println("Update count is: " + uc);
200
201 System.out.println("test setNull on different data types for input parameters of a Prepared Statement");
203 ps = conn.prepareStatement("insert into t2 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? , ?)");
204 ps.setNull(1, java.sql.Types.BIT);
205 ps.setNull(2, java.sql.Types.TINYINT);
206 ps.setNull(3, java.sql.Types.SMALLINT);
207 ps.setNull(4, java.sql.Types.INTEGER);
208 ps.setNull(5, java.sql.Types.BIGINT);
209 ps.setNull(6, java.sql.Types.REAL);
210 ps.setNull(7, java.sql.Types.FLOAT);
211 ps.setNull(8, java.sql.Types.DOUBLE);
212 ps.setNull(9, java.sql.Types.NUMERIC);
213 ps.setNull(10, java.sql.Types.DECIMAL);
214 ps.setNull(11, java.sql.Types.CHAR);
215 ps.setNull(12, java.sql.Types.VARCHAR);
216 ps.setNull(13, java.sql.Types.LONGVARCHAR);
217 ps.setNull(14, java.sql.Types.BINARY);
218 ps.setNull(15, java.sql.Types.VARBINARY);
219 ps.setNull(16, java.sql.Types.LONGVARBINARY);
220 ps.setNull(17, java.sql.Types.DATE);
221 ps.setNull(18, java.sql.Types.TIME);
222
223 ps.setTimestamp(18, Timestamp.valueOf("2002-04-12 11:44:31.000000000")); hasResultSet = ps.execute();
225 uc = ps.getUpdateCount();
226 if (uc != -1)
227 System.out.println("Update count is: " + uc);
228
229 ps = conn.prepareStatement("select * from t2");
230 rs = ps.executeQuery();
231 while (rs.next())
232 {
233 System.out.println("got row: "+" " +
234 " "+rs.getByte(1)+" "+rs.getShort(2)+
235 " "+rs.getInt(3)+" "+rs.getLong(4)+
236 " "+rs.getFloat(5)+" "+rs.getDouble(6)+
237 " "+rs.getDouble(7)+" "+rs.getBigDecimal(8)+
238 " "+rs.getBigDecimal(9)+" "+rs.getString(10)+
239 " "+rs.getString(11)+" "+rs.getString(12)+
240 " "+bytesToString(rs.getBytes(13)) +
241 " "+bytesToString(rs.getBytes(14)) +
242 " "+bytesToString(rs.getBytes(15)) +
243 " "+rs.getDate(16)+
244 " "+rs.getTime(17)+" "+rs.getTimestamp(18));
245 Timestamp ts = rs.getTimestamp(18);
246 Timestamp temp = Timestamp.valueOf("2002-04-12 11:44:30.000000000");
247 if (ts.after(temp))
248 System.out.println("After first Timestamp!");
249 else if (ts.before(temp))
250 System.out.println("Before first Timestamp!");
251 else
252 System.out.println("Timestamp match!");
253 }
254 System.out.println("end of rows");
255
256 try {
257 ps = conn.prepareStatement("select * from t2 where i = ?");
258 rs = ps.executeQuery();
259 }
260 catch (SQLException e) {
261 System.out.println("SQLState: " + e.getSQLState() + " message: " + e.getMessage());
262 }
263 try {
264 ps = conn.prepareStatement("insert into t2 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
265 ps.executeUpdate();
266 }
267 catch (SQLException e) {
268 System.out.println("SQLState: " + e.getSQLState() + " message: " + e.getMessage());
269 }
270 try {
271 int tabSize = 1000;
272 String createBigTabSql = "create table bigtab (";
273 for (int i = 1; i <= tabSize; i++)
274 {
275 createBigTabSql += "c"+ i + " int";
276 if (i != tabSize)
277 createBigTabSql += ", ";
278 else
279 createBigTabSql += " )";
280 }
281 ps = conn.prepareStatement(createBigTabSql);
283 uc = ps.executeUpdate();
284
285 insertTab("bigtab",50);
286 insertTab("bigtab",200);
287 insertTab("bigtab", 300);
288 insertTab("bigtab",500);
289 insertTab("bigtab", 1000);
291 selectFromBigTab();
292 System.out.println("Insert too many Columns");
294 insertTab("bigtab", 1001);
295 System.out.println("Expected Syntax error ");
297 insertTab("bigtab", 0);
298 System.out.println("Expected Table does not exist ");
300 insertTab("wrongtab",1000);
301 }
302 catch (SQLException e) {
303 System.out.println("SQLState: " + e.getSQLState() +
304 " message: " + e.getMessage());
305 }
306 finally
307 {
308 try
309 {
310 if(rs != null)
311 {
312 rs.close();
313 rs = null;
314 }
315 if(ps != null)
316 {
317 ps.close();
318 ps = null;
319 }
320 }
321 catch(Exception e){ }
322 }
323
324 test4975(conn);
325 test5130(conn);
326 test5172(conn);
327 testLobInRS(conn);
328
329 conn.close();
330 conn = ij.startJBMS();
332 cleanstmt = conn.createStatement();
333 TestUtil.cleanUpTest(cleanstmt, testObjects);
334 cleanstmt.close();
335 System.out.println("csPrepStmt Test Ends");
336 }
337 catch (Exception e)
338 {
339 e.printStackTrace();
340 }
341 }
342
343 private static void test5130 (Connection conn) throws Exception
346 {
347 int numOfPreparedStatement = 500;
348
349 PreparedStatement [] tempPreparedStatement = new
350 PreparedStatement [numOfPreparedStatement];
351 ResultSet rs;
352 String [] tableName = new String [numOfPreparedStatement];
353 for (int i = 0; i < numOfPreparedStatement; i++)
354 {
355 tempPreparedStatement[i] = conn.prepareStatement(
356 "SELECT COUNT(*) from SYS.SYSTABLES",
357 ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
358 rs = tempPreparedStatement[i].executeQuery();
359 rs.close();
360 }
361 for (int i = 0; i < numOfPreparedStatement; i++)
362 tempPreparedStatement[i].close();
363
364 }
365
366 private static void test5172(Connection conn) throws Exception
367 {
368
369 Statement stmt = conn.createStatement();
370
371 try {
372 stmt.executeUpdate("drop table tab1");
373 }
374 catch (SQLException se)
375 {
376 }
377
378 stmt.executeUpdate( "CREATE TABLE TSTAB (I int, STATUS_TS Timestamp, PROPERTY_TS Timestamp)" );
379 stmt.executeUpdate("INSERT INTO TSTAB VALUES(1 , '2003-08-15 21:20:00','2003-08-15 21:20:00')");
380 stmt.executeUpdate("INSERT INTO TSTAB VALUES(2 , '1969-12-31 16:00:00.0', '2003-08-15 21:20:00')");
381
382 stmt.close();
383
384 String timestamp = "20";
385 String query = "select STATUS_TS " +
386 "from TSTAB " +
387 "where (STATUS_TS >= ? or " +
388 " PROPERTY_TS<?)";
389
390 System.out.println("Negative test setString with Invalid Timestamp:" + timestamp);
391
392 PreparedStatement ps = conn.prepareStatement(query);
393 ps.setString(1,timestamp);
394 ps.setString(2, timestamp );
395 try {
396 ResultSet rs = ps.executeQuery();
397 rs.close();
398 ps.close( );
399 }
400 catch (SQLException e) {
401 System.out.println("SQLState: " + e.getSQLState() + " message: " + e.getMessage());
402 }
403
404 }
405
406
407 private static void test4975(Connection conn) throws Exception
408 {
409 BigDecimal minBigDecimalVal = null;
410 BigDecimal rBigDecimalVal = null;
411 String sminBigDecimalVal = null;
412
413 PreparedStatement pstmt = null;
414 ResultSet rs = null;
415 Statement stmt = null;
416
417 try
418 {
419 stmt = conn.createStatement();
420 String createTableSQL = "create table Numeric_Tab (MAX_VAL NUMERIC(30,15), MIN_VAL NUMERIC(30,15), NULL_VAL NUMERIC(30,15))";
421 stmt.executeUpdate(createTableSQL);
423
424 String insertSQL = "insert into Numeric_Tab values(999999999999999,0.000000000000001, null)";
425 stmt.executeUpdate(insertSQL);
426
427 sminBigDecimalVal = "0.000000000000001";
429 minBigDecimalVal = new BigDecimal (sminBigDecimalVal);
430 logMsg("Minimum BigDecimal Value: " + minBigDecimalVal);
431
432 String sPrepStmt = "update Numeric_Tab set NULL_VAL=?";
434
435 logMsg("Prepared Statement String: " + sPrepStmt);
438
439 pstmt = conn.prepareStatement(sPrepStmt);
441 pstmt.setObject(1,minBigDecimalVal);
442 pstmt.executeUpdate();
443
444 String Null_Val_Query = "Select NULL_VAL from Numeric_Tab";
447 logMsg(Null_Val_Query);
448 rs = stmt.executeQuery(Null_Val_Query);
449 rs.next();
450
451 rBigDecimalVal = (BigDecimal ) rs.getObject(1);
452 logMsg("Returned BigDecimal Value after Updation: " + rBigDecimalVal);
453 logMsg("Value returned from ctssql.stmt: " + minBigDecimalVal);
454
455 if(rBigDecimalVal.compareTo(minBigDecimalVal) == 0)
456 {
457 logMsg("setObject Method sets the designated parameter with the Object");
458 }
459 else
460 {
461 logErr("setObject Method does not set the designated parameter with the Object");
462 throw new Exception ("Call to setObject Method is Failed!");
463 }
464 }
465 catch(SQLException sqle)
466 {
467 logErr("SQL Exception: " + sqle.getMessage());
468 throw sqle;
469 }
470 catch(Exception e)
471 {
472 logErr("Unexpected Exception: " + e.getMessage());
473 throw e;
474 }
475
476 finally
477 {
478 try
479 {
480 if(rs != null)
481 {
482 rs.close();
483 rs = null;
484 }
485 if(pstmt != null)
486 {
487 pstmt.close();
488 pstmt = null;
489 }
490 stmt.executeUpdate("drop table Numeric_Tab");
491 if(stmt != null)
492 {
493 stmt.close();
494 stmt = null;
495 }
496 }
497 catch(Exception e){ }
498 }
499 }
500
501 private static void logErr(String s)
502 {
503 System.err.println(s);
504 }
505
506 private static void logMsg(String s)
507 {
508 System.out.println(s);
509 }
510
511 private static void insertTab(String tabname , int numCols) throws SQLException
512 {
513 PreparedStatement ps;
514 System.out.println("insertTab ( " + tabname + "," + numCols + ")" );
515 String insertSql = "insert into " + tabname + "(";
516 int i;
517
518 for (i = 1; i < numCols; i++)
519 insertSql += "c"+ i + ", ";
520
521 insertSql += "c" + i + ") values ( ";
522
523 for (i = 1; i <= numCols; i++)
524 {
525 insertSql += "?";
526 if (i != numCols)
527 insertSql += ", ";
528 else
529 insertSql += " )";
530 }
531
532 try {
533 ps = conn.prepareStatement(insertSql);
534 for (i = 1; i <= numCols; i++)
536 ps.setInt(i,i);
537 ps.executeUpdate();
538 ps.close();
539 } catch (SQLException e)
540 {
541 System.out.println("SQLState: " + e.getSQLState() +
542 " message: " + e.getMessage());
543 }
545
546 }
547
548 private static void selectFromBigTab() throws SQLException
549 {
550 PreparedStatement ps = null;
551 ResultSet rs = null;
552
553 String selectSQL = "select * from bigtab";
554 System.out.println(selectSQL);
555 ps = conn.prepareStatement(selectSQL);
556 rs = ps.executeQuery();
557 while (rs.next())
558 {
559 System.out.println("Col # 500 = " + rs.getObject(500) +
560 " Col 1000 = " + rs.getObject(1000));
561 }
562
563 rs.close();
564 ps.close();
565
566 }
567
568 private static String bytesToString(byte[] ba)
569 {
570 String s = null;
571 if (ba == null)
572 return s;
573 s = new String ();
574 for (int i = 0; i < ba.length; i++)
575 s += (Integer.toHexString(ba[i] & 0x00ff));
576 return s;
577 }
578
579
581 static void testLobInRS(Connection conn) {
582
583 try {
585 Statement st = conn.createStatement();
586 st.execute("create table lobCheckOne (c clob(30))");
588 st.execute("insert into lobCheckOne values (cast " +
589 "('yayorsomething' as clob(30)))");
590 st.execute("create table lobCheckTwo (b blob(30))");
592 st.execute("insert into lobCheckTwo values (cast " + "( "+
593 TestUtil.stringToHexLiteral("101010001101") +
594 " as blob(30)))");
595 } catch (SQLException e) {
596 System.out.println("FAIL: Couldn't create required objects:");
597 e.printStackTrace();
598 return;
599 }
600
601 try {
602
603
605 System.out.println("CLOB result.");
606 Statement st = conn.createStatement();
607 ResultSet rs = st.executeQuery("select * from lobCheckOne");
608 if (rs.next())
609 System.out.println("GOT ROW: " + rs.getString(1));
610 else
611 System.out.println("FAIL: Statement executed, but returned " +
612 "an empty result set.");
613
614
616 System.out.println("BLOB result.");
617 st = conn.createStatement();
618 rs = st.executeQuery("select * from lobCheckTwo");
619 if (rs.next())
620 System.out.println("GOT ROW: " + rs.getString(1));
621 else
622 System.out.println("FAIL: Statement executed, but returned " +
623 "an empty result set.");
624 rs.close();
625 st.close();
626 } catch (Exception e) {
627 System.out.println("FAIL: Encountered exception:");
628 e.printStackTrace();
629 return;
630 }
631
632 return;
633
634 }
635
636 }
637 |
Popular Tags |
Java API By Example, From Geeks To Geeks.