/**
* Tests the implementation of getCharacterStream(long pos, long length).
*
* @throws Exception
*/
public void testGetCharacterStreamLong() throws Exception {
String str1 = "This is a test String. This is a test String";
Reader r1 = new java.io.StringReader(str1);
PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
int id = BlobClobTestSetup.getID();
ps.setInt(1, id);
ps.setCharacterStream(2, r1);
ps.execute();
ps.close();
Statement st = createStatement();
ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id);
rs.next();
Clob clob = rs.getClob(1);
Reader r_1 = clob.getCharacterStream(2L, 5L);
String str2 = str1.substring(1, 6);
Reader r_2 = new java.io.StringReader(str2);
assertEquals(r_2, r_1);
rs.close();
st.close();
}
/**
* Test that <code>Clob.getCharacterStream(long,long)</code> works on CLOBs that are streamed from
* store. (DERBY-2891)
*/
public void testGetCharacterStreamLongOnLargeClob() throws Exception {
getConnection().setAutoCommit(false);
// create large (>32k) clob that can be read from store
final int size = 33000;
StringBuilder sb = new StringBuilder(size);
for (int i = 0; i < size; i += 10) {
sb.append("1234567890");
}
final int id = BlobClobTestSetup.getID();
PreparedStatement ps =
prepareStatement("insert into blobclob(id, clobdata) values (?,cast(? as clob))");
ps.setInt(1, id);
ps.setString(2, sb.toString());
ps.executeUpdate();
ps.close();
Statement s = createStatement();
ResultSet rs = s.executeQuery("select clobdata from blobclob where id = " + id);
assertTrue(rs.next());
Clob c = rs.getClob(1);
// request a small region of the clob
BufferedReader r = new BufferedReader(c.getCharacterStream(4L, 3L));
assertEquals("456", r.readLine());
r.close();
c.free();
rs.close();
s.close();
rollback();
}
//
// Examine BLOBs and CLOBs.
//
private void vetLargeObjects(
Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList)
throws Exception {
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE t (id INT PRIMARY KEY, " + "b BLOB(10), c CLOB(10))");
stmt.execute(
"INSERT INTO t (id, b, c) VALUES (1, "
+ "CAST ("
+ TestUtil.stringToHexLiteral("101010001101")
+ "AS BLOB(10)), CAST ('hello' AS CLOB(10)))");
ResultSet rs = stmt.executeQuery("SELECT id, b, c FROM t");
rs.next();
Blob blob = rs.getBlob(2);
Clob clob = rs.getClob(3);
vetObject(blob, unsupportedList, notUnderstoodList);
vetObject(clob, unsupportedList, notUnderstoodList);
stmt.close();
conn.rollback();
}
public void testAny() {
try {
String ddl =
"drop table PRICE_RELATE_USER_ORDER_V2 if exists;"
+ "create table PRICE_RELATE_USER_ORDER_V2 "
+ "(ID_ORDER_V2 BIGINT, ID_USER NUMERIC, DATE_CREATE TIMESTAMP)";
String sql =
"insert into PRICE_RELATE_USER_ORDER_V2 "
+ "(ID_ORDER_V2, ID_USER, DATE_CREATE) "
+ "values "
+ "(?, ?, ?)";
Statement st = connection.createStatement();
st.execute(ddl);
PreparedStatement ps = connection.prepareStatement(sql);
ps.setLong(1, 1);
ps.setNull(2, Types.NUMERIC);
ps.setTimestamp(3, new java.sql.Timestamp(System.currentTimeMillis()));
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("TestSql.testAny() error: " + e.getMessage());
}
System.out.println("testAny complete");
}
public void tearDown() {
try {
Connection con = AllTests.getConnection();
Statement st = con.createStatement();
st.execute("drop table " + table);
st.close();
} catch (Throwable e) {
// e.printStackTrace();
}
}
/**
* Inserts a Clob with the specified length, using a stream source, then fetches it from the
* database and checks the length.
*
* @param length number of characters in the Clob
* @throws IOException if reading from the source fails
* @throws SQLException if something goes wrong
*/
private void insertAndFetchTest(long length) throws IOException, SQLException {
PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
int id = BlobClobTestSetup.getID();
ps.setInt(1, id);
ps.setCharacterStream(2, new LoopingAlphabetReader(length), length);
long tsStart = System.currentTimeMillis();
ps.execute();
println(
"Inserted "
+ length
+ " chars (length specified) in "
+ (System.currentTimeMillis() - tsStart)
+ " ms");
Statement stmt = createStatement();
tsStart = System.currentTimeMillis();
ResultSet rs = stmt.executeQuery("select CLOBDATA from BLOBCLOB where id = " + id);
assertTrue("Clob not inserted", rs.next());
Clob aClob = rs.getClob(1);
assertEquals("Invalid length", length, aClob.length());
println("Fetched length (" + length + ") in " + (System.currentTimeMillis() - tsStart) + " ms");
rs.close();
// Insert same Clob again, using the lengthless override.
id = BlobClobTestSetup.getID();
ps.setInt(1, id);
ps.setCharacterStream(2, new LoopingAlphabetReader(length));
tsStart = System.currentTimeMillis();
ps.executeUpdate();
println(
"Inserted "
+ length
+ " chars (length unspecified) in "
+ (System.currentTimeMillis() - tsStart)
+ " ms");
rs = stmt.executeQuery("select CLOBDATA from BLOBCLOB where id = " + id);
assertTrue("Clob not inserted", rs.next());
aClob = rs.getClob(1);
assertEquals("Invalid length", length, aClob.length());
println("Fetched length (" + length + ") in " + (System.currentTimeMillis() - tsStart) + " ms");
rs.close();
rollback();
}
public void setUp() {
tearDown();
try {
Connection con = AllTests.getConnection();
Statement st = con.createStatement();
st.execute(
"create table " + table + "(a " + testValue.dataType + ", b " + testValue.dataType + ")");
st.close();
PreparedStatement pr = con.prepareStatement("INSERT into " + table + "(a,b) Values(?,?)");
pr.setObject(1, testValue.small);
pr.setObject(2, testValue.large);
pr.execute();
pr.setObject(1, testValue.small);
pr.setObject(2, testValue.small);
pr.execute();
pr.setObject(1, testValue.large);
pr.setObject(2, testValue.large);
pr.execute();
pr.setObject(1, testValue.large);
pr.setObject(2, testValue.small);
pr.execute();
pr.setObject(1, null);
pr.setObject(2, testValue.small);
pr.execute();
pr.setObject(1, testValue.small);
pr.setObject(2, null);
pr.execute();
pr.setObject(1, null);
pr.setObject(2, null);
pr.execute();
pr.close();
} catch (Throwable e) {
e.printStackTrace();
}
}
/**
* Insert a row with a large clob into the test table. Read the row from the database and assign
* the clob value to <code>clob</code>.
*
* @return The id of the row that was inserted
* @throws java.sql.SQLException
*/
private int initializeLongClob() throws SQLException {
// Clob needs to be larger than one page for locking to occur
final int lobLength = 40000;
// Insert a long Clob
PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
int id = BlobClobTestSetup.getID();
ps.setInt(1, id);
ps.setCharacterStream(2, new LoopingAlphabetReader(lobLength), lobLength);
ps.execute();
ps.close();
commit();
// Fetch the Clob object from the database
Statement st = createStatement();
ResultSet rs = st.executeQuery("select CLOBDATA from BLOBCLOB where ID=" + id);
rs.next();
clob = rs.getClob(1);
rs.close();
st.close();
return id;
}
/**
* Try to update the row with the given error. Flag a failure if a timeout occurs when not
* expected, and vice versa.
*
* @param id The id of the row to be updated
* @param timeoutExpected true if it is expected that the update times out
* @throws java.sql.SQLException
*/
private void executeParallelUpdate(int id, boolean timeoutExpected) throws SQLException {
Connection conn2 = openDefaultConnection();
Statement stmt2 = conn2.createStatement();
try {
stmt2.executeUpdate(
"update BLOBCLOB set BLOBDATA = " + "cast(X'FFFFFF' as blob) where ID=" + id);
stmt2.close();
conn2.commit();
conn2.close();
if (timeoutExpected) {
fail("FAIL - should have gotten lock timeout");
}
} catch (SQLException se) {
stmt2.close();
conn2.rollback();
conn2.close();
if (timeoutExpected) {
assertSQLState(LOCK_TIMEOUT, se);
} else {
throw se;
}
}
}
/**
* Tests the exceptions thrown by the getCharacterStream (long pos, long length) for the following
* conditions a) pos <= 0 b) pos > (length of LOB) c) length < 0 d) pos + length > (length of
* LOB).
*
* @throws SQLException
*/
public void testGetCharacterStreamLongExceptionConditions() throws SQLException {
String str1 = "This is a test String. This is a test String";
Reader r1 = new java.io.StringReader(str1);
PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
int id = BlobClobTestSetup.getID();
ps.setInt(1, id);
ps.setCharacterStream(2, r1);
ps.execute();
ps.close();
Statement st = createStatement();
ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id);
rs.next();
Clob clob = rs.getClob(1);
// check the case where pos <= 0
try {
// set pos as negative
clob.getCharacterStream(-2L, 5L);
// Should not come here. The exception has to be thrown.
fail("FAIL: Expected SQLException for pos being negative " + "not thrown");
} catch (SQLException sqle) {
// The SQLState for the exception thrown when pos <= 0 is XJ070
assertSQLState("XJ070", sqle);
}
// check for the case pos > length of clob
try {
// set the pos to any value greater than the Clob length
clob.getCharacterStream(clob.length() + 1, 5L);
// Should not come here. The exception has to be thrown.
fail(
"FAIL: Expected SQLException for position being greater than "
+ "length of LOB not thrown");
} catch (SQLException sqle) {
// The SQLState for the exception thrown when pos > length of Clob
// is XJ076
assertSQLState("XJ087", sqle);
}
// check for the case when length < 0
try {
// set length as negative
clob.getCharacterStream(2L, -5L);
// Should not come here. The exception has to be thrown.
fail("Fail: expected exception for the length being negative " + "not thrown");
} catch (SQLException sqle) {
// The SQLState for the exception thrown when length < 0 of Clob
// is XJ071
assertSQLState("XJ071", sqle);
}
// check for the case when pos + length > length of Clob
try {
// set pos + length > length of Clob
clob.getCharacterStream((clob.length() - 4), 10L);
// Should not come here. The exception has to be thrown.
fail(
"Fail: expected exception for the sum of position and length"
+ " being greater than the LOB size not thrown");
} catch (SQLException sqle) {
// The SQLState for the exception thrown when length < 0 of Clob
// is XJ087
assertSQLState("XJ087", sqle);
}
}
public void runTest() throws Exception {
Connection con = AllTests.getConnection();
Statement st = con.createStatement();
ResultSet rs;
rs = st.executeQuery("Select * from " + table + " where 1 = 0");
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where a = b");
assertTrue("To few rows", rs.next());
assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false);
assertTrue("To few rows", rs.next());
assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false);
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where a <= b and b <= a");
assertTrue("To few rows", rs.next());
assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false);
assertTrue("To few rows", rs.next());
assertEqualsObject("Values not equals", rs.getObject(1), rs.getObject(2), false);
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where (a > (b))");
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where a >= b");
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where not (a >= b)");
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where a < b");
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where a < b or a>b");
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where a <= b");
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
rs = st.executeQuery("Select * from " + table + " where a <> b");
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
PreparedStatement pr =
con.prepareStatement("Select * from " + table + " where a between ? and ?");
pr.setObject(1, testValue.small);
pr.setObject(2, testValue.large);
rs = pr.executeQuery();
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
pr.close();
pr = con.prepareStatement("Select * from " + table + " where a not between ? and ?");
pr.setObject(1, testValue.small);
pr.setObject(2, testValue.large);
rs = pr.executeQuery();
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
pr.close();
pr = con.prepareStatement("Select * from " + table + " where a in(?,?)");
pr.setObject(1, testValue.small);
pr.setObject(2, testValue.large);
rs = pr.executeQuery();
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
pr.close();
pr = con.prepareStatement("Select * from " + table + " where a not in(?,?)");
pr.setObject(1, testValue.small);
pr.setObject(2, testValue.large);
rs = pr.executeQuery();
assertTrue("To few rows", rs.next());
assertTrue("To few rows", rs.next());
assertFalse("To many rows", rs.next());
pr.close();
st.close();
}
public void testMetaData() {
String ddl0 =
"DROP TABLE ADDRESSBOOK IF EXISTS; DROP TABLE ADDRESSBOOK_CATEGORY IF EXISTS; DROP TABLE USER IF EXISTS;";
String ddl1 =
"CREATE TABLE USER(USER_ID INTEGER NOT NULL PRIMARY KEY,LOGIN_ID VARCHAR(128) NOT NULL,USER_NAME VARCHAR(254) DEFAULT ' ' NOT NULL,CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UPDATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,LAST_ACCESS_DATE TIMESTAMP,CONSTRAINT IXUQ_LOGIN_ID0 UNIQUE(LOGIN_ID))";
String ddl2 =
"CREATE TABLE ADDRESSBOOK_CATEGORY(USER_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,CATEGORY_NAME VARCHAR(60) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK_CATEGORY PRIMARY KEY(USER_ID,CATEGORY_ID),CONSTRAINT FK_ADRBKCAT1 FOREIGN KEY(USER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE)";
String ddl3 =
"CREATE TABLE ADDRESSBOOK(USER_ID INTEGER NOT NULL,ADDRESSBOOK_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,FIRST VARCHAR(64) DEFAULT '' NOT NULL,LAST VARCHAR(64) DEFAULT '' NOT NULL,NOTE VARCHAR(128) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK PRIMARY KEY(USER_ID,ADDRESSBOOK_ID),CONSTRAINT FK_ADRBOOK1 FOREIGN KEY(USER_ID,CATEGORY_ID) REFERENCES ADDRESSBOOK_CATEGORY(USER_ID,CATEGORY_ID) ON DELETE CASCADE)";
String result1 = "1";
String result2 = "2";
String result3 = "3";
String result4 = "4";
String result5 = "5";
try {
stmnt.execute(ddl0);
stmnt.execute(ddl1);
stmnt.execute(ddl2);
stmnt.execute(ddl3);
DatabaseMetaData md = connection.getMetaData();
{
// System.out.println(md.getDatabaseMajorVersion());
// System.out.println(md.getDatabaseMinorVersion());
System.out.println(md.getDatabaseProductName());
System.out.println(md.getDatabaseProductVersion());
System.out.println(md.getDefaultTransactionIsolation());
System.out.println(md.getDriverMajorVersion());
System.out.println(md.getDriverMinorVersion());
System.out.println(md.getDriverName());
System.out.println(md.getDriverVersion());
System.out.println(md.getExtraNameCharacters());
System.out.println(md.getIdentifierQuoteString());
// System.out.println(md.getJDBCMajorVersion());
// System.out.println(md.getJDBCMinorVersion());
System.out.println(md.getMaxBinaryLiteralLength());
System.out.println(md.getMaxCatalogNameLength());
System.out.println(md.getMaxColumnsInGroupBy());
System.out.println(md.getMaxColumnsInIndex());
System.out.println(md.getMaxColumnsInOrderBy());
System.out.println(md.getMaxColumnsInSelect());
System.out.println(md.getMaxColumnsInTable());
System.out.println(md.getMaxConnections());
System.out.println(md.getMaxCursorNameLength());
System.out.println(md.getMaxIndexLength());
System.out.println(md.getMaxProcedureNameLength());
System.out.println(md.getMaxRowSize());
System.out.println(md.getMaxSchemaNameLength());
System.out.println(md.getMaxStatementLength());
System.out.println(md.getMaxStatements());
System.out.println(md.getMaxTableNameLength());
System.out.println(md.getMaxUserNameLength());
System.out.println(md.getNumericFunctions());
System.out.println(md.getProcedureTerm());
// System.out.println(md.getResultSetHoldability());
System.out.println(md.getSchemaTerm());
System.out.println(md.getSearchStringEscape());
System.out.println(md.getSQLKeywords());
// System.out.println(md.getSQLStateType());
System.out.println(md.getStringFunctions());
System.out.println(md.getSystemFunctions());
System.out.println(md.getTimeDateFunctions());
System.out.println(md.getURL());
System.out.println(md.getUserName());
System.out.println(md.importedKeyCascade);
System.out.println(md.isCatalogAtStart());
System.out.println(md.isReadOnly());
ResultSet rs;
rs = md.getPrimaryKeys(null, null, "USER");
ResultSetMetaData rsmd = rs.getMetaData();
String result0 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result0 += rs.getString(i + 1) + ":";
}
result0 += "\n";
}
rs.close();
System.out.println(result0);
}
{
ResultSet rs;
rs = md.getBestRowIdentifier(null, null, "USER", 0, true);
ResultSetMetaData rsmd = rs.getMetaData();
String result0 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result0 += rs.getString(i + 1) + ":";
}
result0 += "\n";
}
rs.close();
System.out.println(result0);
}
{
ResultSet rs = md.getImportedKeys(null, null, "ADDRESSBOOK");
ResultSetMetaData rsmd = rs.getMetaData();
result1 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result1 += rs.getString(i + 1) + ":";
}
result1 += "\n";
}
rs.close();
System.out.println(result1);
}
{
ResultSet rs =
md.getCrossReference(null, null, "ADDRESSBOOK_CATEGORY", null, null, "ADDRESSBOOK");
ResultSetMetaData rsmd = rs.getMetaData();
result2 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result2 += rs.getString(i + 1) + ":";
}
result2 += "\n";
}
rs.close();
System.out.println(result2);
}
{
ResultSet rs = md.getExportedKeys(null, null, "USER");
ResultSetMetaData rsmd = rs.getMetaData();
result3 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result3 += rs.getString(i + 1) + ":";
}
result3 += "\n";
}
rs.close();
System.out.println(result3);
}
{
ResultSet rs = md.getCrossReference(null, null, "USER", null, null, "ADDRESSBOOK_CATEGORY");
ResultSetMetaData rsmd = rs.getMetaData();
result4 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result4 += rs.getString(i + 1) + ":";
}
result4 += "\n";
}
rs.close();
System.out.println(result4);
}
{
stmnt.execute("DROP TABLE T IF EXISTS;");
stmnt.executeQuery("CREATE TABLE T (I IDENTITY, A CHAR, B CHAR);");
stmnt.executeQuery(
"INSERT INTO T VALUES (NULL, 'get_column_name', '" + getColumnName + "');");
ResultSet rs = stmnt.executeQuery("SELECT I, A, B, A \"aliasA\", B \"aliasB\" FROM T;");
ResultSetMetaData rsmd = rs.getMetaData();
result5 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result5 += rsmd.getColumnName(i + 1) + ":" + rs.getString(i + 1) + ":";
}
result5 += "\n";
}
rs.close();
rs = stmnt.executeQuery("SELECT I, A, B, A \"aliasA\", B \"aliasB\" FROM T;");
;
rsmd = rs.getMetaData();
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result5 += rsmd.getColumnLabel(i + 1) + ":" + rs.getString(i + 1) + ":";
}
result5 += "\n";
}
System.out.println(result5);
System.out.println("first column identity: " + rsmd.isAutoIncrement(1));
rsmd.isCaseSensitive(1);
rsmd.isCurrency(1);
rsmd.isDefinitelyWritable(1);
rsmd.isNullable(1);
rsmd.isReadOnly(1);
rsmd.isSearchable(1);
rsmd.isSigned(1);
rsmd.isWritable(1);
rs.close();
// test identity with PreparedStatement
pstmnt = connection.prepareStatement("INSERT INTO T VALUES (?,?,?)");
pstmnt.setString(1, null);
pstmnt.setString(2, "test");
pstmnt.setString(3, "test2");
pstmnt.executeUpdate();
pstmnt = connection.prepareStatement("call identity()");
ResultSet rsi = pstmnt.executeQuery();
rsi.next();
int identity = rsi.getInt(1);
System.out.println("call identity(): " + identity);
rsi.close();
}
} catch (SQLException e) {
fail(e.getMessage());
}
System.out.println("testMetaData complete");
// assert equality of exported and imported with xref
assertEquals(result1, result2);
assertEquals(result3, result4);
}
/**
* Demonstration of a reported bug.
*
* <p>Because all values were turned into strings with toString before
* PreparedStatement.executeQuery() was called, special values such as NaN were not accepted. In
* 1.7.0 these values are inserted as nulls (fredt)<b>
*
* <p>This test can be extended to cover various conversions through JDBC
*/
public void testDoubleNaN() {
double value = 0;
boolean wasEqual = false;
String message = "DB operation completed";
String ddl1 =
"DROP TABLE t1 IF EXISTS;"
+ "CREATE TABLE t1 ( d DECIMAL, f DOUBLE, l BIGINT, i INTEGER, s SMALLINT, t TINYINT, "
+ "dt DATE DEFAULT CURRENT_DATE, ti TIME DEFAULT CURRENT_TIME, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP );";
try {
stmnt.execute(ddl1);
PreparedStatement ps =
connection.prepareStatement(
"INSERT INTO t1 (d,f,l,i,s,t,dt,ti,ts) VALUES (?,?,?,?,?,?,?,?,?)");
ps.setString(1, "0.2");
ps.setDouble(2, 0.2);
ps.setLong(3, java.lang.Long.MAX_VALUE);
ps.setInt(4, Integer.MAX_VALUE);
ps.setInt(5, Short.MAX_VALUE);
ps.setInt(6, 0);
ps.setDate(7, new java.sql.Date(System.currentTimeMillis()));
ps.setTime(8, new java.sql.Time(System.currentTimeMillis()));
ps.setTimestamp(9, new java.sql.Timestamp(System.currentTimeMillis()));
ps.execute();
ps.setInt(1, 0);
ps.setDouble(2, java.lang.Double.NaN);
ps.setLong(3, java.lang.Long.MIN_VALUE);
ps.setInt(4, Integer.MIN_VALUE);
ps.setInt(5, Short.MIN_VALUE);
ps.setInt(6, 0);
// allowed conversions
ps.setTimestamp(7, new java.sql.Timestamp(System.currentTimeMillis() + 1));
ps.setTime(8, new java.sql.Time(System.currentTimeMillis() + 1));
ps.setDate(9, new java.sql.Date(System.currentTimeMillis() + 1));
ps.execute();
//
ps.setInt(1, 0);
ps.setDouble(2, java.lang.Double.POSITIVE_INFINITY);
ps.setInt(4, Integer.MIN_VALUE);
// test conversion
ps.setObject(5, Boolean.TRUE);
ps.setBoolean(5, true);
ps.setObject(5, new Short((short) 2), Types.SMALLINT);
ps.setObject(6, new Integer(2), Types.TINYINT);
// allowed conversions
ps.setObject(7, new java.sql.Date(System.currentTimeMillis() + 2));
ps.setObject(8, new java.sql.Time(System.currentTimeMillis() + 2));
ps.setObject(9, new java.sql.Timestamp(System.currentTimeMillis() + 2));
ps.execute();
ResultSet rs = stmnt.executeQuery("SELECT d, f, l, i, s*2, t FROM t1");
boolean result = rs.next();
value = rs.getDouble(2);
// int smallintValue = rs.getShort(3);
int integerValue = rs.getInt(4);
if (rs.next()) {
value = rs.getDouble(2);
wasEqual = Double.isNaN(value);
integerValue = rs.getInt(4);
// tests for conversion
// getInt on DECIMAL
integerValue = rs.getInt(1);
}
rs = stmnt.executeQuery("SELECT MAX(i) FROM t1");
if (rs.next()) {
int max = rs.getInt(1);
System.out.println("Max value for i: " + max);
}
{
stmnt.execute("drop table CDTYPE if exists");
// test for the value MAX(column) in an empty table
rs =
stmnt.executeQuery(
"CREATE TABLE cdType (ID INTEGER NOT NULL, name VARCHAR(50), PRIMARY KEY(ID))");
rs = stmnt.executeQuery("SELECT MAX(ID) FROM cdType");
if (rs.next()) {
int max = rs.getInt(1);
System.out.println("Max value for ID: " + max);
} else {
System.out.println("Max value for ID not returned");
}
stmnt.executeUpdate("INSERT INTO cdType VALUES (10,'Test String');");
stmnt.executeQuery("CALL IDENTITY();");
try {
stmnt.executeUpdate("INSERT INTO cdType VALUES (10,'Test String');");
} catch (SQLException e1) {
stmnt.execute("ROLLBACK");
connection.rollback();
}
}
} catch (SQLException e) {
fail(e.getMessage());
}
System.out.println("testDoubleNaN complete");
// assert new behaviour
assertEquals(true, wasEqual);
}