2
Copyright (C) 2002 MySQL AB
4
This program is free software; you can redistribute it and/or modify
5
it under the terms of the GNU General Public License as published by
6
the Free Software Foundation; either version 2 of the License, or
7
(at your option) any later version.
9
This program is distributed in the hope that it will be useful,
10
but WITHOUT ANY WARRANTY; without even the implied warranty of
11
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12
GNU General Public License for more details.
14
You should have received a copy of the GNU General Public License
15
along with this program; if not, write to the Free Software
16
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
19
package testsuite.simple;
21
import java.sql.SQLException;
22
import java.sql.Statement;
24
import testsuite.BaseTestCase;
26
import com.mysql.jdbc.NotImplemented;
30
* @author Mark Matthews
31
* @version $Id: StatementsTest.java,v 1.14.2.2 2003/10/07 18:28:23 mmatthew Exp $
33
public class StatementsTest
34
extends BaseTestCase {
36
//~ Instance/static variables .............................................
38
private static final int MAX_COLUMNS_TO_TEST = 40;
39
private static final int STEP = 8;
40
private static final int MAX_COLUMN_LENGTH = 255;
41
private static final int MIN_COLUMN_LENGTH = 10;
43
//~ Constructors ..........................................................
46
* Creates a new StatementsTest object.
48
* @param name DOCUMENT ME!
50
public StatementsTest(String name) {
54
//~ Methods ...............................................................
59
* @param args DOCUMENT ME!
60
* @throws Exception DOCUMENT ME!
62
public static void main(String[] args)
64
new StatementsTest("testStubbed").run();
65
new StatementsTest("testInsert").run();
66
new StatementsTest("testAutoIncrement").run();
67
new StatementsTest("testPreparedStatement").run();
68
new StatementsTest("testPreparedStatementBatch").run();
69
new StatementsTest("testClose").run();
70
new StatementsTest("testSelectColumns").run();
76
* @throws Exception DOCUMENT ME!
83
stmt.executeUpdate("DROP TABLE statement_test");
84
} /* ignore */ catch (SQLException sqlEx) {
89
stmt.executeUpdate("DROP TABLE statement_batch_test");
90
} /* ignore */ catch (SQLException sqlEx) {
95
"CREATE TABLE statement_test (id int not null primary key auto_increment, strdata1 varchar(255) not null, strdata2 varchar(255))");
98
"CREATE TABLE statement_batch_test "
99
+ "(id int not null primary key auto_increment, "
100
+ "strdata1 varchar(255) not null, strdata2 varchar(255), "
101
+ "UNIQUE INDEX (strdata1))");
104
for (int i = 6; i < MAX_COLUMNS_TO_TEST; i += STEP) {
106
StringBuffer insertBuf = new StringBuffer(
107
"INSERT INTO statement_col_test_");
108
StringBuffer stmtBuf = new StringBuffer(
109
"CREATE TABLE IF NOT EXISTS statement_col_test_");
112
stmtBuf.append(" (");
113
insertBuf.append(" VALUES (");
115
boolean firstTime = true;
117
for (int j = 0; j < i; j++) {
121
insertBuf.append(",");
126
stmtBuf.append("col_");
128
stmtBuf.append(" VARCHAR(");
129
stmtBuf.append(MAX_COLUMN_LENGTH);
131
insertBuf.append("'");
133
int numChars = (int) (Math.random() * (MAX_COLUMN_LENGTH - MIN_COLUMN_LENGTH))
136
for (int k = 0; k < numChars; k++) {
137
insertBuf.append("A");
140
insertBuf.append("'");
144
insertBuf.append(")");
145
stmt.executeUpdate(stmtBuf.toString());
146
stmt.executeUpdate(insertBuf.toString());
149
// explicitly set the catalog to exercise code in execute(), executeQuery() and
151
// FIXME: Only works on Windows!
152
//conn.setCatalog(conn.getCatalog().toUpperCase());
158
* @throws Exception DOCUMENT ME!
160
public void tearDown()
162
stmt.executeUpdate("DROP TABLE statement_test");
164
for (int i = 0; i < MAX_COLUMNS_TO_TEST; i += STEP) {
166
StringBuffer stmtBuf = new StringBuffer(
167
"DROP TABLE IF EXISTS statement_col_test_");
169
stmt.executeUpdate(stmtBuf.toString());
173
stmt.executeUpdate("DROP TABLE statement_batch_test");
174
} /* ignore */ catch (SQLException sqlEx) {
184
* @throws SQLException DOCUMENT ME!
186
public void testAccessorsAndMutators()
187
throws SQLException {
188
assertTrue("Connection can not be null, and must be same connection",
189
stmt.getConnection() == conn);
191
// Set max rows, to exercise code in execute(), executeQuery() and executeUpdate()
192
Statement accessorStmt = null;
195
accessorStmt = conn.createStatement();
196
accessorStmt.setMaxRows(1);
197
accessorStmt.setMaxRows(0); // FIXME, test that this actually affects rows returned
198
accessorStmt.setMaxFieldSize(255);
199
assertTrue("Max field size should match what was set",
200
accessorStmt.getMaxFieldSize() == 255);
203
accessorStmt.setMaxFieldSize(Integer.MAX_VALUE);
204
fail("Should not be able to set max field size > max_packet_size");
205
} /* ignore */ catch (SQLException sqlEx) {
209
accessorStmt.setCursorName("undef");
210
accessorStmt.setEscapeProcessing(true);
211
accessorStmt.setFetchDirection(java.sql.ResultSet.FETCH_FORWARD);
213
int fetchDirection = accessorStmt.getFetchDirection();
214
assertTrue("Set fetch direction != get fetch direction",
215
fetchDirection == java.sql.ResultSet.FETCH_FORWARD);
218
accessorStmt.setFetchDirection(Integer.MAX_VALUE);
219
fail("Should not be able to set fetch direction to invalid value");
220
} /* ignore */ catch (SQLException sqlEx) {
225
accessorStmt.setMaxRows(50000000 + 10);
226
fail("Should not be able to set max rows > 50000000");
227
} /* ignore */ catch (SQLException sqlEx) {
232
accessorStmt.setMaxRows(Integer.MIN_VALUE);
233
fail("Should not be able to set max rows < 0");
234
} /* ignore */ catch (SQLException sqlEx) {
238
int fetchSize = stmt.getFetchSize();
241
accessorStmt.setMaxRows(4);
242
accessorStmt.setFetchSize(Integer.MAX_VALUE);
243
fail("Should not be able to set FetchSize > max rows");
244
} /* ignore */ catch (SQLException sqlEx) {
249
accessorStmt.setFetchSize(-2);
250
fail("Should not be able to set FetchSize < 0");
251
} /* ignore */ catch (SQLException sqlEx) {
255
assertTrue("Fetch size before invalid setFetchSize() calls should match fetch size now",
256
fetchSize == stmt.getFetchSize());
259
if (accessorStmt != null) {
262
accessorStmt.close();
263
} /* ignore */ catch (SQLException sqlEx) {
275
* @throws SQLException DOCUMENT ME!
277
public void testSelectColumns()
278
throws SQLException {
280
for (int i = 6; i < MAX_COLUMNS_TO_TEST; i += STEP) {
282
long start = System.currentTimeMillis();
283
rs = stmt.executeQuery("SELECT * from statement_col_test_" + i);
289
long end = System.currentTimeMillis();
290
System.out.println(i + " columns = " + (end - start));
297
* @throws SQLException DOCUMENT ME!
299
public void testAutoIncrement()
300
throws SQLException {
303
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
304
java.sql.ResultSet.CONCUR_READ_ONLY);
305
stmt.setFetchSize(Integer.MIN_VALUE);
307
"INSERT INTO statement_test (strdata1) values ('blah')");
309
int autoIncKeyFromApi = -1;
310
rs = stmt.getGeneratedKeys();
313
autoIncKeyFromApi = rs.getInt(1);
315
fail("Failed to retrieve AUTO_INCREMENT using Statement.getGeneratedKeys()");
320
int autoIncKeyFromFunc = -1;
321
rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
324
autoIncKeyFromFunc = rs.getInt(1);
326
fail("Failed to retrieve AUTO_INCREMENT using LAST_INSERT_ID()");
329
if (autoIncKeyFromApi != -1 && autoIncKeyFromFunc != -1) {
330
assertTrue("Key retrieved from API (" + autoIncKeyFromApi
331
+ ") does not match key retrieved from LAST_INSERT_ID() "
332
+ autoIncKeyFromFunc + ") function",
333
autoIncKeyFromApi == autoIncKeyFromFunc);
335
fail("AutoIncrement keys were '0'");
343
} catch (Exception ex) { /* ignore */
355
* @throws SQLException DOCUMENT ME!
357
public void testClose()
358
throws SQLException {
360
Statement closeStmt = null;
361
boolean exceptionAfterClosed = false;
364
closeStmt = conn.createStatement();
368
closeStmt.executeQuery("SELECT 1");
369
} catch (SQLException sqlEx) {
370
exceptionAfterClosed = true;
374
if (closeStmt != null) {
378
} catch (SQLException sqlEx) {
387
assertTrue("Operations not allowed on Statement after .close() is called!",
388
exceptionAfterClosed);
394
* @throws SQLException DOCUMENT ME!
396
public void testInsert()
397
throws SQLException {
401
boolean autoCommit = conn.getAutoCommit();
403
// Test running a query for an update. It should fail.
405
conn.setAutoCommit(false);
406
stmt.executeUpdate("SELECT * FROM statement_test");
407
} catch (SQLException sqlEx) {
408
assertTrue("Exception thrown for unknown reason",
409
sqlEx.getSQLState().equalsIgnoreCase("S1009"));
411
conn.setAutoCommit(autoCommit);
414
// Test running a update for an query. It should fail.
416
conn.setAutoCommit(false);
418
"UPDATE statement_test SET strdata1='blah' WHERE 1=0");
419
} catch (SQLException sqlEx) {
420
assertTrue("Exception thrown for unknown reason",
421
sqlEx.getSQLState().equalsIgnoreCase("S1009"));
423
conn.setAutoCommit(autoCommit);
426
for (int i = 0; i < 10; i++) {
428
int updateCount = stmt.executeUpdate(
429
"INSERT INTO statement_test (strdata1,strdata2) values ('abcdefg', 'poi')");
430
assertTrue("Update count must be '1', was '" + updateCount
431
+ "'", (updateCount == 1));
435
"INSERT INTO statement_test (strdata1, strdata2) values ('a', 'a'), ('b', 'b'), ('c', 'c')");
436
rs = stmt.getGeneratedKeys();
445
rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
447
int updateCountFromServer = 0;
450
updateCountFromServer = rs.getInt(1);
454
"Update count from server: " + updateCountFromServer);
461
} catch (Exception ex) { /* ignore */
473
* @throws SQLException DOCUMENT ME!
475
public void testPreparedStatement()
476
throws SQLException {
478
"INSERT INTO statement_test (id, strdata1,strdata2) values (999,'abcdefg', 'poi')");
479
pstmt = conn.prepareStatement(
480
"UPDATE statement_test SET strdata1=?, strdata2=? where id=?");
481
pstmt.setString(1, "iop");
482
pstmt.setString(2, "higjklmn");
483
pstmt.setInt(3, 999);
485
int updateCount = pstmt.executeUpdate();
486
assertTrue("Update count must be '1', was '" + updateCount + "'",
493
* @throws SQLException DOCUMENT ME!
495
public void testPreparedStatementBatch()
496
throws SQLException {
497
pstmt = conn.prepareStatement(
499
+ "statement_batch_test (strdata1, strdata2) VALUES (?,?)");
501
for (int i = 0; i < 10; i++) {
502
pstmt.setString(1, "batch_" + i);
503
pstmt.setString(2, "batch_" + i);
507
int[] updateCounts = pstmt.executeBatch();
509
for (int i = 0; i < updateCounts.length; i++) {
510
assertTrue("Update count must be '1', was '" + updateCounts[i]
511
+ "'", (updateCounts[i] == 1));
518
* @throws SQLException DOCUMENT ME!
520
public void testStubbed()
521
throws SQLException {
524
stmt.getResultSetHoldability();
525
} /* ignore */ catch (NotImplemented notImplEx) {
531
* Tests that NULLs and '' work correctly.
533
* @throws SQLException if an error occurs
535
public void testNulls() throws SQLException {
537
stmt.executeUpdate("DROP TABLE IF EXISTS nullTest");
538
stmt.executeUpdate("CREATE TABLE IF NOT EXISTS nullTest (field_1 CHAR(20), rowOrder INT)");
539
stmt.executeUpdate("INSERT INTO nullTest VALUES (null, 1), ('', 2)");
541
rs = stmt.executeQuery("SELECT field_1 FROM nullTest ORDER BY rowOrder");
545
assertTrue("NULL field not returned as NULL", rs.getString("field_1") == null && rs.wasNull());
549
assertTrue("Empty field not returned as \"\"", rs.getString("field_1").equals("") && !rs.wasNull());
556
} catch (Exception ex) {
561
stmt.executeUpdate("DROP TABLE IF EXISTS nullTest");