001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: import java.io.ByteArrayInputStream;
034: import java.io.IOException;
035: import java.io.InputStream;
036: import java.sql.Connection;
037: import java.sql.DriverManager;
038: import java.sql.PreparedStatement;
039: import java.sql.ResultSet;
040: import java.sql.SQLException;
041: import java.sql.Statement;
042: import java.sql.Types;
043:
044: import junit.framework.TestCase;
045: import junit.framework.TestResult;
046:
047: /**
048: * Test sql statements via jdbc against a database with cached tables
049: * @author fredt@users
050: */
051: public class TestSqlPersistent extends TestCase {
052:
053: // change the url to reflect your preferred db location and name
054: // String url = "jdbc:hsqldb:hsql://localhost/mytest";
055: String url = "jdbc:hsqldb:/hsql/test/testpersistent";
056: String user;
057: String password;
058: Statement sStatement;
059: Connection cConnection;
060:
061: public TestSqlPersistent(String name) {
062: super (name);
063: }
064:
065: protected void setUp() throws Exception {
066:
067: super .setUp();
068:
069: user = "sa";
070: password = "";
071: sStatement = null;
072: cConnection = null;
073:
074: TestSelf.deleteDatabase("/hsql/test/testpersistent");
075:
076: try {
077: Class.forName("org.hsqldb.jdbcDriver");
078:
079: cConnection = DriverManager.getConnection(url, user,
080: password);
081: sStatement = cConnection.createStatement();
082: } catch (Exception e) {
083: e.printStackTrace();
084: System.out.println("TestSqlPersistence.setUp() error: "
085: + e.getMessage());
086: }
087: }
088:
089: /**
090: * demonstration of bug fix #482109 - inserting Integers
091: * and Strings with PreparedStatement.setObject() did not work;
092: * String, Integer and Array types are inserted and retrieved<b>
093: *
094: * demonstration of retrieving values using different getXXX methods
095: */
096: public void testInsertObject() {
097:
098: Object stringValue = null;
099: Object integerValue = null;
100: Object arrayValue = null;
101: Object bytearrayValue = null;
102: Object stringValueResult = null;
103: Object integerValueResult = null;
104: Object arrayValueResult = null;
105: boolean wasNull = false;
106: String message = "DB operation completed";
107:
108: try {
109: String sqlString = "DROP TABLE PREFERENCE IF EXISTS;"
110: + "CREATE CACHED TABLE PREFERENCE ("
111: + "User_Id INTEGER NOT NULL, "
112: + "Pref_Name VARCHAR(30) NOT NULL, "
113: + "Pref_Value OBJECT NOT NULL, "
114: + "DateCreated DATETIME DEFAULT NOW NOT NULL, "
115: + "PRIMARY KEY(User_Id, Pref_Name) )";
116:
117: sStatement.execute(sqlString);
118:
119: sqlString = "INSERT INTO PREFERENCE "
120: + "(User_Id,Pref_Name,Pref_Value,DateCreated) "
121: + "VALUES (?,?,?,current_timestamp)";
122:
123: PreparedStatement ps = cConnection
124: .prepareStatement(sqlString);
125:
126: // initialise
127: stringValue = "String Value for Preference 1";
128: integerValue = new Integer(1000);
129: arrayValue = new Double[] { new Double(1),
130: new Double(Double.NaN),
131: new Double(Double.NEGATIVE_INFINITY),
132: new Double(Double.POSITIVE_INFINITY) };
133: bytearrayValue = new byte[] { 1, 2, 3, 4, 5, 6, };
134:
135: // String as Object
136: ps.setInt(1, 1);
137: ps.setString(2, "String Type Object 1");
138:
139: // fredt - in order to store Strings in OBJECT columns setObject should
140: // explicitly be called with a Types.OTHER type
141: // ps.setObject(3, stringValue); will throw an exception
142: ps.setObject(3, stringValue, Types.OTHER);
143: ps.execute();
144:
145: // Integer as Object
146: ps.setInt(1, 2);
147: ps.setString(2, "Integer Type Object 2");
148:
149: // ps.setObject(3, integerValue, Types.OTHER); should work too
150: ps.setObject(3, integerValue);
151: ps.execute();
152:
153: // Array as object
154: ps.setInt(1, 3);
155: ps.setString(2, "Array Type Object 3");
156: /*
157: ps.setCharacterStream(
158: 2, new java.io.StringReader("Array Type Object 3"), 19);
159: */
160:
161: // ps.setObject(3, arrayValue, Types.OTHER); should work too
162: ps.setObject(3, arrayValue);
163: ps.execute();
164:
165: // byte arrray as object
166: ps.setInt(1, 3);
167: ps.setString(2, "byte Array Type Object 3");
168: /*
169: ps.setCharacterStream(
170: 2, new java.io.StringReader("byte Array Type Object 3"), 19);
171: */
172:
173: // ps.setObject(3, bytearrayValue); will fail
174: // must use this to indicate we are inserting into an OTHER column
175: ps.setObject(3, bytearrayValue, Types.OTHER);
176: ps.execute();
177:
178: ResultSet rs = sStatement
179: .executeQuery("SELECT * FROM PREFERENCE");
180: boolean result = rs.next();
181:
182: // a string can be retrieved as a String or a stream
183: // as Unicode string
184: String str = rs.getString(2);
185:
186: System.out.println(str);
187:
188: // as Unicode stream
189: InputStream is = rs.getUnicodeStream(2);
190: int c;
191:
192: while ((c = is.read()) > -1) {
193: c = is.read();
194:
195: System.out.print((char) c);
196: }
197:
198: System.out.println();
199:
200: // as ASCII stream, ignoring the high order bytes
201: is = rs.getAsciiStream(2);
202:
203: while ((c = is.read()) > -1) {
204: System.out.print((char) c);
205: }
206:
207: System.out.println();
208:
209: // JAVA 2 specific
210: // as character stream via a Reader
211: /*
212: Reader re = rs.getCharacterStream(2);
213:
214: while ((c = re.read()) > -1) {
215: System.out.print((char) c);
216: }
217: */
218:
219: // retrieving objects inserted into the third column
220: stringValueResult = rs.getObject(3);
221:
222: rs.next();
223:
224: integerValueResult = rs.getObject(3);
225:
226: rs.next();
227:
228: arrayValueResult = rs.getObject(3);
229:
230: // how to check if the last retrieved value was null
231: wasNull = rs.wasNull();
232:
233: // cast objects to original types - will throw if type is wrong
234: String castStringValue = (String) stringValueResult;
235: Integer castIntegerValue = (Integer) integerValueResult;
236: Double[] castDoubleArrayValue = (Double[]) arrayValueResult;
237:
238: {
239: sqlString = "DELETE FROM PREFERENCE WHERE user_id = ?";
240:
241: PreparedStatement st = cConnection
242: .prepareStatement(sqlString);
243:
244: st.setString(1, "2");
245:
246: int ret = st.executeUpdate();
247:
248: // here, ret is equal to 1, that is expected
249: //conn.commit(); // not needed, as far as AUTO_COMMIT is set to TRUE
250: st.close();
251:
252: st = cConnection
253: .prepareStatement("SELECT user_id FROM PREFERENCE WHERE user_id=?");
254:
255: st.setString(1, "2");
256:
257: rs = st.executeQuery();
258:
259: while (rs.next()) {
260: System.out.println(rs.getString(1));
261: }
262: }
263: } catch (SQLException e) {
264: System.out.println(e.getMessage());
265: } catch (IOException e1) {
266: }
267:
268: /*
269: boolean success = stringValue.equals(stringValueResult)
270: && integerValue.equals(integerValueResult)
271: && java.util.Arrays.equals((Double[]) arrayValue,
272: (Double[]) arrayValueResult);
273: */
274: boolean success = true;
275:
276: assertEquals(true, success);
277: }
278:
279: public void testSelectObject() throws IOException {
280:
281: String stringValue = null;
282: Integer integerValue = null;
283: Double[] arrayValue = null;
284: byte[] byteArrayValue = null;
285: String stringValueResult = null;
286: Integer integerValueResult = null;
287: Double[] arrayValueResult = null;
288: boolean wasNull = false;
289: String message = "DB operation completed";
290:
291: try {
292: String sqlString = "DROP TABLE TESTOBJECT IF EXISTS;"
293: + "CREATE CACHED TABLE TESTOBJECT ("
294: + "ID INTEGER NOT NULL IDENTITY, "
295: + "STOREDOBJECT OTHER, STOREDBIN BINARY )";
296:
297: sStatement.execute(sqlString);
298:
299: sqlString = "INSERT INTO TESTOBJECT "
300: + "(STOREDOBJECT, STOREDBIN) " + "VALUES (?,?)";
301:
302: PreparedStatement ps = cConnection
303: .prepareStatement(sqlString);
304:
305: // initialise
306: stringValue = "Test String Value";
307: integerValue = new Integer(1000);
308: arrayValue = new Double[] { new Double(1),
309: new Double(Double.NaN),
310: new Double(Double.NEGATIVE_INFINITY),
311: new Double(Double.POSITIVE_INFINITY) };
312: byteArrayValue = new byte[] { 1, 2, 3 };
313:
314: // String as Object
315: // fredt - in order to store Strings in OBJECT columns setObject should
316: // explicitly be called with a Types.OTHER type
317: ps.setObject(1, stringValue, Types.OTHER);
318: ps.setBytes(2, byteArrayValue);
319: ps.execute();
320:
321: // Integer as Object
322: ps.setObject(1, integerValue, Types.OTHER);
323: ps.setBinaryStream(2, new ByteArrayInputStream(
324: byteArrayValue), byteArrayValue.length);
325: ps.execute();
326:
327: // Array as object
328: ps.setObject(1, arrayValue, Types.OTHER);
329:
330: // file as binary - works fine but file path and name has to be modified for test environment
331: /*
332: int length = (int) new File("c://ft/db.jar").length();
333: FileInputStream fis = new FileInputStream("c://ft/db.jar");
334: ps.setBinaryStream(2,fis,length);
335: */
336: ps.execute();
337:
338: ResultSet rs = sStatement
339: .executeQuery("SELECT * FROM TESTOBJECT");
340: boolean result = rs.next();
341:
342: // retrieving objects inserted into the third column
343: stringValueResult = (String) rs.getObject(2);
344:
345: rs.next();
346:
347: integerValueResult = (Integer) rs.getObject(2);
348:
349: rs.next();
350:
351: arrayValueResult = (Double[]) rs.getObject(2);
352:
353: // cast objects to original types - will throw if type is wrong
354: String castStringValue = (String) stringValueResult;
355: Integer castIntegerValue = (Integer) integerValueResult;
356: Double[] castDoubleArrayValue = (Double[]) arrayValueResult;
357:
358: for (int i = 0; i < arrayValue.length; i++) {
359: if (!arrayValue[i].equals(arrayValueResult[i])) {
360: System.out.println("array mismatch: "
361: + arrayValue[i] + " : "
362: + arrayValueResult[i]);
363: }
364: }
365:
366: rs.close();
367: ps.close();
368:
369: sqlString = "SELECT * FROM TESTOBJECT WHERE STOREDOBJECT = ?";
370: ps = cConnection.prepareStatement(sqlString);
371:
372: ps.setObject(1, new Integer(1000));
373:
374: rs = ps.executeQuery();
375:
376: rs.next();
377:
378: Object returnVal = rs.getObject(2);
379:
380: rs.next();
381: } catch (SQLException e) {
382: System.out.println(e.getMessage());
383: }
384:
385: boolean success = stringValue.equals(stringValueResult)
386: && integerValue.equals(integerValueResult)
387: && java.util.Arrays.equals((Double[]) arrayValue,
388: (Double[]) arrayValueResult);
389:
390: assertEquals(true, success);
391:
392: try {
393: String sqlString = "drop table objects if exists";
394: PreparedStatement ps = cConnection
395: .prepareStatement(sqlString);
396:
397: ps.execute();
398:
399: sqlString = "create cached table objects (object_id INTEGER IDENTITY,"
400: + "object_name VARCHAR(128) NOT NULL,role_name VARCHAR(128) NOT NULL,"
401: + "value LONGVARBINARY NOT NULL,description LONGVARCHAR)";
402: ps = cConnection.prepareStatement(sqlString);
403:
404: ps.execute();
405:
406: sqlString = "INSERT INTO objects VALUES(1, 'name','role',?,'description')";
407: ps = cConnection.prepareStatement(sqlString);
408:
409: ps.setBytes(1, new byte[] { 1, 2, 3, 4, 5 });
410: ps.executeUpdate();
411:
412: sqlString = "UPDATE objects SET value = ? AND description = ? WHERE "
413: + "object_name = ? AND role_name = ?";
414: ps = cConnection.prepareStatement(sqlString);
415:
416: ps.setBytes(1, new byte[] { 1, 2, 3, 4, 5 });
417: ps.setString(2, "desc");
418: ps.setString(3, "new");
419: ps.setString(4, "role");
420: ps.executeUpdate();
421: } catch (SQLException e) {
422: System.out.println(e.getMessage());
423: }
424: }
425:
426: protected void tearDown() {
427:
428: try {
429: cConnection.close();
430: } catch (Exception e) {
431: e.printStackTrace();
432: System.out.println("TestSql.tearDown() error: "
433: + e.getMessage());
434: }
435: }
436:
437: public static void main(String[] argv) {
438:
439: TestResult result = new TestResult();
440: TestCase testC = new TestSqlPersistent("testInsertObject");
441: TestCase testD = new TestSqlPersistent("testSelectObject");
442:
443: testC.run(result);
444: testD.run(result);
445: System.out.println("TestSqlPersistent error count: "
446: + result.failureCount());
447: }
448: }
|