Source Code Cross Referenced for forbitdata.java in  » Database-DBMS » db-derby-10.2 » org » apache » derbyTesting » functionTests » tests » lang » Java Source Code / Java DocumentationJava Source Code and Java Documentation

Java Source Code / Java Documentation
1. 6.0 JDK Core
2. 6.0 JDK Modules
3. 6.0 JDK Modules com.sun
4. 6.0 JDK Modules com.sun.java
5. 6.0 JDK Modules sun
6. 6.0 JDK Platform
7. Ajax
8. Apache Harmony Java SE
9. Aspect oriented
10. Authentication Authorization
11. Blogger System
12. Build
13. Byte Code
14. Cache
15. Chart
16. Chat
17. Code Analyzer
18. Collaboration
19. Content Management System
20. Database Client
21. Database DBMS
22. Database JDBC Connection Pool
23. Database ORM
24. Development
25. EJB Server geronimo
26. EJB Server GlassFish
27. EJB Server JBoss 4.2.1
28. EJB Server resin 3.1.5
29. ERP CRM Financial
30. ESB
31. Forum
32. GIS
33. Graphic Library
34. Groupware
35. HTML Parser
36. IDE
37. IDE Eclipse
38. IDE Netbeans
39. Installer
40. Internationalization Localization
41. Inversion of Control
42. Issue Tracking
43. J2EE
44. JBoss
45. JMS
46. JMX
47. Library
48. Mail Clients
49. Net
50. Parser
51. PDF
52. Portal
53. Profiler
54. Project Management
55. Report
56. RSS RDF
57. Rule Engine
58. Science
59. Scripting
60. Search Engine
61. Security
62. Sevlet Container
63. Source Control
64. Swing Library
65. Template Engine
66. Test Coverage
67. Testing
68. UML
69. Web Crawler
70. Web Framework
71. Web Mail
72. Web Server
73. Web Services
74. Web Services apache cxf 2.0.1
75. Web Services AXIS2
76. Wiki Engine
77. Workflow Engines
78. XML
79. XML UI
Java
Java Tutorial
Java Open Source
Jar File Download
Java Articles
Java Products
Java by API
Photoshop Tutorials
Maya Tutorials
Flash Tutorials
3ds-Max Tutorials
Illustrator Tutorials
GIMP Tutorials
C# / C Sharp
C# / CSharp Tutorial
C# / CSharp Open Source
ASP.Net
ASP.NET Tutorial
JavaScript DHTML
JavaScript Tutorial
JavaScript Reference
HTML / CSS
HTML CSS Reference
C / ANSI-C
C Tutorial
C++
C++ Tutorial
Ruby
PHP
Python
Python Tutorial
Python Open Source
SQL Server / T-SQL
SQL Server / T-SQL Tutorial
Oracle PL / SQL
Oracle PL/SQL Tutorial
PostgreSQL
SQL / MySQL
MySQL Tutorial
VB.Net
VB.Net Tutorial
Flash / Flex / ActionScript
VBA / Excel / Access / Word
XML
XML Tutorial
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
Java Source Code / Java Documentation » Database DBMS » db derby 10.2 » org.apache.derbyTesting.functionTests.tests.lang 
Source Cross Referenced  Class Diagram Java Document (Java Doc) 


001:        /*
002:
003:           Derby - Class org.apache.derbyTesting.functionTests.tests.lang.forbitdata
004:
005:           Licensed to the Apache Software Foundation (ASF) under one or more
006:           contributor license agreements.  See the NOTICE file distributed with
007:           this work for additional information regarding copyright ownership.
008:           The ASF licenses this file to You under the Apache License, Version 2.0
009:           (the "License"); you may not use this file except in compliance with
010:           the License.  You may obtain a copy of the License at
011:
012:              http://www.apache.org/licenses/LICENSE-2.0
013:
014:           Unless required by applicable law or agreed to in writing, software
015:           distributed under the License is distributed on an "AS IS" BASIS,
016:           WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017:           See the License for the specific language governing permissions and
018:           limitations under the License.
019:
020:         */
021:
022:        package org.apache.derbyTesting.functionTests.tests.lang;
023:
024:        import java.sql.*;
025:
026:        import org.apache.derby.tools.ij;
027:        import java.io.*;
028:        import java.math.BigInteger;
029:        import java.math.BigDecimal;
030:
031:        public class forbitdata {
032:
033:            static private boolean isDB2jNet;
034:
035:            public static void main(String[] argv) throws Throwable {
036:                try {
037:
038:                    ij.getPropertyArg(argv);
039:                    Connection conn = ij.startJBMS();
040:                    // waiting for meta data
041:                    String framework = System.getProperty("framework");
042:                    if (framework != null
043:                            && framework.toUpperCase().equals("DB2JNET"))
044:                        isDB2jNet = true;
045:
046:                    runTests(conn);
047:                } catch (Throwable t) {
048:                    System.out.println("FAIL " + t);
049:                    t.printStackTrace(System.out);
050:                }
051:            }
052:
053:            public static void runTests(Connection conn) throws Throwable {
054:                try {
055:                    testNegative(conn);
056:                    testTypes(conn);
057:                    testValues(conn);
058:                    testCompare(conn);
059:                    testEncodedLengths(conn);
060:
061:                } catch (SQLException sqle) {
062:                    org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
063:                            System.out, sqle);
064:                    sqle.printStackTrace(System.out);
065:                }
066:
067:            }
068:
069:            /**
070:            	Negative for bit data tests.
071:            	FBD001,FBD007 negative syntax
072:            	FBD005 maximum char length
073:            	FBD009 maximum varchar length
074:             */
075:            public static void testNegative(Connection conn)
076:                    throws SQLException {
077:
078:                System.out.println("START testNegative");
079:
080:                Statement s = conn.createStatement();
081:
082:                // 
083:                statementExceptionExpected(s,
084:                        "CREATE TABLE FBDFAIL.T001 (C001 CHAR(255) FOR BIT DATA)");
085:                statementExceptionExpected(s,
086:                        "CREATE TABLE FBDFAIL.T002 (C002 VARCHAR(32673) FOR BIT DATA)");
087:                statementExceptionExpected(s,
088:                        "CREATE TABLE FBDFAIL.T003 (C003 VARCHAR FOR BIT DATA)");
089:                statementExceptionExpected(s,
090:                        "CREATE TABLE FBDFAIL.T004 (C004 LONG VARCHAR(100) FOR BIT DATA)");
091:
092:                s.close();
093:                System.out.println("END testNegative");
094:            }
095:
096:            /**
097:            	FBD001,FBD007 - positive syntax 
098:            	FBD004 - CHAR length defaults to one
099:            	FBD037 - create table
100:            	FBD006, FBD011, FBD014 - correct JDBC type
101:
102:             */
103:            public static void testTypes(Connection conn) throws SQLException {
104:
105:                System.out.println("START testTypes");
106:
107:                Statement s = conn.createStatement();
108:
109:                for (int i = 1; i <= 8; i++)
110:                    executeDrop(s, "DROP TABLE FBDOK.T00" + i);
111:
112:                // FBD037
113:
114:                executeOK(s, "CREATE TABLE FBDOK.T001 (C001 CHAR FOR BIT DATA)");
115:                executeOK(s,
116:                        "CREATE TABLE FBDOK.T002 (C002 CHAR(1) FOR BIT DATA)");
117:                executeOK(s,
118:                        "CREATE TABLE FBDOK.T003 (C003 CHAR(10) FOR BIT DATA)");
119:                executeOK(s,
120:                        "CREATE TABLE FBDOK.T004 (C004 CHAR(254) FOR BIT DATA)");
121:                executeOK(s,
122:                        "CREATE TABLE FBDOK.T005 (C005 VARCHAR(1) FOR BIT DATA)");
123:                executeOK(s,
124:                        "CREATE TABLE FBDOK.T006 (C006 VARCHAR(100) FOR BIT DATA)");
125:                executeOK(s,
126:                        "CREATE TABLE FBDOK.T007 (C007 VARCHAR(32672) FOR BIT DATA)");
127:                executeOK(s,
128:                        "CREATE TABLE FBDOK.T008 (C008 LONG VARCHAR FOR BIT DATA)");
129:
130:                ResultSet rs = conn.getMetaData().getColumns(null, "FBDOK",
131:                        null, null);
132:                while (rs.next()) {
133:                    // skip 1 catalog
134:                    System.out.print(rs.getString(2) + ",");
135:                    System.out.print(rs.getString(3) + ",");
136:                    System.out.print(rs.getString(4) + ",");
137:                    System.out.print(rs.getString(5) + ",");
138:                    System.out.print(rs.getString(6) + ",");
139:                    System.out.print(rs.getString(7) + ",");
140:                    // skip 8 - unused
141:                    System.out.print(rs.getString(9) + ",");
142:                    System.out.print(rs.getString(10) + ",");
143:                    System.out.print(rs.getString(11) + ",");
144:                    // skip 12 remarks
145:                    System.out.print(rs.getString(13) + ",");
146:                    // skip 14,15 unused
147:                    System.out.print(rs.getString(16) + ",");
148:                    System.out.print(rs.getString(17) + ",");
149:                    System.out.println(rs.getString(18));
150:                }
151:                rs.close();
152:
153:                for (int i = 1; i <= 8; i++) {
154:                    try {
155:                        PreparedStatement ps = conn
156:                                .prepareStatement("SELECT * FROM FBDOK.T00" + i);
157:                        ResultSetMetaData rsmd = ps.getMetaData();
158:                        System.out.println("TABLE FBDOK.T00" + i);
159:                        System.out.println("  " + rsmd.getColumnName(1) + " "
160:                                + rsmd.getColumnTypeName(1) + " precision "
161:                                + rsmd.getPrecision(1));
162:                        ps.close();
163:                    } catch (SQLException sqle) {
164:                        showSQLE(sqle);
165:                    }
166:
167:                }
168:
169:                for (int i = 1; i <= 8; i++)
170:                    executeDrop(s, "DROP TABLE FBDOK.T00" + i);
171:
172:                s.execute("DROP SCHEMA FBDOK RESTRICT");
173:
174:                System.out.println("DATABASE META DATA.getTypeInfo()");
175:                DatabaseMetaData dmd = conn.getMetaData();
176:
177:                rs = dmd.getTypeInfo();
178:                while (rs.next()) {
179:                    String name = rs.getString(1);
180:                    int jdbcType = rs.getInt(2);
181:                    switch (jdbcType) {
182:                    case Types.BINARY:
183:                    case Types.VARBINARY:
184:                    case Types.LONGVARBINARY:
185:                        break;
186:                    default:
187:                        continue;
188:                    }
189:
190:                    System.out.print(name + "(" + jdbcType + ") ");
191:                    System.out.print("precision " + rs.getInt(3));
192:                    System.out.println("");
193:                }
194:
195:                rs.close();
196:
197:                {
198:                    String sql = "VALUES X'2345d45a2e44'";
199:                    PreparedStatement psv = conn.prepareStatement(sql);
200:                    ResultSetMetaData rsmd = psv.getMetaData();
201:                    System.out.println(sql);
202:                    System.out.println("  " + rsmd.getColumnName(1) + " "
203:                            + rsmd.getColumnTypeName(1) + " precision "
204:                            + rsmd.getPrecision(1));
205:                }
206:
207:                {
208:                    String sql = "VALUES X''";
209:                    PreparedStatement psv = conn.prepareStatement(sql);
210:                    ResultSetMetaData rsmd = psv.getMetaData();
211:                    System.out.println(sql);
212:                    System.out.println("  " + rsmd.getColumnName(1) + " "
213:                            + rsmd.getColumnTypeName(1) + " precision "
214:                            + rsmd.getPrecision(1));
215:                }
216:
217:                s.close();
218:                System.out.println("END testTypes");
219:            }
220:
221:            public static void testCast(Connection conn) throws SQLException {
222:            }
223:
224:            public static void testValues(Connection conn) throws SQLException {
225:
226:                System.out.println("START testValues");
227:
228:                Statement s = conn.createStatement();
229:                executeDrop(s, "DROP TABLE FBDVAL.T001");
230:                executeDrop(s, "DROP TABLE FBDVAL.X001");
231:                s
232:                        .execute("CREATE TABLE FBDVAL.T001(ID INT NOT NULL PRIMARY KEY, C1 CHAR(10) FOR BIT DATA, C2 VARCHAR(10) FOR BIT DATA, C3 LONG VARCHAR FOR BIT DATA, C4 BLOB(10))");
233:                PreparedStatement psI = conn
234:                        .prepareStatement("INSERT INTO FBDVAL.T001 VALUES(?, ?, ?, ?, ?)");
235:                PreparedStatement psS = conn
236:                        .prepareStatement("SELECT C1, C2, C3, C4, ID FROM FBDVAL.T001 WHERE ID >= ? AND ID < ? ORDER BY ID");
237:
238:                System.out.println("**** NULL");
239:                insertData(psI, 0, null, 10, true);
240:                showData(psS, 0, null);
241:
242:                System.out.println("**** 7 bytes (EMPTY)");
243:                byte[] empty = new byte[7];
244:                insertData(psI, 10, empty, 10, true);
245:                showData(psS, 10, empty);
246:
247:                // DB2
248:                //	CHAR			-- FAIL TOO BIG			
249:                //	VARCHAR			-- FAIL TOO BIG
250:                //	LONG VARCHAR	-- OK
251:                //	BLOB			-- FAIL TOO BIG
252:                System.out.println("**** 15 bytes (EMPTY)");
253:                byte[] empty2 = new byte[15];
254:                insertData(psI, 20, empty2, 10, true);
255:                showData(psS, 20, empty2);
256:
257:                // DB2 - ALL OK
258:                System.out.println("**** 4 bytes");
259:                byte[] four = new byte[4];
260:                four[0] = (byte) 0x04;
261:                four[1] = (byte) 0x23;
262:                four[2] = (byte) 0xA2;
263:                four[3] = (byte) 0xFD;
264:
265:                insertData(psI, 30, four, 10, true);
266:                showData(psS, 30, four);
267:
268:                // DB2 - ALL OK
269:                System.out.println("**** 10 bytes");
270:                byte[] ten = new byte[10];
271:                ten[0] = (byte) 0x0B;
272:                ten[1] = (byte) 0x27;
273:                ten[2] = (byte) 0xA2;
274:                ten[3] = (byte) 0xFD;
275:                ten[4] = (byte) 0x01;
276:                ten[5] = (byte) 0x6D;
277:                ten[6] = (byte) 0xE2;
278:                ten[7] = (byte) 0x35;
279:                ten[8] = (byte) 0x66;
280:                ten[9] = (byte) 0x90;
281:
282:                insertData(psI, 40, ten, 10, true);
283:                showData(psS, 40, ten);
284:
285:                // DB2
286:                //	CHAR			-- FAIL TOO BIG			
287:                //	VARCHAR			-- FAIL TOO BIG
288:                //	LONG VARCHAR	-- OK
289:                //	BLOB			-- FAIL TOO BIG
290:                System.out.println("**** 15 bytes");
291:                byte[] l15 = new byte[15];
292:                l15[0] = (byte) 0xEB;
293:                l15[1] = (byte) 0xCA;
294:                l15[2] = (byte) 0xFE;
295:                l15[3] = (byte) 0xBA;
296:                l15[4] = (byte) 0xBE;
297:                l15[5] = (byte) 0xFE;
298:                l15[6] = (byte) 0xED;
299:                l15[7] = (byte) 0xFA;
300:                l15[8] = (byte) 0xCE;
301:                l15[9] = (byte) 0x24;
302:                l15[10] = (byte) 0x78;
303:                l15[11] = (byte) 0x43;
304:                l15[12] = (byte) 0x92;
305:                l15[13] = (byte) 0x31;
306:                l15[14] = (byte) 0x6D;
307:
308:                insertData(psI, 50, l15, 10, true);
309:                showData(psS, 50, l15);
310:
311:                // DB2 UDB LUW no truncation of spaces for VARCHAR FBD, LONG VARCHAR FBD
312:                System.out.println("**** 4 spaces ");
313:                byte[] space4 = new byte[4];
314:                space4[0] = (byte) 0x20;
315:                space4[1] = (byte) 0x20;
316:                space4[2] = (byte) 0x20;
317:                space4[3] = (byte) 0x20;
318:                insertData(psI, 60, space4, 10, true);
319:                showData(psS, 60, space4);
320:
321:                // DB2 UDB LUW no truncation of spaces for VARCHAR FBD, LONG VARCHAR FBD
322:                System.out.println("**** 6 data with trailing space ");
323:                byte[] space6 = new byte[6];
324:                space6[0] = (byte) 0xca;
325:                space6[1] = (byte) 0xfe;
326:                space6[2] = (byte) 0x20;
327:                space6[3] = (byte) 0x20;
328:                space6[4] = (byte) 0x20;
329:                space6[5] = (byte) 0x20;
330:                insertData(psI, 70, space6, 10, true);
331:                showData(psS, 70, space6);
332:
333:                // DB2
334:                //	CHAR			-- FAIL TOO BIG			
335:                //	VARCHAR			-- FAIL TOO BIG
336:                //	LONG VARCHAR	-- OK
337:                //	BLOB			-- FAIL TOO BIG
338:                System.out.println("**** 12 data with trailing space ");
339:                byte[] space12 = new byte[12];
340:                space12[0] = (byte) 0xca;
341:                space12[1] = (byte) 0xfe;
342:                space12[2] = (byte) 0x20;
343:                space12[3] = (byte) 0x20;
344:                space12[4] = (byte) 0x20;
345:                space12[5] = (byte) 0x20;
346:                space12[6] = (byte) 0xca;
347:                space12[7] = (byte) 0xfe;
348:                space12[8] = (byte) 0x20;
349:                space12[9] = (byte) 0x20;
350:                space12[10] = (byte) 0x20;
351:                space12[11] = (byte) 0x20;
352:                insertData(psI, 210, space12, 10, true);
353:                showData(psS, 210, space12);
354:
355:                String sql = "INSERT INTO FBDVAL.T001 VALUES(80, X'2020202020', X'2020202020', X'2020202020', null)";
356:                System.out.println("**** " + sql);
357:                s.executeUpdate(sql);
358:                showData(psS, 80, space4);
359:
360:                // With a literal the value is truncated into CHAR FBD
361:                sql = "INSERT INTO FBDVAL.T001 VALUES(90, X'CAFE20202020CAFE20202020', null, null, null)";
362:                System.out.println("**** " + sql);
363:                s.executeUpdate(sql);
364:                showData(psS, 90, space12);
365:
366:                sql = "INSERT INTO FBDVAL.T001 VALUES(100, null, X'CAFE20202020CAFE20202020', null, null)";
367:                System.out.println("**** " + sql);
368:                s.executeUpdate(sql);
369:                showData(psS, 100, space12);
370:
371:                sql = "INSERT INTO FBDVAL.T001 VALUES(110, null, null, X'CAFE20202020CAFE20202020', null)";
372:                System.out.println("**** " + sql);
373:                s.executeUpdate(sql);
374:                showData(psS, 110, space12);
375:                /*
376:                 sql = "INSERT INTO FBDVAL.T001 VALUES(150, null, null, null, X'CAFE20202020CAFE20202020')";
377:                 System.out.println("**** " + sql);
378:                 s.executeUpdate(sql);
379:                 showData(psS, 150, space12);
380:                 */
381:                // insert with non-trailing blank from literal
382:                // DB2 22001 error.
383:                sql = "INSERT INTO FBDVAL.T001 VALUES(120, X'CAFE20202020CAFE20202020DD', null, null, null)";
384:                System.out.println("**** " + sql);
385:                try {
386:                    s.executeUpdate(sql);
387:                    System.out.println("FAIL - literal too long on CHAR FBD");
388:                } catch (SQLException sqle) {
389:                    if ("22001".equals(sqle.getSQLState()))
390:                        System.out.println("22001 truncation error");
391:                    else
392:                        showSQLE(sqle);
393:                }
394:
395:                sql = "INSERT INTO FBDVAL.T001 VALUES(130, null, X'CAFE20202020CAFE20202020DD', null, null)";
396:                System.out.println("**** " + sql);
397:                try {
398:                    s.executeUpdate(sql);
399:                    System.out
400:                            .println("FAIL - literal too long on VARCHAR FBD");
401:                } catch (SQLException sqle) {
402:                    if ("22001".equals(sqle.getSQLState()))
403:                        System.out.println("22001 truncation error");
404:                    else
405:                        showSQLE(sqle);
406:                }
407:
408:                sql = "INSERT INTO FBDVAL.T001 VALUES(140, null, null, X'CAFE20202020CAFE20202020DD', null)";
409:                System.out.println("**** " + sql);
410:                s.executeUpdate(sql);
411:                showData(psS, 140, space12);
412:
413:                s
414:                        .execute("CREATE TABLE FBDVAL.X001(XID INT NOT NULL PRIMARY KEY, X1 CHAR(12) FOR BIT DATA, C2 VARCHAR(12) FOR BIT DATA, C3 LONG VARCHAR FOR BIT DATA, C4 BLOB(12))");
415:
416:                sql = "INSERT INTO FBDVAL.X001 VALUES(200, X'CAFE20202020CAFE20202020', null, null, null)";
417:                System.out.println("**** " + sql);
418:                s.executeUpdate(sql);
419:
420:                sql = "INSERT INTO FBDVAL.T001 SELECT * FROM FBDVAL.X001";
421:                System.out.println("**** " + sql);
422:                s.executeUpdate(sql);
423:                showData(psS, 200, space12);
424:
425:                System.out.println("END testValues");
426:            }
427:
428:            private static void insertData(PreparedStatement psI, int id,
429:                    byte[] original, int maxLen, boolean streamAsWell)
430:                    throws SQLException {
431:
432:                int ol = original == null ? 0 : original.length;
433:
434:                if (original == null || original.length <= maxLen) {
435:                    // simple case.
436:                    psI.setInt(1, id);
437:                    psI.setBytes(2, original);
438:                    psI.setBytes(3, original);
439:                    psI.setBytes(4, original);
440:                    psI.setBytes(5, original);
441:                    psI.executeUpdate();
442:
443:                    if (streamAsWell) {
444:                        psI.setInt(1, id + 1);
445:                        psI.setBinaryStream(2, original == null ? null
446:                                : new ByteArrayInputStream(original), ol);
447:                        psI.setBinaryStream(3, original == null ? null
448:                                : new ByteArrayInputStream(original), ol);
449:                        psI.setBinaryStream(4, original == null ? null
450:                                : new ByteArrayInputStream(original), ol);
451:                        psI.setBinaryStream(5, original == null ? null
452:                                : new ByteArrayInputStream(original), ol);
453:                        psI.executeUpdate();
454:                    }
455:                    return;
456:                }
457:
458:                boolean okI1;
459:                boolean okI2;
460:
461:                // Insert potentially out of range value one at a time into the table
462:                System.out.println("  >> CHAR FOR BIT DATA");
463:                try {
464:                    psI.setInt(1, id);
465:                    psI.setBytes(2, original);
466:                    psI.setBytes(3, null);
467:                    psI.setBytes(4, null);
468:                    psI.setBytes(5, null);
469:                    psI.executeUpdate();
470:                    okI1 = true;
471:                } catch (SQLException sqle) {
472:                    okI1 = false;
473:                    if ("22001".equals(sqle.getSQLState())) {
474:                        System.out.println("22001 truncation error");
475:                    } else
476:                        showSQLE(sqle);
477:                }
478:                if (streamAsWell) {
479:                    try {
480:                        psI.setInt(1, id + 1);
481:                        psI.setBinaryStream(2, original == null ? null
482:                                : new ByteArrayInputStream(original), ol);
483:                        psI.executeUpdate();
484:                        okI2 = true;
485:                    } catch (SQLException sqle) {
486:                        okI2 = false;
487:                        if ("22001".equals(sqle.getSQLState())) {
488:                            System.out.println("22001 truncation error");
489:                        } else
490:                            showSQLE(sqle);
491:                    }
492:
493:                    if (okI1 != okI2)
494:                        System.out.println("FAIL - mismatched failures");
495:                }
496:
497:                System.out.println("  >> VARCHAR FOR BIT DATA");
498:                try {
499:                    psI.setInt(1, id + 2);
500:                    psI.setBytes(2, null);
501:                    psI.setBytes(3, original);
502:                    psI.setBytes(4, null);
503:                    psI.setBytes(5, null);
504:                    psI.executeUpdate();
505:                    okI1 = true;
506:
507:                } catch (SQLException sqle) {
508:                    okI1 = false;
509:                    if ("22001".equals(sqle.getSQLState()))
510:                        System.out.println("22001 truncation error");
511:                    else
512:                        showSQLE(sqle);
513:                }
514:                if (streamAsWell) {
515:                    try {
516:                        psI.setInt(1, id + 3);
517:                        psI.setBinaryStream(3, original == null ? null
518:                                : new ByteArrayInputStream(original), ol);
519:                        psI.executeUpdate();
520:                        okI2 = true;
521:
522:                    } catch (SQLException sqle) {
523:                        okI2 = false;
524:                        if ("22001".equals(sqle.getSQLState()))
525:                            System.out.println("22001 truncation error");
526:                        else
527:                            showSQLE(sqle);
528:                    }
529:                    if (okI1 != okI2)
530:                        System.out.println("FAIL - mismatched failures");
531:                }
532:
533:                System.out.println("  >> LONG VARCHAR FOR BIT DATA");
534:                try {
535:                    psI.setInt(1, id + 4);
536:                    psI.setBytes(2, null);
537:                    psI.setBytes(3, null);
538:                    psI.setBytes(4, original);
539:                    psI.setBytes(5, null);
540:                    psI.executeUpdate();
541:                    okI1 = true;
542:                } catch (SQLException sqle) {
543:                    okI1 = false;
544:                    if ("22001".equals(sqle.getSQLState()))
545:                        System.out.println("22001 truncation error");
546:                    else
547:                        showSQLE(sqle);
548:                }
549:
550:                if (streamAsWell) {
551:                    try {
552:                        psI.setInt(1, id + 5);
553:                        psI.setBinaryStream(4, original == null ? null
554:                                : new ByteArrayInputStream(original), ol);
555:                        psI.executeUpdate();
556:                        okI2 = true;
557:                    } catch (SQLException sqle) {
558:                        okI2 = false;
559:                        if ("22001".equals(sqle.getSQLState()))
560:                            System.out.println("22001 truncation error");
561:                        else
562:                            showSQLE(sqle);
563:                    }
564:                    if (okI1 != okI2)
565:                        System.out.println("FAIL - mismatched failures");
566:                }
567:
568:                System.out.println("  >> BLOB");
569:                try {
570:                    psI.setInt(1, id + 6);
571:                    psI.setBytes(2, null);
572:                    psI.setBytes(3, null);
573:                    psI.setBytes(4, null);
574:                    psI.setBytes(5, original);
575:                    okI1 = true;
576:                    psI.executeUpdate();
577:                } catch (SQLException sqle) {
578:                    okI1 = false;
579:                    if ("22001".equals(sqle.getSQLState()))
580:                        System.out.println("22001 truncation error");
581:                    else
582:                        showSQLE(sqle);
583:                }
584:                if (streamAsWell) {
585:                    try {
586:                        psI.setInt(1, id + 7);
587:                        psI.setBinaryStream(5, original == null ? null
588:                                : new ByteArrayInputStream(original), ol);
589:                        psI.executeUpdate();
590:                        okI2 = true;
591:                    } catch (SQLException sqle) {
592:                        okI2 = false;
593:                        if ("22001".equals(sqle.getSQLState()))
594:                            System.out.println("22001 truncation error");
595:                        else
596:                            showSQLE(sqle);
597:                    }
598:                    if (okI1 != okI2)
599:                        System.out.println("FAIL - mismatched failures");
600:                }
601:            }
602:
603:            public static void testCompare(Connection conn) throws SQLException {
604:
605:                System.out.println("START testCompare");
606:
607:                Statement s = conn.createStatement();
608:                executeDrop(s, "DROP TABLE FBDVAL.T001");
609:                executeDrop(s, "DROP TABLE FBDVAL.T002");
610:                s
611:                        .execute("CREATE TABLE FBDVAL.T001(ID INT NOT NULL PRIMARY KEY, C1 CHAR(10) FOR BIT DATA, C2 VARCHAR(10) FOR BIT DATA, C3 LONG VARCHAR FOR BIT DATA, C4 BLOB(10))");
612:                s
613:                        .execute("CREATE TABLE FBDVAL.T002(ID INT NOT NULL PRIMARY KEY, C1 CHAR(10) FOR BIT DATA, C2 VARCHAR(10) FOR BIT DATA, C3 LONG VARCHAR FOR BIT DATA, C4 BLOB(10))");
614:                PreparedStatement psI = conn
615:                        .prepareStatement("INSERT INTO FBDVAL.T001 VALUES(?, ?, ?, ?, ?)");
616:                PreparedStatement psI2 = conn
617:                        .prepareStatement("INSERT INTO FBDVAL.T002 VALUES(?, ?, ?, ?, ?)");
618:
619:                insertData(psI, 0, null, 10, false);
620:                insertData(psI2, 0, null, 10, false);
621:
622:                byte[] four = new byte[4];
623:                four[0] = (byte) 0x04;
624:                four[1] = (byte) 0x23;
625:                four[2] = (byte) 0xA2;
626:                four[3] = (byte) 0xFD;
627:
628:                insertData(psI, 30, four, 10, false);
629:                insertData(psI2, 30, four, 10, false);
630:                four[2] = (byte) 0xA1;
631:                insertData(psI, 40, four, 10, false);
632:                insertData(psI2, 40, four, 10, false);
633:                four[2] = (byte) 0xA2;
634:                four[3] = (byte) 0xFF;
635:                insertData(psI, 50, four, 10, false);
636:                insertData(psI2, 50, four, 10, false);
637:
638:                byte[] four_plus_space = new byte[5];
639:                four_plus_space[0] = (byte) 0x04;
640:                four_plus_space[1] = (byte) 0x23;
641:                four_plus_space[2] = (byte) 0xA2;
642:                four_plus_space[3] = (byte) 0xFD;
643:                four_plus_space[4] = (byte) 0x20;
644:                insertData(psI, 60, four_plus_space, 10, false);
645:                insertData(psI2, 60, four_plus_space, 10, false);
646:
647:                byte[] ten = new byte[10];
648:                ten[0] = (byte) 0x0B;
649:                ten[1] = (byte) 0x27;
650:                ten[2] = (byte) 0xA2;
651:                ten[3] = (byte) 0xFD;
652:                ten[4] = (byte) 0x01;
653:                ten[5] = (byte) 0x6D;
654:                ten[6] = (byte) 0xE2;
655:                ten[7] = (byte) 0x35;
656:                ten[8] = (byte) 0x66;
657:                ten[9] = (byte) 0x90;
658:
659:                insertData(psI, 70, ten, 10, false);
660:                insertData(psI2, 70, ten, 10, false);
661:
662:                String[] COLS = { "C1", "C2", "C3", "C4" };
663:                String[] OPS = { "=", "<>", "<", "<=", ">", ">=" };
664:
665:                for (int t = 0; t < COLS.length; t++) {
666:                    for (int o = 0; o < COLS.length; o++) {
667:                        for (int a = 0; a < OPS.length; a++) {
668:
669:                            String sql = "SELECT T.ID, T."
670:                                    + COLS[t]
671:                                    + ", O.ID, O."
672:                                    + COLS[o]
673:                                    + " FROM FBDVAL.T001 O, FBDVAL.T002 T WHERE T."
674:                                    + COLS[t] + " " + OPS[a] + " O." + COLS[o]
675:                                    + " ORDER BY 1,3";
676:
677:                            System.out.println(sql);
678:                            try {
679:                                PreparedStatement psS = conn
680:                                        .prepareStatement(sql);
681:                                showCompareData(psS);
682:                            } catch (SQLException sqle) {
683:                                if ("42818".equals(sqle.getSQLState()))
684:                                    System.out
685:                                            .println("42818 types not comparable "
686:                                                    + COLS[t]
687:                                                    + " ... "
688:                                                    + COLS[o]);
689:                                else
690:                                    showSQLE(sqle);
691:                            }
692:                            conn.commit();
693:                        }
694:                    }
695:                }
696:                System.out.println("END testCompare");
697:            }
698:
699:            /**
700:            	The length of a binary type is encoded when stored, this
701:            	test makes sure all the code paths are tested.
702:            	The encoded length is hidden from the JDBC client.
703:             */
704:            public static void testEncodedLengths(Connection conn)
705:                    throws SQLException, IOException {
706:
707:                System.out.println("START testEncodedLengths");
708:
709:                Statement s = conn.createStatement();
710:                executeDrop(s, "DROP TABLE FBDVAL.TEL");
711:                s
712:                        .execute("CREATE TABLE FBDVAL.TEL(C2 VARCHAR(32672) FOR BIT DATA, C3 LONG VARCHAR FOR BIT DATA, C4 BLOB(128k))");
713:
714:                PreparedStatement psi = conn
715:                        .prepareStatement("INSERT INTO FBDVAL.TEL VALUES(?, ?, ?)");
716:                PreparedStatement pss = conn
717:                        .prepareStatement("SELECT * FROM FBDVAL.TEL");
718:                PreparedStatement psd = conn
719:                        .prepareStatement("DELETE FROM FBDVAL.TEL");
720:
721:                //insertEL(psi, pss, psd, 0);
722:                insertEL(psi, pss, psd, 10);
723:                insertEL(psi, pss, psd, 30);
724:                insertEL(psi, pss, psd, 31);
725:                insertEL(psi, pss, psd, 32); // switch to 2 byte length
726:                insertEL(psi, pss, psd, 1345);
727:                insertEL(psi, pss, psd, 23456);
728:                insertEL(psi, pss, psd, 32672);
729:                insertEL(psi, pss, psd, 32700);
730:                insertEL(psi, pss, psd, (32 * 1024) - 1);
731:                insertEL(psi, pss, psd, (32 * 1024));
732:                insertEL(psi, pss, psd, (32 * 1024) + 1);
733:                insertEL(psi, pss, psd, (64 * 1024) - 1);
734:                insertEL(psi, pss, psd, (64 * 1024)); // switch to 4 byte length
735:                insertEL(psi, pss, psd, (64 * 1024) + 1);
736:                insertEL(psi, pss, psd, (110 * 1024) + 3242);
737:
738:                psi.close();
739:                pss.close();
740:                psd.close();
741:                executeDrop(s, "DROP TABLE FBDVAL.TEL");
742:                s.close();
743:                System.out.println("END testEncodedLengths");
744:
745:            }
746:
747:            private static void insertEL(PreparedStatement psi,
748:                    PreparedStatement pss, PreparedStatement psd, int length)
749:                    throws SQLException, IOException {
750:
751:                Connection conn = psi.getConnection();
752:                byte[] data = new byte[length];
753:
754:                // random simple value check
755:                int off = (int) (System.currentTimeMillis() % ((long) length));
756:                data[off] = 0x23;
757:
758:                psi.setBytes(1, (length <= 32672) ? data : null);
759:                psi.setBytes(2, (length <= 32700) ? data : null);
760:                psi.setBinaryStream(3, new java.io.ByteArrayInputStream(data),
761:                        length); // BLOB column
762:                psi.executeUpdate();
763:                conn.commit();
764:
765:                selectData(pss, data, off, length);
766:
767:                conn.commit();
768:
769:                psd.executeUpdate();
770:                conn.commit();
771:
772:                // Set values using stream and then verify that select is successful
773:                psi.setBinaryStream(1,
774:                        (length <= 32672) ? new java.io.ByteArrayInputStream(
775:                                data) : null, length);
776:                psi.setBinaryStream(2,
777:                        (length <= 32700) ? new java.io.ByteArrayInputStream(
778:                                data) : null, length);
779:                psi.setBinaryStream(3, new java.io.ByteArrayInputStream(data),
780:                        length); // BLOB column
781:                psi.executeUpdate();
782:                conn.commit();
783:
784:                selectData(pss, data, off, length);
785:
786:                conn.commit();
787:
788:                psd.executeUpdate();
789:                conn.commit();
790:
791:            }
792:
793:            private static void selectData(PreparedStatement pss, byte[] data,
794:                    int off, int length) throws SQLException, IOException {
795:
796:                ResultSet rs = pss.executeQuery();
797:                while (rs.next()) {
798:                    System.out.print(" EL byte[] " + length);
799:                    byte[] v = rs.getBytes(1);
800:                    if (v != null) {
801:                        System.out.print(" C1 "
802:                                + ((v.length == length) ? "OK" : ("FAIL <"
803:                                        + v.length + ">")));
804:                        System.out.print(" DATA "
805:                                + ((v[off] == 0x23) ? "OK" : ("FAIL " + off)));
806:                    } else
807:                        System.out.print(" C1 NULL");
808:
809:                    v = rs.getBytes(2);
810:                    if (v != null) {
811:                        System.out.print(" C2 "
812:                                + ((v.length == length) ? "OK" : ("FAIL <"
813:                                        + v.length + ">")));
814:                        System.out.print(" DATA "
815:                                + ((v[off] == 0x23) ? "OK" : ("FAIL " + off)));
816:                    } else
817:                        System.out.print(" C2 NULL");
818:                    InputStream c3 = rs.getBinaryStream(3);
819:                    checkEncodedLengthValue("C3", c3, length, off);
820:
821:                    System.out.println("");
822:                }
823:                rs.close();
824:
825:                rs = pss.executeQuery();
826:                while (rs.next()) {
827:                    System.out.print(" EL stream " + length);
828:
829:                    checkEncodedLengthValue("C1", rs.getBinaryStream(1),
830:                            length, off);
831:                    checkEncodedLengthValue("C2", rs.getBinaryStream(2),
832:                            length, off);
833:                    checkEncodedLengthValue("C3", rs.getBinaryStream(3),
834:                            length, off);
835:
836:                    System.out.println("");
837:                }
838:                rs.close();
839:
840:            }
841:
842:            private static void checkEncodedLengthValue(String col,
843:                    InputStream is, int length, int off) throws IOException {
844:
845:                if (is == null) {
846:                    System.out.print(" " + col + " NULL");
847:                    return;
848:                }
849:                byte[] buf = new byte[3213];
850:                boolean dataOK = false;
851:                int sl = 0;
852:                for (;;) {
853:                    int r = is.read(buf);
854:                    if (r < 0)
855:                        break;
856:
857:                    if ((off >= sl) && (off < (sl + r))) {
858:                        if (buf[off - sl] == 0x23)
859:                            dataOK = true;
860:                    }
861:                    sl += r;
862:                }
863:                System.out.print(" " + col + " "
864:                        + ((sl == length) ? "OK" : ("FAIL <" + sl + ">")));
865:                System.out.print(" DATA " + (dataOK ? "OK" : ("FAIL " + off)));
866:            }
867:
868:            private static void showData(PreparedStatement psS, int id,
869:                    byte[] original) throws SQLException {
870:                psS.setInt(1, id);
871:                psS.setInt(2, id + 10);
872:                ResultSet rs = psS.executeQuery();
873:                while (rs.next()) {
874:
875:                    System.out.print("  ORG ");
876:                    System.out.print(showData(original));
877:                    System.out.print("CHR ");
878:                    System.out.print(showData(rs.getBytes(1)));
879:                    System.out.print("VAR ");
880:                    System.out.print(showData(rs.getBytes(2)));
881:                    System.out.print("LVC ");
882:                    System.out.print(showData(rs.getBytes(3)));
883:                    System.out.print("BLOB ");
884:                    System.out.print(showData(rs.getBytes(4)));
885:
886:                    System.out.println("");
887:                }
888:                rs.close();
889:
890:            }
891:
892:            private static void showCompareData(PreparedStatement psS)
893:                    throws SQLException {
894:                ResultSet rs = psS.executeQuery();
895:                while (rs.next()) {
896:                    System.out.print("  " + rs.getInt(1) + " ");
897:                    System.out.print(showData(rs.getBytes(2)));
898:                    System.out.print("  " + rs.getInt(3) + " ");
899:                    System.out.println(showData(rs.getBytes(4)));
900:                }
901:                rs.close();
902:                psS.close();
903:            }
904:
905:            private static String showData(byte[] data) {
906:                if (data == null)
907:                    return "<NULL> ";
908:
909:                StringBuffer sb = new StringBuffer();
910:                for (int i = 0; i < data.length; i++) {
911:                    String s = Integer.toHexString(data[i] & 0xff);
912:                    if (s.length() == 1)
913:                        sb.append('0');
914:                    sb.append(s);
915:                }
916:
917:                sb.append(' ');
918:                sb.append('(');
919:                sb.append(data.length);
920:                sb.append(')');
921:                sb.append(' ');
922:
923:                return sb.toString();
924:            }
925:
926:            private static void showSQLE(SQLException sqle) {
927:                do {
928:                    System.out.println(sqle.getSQLState() + ": "
929:                            + sqle.getMessage());
930:                    //sqle.printStackTrace(System.out);
931:                    sqle = sqle.getNextException();
932:                } while (sqle != null);
933:            }
934:
935:            private static void executeDrop(Statement s, String sql) {
936:                try {
937:                    s.execute(sql);
938:                } catch (SQLException sqle) {
939:                }
940:            }
941:
942:            private static void executeOK(Statement s, String sql) {
943:                System.out.println(sql);
944:                try {
945:                    s.execute(sql);
946:                } catch (SQLException sqle) {
947:                    System.out.println("FAIL ");
948:                    showSQLE(sqle);
949:                }
950:            }
951:
952:            private static void statementExceptionExpected(Statement s,
953:                    String sql) {
954:                System.out.println(sql);
955:                try {
956:                    s.execute(sql);
957:                    System.out
958:                            .println("FAIL - SQL expected to throw exception");
959:                } catch (SQLException sqle) {
960:                    expectedException(sqle);
961:                }
962:            }
963:
964:            private static void expectedException(SQLException sqle) {
965:                String sqlState = sqle.getSQLState();
966:                if (sqlState == null) {
967:                    sqlState = "<NULL>";
968:                }
969:                System.out.println("EXPECTED SQL Exception: (" + sqlState
970:                        + ") " + sqle.getMessage());
971:            }
972:
973:        }
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.