~pbms-core/pbms/async_read
» Revision
1
: mybs/java/src/testsuite/regression/StatementRegressionTest.java
« back to all changes in this revision
Viewing changes to mybs/java/src/testsuite/regression/StatementRegressionTest.java
- browse files at revision 1
- compare with another revision
- download diff
- download tarball
- view history from revision 1
- Committer: paul-mccullagh
- Date: 2008-03-26 11:35:17 UTC
- Revision ID: paul-mccullagh-afb1610c21464a577ae428d72fc725eb986c05a5
Initial import
- files added:
- mybs
- mybs/config
- mybs/java
- mybs/java/docs
- mybs/java/src
- mybs/java/src/com
- mybs/java/src/com/mysql
- mybs/java/src/com/mysql/jdbc
- mybs/java/src/com/mysql/jdbc/configs
- mybs/java/src/com/mysql/jdbc/exceptions
- mybs/java/src/com/mysql/jdbc/integration
- mybs/java/src/com/mysql/jdbc/integration/c3p0
- mybs/java/src/com/mysql/jdbc/integration/jboss
- mybs/java/src/com/mysql/jdbc/jdbc2
- mybs/java/src/com/mysql/jdbc/jdbc2/optional
- mybs/java/src/com/mysql/jdbc/log
- mybs/java/src/com/mysql/jdbc/profiler
- mybs/java/src/com/mysql/jdbc/trace
- mybs/java/src/com/mysql/jdbc/util
- mybs/java/src/doc
- mybs/java/src/doc/sources
- mybs/java/src/java.xcodeproj
- mybs/java/src/lib
- mybs/java/src/org
- mybs/java/src/org/blobstreaming
- mybs/java/src/org/blobstreaming/jdbc
- mybs/java/src/org/blobstreaming/lib
- mybs/java/src/org/blobstreaming/www
- mybs/java/src/org/gjt
- mybs/java/src/org/gjt/mm
- mybs/java/src/org/gjt/mm/mysql
- mybs/java/src/testsuite
- mybs/java/src/testsuite/perf
- mybs/java/src/testsuite/regression
- mybs/java/src/testsuite/simple
- mybs/java/src/testsuite/ssl-test-certs
- mybs/src
- mybs/src/cslib
added
removed
mybs/java/src/testsuite/regression/StatementRegressionTest.java
1
/*
2
Copyright (C) 2002-2007 MySQL AB
3
4
This program is free software; you can redistribute it and/or modify
5
it under the terms of version 2 of the GNU General Public License as
6
published by the Free Software Foundation.
7
8
There are special exceptions to the terms and conditions of the GPL
9
as it is applied to this software. View the full text of the
10
exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
11
software distribution.
12
13
This program is distributed in the hope that it will be useful,
14
but WITHOUT ANY WARRANTY; without even the implied warranty of
15
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16
GNU General Public License for more details.
17
18
You should have received a copy of the GNU General Public License
19
along with this program; if not, write to the Free Software
20
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
21
22
23
24
*/
25
package testsuite.regression;
26
27
import java.io.ByteArrayInputStream;
28
import java.io.ByteArrayOutputStream;
29
import java.io.CharArrayReader;
30
import java.io.File;
31
import java.io.FileOutputStream;
32
import java.io.FileWriter;
33
import java.io.IOException;
34
import java.io.PrintStream;
35
import java.io.StringReader;
36
import java.io.Writer;
37
import java.math.BigDecimal;
38
import java.math.BigInteger;
39
import java.sql.BatchUpdateException;
40
import java.sql.Blob;
41
import java.sql.Clob;
42
import java.sql.Connection;
43
import java.sql.DataTruncation;
44
import java.sql.Date;
45
import java.sql.PreparedStatement;
46
import java.sql.ResultSet;
47
import java.sql.SQLException;
48
import java.sql.SQLWarning;
49
import java.sql.Statement;
50
import java.sql.Time;
51
import java.sql.Timestamp;
52
import java.sql.Types;
53
import java.text.SimpleDateFormat;
54
import java.util.Calendar;
55
import java.util.Locale;
56
import java.util.Properties;
57
import java.util.TimeZone;
58
59
import testsuite.BaseTestCase;
60
61
import com.mysql.jdbc.SQLError;
62
import com.mysql.jdbc.ServerPreparedStatement;
63
import com.mysql.jdbc.exceptions.MySQLTimeoutException;
64
65
/**
66
* Regression tests for the Statement class
67
*
68
* @author Mark Matthews
69
*/
70
public class StatementRegressionTest extends BaseTestCase {
71
class PrepareThread extends Thread {
72
Connection c;
73
74
PrepareThread(Connection cn) {
75
this.c = cn;
76
}
77
78
public void run() {
79
for (int i = 0; i < 20; i++) // force this to end eventually
80
{
81
try {
82
this.c.prepareStatement("SELECT 1");
83
StatementRegressionTest.this.testServerPrepStmtDeadlockCounter++;
84
Thread.sleep(400);
85
} catch (SQLException sqlEx) {
86
throw new RuntimeException(sqlEx);
87
} catch (InterruptedException e) {
88
e.printStackTrace();
89
}
90
}
91
}
92
}
93
94
static int count = 0;
95
96
static int nextID = 1; // The next ID we expected to generate
97
98
/*
99
* Each row in this table is to be converted into a single REPLACE
100
* statement. If the value is zero, a new record is to be created using then
101
* autoincrement feature. If the value is non-zero, the existing row of that
102
* value is to be replace with, obviously, the same key. I expect one
103
* Generated Key for each zero value - but I would accept one key for each
104
* value, with non-zero values coming back as themselves.
105
*/
106
static final int[][] tests = { { 0 }, // generate 1
107
{ 1, 0, 0 }, // update 1, generate 2, 3
108
{ 2, 0, 0, }, // update 2, generate 3, 4
109
};
110
111
/**
112
* Runs all test cases in this test suite
113
*
114
* @param args
115
*/
116
public static void main(String[] args) {
117
junit.textui.TestRunner.run(StatementRegressionTest.class);
118
}
119
120
private int testServerPrepStmtDeadlockCounter = 0;
121
122
/**
123
* Constructor for StatementRegressionTest.
124
*
125
* @param name
126
* the name of the test to run
127
*/
128
public StatementRegressionTest(String name) {
129
super(name);
130
}
131
132
private void addBatchItems(Statement statement, PreparedStatement pStmt,
133
String tableName, int i) throws SQLException {
134
pStmt.setString(1, "ps_batch_" + i);
135
pStmt.setString(2, "ps_batch_" + i);
136
pStmt.addBatch();
137
138
statement.addBatch("INSERT INTO " + tableName
139
+ " (strdata1, strdata2) VALUES " + "(\"s_batch_" + i
140
+ "\",\"s_batch_" + i + "\")");
141
}
142
143
private void createGGKTables() throws Exception {
144
// Delete and recreate table
145
dropGGKTables();
146
147
this.stmt.executeUpdate("CREATE TABLE testggk ("
148
+ "id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,"
149
+ "val INT NOT NULL" + ")");
150
}
151
152
private void doGGKTestPreparedStatement(int[] values, boolean useUpdate)
153
throws Exception {
154
// Generate the the multiple replace command
155
StringBuffer cmd = new StringBuffer("REPLACE INTO testggk VALUES ");
156
int newKeys = 0;
157
158
for (int i = 0; i < values.length; i++) {
159
cmd.append("(");
160
161
if (values[i] == 0) {
162
cmd.append("NULL");
163
newKeys += 1;
164
} else {
165
cmd.append(values[i]);
166
}
167
168
cmd.append(", ");
169
cmd.append(count++);
170
cmd.append("), ");
171
}
172
173
cmd.setLength(cmd.length() - 2); // trim the final ", "
174
175
// execute and print it
176
System.out.println(cmd.toString());
177
178
PreparedStatement pStmt = this.conn.prepareStatement(cmd.toString(),
179
Statement.RETURN_GENERATED_KEYS);
180
181
if (useUpdate) {
182
pStmt.executeUpdate();
183
} else {
184
pStmt.execute();
185
}
186
187
// print out what actually happened
188
System.out.println("Expect " + newKeys
189
+ " generated keys, starting from " + nextID);
190
191
this.rs = pStmt.getGeneratedKeys();
192
StringBuffer res = new StringBuffer("Got keys");
193
194
int[] generatedKeys = new int[newKeys];
195
int i = 0;
196
197
while (this.rs.next()) {
198
if (i < generatedKeys.length) {
199
generatedKeys[i] = this.rs.getInt(1);
200
}
201
202
i++;
203
204
res.append(" " + this.rs.getInt(1));
205
}
206
207
int numberOfGeneratedKeys = i;
208
209
assertTrue(
210
"Didn't retrieve expected number of generated keys, expected "
211
+ newKeys + ", found " + numberOfGeneratedKeys,
212
numberOfGeneratedKeys == newKeys);
213
assertTrue("Keys didn't start with correct sequence: ",
214
generatedKeys[0] == nextID);
215
216
System.out.println(res.toString());
217
218
// Read and print the new state of the table
219
this.rs = this.stmt.executeQuery("SELECT id, val FROM testggk");
220
System.out.println("New table contents ");
221
222
while (this.rs.next())
223
System.out.println("Id " + this.rs.getString(1) + " val "
224
+ this.rs.getString(2));
225
226
// Tidy up
227
System.out.println("");
228
nextID += newKeys;
229
}
230
231
private void doGGKTestStatement(int[] values, boolean useUpdate)
232
throws Exception {
233
// Generate the the multiple replace command
234
StringBuffer cmd = new StringBuffer("REPLACE INTO testggk VALUES ");
235
int newKeys = 0;
236
237
for (int i = 0; i < values.length; i++) {
238
cmd.append("(");
239
240
if (values[i] == 0) {
241
cmd.append("NULL");
242
newKeys += 1;
243
} else {
244
cmd.append(values[i]);
245
}
246
247
cmd.append(", ");
248
cmd.append(count++);
249
cmd.append("), ");
250
}
251
252
cmd.setLength(cmd.length() - 2); // trim the final ", "
253
254
// execute and print it
255
System.out.println(cmd.toString());
256
257
if (useUpdate) {
258
this.stmt.executeUpdate(cmd.toString(),
259
Statement.RETURN_GENERATED_KEYS);
260
} else {
261
this.stmt.execute(cmd.toString(), Statement.RETURN_GENERATED_KEYS);
262
}
263
264
// print out what actually happened
265
System.out.println("Expect " + newKeys
266
+ " generated keys, starting from " + nextID);
267
268
this.rs = this.stmt.getGeneratedKeys();
269
StringBuffer res = new StringBuffer("Got keys");
270
271
int[] generatedKeys = new int[newKeys];
272
int i = 0;
273
274
while (this.rs.next()) {
275
if (i < generatedKeys.length) {
276
generatedKeys[i] = this.rs.getInt(1);
277
}
278
279
i++;
280
281
res.append(" " + this.rs.getInt(1));
282
}
283
284
int numberOfGeneratedKeys = i;
285
286
assertTrue(
287
"Didn't retrieve expected number of generated keys, expected "
288
+ newKeys + ", found " + numberOfGeneratedKeys,
289
numberOfGeneratedKeys == newKeys);
290
assertTrue("Keys didn't start with correct sequence: ",
291
generatedKeys[0] == nextID);
292
293
System.out.println(res.toString());
294
295
// Read and print the new state of the table
296
this.rs = this.stmt.executeQuery("SELECT id, val FROM testggk");
297
System.out.println("New table contents ");
298
299
while (this.rs.next())
300
System.out.println("Id " + this.rs.getString(1) + " val "
301
+ this.rs.getString(2));
302
303
// Tidy up
304
System.out.println("");
305
nextID += newKeys;
306
}
307
308
private void dropGGKTables() throws Exception {
309
this.stmt.executeUpdate("DROP TABLE IF EXISTS testggk");
310
}
311
312
/**
313
* @param pStmt
314
* @param catId
315
* @throws SQLException
316
*/
317
private void execQueryBug5191(PreparedStatement pStmt, int catId)
318
throws SQLException {
319
pStmt.setInt(1, catId);
320
321
this.rs = pStmt.executeQuery();
322
323
assertTrue(this.rs.next());
324
assertTrue(this.rs.next());
325
// assertTrue(rs.next());
326
327
assertFalse(this.rs.next());
328
}
329
330
private String getByteArrayString(byte[] ba) {
331
StringBuffer buffer = new StringBuffer();
332
if (ba != null) {
333
for (int i = 0; i < ba.length; i++) {
334
buffer.append("0x" + Integer.toHexString(ba[i] & 0xff) + " ");
335
}
336
} else {
337
buffer.append("null");
338
}
339
return buffer.toString();
340
}
341
342
/**
343
* @param continueBatchOnError
344
* @throws SQLException
345
*/
346
private void innerBug6823(boolean continueBatchOnError) throws SQLException {
347
Properties continueBatchOnErrorProps = new Properties();
348
continueBatchOnErrorProps.setProperty("continueBatchOnError", String
349
.valueOf(continueBatchOnError));
350
this.conn = getConnectionWithProps(continueBatchOnErrorProps);
351
Statement statement = this.conn.createStatement();
352
353
String tableName = "testBug6823";
354
355
createTable(tableName, "(id int not null primary key auto_increment,"
356
+ " strdata1 varchar(255) not null, strdata2 varchar(255),"
357
+ " UNIQUE INDEX (strdata1))");
358
359
PreparedStatement pStmt = this.conn.prepareStatement("INSERT INTO "
360
+ tableName + " (strdata1, strdata2) VALUES (?,?)");
361
362
int c = 0;
363
addBatchItems(statement, pStmt, tableName, ++c);
364
addBatchItems(statement, pStmt, tableName, ++c);
365
addBatchItems(statement, pStmt, tableName, ++c);
366
addBatchItems(statement, pStmt, tableName, c); // duplicate entry
367
addBatchItems(statement, pStmt, tableName, ++c);
368
addBatchItems(statement, pStmt, tableName, ++c);
369
370
int expectedUpdateCounts = continueBatchOnError ? 6 : 3;
371
372
BatchUpdateException e1 = null;
373
BatchUpdateException e2 = null;
374
375
int[] updateCountsPstmt = null;
376
try {
377
updateCountsPstmt = pStmt.executeBatch();
378
} catch (BatchUpdateException e) {
379
e1 = e;
380
updateCountsPstmt = e1.getUpdateCounts();
381
}
382
383
int[] updateCountsStmt = null;
384
try {
385
updateCountsStmt = statement.executeBatch();
386
} catch (BatchUpdateException e) {
387
e2 = e;
388
updateCountsStmt = e1.getUpdateCounts();
389
}
390
391
assertNotNull(e1);
392
assertNotNull(e2);
393
394
assertEquals(expectedUpdateCounts, updateCountsPstmt.length);
395
assertEquals(expectedUpdateCounts, updateCountsStmt.length);
396
397
if (continueBatchOnError) {
398
assertTrue(updateCountsPstmt[3] == Statement.EXECUTE_FAILED);
399
assertTrue(updateCountsStmt[3] == Statement.EXECUTE_FAILED);
400
}
401
402
int psRows = 0;
403
this.rs = this.stmt.executeQuery("SELECT * from " + tableName
404
+ " WHERE strdata1 like \"ps_%\"");
405
while (this.rs.next()) {
406
psRows++;
407
}
408
assertTrue(psRows > 0);
409
410
int sRows = 0;
411
this.rs = this.stmt.executeQuery("SELECT * from " + tableName
412
+ " WHERE strdata1 like \"s_%\"");
413
while (this.rs.next()) {
414
sRows++;
415
}
416
assertTrue(sRows > 0);
417
418
assertTrue(psRows + "!=" + sRows, psRows == sRows);
419
}
420
421
/**
422
* Tests fix for BUG#10155, double quotes not recognized when parsing
423
* client-side prepared statements.
424
*
425
* @throws Exception
426
* if the test fails.
427
*/
428
public void testBug10155() throws Exception {
429
this.conn.prepareStatement(
430
"SELECT \"Test question mark? Test single quote'\"")
431
.executeQuery().close();
432
}
433
434
/**
435
* Tests fix for BUG#10630, Statement.getWarnings() fails with NPE if
436
* statement has been closed.
437
*/
438
public void testBug10630() throws Exception {
439
Connection conn2 = null;
440
Statement stmt2 = null;
441
442
try {
443
conn2 = getConnectionWithProps(null);
444
stmt2 = conn2.createStatement();
445
446
conn2.close();
447
stmt2.getWarnings();
448
fail("Should've caught an exception here");
449
} catch (SQLException sqlEx) {
450
assertEquals("08003", sqlEx.getSQLState());
451
} finally {
452
if (stmt2 != null) {
453
stmt2.close();
454
}
455
456
if (conn2 != null) {
457
conn2.close();
458
}
459
}
460
}
461
462
/**
463
* Tests fix for BUG#11115, Varbinary data corrupted when using server-side
464
* prepared statements.
465
*/
466
public void testBug11115() throws Exception {
467
String tableName = "testBug11115";
468
469
if (versionMeetsMinimum(4, 1, 0)) {
470
471
createTable(tableName,
472
"(pwd VARBINARY(30)) TYPE=InnoDB DEFAULT CHARACTER SET utf8");
473
474
byte[] bytesToTest = new byte[] { 17, 120, -1, -73, -5 };
475
476
PreparedStatement insStmt = this.conn
477
.prepareStatement("INSERT INTO " + tableName
478
+ " (pwd) VALUES (?)");
479
insStmt.setBytes(1, bytesToTest);
480
insStmt.executeUpdate();
481
482
this.rs = this.stmt.executeQuery("SELECT pwd FROM " + tableName);
483
this.rs.next();
484
485
byte[] fromDatabase = this.rs.getBytes(1);
486
487
assertEquals(bytesToTest.length, fromDatabase.length);
488
489
for (int i = 0; i < bytesToTest.length; i++) {
490
assertEquals(bytesToTest[i], fromDatabase[i]);
491
}
492
493
this.rs = this.conn
494
.prepareStatement("SELECT pwd FROM " + tableName)
495
.executeQuery();
496
this.rs.next();
497
498
fromDatabase = this.rs.getBytes(1);
499
500
assertEquals(bytesToTest.length, fromDatabase.length);
501
502
for (int i = 0; i < bytesToTest.length; i++) {
503
assertEquals(bytesToTest[i], fromDatabase[i]);
504
}
505
}
506
}
507
508
public void testBug11540() throws Exception {
509
Locale originalLocale = Locale.getDefault();
510
Connection thaiConn = null;
511
Statement thaiStmt = null;
512
PreparedStatement thaiPrepStmt = null;
513
514
try {
515
createTable("testBug11540", "(field1 DATE, field2 TIMESTAMP)");
516
this.stmt
517
.executeUpdate("INSERT INTO testBug11540 VALUES (NOW(), NOW())");
518
Locale.setDefault(new Locale("th", "TH"));
519
Properties props = new Properties();
520
props.setProperty("jdbcCompliantTruncation", "false");
521
522
thaiConn = getConnectionWithProps(props);
523
thaiStmt = thaiConn.createStatement();
524
525
this.rs = thaiStmt
526
.executeQuery("SELECT field1, field2 FROM testBug11540");
527
this.rs.next();
528
529
Date origDate = this.rs.getDate(1);
530
Timestamp origTimestamp = this.rs.getTimestamp(1);
531
this.rs.close();
532
533
thaiStmt.executeUpdate("TRUNCATE TABLE testBug11540");
534
535
thaiPrepStmt = ((com.mysql.jdbc.Connection) thaiConn)
536
.clientPrepareStatement("INSERT INTO testBug11540 VALUES (?,?)");
537
thaiPrepStmt.setDate(1, origDate);
538
thaiPrepStmt.setTimestamp(2, origTimestamp);
539
thaiPrepStmt.executeUpdate();
540
541
this.rs = thaiStmt
542
.executeQuery("SELECT field1, field2 FROM testBug11540");
543
this.rs.next();
544
545
Date testDate = this.rs.getDate(1);
546
Timestamp testTimestamp = this.rs.getTimestamp(1);
547
this.rs.close();
548
549
assertEquals(origDate, testDate);
550
assertEquals(origTimestamp, testTimestamp);
551
552
} finally {
553
Locale.setDefault(originalLocale);
554
}
555
}
556
557
/**
558
* Tests fix for BUG#11663, autoGenerateTestcaseScript uses bogus parameter
559
* names for server-side prepared statements.
560
*
561
* @throws Exception
562
* if the test fails.
563
*/
564
public void testBug11663() throws Exception {
565
if (versionMeetsMinimum(4, 1, 0)
566
&& ((com.mysql.jdbc.Connection) this.conn)
567
.getUseServerPreparedStmts()) {
568
Connection testcaseGenCon = null;
569
PrintStream oldErr = System.err;
570
571
try {
572
createTable("testBug11663", "(field1 int)");
573
574
Properties props = new Properties();
575
props.setProperty("autoGenerateTestcaseScript", "true");
576
testcaseGenCon = getConnectionWithProps(props);
577
ByteArrayOutputStream testStream = new ByteArrayOutputStream();
578
PrintStream testErr = new PrintStream(testStream);
579
System.setErr(testErr);
580
this.pstmt = testcaseGenCon
581
.prepareStatement("SELECT field1 FROM testBug11663 WHERE field1=?");
582
this.pstmt.setInt(1, 1);
583
this.pstmt.execute();
584
System.setErr(oldErr);
585
String testString = new String(testStream.toByteArray());
586
587
int setIndex = testString.indexOf("SET @debug_stmt_param");
588
int equalsIndex = testString.indexOf("=", setIndex);
589
String paramName = testString.substring(setIndex + 4,
590
equalsIndex);
591
592
int usingIndex = testString.indexOf("USING " + paramName,
593
equalsIndex);
594
595
assertTrue(usingIndex != -1);
596
} finally {
597
System.setErr(oldErr);
598
599
if (this.pstmt != null) {
600
this.pstmt.close();
601
this.pstmt = null;
602
}
603
604
if (testcaseGenCon != null) {
605
testcaseGenCon.close();
606
}
607
608
}
609
}
610
}
611
612
/**
613
* Tests fix for BUG#11798 - Pstmt.setObject(...., Types.BOOLEAN) throws
614
* exception.
615
*
616
* @throws Exception
617
* if the test fails.
618
*/
619
public void testBug11798() throws Exception {
620
if (isRunningOnJdk131()) {
621
return; // test not valid on JDK-1.3.1
622
}
623
624
try {
625
this.pstmt = this.conn.prepareStatement("SELECT ?");
626
this.pstmt.setObject(1, Boolean.TRUE, Types.BOOLEAN);
627
this.pstmt.setObject(1, new BigDecimal("1"), Types.BOOLEAN);
628
this.pstmt.setObject(1, "true", Types.BOOLEAN);
629
} finally {
630
if (this.pstmt != null) {
631
this.pstmt.close();
632
this.pstmt = null;
633
}
634
}
635
}
636
637
/**
638
* Tests fix for BUG#13255 - Reconnect during middle of executeBatch()
639
* should not happen.
640
*
641
* @throws Exception
642
* if the test fails.
643
*/
644
public void testBug13255() throws Exception {
645
646
createTable("testBug13255", "(field_1 int)");
647
648
Properties props = new Properties();
649
props.setProperty("autoReconnect", "true");
650
651
Connection reconnectConn = null;
652
Statement reconnectStmt = null;
653
PreparedStatement reconnectPStmt = null;
654
655
try {
656
reconnectConn = getConnectionWithProps(props);
657
reconnectStmt = reconnectConn.createStatement();
658
659
String connectionId = getSingleIndexedValueWithQuery(reconnectConn,
660
1, "SELECT CONNECTION_ID()").toString();
661
662
reconnectStmt.addBatch("INSERT INTO testBug13255 VALUES (1)");
663
reconnectStmt.addBatch("INSERT INTO testBug13255 VALUES (2)");
664
reconnectStmt.addBatch("KILL " + connectionId);
665
666
for (int i = 0; i < 100; i++) {
667
reconnectStmt.addBatch("INSERT INTO testBug13255 VALUES (" + i
668
+ ")");
669
}
670
671
try {
672
reconnectStmt.executeBatch();
673
} catch (SQLException sqlEx) {
674
// We expect this...we killed the connection
675
}
676
677
assertEquals(2, getRowCount("testBug13255"));
678
679
this.stmt.executeUpdate("TRUNCATE TABLE testBug13255");
680
681
reconnectConn.close();
682
683
reconnectConn = getConnectionWithProps(props);
684
685
connectionId = getSingleIndexedValueWithQuery(reconnectConn, 1,
686
"SELECT CONNECTION_ID()").toString();
687
688
reconnectPStmt = reconnectConn
689
.prepareStatement("INSERT INTO testBug13255 VALUES (?)");
690
reconnectPStmt.setInt(1, 1);
691
reconnectPStmt.addBatch();
692
reconnectPStmt.setInt(1, 2);
693
reconnectPStmt.addBatch();
694
reconnectPStmt.addBatch("KILL " + connectionId);
695
696
for (int i = 3; i < 100; i++) {
697
reconnectPStmt.setInt(1, i);
698
reconnectPStmt.addBatch();
699
}
700
701
try {
702
reconnectPStmt.executeBatch();
703
} catch (SQLException sqlEx) {
704
// We expect this...we killed the connection
705
}
706
707
assertEquals(2, getRowCount("testBug13255"));
708
709
} finally {
710
if (reconnectStmt != null) {
711
reconnectStmt.close();
712
}
713
714
if (reconnectConn != null) {
715
reconnectConn.close();
716
}
717
}
718
}
719
720
/**
721
* Tests fix for BUG#15024 - Driver incorrectly closes streams passed as
722
* arguments to PreparedStatements.
723
*
724
* @throws Exception
725
* if the test fails.
726
*/
727
public void testBug15024() throws Exception {
728
createTable("testBug15024", "(field1 BLOB)");
729
730
try {
731
this.pstmt = this.conn
732
.prepareStatement("INSERT INTO testBug15024 VALUES (?)");
733
testStreamsForBug15024(false, false);
734
735
Properties props = new Properties();
736
props.setProperty("useConfigs", "3-0-Compat");
737
738
Connection compatConn = null;
739
740
try {
741
compatConn = getConnectionWithProps(props);
742
743
this.pstmt = compatConn
744
.prepareStatement("INSERT INTO testBug15024 VALUES (?)");
745
testStreamsForBug15024(true, false);
746
} finally {
747
if (compatConn != null) {
748
compatConn.close();
749
}
750
}
751
} finally {
752
if (this.pstmt != null) {
753
PreparedStatement toClose = this.pstmt;
754
this.pstmt = null;
755
756
toClose.close();
757
}
758
}
759
}
760
761
/**
762
* PreparedStatement should call EscapeProcessor.escapeSQL?
763
*
764
* @throws Exception
765
* if the test fails
766
*/
767
public void testBug15141() throws Exception {
768
try {
769
createTable("testBug15141", "(field1 VARCHAR(32))");
770
this.stmt.executeUpdate("INSERT INTO testBug15141 VALUES ('abc')");
771
772
this.pstmt = this.conn
773
.prepareStatement("select {d '1997-05-24'} FROM testBug15141");
774
this.rs = this.pstmt.executeQuery();
775
assertTrue(this.rs.next());
776
assertEquals("1997-05-24", this.rs.getString(1));
777
this.rs.close();
778
this.rs = null;
779
this.pstmt.close();
780
this.pstmt = null;
781
782
this.pstmt = ((com.mysql.jdbc.Connection) this.conn)
783
.clientPrepareStatement("select {d '1997-05-24'} FROM testBug15141");
784
this.rs = this.pstmt.executeQuery();
785
assertTrue(this.rs.next());
786
assertEquals("1997-05-24", this.rs.getString(1));
787
this.rs.close();
788
this.rs = null;
789
this.pstmt.close();
790
this.pstmt = null;
791
} finally {
792
if (this.rs != null) {
793
ResultSet toCloseRs = this.rs;
794
this.rs = null;
795
toCloseRs.close();
796
}
797
798
if (this.pstmt != null) {
799
PreparedStatement toClosePstmt = this.pstmt;
800
this.pstmt = null;
801
toClosePstmt.close();
802
}
803
}
804
}
805
806
/**
807
* Tests fix for BUG#18041 - Server-side prepared statements don't cause
808
* truncation exceptions to be thrown.
809
*
810
* @throws Exception
811
* if the test fails
812
*/
813
public void testBug18041() throws Exception {
814
if (versionMeetsMinimum(4, 1)) {
815
createTable("testBug18041", "(`a` tinyint(4) NOT NULL,"
816
+ "`b` char(4) default NULL)");
817
818
Properties props = new Properties();
819
props.setProperty("jdbcCompliantTruncation", "true");
820
props.setProperty("useServerPrepStmts", "true");
821
822
Connection truncConn = null;
823
PreparedStatement stm = null;
824
825
try {
826
truncConn = getConnectionWithProps(props);
827
828
stm = truncConn
829
.prepareStatement("insert into testBug18041 values (?,?)");
830
stm.setInt(1, 1000);
831
stm.setString(2, "nnnnnnnnnnnnnnnnnnnnnnnnnnnnnn");
832
stm.executeUpdate();
833
fail("Truncation exception should have been thrown");
834
} catch (DataTruncation truncEx) {
835
// we expect this
836
} finally {
837
if (this.stmt != null) {
838
this.stmt.close();
839
}
840
841
if (truncConn != null) {
842
truncConn.close();
843
}
844
}
845
}
846
}
847
848
private void testStreamsForBug15024(boolean shouldBeClosedStream,
849
boolean shouldBeClosedReader) throws SQLException {
850
IsClosedInputStream bIn = new IsClosedInputStream(new byte[4]);
851
IsClosedReader readerIn = new IsClosedReader("abcdef");
852
853
this.pstmt.setBinaryStream(1, bIn, 4);
854
this.pstmt.execute();
855
assertEquals(shouldBeClosedStream, bIn.isClosed());
856
857
this.pstmt.setCharacterStream(1, readerIn, 6);
858
this.pstmt.execute();
859
assertEquals(shouldBeClosedReader, readerIn.isClosed());
860
861
this.pstmt.close();
862
}
863
864
class IsClosedReader extends StringReader {
865
866
boolean isClosed = false;
867
868
public IsClosedReader(String arg0) {
869
super(arg0);
870
}
871
872
public void close() {
873
super.close();
874
875
this.isClosed = true;
876
}
877
878
public boolean isClosed() {
879
return this.isClosed;
880
}
881
882
}
883
884
class IsClosedInputStream extends ByteArrayInputStream {
885
886
boolean isClosed = false;
887
888
public IsClosedInputStream(byte[] arg0, int arg1, int arg2) {
889
super(arg0, arg1, arg2);
890
}
891
892
public IsClosedInputStream(byte[] arg0) {
893
super(arg0);
894
}
895
896
public void close() throws IOException {
897
// TODO Auto-generated method stub
898
super.close();
899
this.isClosed = true;
900
}
901
902
public boolean isClosed() {
903
return this.isClosed;
904
}
905
}
906
907
/**
908
* Tests fix for BUG#1774 -- Truncated words after double quote
909
*
910
* @throws Exception
911
* if the test fails.
912
*/
913
public void testBug1774() throws Exception {
914
try {
915
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1774");
916
this.stmt
917
.executeUpdate("CREATE TABLE testBug1774 (field1 VARCHAR(255))");
918
919
PreparedStatement pStmt = this.conn
920
.prepareStatement("INSERT INTO testBug1774 VALUES (?)");
921
922
String testString = "The word contains \" character";
923
924
pStmt.setString(1, testString);
925
pStmt.executeUpdate();
926
927
this.rs = this.stmt.executeQuery("SELECT * FROM testBug1774");
928
this.rs.next();
929
assertEquals(this.rs.getString(1), testString);
930
} finally {
931
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1774");
932
}
933
}
934
935
/**
936
* Tests fix for BUG#1901 -- PreparedStatement.setObject(int, Object, int,
937
* int) doesn't support CLOB or BLOB types.
938
*
939
* @throws Exception
940
* if this test fails for any reason
941
*/
942
public void testBug1901() throws Exception {
943
try {
944
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1901");
945
this.stmt
946
.executeUpdate("CREATE TABLE testBug1901 (field1 VARCHAR(255))");
947
this.stmt.executeUpdate("INSERT INTO testBug1901 VALUES ('aaa')");
948
949
this.rs = this.stmt.executeQuery("SELECT field1 FROM testBug1901");
950
this.rs.next();
951
952
Clob valueAsClob = this.rs.getClob(1);
953
Blob valueAsBlob = this.rs.getBlob(1);
954
955
PreparedStatement pStmt = this.conn
956
.prepareStatement("INSERT INTO testBug1901 VALUES (?)");
957
pStmt.setObject(1, valueAsClob, java.sql.Types.CLOB, 0);
958
pStmt.executeUpdate();
959
pStmt.setObject(1, valueAsBlob, java.sql.Types.BLOB, 0);
960
pStmt.executeUpdate();
961
} finally {
962
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1901");
963
}
964
}
965
966
/**
967
* Test fix for BUG#1933 -- Driver property 'maxRows' has no effect.
968
*
969
* @throws Exception
970
* if the test fails.
971
*/
972
public void testBug1933() throws Exception {
973
if (versionMeetsMinimum(4, 0)) {
974
Connection maxRowsConn = null;
975
PreparedStatement maxRowsPrepStmt = null;
976
Statement maxRowsStmt = null;
977
978
try {
979
Properties props = new Properties();
980
981
props.setProperty("maxRows", "1");
982
983
maxRowsConn = getConnectionWithProps(props);
984
985
maxRowsStmt = maxRowsConn.createStatement();
986
987
assertTrue(maxRowsStmt.getMaxRows() == 1);
988
989
this.rs = maxRowsStmt.executeQuery("SELECT 1 UNION SELECT 2");
990
991
this.rs.next();
992
993
maxRowsPrepStmt = maxRowsConn
994
.prepareStatement("SELECT 1 UNION SELECT 2");
995
996
assertTrue(maxRowsPrepStmt.getMaxRows() == 1);
997
998
this.rs = maxRowsPrepStmt.executeQuery();
999
1000
this.rs.next();
1001
1002
assertTrue(!this.rs.next());
1003
1004
props.setProperty("useServerPrepStmts", "false");
1005
1006
maxRowsConn = getConnectionWithProps(props);
1007
1008
maxRowsPrepStmt = maxRowsConn
1009
.prepareStatement("SELECT 1 UNION SELECT 2");
1010
1011
assertTrue(maxRowsPrepStmt.getMaxRows() == 1);
1012
1013
this.rs = maxRowsPrepStmt.executeQuery();
1014
1015
this.rs.next();
1016
1017
assertTrue(!this.rs.next());
1018
} finally {
1019
maxRowsConn.close();
1020
}
1021
}
1022
}
1023
1024
/**
1025
* Tests the fix for BUG#1934 -- prepareStatement dies silently when
1026
* encountering Statement.RETURN_GENERATED_KEY
1027
*
1028
* @throws Exception
1029
* if the test fails
1030
*/
1031
public void testBug1934() throws Exception {
1032
if (isRunningOnJdk131()) {
1033
return; // test not valid on JDK-1.3.1
1034
}
1035
1036
try {
1037
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
1038
this.stmt.executeUpdate("CREATE TABLE testBug1934 (field1 INT)");
1039
1040
System.out.println("Before prepareStatement()");
1041
1042
this.pstmt = this.conn.prepareStatement(
1043
"INSERT INTO testBug1934 VALUES (?)",
1044
java.sql.Statement.RETURN_GENERATED_KEYS);
1045
1046
assertTrue(this.pstmt != null);
1047
1048
System.out.println("After prepareStatement() - " + this.pstmt);
1049
} finally {
1050
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1934");
1051
}
1052
}
1053
1054
/**
1055
* Tests fix for BUG#1958 - Improper bounds checking on
1056
* PreparedStatement.setFoo().
1057
*
1058
* @throws Exception
1059
* if the test fails.
1060
*/
1061
public void testBug1958() throws Exception {
1062
PreparedStatement pStmt = null;
1063
1064
try {
1065
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1958");
1066
this.stmt.executeUpdate("CREATE TABLE testBug1958 (field1 int)");
1067
1068
pStmt = this.conn
1069
.prepareStatement("SELECT * FROM testBug1958 WHERE field1 IN (?, ?, ?)");
1070
1071
try {
1072
pStmt.setInt(4, 1);
1073
} catch (SQLException sqlEx) {
1074
assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
1075
.getSQLState()));
1076
}
1077
} finally {
1078
if (pStmt != null) {
1079
pStmt.close();
1080
}
1081
1082
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1958");
1083
}
1084
}
1085
1086
/**
1087
* Tests the fix for BUG#2606, server-side prepared statements not returning
1088
* datatype YEAR correctly.
1089
*
1090
* @throws Exception
1091
* if the test fails.
1092
*/
1093
public void testBug2606() throws Exception {
1094
try {
1095
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2606");
1096
this.stmt
1097
.executeUpdate("CREATE TABLE testBug2606(year_field YEAR)");
1098
this.stmt.executeUpdate("INSERT INTO testBug2606 VALUES (2004)");
1099
1100
PreparedStatement yrPstmt = this.conn
1101
.prepareStatement("SELECT year_field FROM testBug2606");
1102
1103
this.rs = yrPstmt.executeQuery();
1104
1105
assertTrue(this.rs.next());
1106
1107
assertEquals(2004, this.rs.getInt(1));
1108
} finally {
1109
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2606");
1110
}
1111
}
1112
1113
/**
1114
* Tests the fix for BUG#2671, nulls encoded incorrectly in server-side
1115
* prepared statements.
1116
*
1117
* @throws Exception
1118
* if an error occurs.
1119
*/
1120
public void testBug2671() throws Exception {
1121
if (versionMeetsMinimum(4, 1)) {
1122
try {
1123
this.stmt.executeUpdate("DROP TABLE IF EXISTS test3");
1124
this.stmt
1125
.executeUpdate("CREATE TABLE test3 ("
1126
+ " `field1` int(8) NOT NULL auto_increment,"
1127
+ " `field2` int(8) unsigned zerofill default NULL,"
1128
+ " `field3` varchar(30) binary NOT NULL default '',"
1129
+ " `field4` varchar(100) default NULL,"
1130
+ " `field5` datetime NULL default '0000-00-00 00:00:00',"
1131
+ " PRIMARY KEY (`field1`),"
1132
+ " UNIQUE KEY `unq_id` (`field2`),"
1133
+ " UNIQUE KEY (`field3`),"
1134
+ " UNIQUE KEY (`field2`)"
1135
+ " ) TYPE=InnoDB CHARACTER SET utf8");
1136
1137
this.stmt
1138
.executeUpdate("insert into test3 (field1, field3, field4) values (1,'blewis','Bob Lewis')");
1139
1140
String query = " " + "UPDATE "
1141
+ " test3 "
1142
+ "SET "
1143
+ " field2=? " + " ,field3=? "
1144
+ " ,field4=? " + " ,field5=? "
1145
+ "WHERE "
1146
+ " field1 = ? ";
1147
1148
java.sql.Date mydate = null;
1149
1150
this.pstmt = this.conn.prepareStatement(query);
1151
1152
this.pstmt.setInt(1, 13);
1153
this.pstmt.setString(2, "abc");
1154
this.pstmt.setString(3, "def");
1155
this.pstmt.setDate(4, mydate);
1156
this.pstmt.setInt(5, 1);
1157
1158
int retval = this.pstmt.executeUpdate();
1159
assertTrue(retval == 1);
1160
} finally {
1161
this.stmt.executeUpdate("DROP TABLE IF EXISTS test3");
1162
}
1163
}
1164
}
1165
1166
/**
1167
* Tests fix for BUG#3103 -- java.util.Date not accepted as parameter to
1168
* PreparedStatement.setObject().
1169
*
1170
* @throws Exception
1171
* if the test fails
1172
*
1173
* @deprecated uses deprecated methods of Date class
1174
*/
1175
public void testBug3103() throws Exception {
1176
try {
1177
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3103");
1178
this.stmt
1179
.executeUpdate("CREATE TABLE testBug3103 (field1 DATETIME)");
1180
1181
PreparedStatement pStmt = this.conn
1182
.prepareStatement("INSERT INTO testBug3103 VALUES (?)");
1183
1184
java.util.Date utilDate = new java.util.Date();
1185
1186
pStmt.setObject(1, utilDate);
1187
pStmt.executeUpdate();
1188
1189
this.rs = this.stmt.executeQuery("SELECT field1 FROM testBug3103");
1190
this.rs.next();
1191
1192
java.util.Date retrUtilDate = new java.util.Date(this.rs
1193
.getTimestamp(1).getTime());
1194
1195
// We can only compare on the day/month/year hour/minute/second
1196
// interval, because the timestamp has added milliseconds to the
1197
// internal date...
1198
assertTrue("Dates not equal", (utilDate.getMonth() == retrUtilDate
1199
.getMonth())
1200
&& (utilDate.getDate() == retrUtilDate.getDate())
1201
&& (utilDate.getYear() == retrUtilDate.getYear())
1202
&& (utilDate.getHours() == retrUtilDate.getHours())
1203
&& (utilDate.getMinutes() == retrUtilDate.getMinutes())
1204
&& (utilDate.getSeconds() == retrUtilDate.getSeconds()));
1205
} finally {
1206
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3103");
1207
}
1208
}
1209
1210
/**
1211
* Tests fix for BUG#3520
1212
*
1213
* @throws Exception
1214
* ...
1215
*/
1216
public void testBug3520() throws Exception {
1217
try {
1218
this.stmt.executeUpdate("DROP TABLE IF EXISTS t");
1219
this.stmt.executeUpdate("CREATE TABLE t (s1 int,primary key (s1))");
1220
this.stmt.executeUpdate("INSERT INTO t VALUES (1)");
1221
this.stmt.executeUpdate("INSERT INTO t VALUES (1)");
1222
} catch (SQLException sqlEx) {
1223
System.out.println(sqlEx.getSQLState());
1224
} finally {
1225
this.stmt.executeUpdate("DROP TABLE IF EXISTS t");
1226
}
1227
}
1228
1229
/**
1230
* Test fix for BUG#3557 -- UpdatableResultSet not picking up default values
1231
*
1232
* @throws Exception
1233
* if test fails.
1234
*/
1235
public void testBug3557() throws Exception {
1236
boolean populateDefaults = ((com.mysql.jdbc.ConnectionProperties) this.conn)
1237
.getPopulateInsertRowWithDefaultValues();
1238
1239
try {
1240
((com.mysql.jdbc.ConnectionProperties) this.conn)
1241
.setPopulateInsertRowWithDefaultValues(true);
1242
1243
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3557");
1244
1245
this.stmt.executeUpdate("CREATE TABLE testBug3557 ( "
1246
+ "`a` varchar(255) NOT NULL default 'XYZ', "
1247
+ "`b` varchar(255) default '123', "
1248
+ "PRIMARY KEY (`a`))");
1249
1250
Statement updStmt = this.conn
1251
.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
1252
ResultSet.CONCUR_UPDATABLE);
1253
this.rs = updStmt.executeQuery("SELECT * FROM testBug3557");
1254
1255
assertTrue(this.rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE);
1256
1257
this.rs.moveToInsertRow();
1258
1259
assertEquals("XYZ", this.rs.getObject(1));
1260
assertEquals("123", this.rs.getObject(2));
1261
} finally {
1262
((com.mysql.jdbc.ConnectionProperties) this.conn)
1263
.setPopulateInsertRowWithDefaultValues(populateDefaults);
1264
1265
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3557");
1266
}
1267
}
1268
1269
/**
1270
* Tests fix for BUG#3620 -- Timezone not respected correctly.
1271
*
1272
* @throws SQLException
1273
* if the test fails.
1274
*/
1275
public void testBug3620() throws SQLException {
1276
if (isRunningOnJRockit()) {
1277
// bug with their timezones
1278
return;
1279
}
1280
1281
if (isRunningOnJdk131()) {
1282
// bug with timezones, no update
1283
// for new DST in USA
1284
return;
1285
}
1286
1287
long epsillon = 3000; // 3 seconds time difference
1288
1289
try {
1290
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3620");
1291
this.stmt
1292
.executeUpdate("CREATE TABLE testBug3620 (field1 TIMESTAMP)");
1293
1294
PreparedStatement tsPstmt = this.conn
1295
.prepareStatement("INSERT INTO testBug3620 VALUES (?)");
1296
1297
Calendar pointInTime = Calendar.getInstance();
1298
pointInTime.set(2004, 02, 29, 10, 0, 0);
1299
1300
long pointInTimeOffset = pointInTime.getTimeZone().getRawOffset();
1301
1302
java.sql.Timestamp ts = new java.sql.Timestamp(pointInTime
1303
.getTime().getTime());
1304
1305
tsPstmt.setTimestamp(1, ts);
1306
tsPstmt.executeUpdate();
1307
1308
String tsValueAsString = getSingleValue("testBug3620", "field1",
1309
null).toString();
1310
1311
System.out.println("Timestamp as string with no calendar: "
1312
+ tsValueAsString.toString());
1313
1314
Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
1315
1316
this.stmt.executeUpdate("DELETE FROM testBug3620");
1317
1318
Properties props = new Properties();
1319
props.put("useTimezone", "true");
1320
// props.put("serverTimezone", "UTC");
1321
1322
Connection tzConn = getConnectionWithProps(props);
1323
1324
Statement tsStmt = tzConn.createStatement();
1325
1326
tsPstmt = tzConn
1327
.prepareStatement("INSERT INTO testBug3620 VALUES (?)");
1328
1329
tsPstmt.setTimestamp(1, ts, cal);
1330
tsPstmt.executeUpdate();
1331
1332
tsValueAsString = getSingleValue("testBug3620", "field1", null)
1333
.toString();
1334
1335
Timestamp tsValueAsTimestamp = (Timestamp) getSingleValue(
1336
"testBug3620", "field1", null);
1337
1338
System.out.println("Timestamp as string with UTC calendar: "
1339
+ tsValueAsString.toString());
1340
System.out.println("Timestamp as Timestamp with UTC calendar: "
1341
+ tsValueAsTimestamp);
1342
1343
this.rs = tsStmt.executeQuery("SELECT field1 FROM testBug3620");
1344
this.rs.next();
1345
1346
Timestamp tsValueUTC = this.rs.getTimestamp(1, cal);
1347
1348
//
1349
// We use this testcase with other vendors, JDBC spec
1350
// requires result set fields can only be read once,
1351
// although MySQL doesn't require this ;)
1352
//
1353
this.rs = tsStmt.executeQuery("SELECT field1 FROM testBug3620");
1354
this.rs.next();
1355
1356
Timestamp tsValueStmtNoCal = this.rs.getTimestamp(1);
1357
1358
System.out
1359
.println("Timestamp specifying UTC calendar from normal statement: "
1360
+ tsValueUTC.toString());
1361
1362
PreparedStatement tsPstmtRetr = tzConn
1363
.prepareStatement("SELECT field1 FROM testBug3620");
1364
1365
this.rs = tsPstmtRetr.executeQuery();
1366
this.rs.next();
1367
1368
Timestamp tsValuePstmtUTC = this.rs.getTimestamp(1, cal);
1369
1370
System.out
1371
.println("Timestamp specifying UTC calendar from prepared statement: "
1372
+ tsValuePstmtUTC.toString());
1373
1374
//
1375
// We use this testcase with other vendors, JDBC spec
1376
// requires result set fields can only be read once,
1377
// although MySQL doesn't require this ;)
1378
//
1379
this.rs = tsPstmtRetr.executeQuery();
1380
this.rs.next();
1381
1382
Timestamp tsValuePstmtNoCal = this.rs.getTimestamp(1);
1383
1384
System.out
1385
.println("Timestamp specifying no calendar from prepared statement: "
1386
+ tsValuePstmtNoCal.toString());
1387
1388
long stmtDeltaTWithCal = (ts.getTime() - tsValueStmtNoCal.getTime());
1389
1390
long deltaOrig = Math.abs(stmtDeltaTWithCal - pointInTimeOffset);
1391
1392
assertTrue(
1393
"Difference between original timestamp and timestamp retrieved using java.sql.Statement "
1394
+ "set in database using UTC calendar is not ~= "
1395
+ epsillon + ", it is actually " + deltaOrig,
1396
(deltaOrig < epsillon));
1397
1398
long pStmtDeltaTWithCal = (ts.getTime() - tsValuePstmtNoCal
1399
.getTime());
1400
1401
System.out
1402
.println(Math.abs(pStmtDeltaTWithCal - pointInTimeOffset)
1403
+ " < "
1404
+ epsillon
1405
+ (Math.abs(pStmtDeltaTWithCal - pointInTimeOffset) < epsillon));
1406
assertTrue(
1407
"Difference between original timestamp and timestamp retrieved using java.sql.PreparedStatement "
1408
+ "set in database using UTC calendar is not ~= "
1409
+ epsillon
1410
+ ", it is actually "
1411
+ pStmtDeltaTWithCal, (Math.abs(pStmtDeltaTWithCal
1412
- pointInTimeOffset) < epsillon));
1413
1414
System.out
1415
.println("Difference between original ts and ts with no calendar: "
1416
+ (ts.getTime() - tsValuePstmtNoCal.getTime())
1417
+ ", offset should be " + pointInTimeOffset);
1418
} finally {
1419
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3620");
1420
}
1421
}
1422
1423
/**
1424
* Tests that DataTruncation is thrown when data is truncated.
1425
*
1426
* @throws Exception
1427
* if the test fails.
1428
*/
1429
public void testBug3697() throws Exception {
1430
try {
1431
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3697");
1432
this.stmt
1433
.executeUpdate("CREATE TABLE testBug3697 (field1 VARCHAR(255))");
1434
1435
StringBuffer updateBuf = new StringBuffer(
1436
"INSERT INTO testBug3697 VALUES ('");
1437
1438
for (int i = 0; i < 512; i++) {
1439
updateBuf.append("A");
1440
}
1441
1442
updateBuf.append("')");
1443
1444
try {
1445
this.stmt.executeUpdate(updateBuf.toString());
1446
} catch (DataTruncation dtEx) {
1447
// This is an expected exception....
1448
}
1449
1450
SQLWarning warningChain = this.stmt.getWarnings();
1451
1452
System.out.println(warningChain);
1453
} finally {
1454
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3697");
1455
}
1456
}
1457
1458
/**
1459
* Tests fix for BUG#3804, data truncation on server should throw
1460
* DataTruncation exception.
1461
*
1462
* @throws Exception
1463
* if the test fails
1464
*/
1465
public void testBug3804() throws Exception {
1466
if (versionMeetsMinimum(4, 1)) {
1467
try {
1468
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3804");
1469
this.stmt
1470
.executeUpdate("CREATE TABLE testBug3804 (field1 VARCHAR(5))");
1471
1472
boolean caughtTruncation = false;
1473
1474
try {
1475
this.stmt
1476
.executeUpdate("INSERT INTO testBug3804 VALUES ('1234567')");
1477
} catch (DataTruncation truncationEx) {
1478
caughtTruncation = true;
1479
System.out.println(truncationEx);
1480
}
1481
1482
assertTrue("Data truncation exception should've been thrown",
1483
caughtTruncation);
1484
} finally {
1485
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3804");
1486
}
1487
}
1488
}
1489
1490
/**
1491
* Tests BUG#3873 - PreparedStatement.executeBatch() not returning all
1492
* generated keys (even though that's not JDBC compliant).
1493
*
1494
* @throws Exception
1495
* if the test fails
1496
*/
1497
public void testBug3873() throws Exception {
1498
if (isRunningOnJdk131()) {
1499
return; // test not valid on JDK-1.3.1
1500
}
1501
1502
PreparedStatement batchStmt = null;
1503
1504
try {
1505
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3873");
1506
this.stmt
1507
.executeUpdate("CREATE TABLE testBug3873 (keyField INT NOT NULL PRIMARY KEY AUTO_INCREMENT, dataField VARCHAR(32))");
1508
batchStmt = this.conn.prepareStatement(
1509
"INSERT INTO testBug3873 (dataField) VALUES (?)",
1510
Statement.RETURN_GENERATED_KEYS);
1511
batchStmt.setString(1, "abc");
1512
batchStmt.addBatch();
1513
batchStmt.setString(1, "def");
1514
batchStmt.addBatch();
1515
batchStmt.setString(1, "ghi");
1516
batchStmt.addBatch();
1517
1518
int[] updateCounts = batchStmt.executeBatch();
1519
1520
this.rs = batchStmt.getGeneratedKeys();
1521
1522
while (this.rs.next()) {
1523
System.out.println(this.rs.getInt(1));
1524
}
1525
1526
this.rs = batchStmt.getGeneratedKeys();
1527
assertTrue(this.rs.next());
1528
assertTrue(1 == this.rs.getInt(1));
1529
assertTrue(this.rs.next());
1530
assertTrue(2 == this.rs.getInt(1));
1531
assertTrue(this.rs.next());
1532
assertTrue(3 == this.rs.getInt(1));
1533
assertTrue(!this.rs.next());
1534
} finally {
1535
if (batchStmt != null) {
1536
batchStmt.close();
1537
}
1538
1539
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3873");
1540
}
1541
}
1542
1543
/**
1544
* Tests fix for BUG#4119 -- misbehavior in a managed environment from
1545
* MVCSoft JDO
1546
*
1547
* @throws Exception
1548
* if the test fails.
1549
*/
1550
public void testBug4119() throws Exception {
1551
try {
1552
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4119");
1553
this.stmt.executeUpdate("CREATE TABLE `testBug4119` ("
1554
+ "`field1` varchar(255) NOT NULL default '',"
1555
+ "`field2` bigint(20) default NULL,"
1556
+ "`field3` int(11) default NULL,"
1557
+ "`field4` datetime default NULL,"
1558
+ "`field5` varchar(75) default NULL,"
1559
+ "`field6` varchar(75) default NULL,"
1560
+ "`field7` varchar(75) default NULL,"
1561
+ "`field8` datetime default NULL,"
1562
+ " PRIMARY KEY (`field1`)" + ")");
1563
1564
PreparedStatement pStmt = this.conn
1565
.prepareStatement("insert into testBug4119 (field2, field3,"
1566
+ "field4, field5, field6, field7, field8, field1) values (?, ?,"
1567
+ "?, ?, ?, ?, ?, ?)");
1568
1569
pStmt.setString(1, "0");
1570
pStmt.setString(2, "0");
1571
pStmt.setTimestamp(3, new java.sql.Timestamp(System
1572
.currentTimeMillis()));
1573
pStmt.setString(4, "ABC");
1574
pStmt.setString(5, "DEF");
1575
pStmt.setString(6, "AA");
1576
pStmt.setTimestamp(7, new java.sql.Timestamp(System
1577
.currentTimeMillis()));
1578
pStmt.setString(8, "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
1579
pStmt.executeUpdate();
1580
} finally {
1581
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4119");
1582
}
1583
}
1584
1585
/**
1586
* Tests fix for BUG#4311 - Error in JDBC retrieval of mediumint column when
1587
* using prepared statements and binary result sets.
1588
*
1589
* @throws Exception
1590
* if the test fails.
1591
*/
1592
public void testBug4311() throws Exception {
1593
try {
1594
int lowValue = -8388608;
1595
int highValue = 8388607;
1596
1597
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4311");
1598
this.stmt
1599
.executeUpdate("CREATE TABLE testBug4311 (low MEDIUMINT, high MEDIUMINT)");
1600
this.stmt.executeUpdate("INSERT INTO testBug4311 VALUES ("
1601
+ lowValue + ", " + highValue + ")");
1602
1603
PreparedStatement pStmt = this.conn
1604
.prepareStatement("SELECT low, high FROM testBug4311");
1605
this.rs = pStmt.executeQuery();
1606
assertTrue(this.rs.next());
1607
assertTrue(this.rs.getInt(1) == lowValue);
1608
assertTrue(this.rs.getInt(2) == highValue);
1609
} finally {
1610
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4311");
1611
}
1612
}
1613
1614
/**
1615
* Tests fix for BUG#4510 -- Statement.getGeneratedKeys() fails when key >
1616
* 32767
1617
*
1618
* @throws Exception
1619
* if the test fails
1620
*/
1621
public void testBug4510() throws Exception {
1622
if (isRunningOnJdk131()) {
1623
return; // test not valid on JDK-1.3.1
1624
}
1625
1626
try {
1627
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4510");
1628
this.stmt.executeUpdate("CREATE TABLE testBug4510 ("
1629
+ "field1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,"
1630
+ "field2 VARCHAR(100))");
1631
this.stmt
1632
.executeUpdate("INSERT INTO testBug4510 (field1, field2) VALUES (32767, 'bar')");
1633
1634
PreparedStatement p = this.conn.prepareStatement(
1635
"insert into testBug4510 (field2) values (?)",
1636
Statement.RETURN_GENERATED_KEYS);
1637
1638
p.setString(1, "blah");
1639
1640
p.executeUpdate();
1641
1642
ResultSet rs = p.getGeneratedKeys();
1643
rs.next();
1644
System.out.println("Id: " + rs.getInt(1));
1645
rs.close();
1646
} finally {
1647
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4510");
1648
}
1649
}
1650
1651
/**
1652
* Server doesn't accept everything as a server-side prepared statement, so
1653
* by default we scan for stuff it can't handle.
1654
*
1655
* @throws SQLException
1656
*/
1657
public void testBug4718() throws SQLException {
1658
if (versionMeetsMinimum(4, 1, 0)
1659
&& ((com.mysql.jdbc.Connection) this.conn)
1660
.getUseServerPreparedStmts()) {
1661
this.pstmt = this.conn.prepareStatement("SELECT 1 LIMIT ?");
1662
assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);
1663
1664
this.pstmt = this.conn.prepareStatement("SELECT 1 LIMIT 1");
1665
assertTrue(this.pstmt instanceof com.mysql.jdbc.ServerPreparedStatement);
1666
1667
this.pstmt = this.conn.prepareStatement("SELECT 1 LIMIT 1, ?");
1668
assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);
1669
1670
try {
1671
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4718");
1672
this.stmt
1673
.executeUpdate("CREATE TABLE testBug4718 (field1 char(32))");
1674
1675
this.pstmt = this.conn
1676
.prepareStatement("ALTER TABLE testBug4718 ADD INDEX (field1)");
1677
assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);
1678
1679
this.pstmt = this.conn.prepareStatement("SELECT 1");
1680
assertTrue(this.pstmt instanceof ServerPreparedStatement);
1681
1682
this.pstmt = this.conn
1683
.prepareStatement("UPDATE testBug4718 SET field1=1");
1684
assertTrue(this.pstmt instanceof ServerPreparedStatement);
1685
1686
this.pstmt = this.conn
1687
.prepareStatement("UPDATE testBug4718 SET field1=1 LIMIT 1");
1688
assertTrue(this.pstmt instanceof ServerPreparedStatement);
1689
1690
this.pstmt = this.conn
1691
.prepareStatement("UPDATE testBug4718 SET field1=1 LIMIT ?");
1692
assertTrue(this.pstmt instanceof com.mysql.jdbc.PreparedStatement);
1693
1694
this.pstmt = this.conn
1695
.prepareStatement("UPDATE testBug4718 SET field1='Will we ignore LIMIT ?,?'");
1696
assertTrue(this.pstmt instanceof ServerPreparedStatement);
1697
1698
} finally {
1699
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4718");
1700
}
1701
}
1702
}
1703
1704
/**
1705
* Tests fix for BUG#5012 -- ServerPreparedStatements dealing with return of
1706
* DECIMAL type don't work.
1707
*
1708
* @throws Exception
1709
* if the test fails.
1710
*/
1711
public void testBug5012() throws Exception {
1712
PreparedStatement pStmt = null;
1713
String valueAsString = "12345.12";
1714
1715
try {
1716
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5012");
1717
this.stmt
1718
.executeUpdate("CREATE TABLE testBug5012(field1 DECIMAL(10,2))");
1719
this.stmt.executeUpdate("INSERT INTO testBug5012 VALUES ("
1720
+ valueAsString + ")");
1721
1722
pStmt = this.conn
1723
.prepareStatement("SELECT field1 FROM testBug5012");
1724
this.rs = pStmt.executeQuery();
1725
assertTrue(this.rs.next());
1726
assertEquals(new BigDecimal(valueAsString), this.rs
1727
.getBigDecimal(1));
1728
} finally {
1729
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5012");
1730
1731
if (pStmt != null) {
1732
pStmt.close();
1733
}
1734
}
1735
}
1736
1737
/**
1738
* Tests fix for BUG#5133 -- PreparedStatement.toString() doesn't return
1739
* correct value if no parameters are present in statement.
1740
*
1741
* @throws Exception
1742
*/
1743
public void testBug5133() throws Exception {
1744
String query = "SELECT 1";
1745
String output = this.conn.prepareStatement(query).toString();
1746
System.out.println(output);
1747
1748
assertTrue(output.indexOf(query) != -1);
1749
}
1750
1751
/**
1752
* Tests for BUG#5191 -- PreparedStatement.executeQuery() gives
1753
* OutOfMemoryError
1754
*
1755
* @throws Exception
1756
* if the test fails.
1757
*/
1758
public void testBug5191() throws Exception {
1759
PreparedStatement pStmt = null;
1760
1761
try {
1762
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5191Q");
1763
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5191C");
1764
1765
this.stmt.executeUpdate("CREATE TABLE testBug5191Q"
1766
+ "(QuestionId int NOT NULL AUTO_INCREMENT, "
1767
+ "Text VARCHAR(200), " + "PRIMARY KEY(QuestionId))");
1768
1769
this.stmt.executeUpdate("CREATE TABLE testBug5191C"
1770
+ "(CategoryId int, " + "QuestionId int)");
1771
1772
String[] questions = new String[] { "What is your name?",
1773
"What is your quest?",
1774
"What is the airspeed velocity of an unladen swollow?",
1775
"How many roads must a man walk?", "Where's the tea?", };
1776
1777
for (int i = 0; i < questions.length; i++) {
1778
this.stmt.executeUpdate("INSERT INTO testBug5191Q(Text)"
1779
+ " VALUES (\"" + questions[i] + "\")");
1780
int catagory = (i < 3) ? 0 : i;
1781
1782
this.stmt.executeUpdate("INSERT INTO testBug5191C"
1783
+ "(CategoryId, QuestionId) VALUES (" + catagory + ", "
1784
+ i + ")");
1785
/*
1786
* this.stmt.executeUpdate("INSERT INTO testBug5191C" +
1787
* "(CategoryId, QuestionId) VALUES (" + catagory + ", (SELECT
1788
* testBug5191Q.QuestionId" + " FROM testBug5191Q " + "WHERE
1789
* testBug5191Q.Text LIKE '" + questions[i] + "'))");
1790
*/
1791
}
1792
1793
pStmt = this.conn.prepareStatement("SELECT qc.QuestionId, q.Text "
1794
+ "FROM testBug5191Q q, testBug5191C qc "
1795
+ "WHERE qc.CategoryId = ? "
1796
+ " AND q.QuestionId = qc.QuestionId");
1797
1798
int catId = 0;
1799
for (int i = 0; i < 100; i++) {
1800
execQueryBug5191(pStmt, catId);
1801
}
1802
1803
} finally {
1804
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5191Q");
1805
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5191C");
1806
1807
if (pStmt != null) {
1808
pStmt.close();
1809
}
1810
}
1811
}
1812
1813
public void testBug5235() throws Exception {
1814
Properties props = new Properties();
1815
props.setProperty("zeroDateTimeBehavior", "convertToNull");
1816
1817
Connection convertToNullConn = getConnectionWithProps(props);
1818
Statement convertToNullStmt = convertToNullConn.createStatement();
1819
try {
1820
convertToNullStmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
1821
convertToNullStmt
1822
.executeUpdate("CREATE TABLE testBug5235(field1 DATE)");
1823
convertToNullStmt
1824
.executeUpdate("INSERT INTO testBug5235 (field1) VALUES ('0000-00-00')");
1825
1826
PreparedStatement ps = convertToNullConn
1827
.prepareStatement("SELECT field1 FROM testBug5235");
1828
this.rs = ps.executeQuery();
1829
1830
if (this.rs.next()) {
1831
Date d = (Date) this.rs.getObject("field1");
1832
System.out.println("date: " + d);
1833
}
1834
} finally {
1835
convertToNullStmt.executeUpdate("DROP TABLE IF EXISTS testBug5235");
1836
}
1837
}
1838
1839
public void testBug5450() throws Exception {
1840
if (versionMeetsMinimum(4, 1)) {
1841
String table = "testBug5450";
1842
String column = "policyname";
1843
1844
try {
1845
Properties props = new Properties();
1846
props.setProperty("characterEncoding", "utf-8");
1847
1848
Connection utf8Conn = getConnectionWithProps(props);
1849
Statement utfStmt = utf8Conn.createStatement();
1850
1851
this.stmt.executeUpdate("DROP TABLE IF EXISTS " + table);
1852
1853
this.stmt.executeUpdate("CREATE TABLE " + table
1854
+ "(policyid int NOT NULL AUTO_INCREMENT, " + column
1855
+ " VARCHAR(200), "
1856
+ "PRIMARY KEY(policyid)) DEFAULT CHARACTER SET utf8");
1857
1858
String pname0 = "inserted \uac00 - foo - \u4e00";
1859
1860
utfStmt.executeUpdate("INSERT INTO " + table + "(" + column
1861
+ ")" + " VALUES (\"" + pname0 + "\")");
1862
1863
this.rs = utfStmt.executeQuery("SELECT " + column + " FROM "
1864
+ table);
1865
1866
this.rs.first();
1867
String pname1 = this.rs.getString(column);
1868
1869
assertEquals(pname0, pname1);
1870
byte[] bytes = this.rs.getBytes(column);
1871
1872
String pname2 = new String(bytes, "utf-8");
1873
assertEquals(pname1, pname2);
1874
1875
utfStmt.executeUpdate("delete from " + table + " where "
1876
+ column + " like 'insert%'");
1877
1878
PreparedStatement s1 = utf8Conn.prepareStatement("insert into "
1879
+ table + "(" + column + ") values (?)");
1880
1881
s1.setString(1, pname0);
1882
s1.executeUpdate();
1883
1884
String byteesque = "byte " + pname0;
1885
byte[] newbytes = byteesque.getBytes("utf-8");
1886
1887
s1.setBytes(1, newbytes);
1888
s1.executeUpdate();
1889
1890
this.rs = utfStmt.executeQuery("select " + column + " from "
1891
+ table + " where " + column + " like 'insert%'");
1892
this.rs.first();
1893
String pname3 = this.rs.getString(column);
1894
assertEquals(pname0, pname3);
1895
1896
this.rs = utfStmt.executeQuery("select " + column + " from "
1897
+ table + " where " + column + " like 'byte insert%'");
1898
this.rs.first();
1899
1900
String pname4 = this.rs.getString(column);
1901
assertEquals(byteesque, pname4);
1902
1903
} finally {
1904
this.stmt.executeUpdate("DROP TABLE IF EXISTS " + table);
1905
}
1906
}
1907
}
1908
1909
public void testBug5510() throws Exception {
1910
// This is a server bug that should be fixed by 4.1.6
1911
if (versionMeetsMinimum(4, 1, 6)) {
1912
try {
1913
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5510");
1914
1915
this.stmt
1916
.executeUpdate("CREATE TABLE `testBug5510` ("
1917
+ "`a` bigint(20) NOT NULL auto_increment,"
1918
+ "`b` varchar(64) default NULL,"
1919
+ "`c` varchar(64) default NULL,"
1920
+ "`d` varchar(255) default NULL,"
1921
+ "`e` int(11) default NULL,"
1922
+ "`f` varchar(32) default NULL,"
1923
+ "`g` varchar(32) default NULL,"
1924
+ "`h` varchar(80) default NULL,"
1925
+ "`i` varchar(255) default NULL,"
1926
+ "`j` varchar(255) default NULL,"
1927
+ "`k` varchar(255) default NULL,"
1928
+ "`l` varchar(32) default NULL,"
1929
+ "`m` varchar(32) default NULL,"
1930
+ "`n` timestamp NOT NULL default CURRENT_TIMESTAMP on update"
1931
+ " CURRENT_TIMESTAMP,"
1932
+ "`o` int(11) default NULL,"
1933
+ "`p` int(11) default NULL,"
1934
+ "PRIMARY KEY (`a`)"
1935
+ ") ENGINE=InnoDB DEFAULT CHARSET=latin1");
1936
PreparedStatement pStmt = this.conn
1937
.prepareStatement("INSERT INTO testBug5510 (a) VALUES (?)");
1938
pStmt.setNull(1, 0);
1939
pStmt.executeUpdate();
1940
1941
} finally {
1942
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug5510");
1943
}
1944
}
1945
}
1946
1947
/**
1948
* Tests fix for BUG#5874, timezone correction goes in wrong 'direction'
1949
* when useTimezone=true and server timezone differs from client timezone.
1950
*
1951
* @throws Exception
1952
* if the test fails.
1953
*/
1954
public void testBug5874() throws Exception {
1955
/*
1956
try {
1957
String clientTimezoneName = "America/Los_Angeles";
1958
String serverTimezoneName = "America/Chicago";
1959
1960
TimeZone.setDefault(TimeZone.getTimeZone(clientTimezoneName));
1961
1962
long epsillon = 3000; // 3 seconds difference
1963
1964
long clientTimezoneOffsetMillis = TimeZone.getDefault()
1965
.getRawOffset();
1966
long serverTimezoneOffsetMillis = TimeZone.getTimeZone(
1967
serverTimezoneName).getRawOffset();
1968
1969
long offsetDifference = clientTimezoneOffsetMillis
1970
- serverTimezoneOffsetMillis;
1971
1972
Properties props = new Properties();
1973
props.put("useTimezone", "true");
1974
props.put("serverTimezone", serverTimezoneName);
1975
1976
Connection tzConn = getConnectionWithProps(props);
1977
Statement tzStmt = tzConn.createStatement();
1978
tzStmt.executeUpdate("DROP TABLE IF EXISTS timeTest");
1979
tzStmt
1980
.executeUpdate("CREATE TABLE timeTest (tstamp DATETIME, t TIME)");
1981
1982
PreparedStatement pstmt = tzConn
1983
.prepareStatement("INSERT INTO timeTest VALUES (?, ?)");
1984
1985
long now = System.currentTimeMillis(); // Time in milliseconds
1986
// since 1/1/1970 GMT
1987
1988
Timestamp nowTstamp = new Timestamp(now);
1989
Time nowTime = new Time(now);
1990
1991
pstmt.setTimestamp(1, nowTstamp);
1992
pstmt.setTime(2, nowTime);
1993
pstmt.executeUpdate();
1994
1995
this.rs = tzStmt.executeQuery("SELECT * from timeTest");
1996
1997
// Timestamps look like this: 2004-11-29 13:43:21
1998
SimpleDateFormat timestampFormat = new SimpleDateFormat(
1999
"yyyy-MM-dd HH:mm:ss");
2000
SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");
2001
2002
while (this.rs.next()) {
2003
// Driver now converts/checks DATE/TIME/TIMESTAMP/DATETIME types
2004
// when calling getString()...
2005
String retrTimestampString = new String(this.rs.getBytes(1));
2006
Timestamp retrTimestamp = this.rs.getTimestamp(1);
2007
2008
java.util.Date timestampOnServer = timestampFormat
2009
.parse(retrTimestampString);
2010
2011
long retrievedOffsetForTimestamp = retrTimestamp.getTime()
2012
- timestampOnServer.getTime();
2013
2014
assertTrue(
2015
"Difference between original timestamp and timestamp retrieved using client timezone is not "
2016
+ offsetDifference, (Math
2017
.abs(retrievedOffsetForTimestamp
2018
- offsetDifference) < epsillon));
2019
2020
String retrTimeString = new String(this.rs.getBytes(2));
2021
Time retrTime = this.rs.getTime(2);
2022
2023
java.util.Date timeOnServerAsDate = timeFormat
2024
.parse(retrTimeString);
2025
Time timeOnServer = new Time(timeOnServerAsDate.getTime());
2026
2027
long retrievedOffsetForTime = retrTime.getTime()
2028
- timeOnServer.getTime();
2029
2030
assertTrue(
2031
"Difference between original times and time retrieved using client timezone is not "
2032
+ offsetDifference,
2033
(Math.abs(retrievedOffsetForTime - offsetDifference) < epsillon));
2034
}
2035
} finally {
2036
this.stmt.executeUpdate("DROP TABLE IF EXISTS timeTest");
2037
} */
2038
}
2039
2040
public void testBug6823() throws SQLException {
2041
innerBug6823(true);
2042
innerBug6823(false);
2043
}
2044
2045
public void testBug7461() throws Exception {
2046
String tableName = "testBug7461";
2047
2048
try {
2049
createTable(tableName, "(field1 varchar(4))");
2050
File tempFile = File.createTempFile("mysql-test", ".txt");
2051
tempFile.deleteOnExit();
2052
2053
FileOutputStream fOut = new FileOutputStream(tempFile);
2054
fOut.write("abcdefghijklmnop".getBytes());
2055
fOut.close();
2056
2057
try {
2058
this.stmt.executeQuery("LOAD DATA LOCAL INFILE '"
2059
+ tempFile.toString() + "' INTO TABLE " + tableName);
2060
} catch (SQLException sqlEx) {
2061
this.stmt.getWarnings();
2062
}
2063
2064
} finally {
2065
dropTable(tableName);
2066
}
2067
2068
}
2069
2070
public void testBug8181() throws Exception {
2071
2072
try {
2073
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug8181");
2074
this.stmt
2075
.executeUpdate("CREATE TABLE testBug8181(col1 VARCHAR(20),col2 INT)");
2076
2077
this.pstmt = this.conn
2078
.prepareStatement("INSERT INTO testBug8181(col1,col2) VALUES(?,?)");
2079
2080
for (int i = 0; i < 20; i++) {
2081
this.pstmt.setString(1, "Test " + i);
2082
this.pstmt.setInt(2, i);
2083
this.pstmt.addBatch();
2084
}
2085
2086
this.pstmt.executeBatch();
2087
2088
} finally {
2089
this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug8181");
2090
2091
if (this.pstmt != null) {
2092
this.pstmt.close();
2093
}
2094
}
2095
}
2096
2097
/**
2098
* Tests fix for BUG#8487 - PreparedStatements not creating streaming result
2099
* sets.
2100
*
2101
* @throws Exception
2102
* if the test fails.
2103
*/
2104
public void testBug8487() throws Exception {
2105
try {
2106
this.pstmt = this.conn.prepareStatement("SELECT 1",
2107
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
2108
2109
this.pstmt.setFetchSize(Integer.MIN_VALUE);
2110
this.rs = this.pstmt.executeQuery();
2111
try {
2112
this.conn.createStatement().executeQuery("SELECT 2");
2113
fail("Should have caught a streaming exception here");
2114
} catch (SQLException sqlEx) {
2115
assertTrue(sqlEx.getMessage() != null
2116
&& sqlEx.getMessage().indexOf("Streaming") != -1);
2117
}
2118
2119
} finally {
2120
if (this.rs != null) {
2121
while (this.rs.next())
2122
;
2123
2124
this.rs.close();
2125
}
2126
2127
if (this.pstmt != null) {
2128
this.pstmt.close();
2129
}
2130
}
2131
}
2132
2133
/**
2134
* Tests multiple statement support with fix for BUG#9704.
2135
*
2136
* @throws Exception
2137
* DOCUMENT ME!
2138
*/
2139
public void testBug9704() throws Exception {
2140
if (versionMeetsMinimum(4, 1)) {
2141
Connection multiStmtConn = null;
2142
Statement multiStmt = null;
2143
2144
try {
2145
Properties props = new Properties();
2146
props.setProperty("allowMultiQueries", "true");
2147
2148
multiStmtConn = getConnectionWithProps(props);
2149
2150
multiStmt = multiStmtConn.createStatement();
2151
2152
multiStmt
2153
.executeUpdate("DROP TABLE IF EXISTS testMultiStatements");
2154
multiStmt
2155
.executeUpdate("CREATE TABLE testMultiStatements (field1 VARCHAR(255), field2 INT, field3 DOUBLE)");
2156
multiStmt
2157
.executeUpdate("INSERT INTO testMultiStatements VALUES ('abcd', 1, 2)");
2158
2159
multiStmt
2160
.execute("SELECT field1 FROM testMultiStatements WHERE field1='abcd';"
2161
+ "UPDATE testMultiStatements SET field3=3;"
2162
+ "SELECT field3 FROM testMultiStatements WHERE field3=3");
2163
2164
this.rs = multiStmt.getResultSet();
2165
2166
assertTrue(this.rs.next());
2167
2168
assertTrue("abcd".equals(this.rs.getString(1)));
2169
this.rs.close();
2170
2171
// Next should be an update count...
2172
assertTrue(!multiStmt.getMoreResults());
2173
2174
assertTrue("Update count was " + multiStmt.getUpdateCount()
2175
+ ", expected 1", multiStmt.getUpdateCount() == 1);
2176
2177
assertTrue(multiStmt.getMoreResults());
2178
2179
this.rs = multiStmt.getResultSet();
2180
2181
assertTrue(this.rs.next());
2182
2183
assertTrue(this.rs.getDouble(1) == 3);
2184
2185
// End of multi results
2186
assertTrue(!multiStmt.getMoreResults());
2187
assertTrue(multiStmt.getUpdateCount() == -1);
2188
} finally {
2189
if (multiStmt != null) {
2190
multiStmt
2191
.executeUpdate("DROP TABLE IF EXISTS testMultiStatements");
2192
2193
multiStmt.close();
2194
}
2195
2196
if (multiStmtConn != null) {
2197
multiStmtConn.close();
2198
}
2199
}
2200
}
2201
}
2202
2203
/**
2204
* Tests that you can close a statement twice without an NPE.
2205
*
2206
* @throws Exception
2207
* if an error occurs.
2208
*/
2209
public void testCloseTwice() throws Exception {
2210
Statement closeMe = this.conn.createStatement();
2211
closeMe.close();
2212
closeMe.close();
2213
}
2214
2215
public void testCsc4194() throws Exception {
2216
if (isRunningOnJdk131()) {
2217
return; // test not valid on JDK-1.3.1
2218
}
2219
2220
Connection sjisConn = null;
2221
Connection windows31JConn = null;
2222
2223
try {
2224
String tableNameText = "testCsc4194Text";
2225
String tableNameBlob = "testCsc4194Blob";
2226
2227
createTable(tableNameBlob, "(field1 BLOB)");
2228
String charset = "";
2229
2230
if (versionMeetsMinimum(5, 0, 3) || versionMeetsMinimum(4, 1, 12)) {
2231
charset = " CHARACTER SET cp932";
2232
} else if (versionMeetsMinimum(4, 1, 0)) {
2233
charset = " CHARACTER SET sjis";
2234
}
2235
2236
createTable(tableNameText, "(field1 TEXT)" + charset);
2237
2238
Properties windows31JProps = new Properties();
2239
windows31JProps.setProperty("useUnicode", "true");
2240
windows31JProps.setProperty("characterEncoding", "Windows-31J");
2241
2242
windows31JConn = getConnectionWithProps(windows31JProps);
2243
testCsc4194InsertCheckBlob(windows31JConn, tableNameBlob);
2244
2245
if (versionMeetsMinimum(4, 1, 0)) {
2246
testCsc4194InsertCheckText(windows31JConn, tableNameText,
2247
"Windows-31J");
2248
}
2249
2250
Properties sjisProps = new Properties();
2251
sjisProps.setProperty("useUnicode", "true");
2252
sjisProps.setProperty("characterEncoding", "sjis");
2253
2254
sjisConn = getConnectionWithProps(sjisProps);
2255
testCsc4194InsertCheckBlob(sjisConn, tableNameBlob);
2256
2257
if (versionMeetsMinimum(5, 0, 3)) {
2258
testCsc4194InsertCheckText(sjisConn, tableNameText,
2259
"Windows-31J");
2260
}
2261
2262
} finally {
2263
2264
if (windows31JConn != null) {
2265
windows31JConn.close();
2266
}
2267
2268
if (sjisConn != null) {
2269
sjisConn.close();
2270
}
2271
}
2272
}
2273
2274
private void testCsc4194InsertCheckBlob(Connection c, String tableName)
2275
throws Exception {
2276
byte[] bArray = new byte[] { (byte) 0xac, (byte) 0xed, (byte) 0x00,
2277
(byte) 0x05 };
2278
2279
PreparedStatement testStmt = c.prepareStatement("INSERT INTO "
2280
+ tableName + " VALUES (?)");
2281
testStmt.setBytes(1, bArray);
2282
testStmt.executeUpdate();
2283
2284
this.rs = c.createStatement().executeQuery(
2285
"SELECT field1 FROM " + tableName);
2286
assertTrue(this.rs.next());
2287
assertEquals(getByteArrayString(bArray), getByteArrayString(this.rs
2288
.getBytes(1)));
2289
this.rs.close();
2290
}
2291
2292
private void testCsc4194InsertCheckText(Connection c, String tableName,
2293
String encoding) throws Exception {
2294
byte[] kabuInShiftJIS = { (byte) 0x87, // a double-byte
2295
// charater("kabu") in Shift JIS
2296
(byte) 0x8a, };
2297
2298
String expected = new String(kabuInShiftJIS, encoding);
2299
PreparedStatement testStmt = c.prepareStatement("INSERT INTO "
2300
+ tableName + " VALUES (?)");
2301
testStmt.setString(1, expected);
2302
testStmt.executeUpdate();
2303
2304
this.rs = c.createStatement().executeQuery(
2305
"SELECT field1 FROM " + tableName);
2306
assertTrue(this.rs.next());
2307
assertEquals(expected, this.rs.getString(1));
2308
this.rs.close();
2309
}
2310
2311
/**
2312
* Tests all forms of statements influencing getGeneratedKeys().
2313
*
2314
* @throws Exception
2315
* if the test fails.
2316
*/
2317
public void testGetGeneratedKeysAllCases() throws Exception {
2318
if (isRunningOnJdk131()) {
2319
return; // test not valid on JDK-1.3.1
2320
}
2321
2322
System.out.println("Using Statement.executeUpdate()\n");
2323
2324
try {
2325
createGGKTables();
2326
2327
// Do the tests
2328
for (int i = 0; i < tests.length; i++) {
2329
doGGKTestStatement(tests[i], true);
2330
}
2331
} finally {
2332
dropGGKTables();
2333
}
2334
2335
nextID = 1;
2336
count = 0;
2337
2338
System.out.println("Using Statement.execute()\n");
2339
2340
try {
2341
createGGKTables();
2342
2343
// Do the tests
2344
for (int i = 0; i < tests.length; i++) {
2345
doGGKTestStatement(tests[i], false);
2346
}
2347
} finally {
2348
dropGGKTables();
2349
}
2350
2351
nextID = 1;
2352
count = 0;
2353
2354
System.out.println("Using PreparedStatement.executeUpdate()\n");
2355
2356
try {
2357
createGGKTables();
2358
2359
// Do the tests
2360
for (int i = 0; i < tests.length; i++) {
2361
doGGKTestPreparedStatement(tests[i], true);
2362
}
2363
} finally {
2364
dropGGKTables();
2365
}
2366
2367
nextID = 1;
2368
count = 0;
2369
2370
System.out.println("Using PreparedStatement.execute()\n");
2371
2372
try {
2373
createGGKTables();
2374
2375
// Do the tests
2376
for (int i = 0; i < tests.length; i++) {
2377
doGGKTestPreparedStatement(tests[i], false);
2378
}
2379
} finally {
2380
dropGGKTables();
2381
}
2382
}
2383
2384
/**
2385
* Tests that max_rows and 'limit' don't cause exceptions to be thrown.
2386
*
2387
* @throws Exception
2388
* if the test fails.
2389
*/
2390
public void testLimitAndMaxRows() throws Exception {
2391
try {
2392
this.stmt.executeUpdate("DROP TABLE IF EXISTS testMaxRowsAndLimit");
2393
this.stmt
2394
.executeUpdate("CREATE TABLE testMaxRowsAndLimit(limitField INT)");
2395
2396
for (int i = 0; i < 500; i++) {
2397
this.stmt
2398
.executeUpdate("INSERT INTO testMaxRowsAndLimit VALUES ("
2399
+ i + ")");
2400
}
2401
2402
this.stmt.setMaxRows(250);
2403
this.stmt
2404
.executeQuery("SELECT limitField FROM testMaxRowsAndLimit");
2405
} finally {
2406
this.stmt.setMaxRows(0);
2407
2408
this.stmt.executeUpdate("DROP TABLE IF EXISTS testMaxRowsAndLimit");
2409
}
2410
}
2411
2412
/*
2413
* public void testBug9595() throws Exception { double[] vals = new double[]
2414
* {52.21, 52.22, 52.23, 52.24};
2415
*
2416
* createTable("testBug9595", "(field1 DECIMAL(10,2), sortField INT)");
2417
*
2418
* this.pstmt = this.conn.prepareStatement("INSERT INTO testBug9595 VALUES
2419
* (?, ?)"); // Try setting as doubles for (int i = 0; i < vals.length; i++) {
2420
* this.pstmt.setDouble(1, vals[i]); this.pstmt.setInt(2, i);
2421
* this.pstmt.executeUpdate(); }
2422
*
2423
* this.pstmt = this.conn.prepareStatement("SELECT field1 FROM testBug9595
2424
* ORDER BY sortField"); this.rs = this.pstmt.executeQuery();
2425
*
2426
* int i = 0;
2427
*
2428
* while (this.rs.next()) { double valToTest = vals[i++];
2429
*
2430
* assertEquals(this.rs.getDouble(1), valToTest, 0.001);
2431
* assertEquals(this.rs.getBigDecimal(1).doubleValue(), valToTest, 0.001); }
2432
*
2433
* this.pstmt = this.conn.prepareStatement("INSERT INTO testBug9595 VALUES
2434
* (?, ?)");
2435
*
2436
* this.stmt.executeUpdate("TRUNCATE TABLE testBug9595"); // Now, as
2437
* BigDecimals for (i = 0; i < vals.length; i++) { BigDecimal foo = new
2438
* BigDecimal(vals[i]);
2439
*
2440
* this.pstmt.setObject(1, foo, Types.DECIMAL, 2); this.pstmt.setInt(2, i);
2441
* this.pstmt.executeUpdate(); }
2442
*
2443
* this.pstmt = this.conn.prepareStatement("SELECT field1 FROM testBug9595
2444
* ORDER BY sortField"); this.rs = this.pstmt.executeQuery();
2445
*
2446
* i = 0;
2447
*
2448
* while (this.rs.next()) { double valToTest = vals[i++];
2449
* System.out.println(this.rs.getString(1));
2450
* assertEquals(this.rs.getDouble(1), valToTest, 0.001);
2451
* assertEquals(this.rs.getBigDecimal(1).doubleValue(), valToTest, 0.001); } }
2452
*/
2453
2454
/**
2455
* Tests that 'LOAD DATA LOCAL INFILE' works
2456
*
2457
* @throws Exception
2458
* if any errors occur
2459
*/
2460
public void testLoadData() throws Exception {
2461
try {
2462
int maxAllowedPacket = 1048576;
2463
2464
this.stmt.executeUpdate("DROP TABLE IF EXISTS loadDataRegress");
2465
this.stmt
2466
.executeUpdate("CREATE TABLE loadDataRegress (field1 int, field2 int)");
2467
2468
File tempFile = File.createTempFile("mysql", ".txt");
2469
2470
// tempFile.deleteOnExit();
2471
System.out.println(tempFile);
2472
2473
Writer out = new FileWriter(tempFile);
2474
2475
int localCount = 0;
2476
int rowCount = 128; // maxAllowedPacket * 4;
2477
2478
for (int i = 0; i < rowCount; i++) {
2479
out.write((localCount++) + "\t" + (localCount++) + "\n");
2480
}
2481
2482
out.close();
2483
2484
StringBuffer fileNameBuf = null;
2485
2486
if (File.separatorChar == '\\') {
2487
fileNameBuf = new StringBuffer();
2488
2489
String fileName = tempFile.getAbsolutePath();
2490
int fileNameLength = fileName.length();
2491
2492
for (int i = 0; i < fileNameLength; i++) {
2493
char c = fileName.charAt(i);
2494
2495
if (c == '\\') {
2496
fileNameBuf.append("/");
2497
} else {
2498
fileNameBuf.append(c);
2499
}
2500
}
2501
} else {
2502
fileNameBuf = new StringBuffer(tempFile.getAbsolutePath());
2503
}
2504
2505
int updateCount = this.stmt
2506
.executeUpdate("LOAD DATA LOCAL INFILE '"
2507
+ fileNameBuf.toString()
2508
+ "' INTO TABLE loadDataRegress");
2509
assertTrue(updateCount == rowCount);
2510
} finally {
2511
this.stmt.executeUpdate("DROP TABLE IF EXISTS loadDataRegress");
2512
}
2513
}
2514
2515
public void testNullClob() throws Exception {
2516
createTable("testNullClob", "(field1 TEXT NULL)");
2517
2518
PreparedStatement pStmt = null;
2519
2520
try {
2521
pStmt = this.conn
2522
.prepareStatement("INSERT INTO testNullClob VALUES (?)");
2523
pStmt.setClob(1, null);
2524
pStmt.executeUpdate();
2525
} finally {
2526
if (pStmt != null) {
2527
pStmt.close();
2528
}
2529
}
2530
}
2531
2532
/**
2533
* Tests fix for BUG#1658
2534
*
2535
* @throws Exception
2536
* if the fix for parameter bounds checking doesn't work.
2537
*/
2538
public void testParameterBoundsCheck() throws Exception {
2539
try {
2540
this.stmt
2541
.executeUpdate("DROP TABLE IF EXISTS testParameterBoundsCheck");
2542
this.stmt
2543
.executeUpdate("CREATE TABLE testParameterBoundsCheck(f1 int, f2 int, f3 int, f4 int, f5 int)");
2544
2545
PreparedStatement pstmt = this.conn
2546
.prepareStatement("UPDATE testParameterBoundsCheck SET f1=?, f2=?,f3=?,f4=? WHERE f5=?");
2547
2548
pstmt.setString(1, "");
2549
pstmt.setString(2, "");
2550
2551
try {
2552
pstmt.setString(25, "");
2553
} catch (SQLException sqlEx) {
2554
assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
2555
.getSQLState()));
2556
}
2557
} finally {
2558
this.stmt
2559
.executeUpdate("DROP TABLE IF EXISTS testParameterBoundsCheck");
2560
}
2561
}
2562
2563
public void testPStmtTypesBug() throws Exception {
2564
try {
2565
this.stmt.executeUpdate("DROP TABLE IF EXISTS testPStmtTypesBug");
2566
this.stmt
2567
.executeUpdate("CREATE TABLE testPStmtTypesBug(field1 INT)");
2568
this.pstmt = this.conn
2569
.prepareStatement("INSERT INTO testPStmtTypesBug VALUES (?)");
2570
this.pstmt.setObject(1, null, Types.INTEGER);
2571
this.pstmt.addBatch();
2572
this.pstmt.setInt(1, 1);
2573
this.pstmt.addBatch();
2574
this.pstmt.executeBatch();
2575
2576
} finally {
2577
this.stmt.executeUpdate("DROP TABLE IF EXISTS testPStmtTypesBug");
2578
}
2579
}
2580
2581
/**
2582
* Tests fix for BUG#1511
2583
*
2584
* @throws Exception
2585
* if the quoteid parsing fix in PreparedStatement doesn't work.
2586
*/
2587
public void testQuotedIdRecognition() throws Exception {
2588
if (!this.versionMeetsMinimum(4, 1)) {
2589
try {
2590
this.stmt.executeUpdate("DROP TABLE IF EXISTS testQuotedId");
2591
this.stmt
2592
.executeUpdate("CREATE TABLE testQuotedId (col1 VARCHAR(32))");
2593
2594
PreparedStatement pStmt = this.conn
2595
.prepareStatement("SELECT * FROM testQuotedId WHERE col1='ABC`DEF' or col1=?");
2596
pStmt.setString(1, "foo");
2597
pStmt.execute();
2598
2599
this.stmt.executeUpdate("DROP TABLE IF EXISTS testQuotedId2");
2600
this.stmt
2601
.executeUpdate("CREATE TABLE testQuotedId2 (`Works?` INT)");
2602
pStmt = this.conn
2603
.prepareStatement("INSERT INTO testQuotedId2 (`Works?`) VALUES (?)");
2604
pStmt.setInt(1, 1);
2605
pStmt.executeUpdate();
2606
} finally {
2607
this.stmt.executeUpdate("DROP TABLE IF EXISTS testQuotedId");
2608
this.stmt.executeUpdate("DROP TABLE IF EXISTS testQuotedId2");
2609
}
2610
}
2611
}
2612
2613
/**
2614
* Tests for BUG#9288, parameter index out of range if LIKE, ESCAPE '\'
2615
* present in query.
2616
*
2617
* @throws Exception
2618
* if the test fails.
2619
*/
2620
/*
2621
* public void testBug9288() throws Exception { String tableName =
2622
* "testBug9288"; PreparedStatement pStmt = null;
2623
*
2624
* try { createTable(tableName, "(field1 VARCHAR(32), field2 INT)"); pStmt =
2625
* ((com.mysql.jdbc.Connection)this.conn).clientPrepareStatement( "SELECT
2626
* COUNT(1) FROM " + tableName + " WHERE " + "field1 LIKE '%' ESCAPE '\\'
2627
* AND " + "field2 > ?"); pStmt.setInt(1, 0);
2628
*
2629
* this.rs = pStmt.executeQuery(); } finally { if (this.rs != null) {
2630
* this.rs.close(); this.rs = null; }
2631
*
2632
* if (pStmt != null) { pStmt.close(); } } }
2633
*/
2634
2635
/*
2636
* public void testBug10999() throws Exception { if (versionMeetsMinimum(5,
2637
* 0, 5)) {
2638
*
2639
* String tableName = "testBug10999"; String updateTrigName =
2640
* "testBug10999Update"; String insertTrigName = "testBug10999Insert"; try {
2641
* createTable(tableName, "(pkfield INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
2642
* field1 VARCHAR(32))");
2643
*
2644
* try { this.stmt.executeUpdate("DROP TRIGGER " + updateTrigName); } catch
2645
* (SQLException sqlEx) { // ignore for now }
2646
*
2647
* this.stmt.executeUpdate("CREATE TRIGGER " + updateTrigName + " AFTER
2648
* UPDATE ON " + tableName + " FOR EACH ROW " + "BEGIN " + "END");
2649
*
2650
* try { this.stmt.executeUpdate("DROP TRIGGER " + insertTrigName); } catch
2651
* (SQLException sqlEx) { // ignore }
2652
*
2653
* this.stmt.executeUpdate("CREATE TRIGGER " + insertTrigName + " AFTER
2654
* INSERT ON " + tableName + " FOR EACH ROW " + " BEGIN " + "END");
2655
*
2656
* this.conn.setAutoCommit(false);
2657
*
2658
* String updateSQL = "INSERT INTO " + tableName + " (field1) VALUES
2659
* ('abcdefg')"; int rowCount = this.stmt.executeUpdate(updateSQL,
2660
* Statement.RETURN_GENERATED_KEYS);
2661
*
2662
* this.rs = stmt.getGeneratedKeys(); if (rs.next()) {
2663
* System.out.println(rs.getInt(1)); int id = rs.getInt(1); //if
2664
* (log.isDebugEnabled()) // log.debug("Retrieved ID = " + id); } //else {
2665
* //log.error("Can't retrieve ID with getGeneratedKeys."); // Retrieve ID
2666
* using a SELECT statement instead. // querySQL = "SELECT id from tab1
2667
* WHERE ...";
2668
*
2669
* //if (log.isDebugEnabled()) // log.debug(querySQL);
2670
*
2671
* //rs = stmt.executeQuery(querySQL); this.rs =
2672
* this.stmt.executeQuery("SELECT pkfield FROM " + tableName); } finally {
2673
* this.conn.setAutoCommit(true);
2674
*
2675
* try { this.stmt.executeUpdate("DROP TRIGGER IF EXISTS " +
2676
* insertTrigName); } catch (SQLException sqlEx) { // ignore }
2677
*
2678
* try { this.stmt.executeUpdate("DROP TRIGGER IF EXISTS " +
2679
* updateTrigName); } catch (SQLException sqlEx) { // ignore } } } }
2680
*/
2681
2682
/**
2683
* Tests that binary dates/times are encoded/decoded correctly.
2684
*
2685
* @throws Exception
2686
* if the test fails.
2687
*
2688
* @deprecated because we need to use this particular constructor for the
2689
* date class, as Calendar-constructed dates don't pass the
2690
* .equals() test :(
2691
*/
2692
public void testServerPrepStmtAndDate() throws Exception {
2693
try {
2694
this.stmt
2695
.executeUpdate("DROP TABLE IF EXISTS testServerPrepStmtAndDate");
2696
this.stmt.executeUpdate("CREATE TABLE testServerPrepStmtAndDate("
2697
+ "`P_ID` int(10) NOT NULL default '0',"
2698
+ "`H_ID` int(10) NOT NULL default '0',"
2699
+ "`R_ID` int(10) NOT NULL default '0',"
2700
+ "`H_Age` int(10) default NULL,"
2701
+ "`R_Date` date NOT NULL default '0000-00-00',"
2702
+ "`Comments` varchar(255) default NULL,"
2703
+ "`Weight` int(10) default NULL,"
2704
+ "`HeadGear` char(1) NOT NULL default '',"
2705
+ "`FinPos` int(10) default NULL,"
2706
+ "`Jock_ID` int(10) default NULL,"
2707
+ "`BtnByPrev` double default NULL,"
2708
+ "`BtnByWinner` double default NULL,"
2709
+ "`Jock_All` int(10) default NULL,"
2710
+ "`Draw` int(10) default NULL,"
2711
+ "`SF` int(10) default NULL,"
2712
+ "`RHR` int(10) default NULL,"
2713
+ "`ORating` int(10) default NULL,"
2714
+ "`Odds` double default NULL,"
2715
+ "`RaceFormPlus` int(10) default NULL,"
2716
+ "`PrevPerform` int(10) default NULL,"
2717
+ "`TrainerID` int(10) NOT NULL default '0',"
2718
+ "`DaysSinceRun` int(10) default NULL,"
2719
+ "UNIQUE KEY `P_ID` (`P_ID`),"
2720
+ "UNIQUE KEY `R_H_ID` (`R_ID`,`H_ID`),"
2721
+ "KEY `R_Date` (`R_Date`)," + "KEY `H_Age` (`H_Age`),"
2722
+ "KEY `TrainerID` (`TrainerID`)," + "KEY `H_ID` (`H_ID`)"
2723
+ ")");
2724
2725
Date dt = new java.sql.Date(102, 1, 2); // Note, this represents the
2726
// date 2002-02-02
2727
2728
PreparedStatement pStmt2 = this.conn
2729
.prepareStatement("INSERT INTO testServerPrepStmtAndDate (P_ID, R_Date) VALUES (171576, ?)");
2730
pStmt2.setDate(1, dt);
2731
pStmt2.executeUpdate();
2732
pStmt2.close();
2733
2734
this.rs = this.stmt
2735
.executeQuery("SELECT R_Date FROM testServerPrepStmtAndDate");
2736
this.rs.next();
2737
2738
System.out.println("Date that was stored (as String) "
2739
+ this.rs.getString(1)); // comes back as 2002-02-02
2740
2741
PreparedStatement pStmt = this.conn
2742
.prepareStatement("Select P_ID,R_Date from testServerPrepStmtAndDate Where R_Date = ? and P_ID = 171576");
2743
pStmt.setDate(1, dt);
2744
2745
this.rs = pStmt.executeQuery();
2746
2747
assertTrue(this.rs.next());
2748
2749
assertEquals("171576", this.rs.getString(1));
2750
2751
assertEquals(dt, this.rs.getDate(2));
2752
} finally {
2753
this.stmt
2754
.executeUpdate("DROP TABLE IF EXISTS testServerPrepStmtAndDate");
2755
}
2756
}
2757
2758
public void testServerPrepStmtDeadlock() throws Exception {
2759
2760
Connection c = getConnectionWithProps(null);
2761
2762
Thread testThread1 = new PrepareThread(c);
2763
Thread testThread2 = new PrepareThread(c);
2764
testThread1.start();
2765
testThread2.start();
2766
Thread.sleep(30000);
2767
assertTrue(this.testServerPrepStmtDeadlockCounter >= 10);
2768
}
2769
2770
/**
2771
* Tests PreparedStatement.setCharacterStream() to ensure it accepts > 4K
2772
* streams
2773
*
2774
* @throws Exception
2775
* if an error occurs.
2776
*/
2777
public void testSetCharacterStream() throws Exception {
2778
try {
2779
((com.mysql.jdbc.Connection) this.conn).setTraceProtocol(true);
2780
2781
this.stmt
2782
.executeUpdate("DROP TABLE IF EXISTS charStreamRegressTest");
2783
this.stmt
2784
.executeUpdate("CREATE TABLE charStreamRegressTest(field1 text)");
2785
2786
this.pstmt = this.conn
2787
.prepareStatement("INSERT INTO charStreamRegressTest VALUES (?)");
2788
2789
// char[] charBuf = new char[16384];
2790
char[] charBuf = new char[32];
2791
2792
for (int i = 0; i < charBuf.length; i++) {
2793
charBuf[i] = 'A';
2794
}
2795
2796
CharArrayReader reader = new CharArrayReader(charBuf);
2797
2798
this.pstmt.setCharacterStream(1, reader, charBuf.length);
2799
this.pstmt.executeUpdate();
2800
2801
this.rs = this.stmt
2802
.executeQuery("SELECT LENGTH(field1) FROM charStreamRegressTest");
2803
2804
this.rs.next();
2805
2806
System.out.println("Character stream length: "
2807
+ this.rs.getString(1));
2808
2809
this.rs = this.stmt
2810
.executeQuery("SELECT field1 FROM charStreamRegressTest");
2811
2812
this.rs.next();
2813
2814
String result = this.rs.getString(1);
2815
2816
assertTrue(result.length() == charBuf.length);
2817
2818
this.stmt.execute("TRUNCATE TABLE charStreamRegressTest");
2819
2820
// Test that EOF is not thrown
2821
reader = new CharArrayReader(charBuf);
2822
this.pstmt.clearParameters();
2823
this.pstmt.setCharacterStream(1, reader, charBuf.length);
2824
this.pstmt.executeUpdate();
2825
2826
this.rs = this.stmt
2827
.executeQuery("SELECT LENGTH(field1) FROM charStreamRegressTest");
2828
2829
this.rs.next();
2830
2831
System.out.println("Character stream length: "
2832
+ this.rs.getString(1));
2833
2834
this.rs = this.stmt
2835
.executeQuery("SELECT field1 FROM charStreamRegressTest");
2836
2837
this.rs.next();
2838
2839
result = this.rs.getString(1);
2840
2841
assertTrue("Retrieved value of length " + result.length()
2842
+ " != length of inserted value " + charBuf.length, result
2843
.length() == charBuf.length);
2844
2845
// Test single quotes inside identifers
2846
this.stmt
2847
.executeUpdate("DROP TABLE IF EXISTS `charStream'RegressTest`");
2848
this.stmt
2849
.executeUpdate("CREATE TABLE `charStream'RegressTest`(field1 text)");
2850
2851
this.pstmt = this.conn
2852
.prepareStatement("INSERT INTO `charStream'RegressTest` VALUES (?)");
2853
2854
reader = new CharArrayReader(charBuf);
2855
this.pstmt.setCharacterStream(1, reader, (charBuf.length * 2));
2856
this.pstmt.executeUpdate();
2857
2858
this.rs = this.stmt
2859
.executeQuery("SELECT field1 FROM `charStream'RegressTest`");
2860
2861
this.rs.next();
2862
2863
result = this.rs.getString(1);
2864
2865
assertTrue("Retrieved value of length " + result.length()
2866
+ " != length of inserted value " + charBuf.length, result
2867
.length() == charBuf.length);
2868
} finally {
2869
((com.mysql.jdbc.Connection) this.conn).setTraceProtocol(false);
2870
2871
if (this.rs != null) {
2872
try {
2873
this.rs.close();
2874
} catch (Exception ex) {
2875
// ignore
2876
}
2877
2878
this.rs = null;
2879
}
2880
2881
this.stmt
2882
.executeUpdate("DROP TABLE IF EXISTS `charStream'RegressTest`");
2883
this.stmt
2884
.executeUpdate("DROP TABLE IF EXISTS charStreamRegressTest");
2885
}
2886
}
2887
2888
/**
2889
* Tests a bug where Statement.setFetchSize() does not work for values other
2890
* than 0 or Integer.MIN_VALUE
2891
*
2892
* @throws Exception
2893
* if any errors occur
2894
*/
2895
public void testSetFetchSize() throws Exception {
2896
int oldFetchSize = this.stmt.getFetchSize();
2897
2898
try {
2899
this.stmt.setFetchSize(10);
2900
} finally {
2901
this.stmt.setFetchSize(oldFetchSize);
2902
}
2903
}
2904
2905
/**
2906
* Tests fix for BUG#907
2907
*
2908
* @throws Exception
2909
* if an error occurs
2910
*/
2911
public void testSetMaxRows() throws Exception {
2912
Statement maxRowsStmt = null;
2913
2914
try {
2915
maxRowsStmt = this.conn.createStatement();
2916
maxRowsStmt.setMaxRows(1);
2917
maxRowsStmt.executeQuery("SELECT 1");
2918
} finally {
2919
if (maxRowsStmt != null) {
2920
maxRowsStmt.close();
2921
}
2922
}
2923
}
2924
2925
/**
2926
* Tests for timestamp NPEs occuring in binary-format timestamps.
2927
*
2928
* @throws Exception
2929
* DOCUMENT ME!
2930
*
2931
* @deprecated yes, we know we are using deprecated methods here :)
2932
*/
2933
public void testTimestampNPE() throws Exception {
2934
try {
2935
Timestamp ts = new Timestamp(System.currentTimeMillis());
2936
2937
this.stmt.executeUpdate("DROP TABLE IF EXISTS testTimestampNPE");
2938
this.stmt
2939
.executeUpdate("CREATE TABLE testTimestampNPE (field1 TIMESTAMP)");
2940
2941
this.pstmt = this.conn
2942
.prepareStatement("INSERT INTO testTimestampNPE VALUES (?)");
2943
this.pstmt.setTimestamp(1, ts);
2944
this.pstmt.executeUpdate();
2945
2946
this.pstmt = this.conn
2947
.prepareStatement("SELECT field1 FROM testTimestampNPE");
2948
2949
this.rs = this.pstmt.executeQuery();
2950
2951
this.rs.next();
2952
2953
System.out.println(this.rs.getString(1));
2954
2955
this.rs.getDate(1);
2956
2957
Timestamp rTs = this.rs.getTimestamp(1);
2958
assertTrue("Retrieved year of " + rTs.getYear()
2959
+ " does not match " + ts.getYear(), rTs.getYear() == ts
2960
.getYear());
2961
assertTrue("Retrieved month of " + rTs.getMonth()
2962
+ " does not match " + ts.getMonth(), rTs.getMonth() == ts
2963
.getMonth());
2964
assertTrue("Retrieved date of " + rTs.getDate()
2965
+ " does not match " + ts.getDate(), rTs.getDate() == ts
2966
.getDate());
2967
} finally {
2968
}
2969
}
2970
2971
public void testTruncationWithChar() throws Exception {
2972
try {
2973
this.stmt
2974
.executeUpdate("DROP TABLE IF EXISTS testTruncationWithChar");
2975
this.stmt
2976
.executeUpdate("CREATE TABLE testTruncationWithChar (field1 char(2))");
2977
2978
this.pstmt = this.conn
2979
.prepareStatement("INSERT INTO testTruncationWithChar VALUES (?)");
2980
this.pstmt.setString(1, "00");
2981
this.pstmt.executeUpdate();
2982
} finally {
2983
this.stmt
2984
.executeUpdate("DROP TABLE IF EXISTS testTruncationWithChar");
2985
}
2986
}
2987
2988
/**
2989
* Tests fix for updatable streams being supported in updatable result sets.
2990
*
2991
* @throws Exception
2992
* if the test fails.
2993
*/
2994
public void testUpdatableStream() throws Exception {
2995
try {
2996
this.stmt.executeUpdate("DROP TABLE IF EXISTS updateStreamTest");
2997
this.stmt
2998
.executeUpdate("CREATE TABLE updateStreamTest (keyField INT NOT NULL AUTO_INCREMENT PRIMARY KEY, field1 BLOB)");
2999
3000
int streamLength = 16385;
3001
byte[] streamData = new byte[streamLength];
3002
3003
/* create an updatable statement */
3004
Statement updStmt = this.conn.createStatement(
3005
ResultSet.TYPE_SCROLL_INSENSITIVE,
3006
ResultSet.CONCUR_UPDATABLE);
3007
3008
/* fill the resultset with some values */
3009
ResultSet updRs = updStmt
3010
.executeQuery("SELECT * FROM updateStreamTest");
3011
3012
/* move to insertRow */
3013
updRs.moveToInsertRow();
3014
3015
/* update the table */
3016
updRs.updateBinaryStream("field1", new ByteArrayInputStream(
3017
streamData), streamLength);
3018
3019
updRs.insertRow();
3020
} finally {
3021
this.stmt.executeUpdate("DROP TABLE IF EXISTS updateStreamTest");
3022
}
3023
}
3024
3025
/**
3026
* Tests fix for BUG#15383 - PreparedStatement.setObject() serializes
3027
* BigInteger as object, rather than sending as numeric value (and is thus
3028
* not complementary to .getObject() on an UNSIGNED LONG type).
3029
*
3030
* @throws Exception
3031
* if the test fails.
3032
*/
3033
public void testBug15383() throws Exception {
3034
createTable(
3035
"testBug15383",
3036
"(id INTEGER UNSIGNED NOT NULL "
3037
+ "AUTO_INCREMENT,value BIGINT UNSIGNED NULL DEFAULT 0,PRIMARY "
3038
+ "KEY(id))ENGINE=InnoDB;");
3039
3040
this.stmt.executeUpdate("INSERT INTO testBug15383(value) VALUES(1)");
3041
3042
Statement updatableStmt = this.conn.createStatement(
3043
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
3044
3045
try {
3046
this.rs = updatableStmt.executeQuery("SELECT * from testBug15383");
3047
3048
assertTrue(this.rs.next());
3049
3050
Object bigIntObj = this.rs.getObject("value");
3051
assertEquals("java.math.BigInteger", bigIntObj.getClass().getName());
3052
3053
this.rs.updateObject("value", new BigInteger("3"));
3054
this.rs.updateRow();
3055
3056
assertEquals("3", this.rs.getString("value"));
3057
} finally {
3058
if (this.rs != null) {
3059
ResultSet toClose = this.rs;
3060
this.rs = null;
3061
toClose.close();
3062
}
3063
3064
if (updatableStmt != null) {
3065
updatableStmt.close();
3066
}
3067
}
3068
}
3069
3070
/**
3071
* Tests fix for BUG#17099 - Statement.getGeneratedKeys() throws NPE when no
3072
* query has been processed.
3073
*
3074
* @throws Exception
3075
* if the test fails
3076
*/
3077
public void testBug17099() throws Exception {
3078
if (isRunningOnJdk131()) {
3079
return; // test not valid
3080
}
3081
3082
Statement newStmt = this.conn.createStatement();
3083
assertNotNull(newStmt.getGeneratedKeys());
3084
3085
PreparedStatement pStmt = this.conn.prepareStatement("SELECT 1");
3086
assertNotNull(pStmt.getGeneratedKeys());
3087
3088
if (versionMeetsMinimum(4, 1)) {
3089
pStmt = ((com.mysql.jdbc.Connection) this.conn)
3090
.clientPrepareStatement("SELECT 1");
3091
assertNotNull(pStmt.getGeneratedKeys());
3092
}
3093
}
3094
3095
/**
3096
* Tests fix for BUG#17587 - clearParameters() on a closed prepared
3097
* statement causes NPE.
3098
*
3099
* @throws Exception
3100
* if the test fails.
3101
*/
3102
public void testBug17587() throws Exception {
3103
createTable("testBug17857", "(field1 int)");
3104
PreparedStatement pStmt = null;
3105
3106
try {
3107
pStmt = this.conn
3108
.prepareStatement("INSERT INTO testBug17857 VALUES (?)");
3109
pStmt.close();
3110
try {
3111
pStmt.clearParameters();
3112
} catch (SQLException sqlEx) {
3113
assertEquals("08003", sqlEx.getSQLState());
3114
}
3115
3116
pStmt = ((com.mysql.jdbc.Connection) this.conn)
3117
.clientPrepareStatement("INSERT INTO testBug17857 VALUES (?)");
3118
pStmt.close();
3119
try {
3120
pStmt.clearParameters();
3121
} catch (SQLException sqlEx) {
3122
assertEquals("08003", sqlEx.getSQLState());
3123
}
3124
3125
} finally {
3126
if (pStmt != null) {
3127
pStmt.close();
3128
}
3129
}
3130
}
3131
3132
/**
3133
* Tests fix for BUG#18740 - Data truncation and getWarnings() only returns
3134
* last warning in set.
3135
*
3136
* @throws Exception
3137
* if the test fails.
3138
*/
3139
public void testBug18740() throws Exception {
3140
if (!versionMeetsMinimum(5, 0, 2)) {
3141
createTable("testWarnings", "(field1 smallint(6),"
3142
+ "field2 varchar(6)," + "UNIQUE KEY field1(field1))");
3143
3144
try {
3145
this.stmt.executeUpdate("INSERT INTO testWarnings VALUES "
3146
+ "(10001, 'data1')," + "(10002, 'data2 foo'),"
3147
+ "(10003, 'data3')," + "(10004999, 'data4'),"
3148
+ "(10005, 'data5')");
3149
} catch (SQLException sqlEx) {
3150
String sqlStateToCompare = "01004";
3151
3152
if (isJdbc4()) {
3153
sqlStateToCompare = "22001";
3154
}
3155
3156
assertEquals(sqlStateToCompare, sqlEx.getSQLState());
3157
assertEquals(sqlStateToCompare, sqlEx.getNextException().getSQLState());
3158
3159
SQLWarning sqlWarn = this.stmt.getWarnings();
3160
assertEquals("01000", sqlWarn.getSQLState());
3161
assertEquals("01000", sqlWarn.getNextWarning().getSQLState());
3162
}
3163
}
3164
}
3165
3166
protected boolean isJdbc4() {
3167
boolean isJdbc4;
3168
3169
try {
3170
Class.forName("java.sql.Wrapper");
3171
isJdbc4 = true;
3172
} catch (Throwable t) {
3173
isJdbc4 = false;
3174
}
3175
3176
return isJdbc4;
3177
}
3178
3179
/**
3180
* Tests fix for BUG#19615, PreparedStatement.setObject(int, Object, int)
3181
* doesn't respect scale of BigDecimals.
3182
*
3183
* @throws Exception
3184
* if the test fails.
3185
*/
3186
public void testBug19615() throws Exception {
3187
createTable("testBug19615", "(field1 DECIMAL(19, 12))");
3188
3189
try {
3190
BigDecimal dec = new BigDecimal("1.234567");
3191
3192
this.pstmt = this.conn
3193
.prepareStatement("INSERT INTO testBug19615 VALUES (?)");
3194
this.pstmt.setObject(1, dec, Types.DECIMAL);
3195
this.pstmt.executeUpdate();
3196
this.pstmt.close();
3197
3198
this.rs = this.stmt.executeQuery("SELECT field1 FROM testBug19615");
3199
this.rs.next();
3200
assertEquals(dec, this.rs.getBigDecimal(1).setScale(6));
3201
this.rs.close();
3202
this.stmt.executeUpdate("TRUNCATE TABLE testBug19615");
3203
3204
this.pstmt = ((com.mysql.jdbc.Connection) this.conn)
3205
.clientPrepareStatement("INSERT INTO testBug19615 VALUES (?)");
3206
this.pstmt.setObject(1, dec, Types.DECIMAL);
3207
this.pstmt.executeUpdate();
3208
this.pstmt.close();
3209
3210
this.rs = this.stmt.executeQuery("SELECT field1 FROM testBug19615");
3211
this.rs.next();
3212
assertEquals(dec, this.rs.getBigDecimal(1).setScale(6));
3213
this.rs.close();
3214
} finally {
3215
closeMemberJDBCResources();
3216
}
3217
}
3218
3219
/**
3220
* Tests fix for BUG#20029 - NPE thrown from executeBatch().
3221
*
3222
* @throws Exception
3223
*/
3224
public void testBug20029() throws Exception {
3225
createTable("testBug20029", ("(field1 int)"));
3226
3227
long initialTimeout = 20; // may need to raise this depending on environment
3228
// we try and do this automatically in this testcase
3229
3230
for (int i = 0; i < 10; i++) {
3231
final Connection toBeKilledConn = getConnectionWithProps(new Properties());
3232
final long timeout = initialTimeout;
3233
PreparedStatement toBeKilledPstmt = null;
3234
3235
try {
3236
toBeKilledPstmt = ((com.mysql.jdbc.Connection)toBeKilledConn).clientPrepareStatement("INSERT INTO testBug20029 VALUES (?)");
3237
3238
for (int j = 0; j < 1000; j++) {
3239
toBeKilledPstmt.setInt(1, j);
3240
toBeKilledPstmt.addBatch();
3241
}
3242
3243
Thread t = new Thread() {
3244
public void run() {
3245
try {
3246
sleep(timeout);
3247
toBeKilledConn.close();
3248
} catch (Throwable t) {
3249
3250
}
3251
}
3252
};
3253
3254
t.start();
3255
3256
try {
3257
if (toBeKilledConn.isClosed()) {
3258
initialTimeout *= 2;
3259
continue;
3260
}
3261
3262
toBeKilledPstmt.executeBatch();
3263
fail("Should've caught a SQLException for the statement being closed here");
3264
} catch (BatchUpdateException batchEx) {
3265
assertEquals("08003", batchEx.getSQLState());
3266
break;
3267
}
3268
3269
fail("Connection didn't close while in the middle of PreparedStatement.executeBatch()");
3270
} finally {
3271
if (toBeKilledPstmt != null) {
3272
toBeKilledPstmt.close();
3273
}
3274
3275
if (toBeKilledConn != null) {
3276
toBeKilledConn.close();
3277
}
3278
}
3279
}
3280
}
3281
3282
/**
3283
* Fixes BUG#20687 - Can't pool server-side prepared statements, exception
3284
* raised when re-using them.
3285
*
3286
* @throws Exception if the test fails.
3287
*/
3288
public void testBug20687() throws Exception {
3289
if (!isRunningOnJdk131() && versionMeetsMinimum(5, 0)) {
3290
createTable("testBug20687", "(field1 int)");
3291
Connection poolingConn = null;
3292
3293
Properties props = new Properties();
3294
props.setProperty("cachePrepStmts", "true");
3295
props.setProperty("useServerPrepStmts", "true");
3296
PreparedStatement pstmt1 = null;
3297
PreparedStatement pstmt2 = null;
3298
3299
try {
3300
poolingConn = getConnectionWithProps(props);
3301
pstmt1 = poolingConn.prepareStatement("SELECT field1 FROM testBug20687");
3302
pstmt1.executeQuery();
3303
pstmt1.close();
3304
3305
pstmt2 = poolingConn.prepareStatement("SELECT field1 FROM testBug20687");
3306
pstmt2.executeQuery();
3307
assertTrue(pstmt1 == pstmt2);
3308
pstmt2.close();
3309
} finally {
3310
if (pstmt1 != null) {
3311
pstmt1.close();
3312
}
3313
3314
if (pstmt2 != null) {
3315
pstmt2.close();
3316
}
3317
3318
if (poolingConn != null) {
3319
poolingConn.close();
3320
}
3321
}
3322
}
3323
}
3324
3325
public void testLikeWithBackslashes() throws Exception {
3326
if (!versionMeetsMinimum(5, 0, 0)) {
3327
return;
3328
}
3329
3330
Connection noBackslashEscapesConn = null;
3331
3332
try {
3333
Properties props = new Properties();
3334
props.setProperty("sessionVariables",
3335
"sql_mode=NO_BACKSLASH_ESCAPES");
3336
3337
noBackslashEscapesConn = getConnectionWithProps(props);
3338
3339
createTable(
3340
"X_TEST",
3341
"(userName varchar(32) not null, ivalue integer, CNAME varchar(255), bvalue CHAR(1), svalue varchar(255), ACTIVE CHAR(1), primary key (userName))");
3342
3343
String insert_sql = "insert into X_TEST (ivalue, CNAME, bvalue, svalue, ACTIVE, userName) values (?, ?, ?, ?, ?, ?)";
3344
3345
this.pstmt = noBackslashEscapesConn.prepareStatement(insert_sql);
3346
this.pstmt.setInt(1, 0);
3347
this.pstmt.setString(2, "c:\\jetson");
3348
this.pstmt.setInt(3, 1);
3349
this.pstmt.setString(4, "c:\\jetson");
3350
this.pstmt.setInt(5, 1);
3351
this.pstmt.setString(6, "c:\\jetson");
3352
this.pstmt.execute();
3353
3354
String select_sql = "select user0_.userName as userName0_0_, user0_.ivalue as ivalue0_0_, user0_.CNAME as CNAME0_0_, user0_.bvalue as bvalue0_0_, user0_.svalue as svalue0_0_, user0_.ACTIVE as ACTIVE0_0_ from X_TEST user0_ where user0_.userName like ?";
3355
this.pstmt = noBackslashEscapesConn.prepareStatement(select_sql);
3356
this.pstmt.setString(1, "c:\\j%");
3357
// if we comment out the previous line and uncomment the following, the like clause matches
3358
// stmt.setString(1,"c:\\\\j%");
3359
System.out.println("about to execute query " + select_sql);
3360
this.rs = this.pstmt.executeQuery();
3361
assertTrue(this.rs.next());
3362
} finally {
3363
closeMemberJDBCResources();
3364
3365
if (noBackslashEscapesConn != null) {
3366
noBackslashEscapesConn.close();
3367
}
3368
}
3369
}
3370
3371
/**
3372
* Tests fix for BUG#20650 - Statement.cancel() causes NullPointerException
3373
* if underlying connection has been closed due to server failure.
3374
*
3375
* @throws Exception if the test fails.
3376
*/
3377
public void testBug20650() throws Exception {
3378
Connection closedConn = null;
3379
Statement cancelStmt = null;
3380
3381
try {
3382
closedConn = getConnectionWithProps(null);
3383
cancelStmt = closedConn.createStatement();
3384
3385
closedConn.close();
3386
3387
cancelStmt.cancel();
3388
} finally {
3389
if (cancelStmt != null) {
3390
cancelStmt.close();
3391
}
3392
3393
if (closedConn != null && !closedConn.isClosed()) {
3394
closedConn.close();
3395
}
3396
}
3397
}
3398
3399
/**
3400
* Tests fix for BUG#20888 - escape of quotes in client-side prepared
3401
* statements parsing not respected.
3402
*
3403
* @throws Exception if the test fails.
3404
*/
3405
public void testBug20888() throws Exception {
3406
3407
try {
3408
String s = "SELECT 'What do you think about D\\'Artanian''?', \"What do you think about D\\\"Artanian\"\"?\"";
3409
this.pstmt = ((com.mysql.jdbc.Connection)this.conn).clientPrepareStatement(s);
3410
3411
this.rs = this.pstmt.executeQuery();
3412
this.rs.next();
3413
assertEquals(this.rs.getString(1), "What do you think about D'Artanian'?");
3414
assertEquals(this.rs.getString(2), "What do you think about D\"Artanian\"?");
3415
} finally {
3416
closeMemberJDBCResources();
3417
}
3418
}
3419
3420
/**
3421
* Tests Bug#21207 - Driver throws NPE when tracing prepared statements that
3422
* have been closed (in asSQL()).
3423
*
3424
* @throws Exception if the test fails
3425
*/
3426
public void testBug21207() throws Exception {
3427
try {
3428
this.pstmt = this.conn.prepareStatement("SELECT 1");
3429
this.pstmt.close();
3430
this.pstmt.toString(); // this used to cause an NPE
3431
} finally {
3432
closeMemberJDBCResources();
3433
}
3434
}
3435
3436
/**
3437
* Tests BUG#21438, server-side PS fails when using jdbcCompliantTruncation.
3438
* If either is set to FALSE (&useServerPrepStmts=false or
3439
* &jdbcCompliantTruncation=false) test succedes.
3440
*
3441
* @throws Exception
3442
* if the test fails.
3443
*/
3444
3445
public void testBug21438() throws Exception {
3446
createTable("testBug21438","(t_id int(10), test_date timestamp(30) NOT NULL,primary key t_pk (t_id));");
3447
3448
assertEquals(1, this.stmt.executeUpdate("insert into testBug21438 values (1,NOW());"));
3449
3450
if (this.versionMeetsMinimum(4, 1)) {
3451
this.pstmt = ((com.mysql.jdbc.Connection)this.conn)
3452
.serverPrepare("UPDATE testBug21438 SET test_date=ADDDATE(?,INTERVAL 1 YEAR) WHERE t_id=1;");
3453
3454
try {
3455
Timestamp ts = new Timestamp(System.currentTimeMillis());
3456
ts.setNanos(999999999);
3457
3458
this.pstmt.setTimestamp(1, ts);
3459
3460
assertEquals(1, this.pstmt.executeUpdate());
3461
3462
Timestamp future = (Timestamp)getSingleIndexedValueWithQuery(1, "SELECT test_date FROM testBug21438");
3463
assertEquals(future.getYear() - ts.getYear(), 1);
3464
3465
} finally {
3466
closeMemberJDBCResources();
3467
}
3468
}
3469
}
3470
3471
/**
3472
* Tests fix for BUG#22359 - Driver was using millis for
3473
* Statement.setQueryTimeout() when spec says argument is
3474
* seconds.
3475
*
3476
* @throws Exception if the test fails.
3477
*/
3478
public void testBug22359() throws Exception {
3479
if (versionMeetsMinimum(5, 0)) {
3480
Statement timeoutStmt = null;
3481
3482
try {
3483
timeoutStmt = this.conn.createStatement();
3484
timeoutStmt.setQueryTimeout(2);
3485
3486
long begin = System.currentTimeMillis();
3487
3488
try {
3489
timeoutStmt.execute("SELECT SLEEP(30)");
3490
} catch (MySQLTimeoutException timeoutEx) {
3491
long end = System.currentTimeMillis();
3492
3493
assertTrue((end - begin) > 1000);
3494
}
3495
} finally {
3496
if (timeoutStmt != null) {
3497
timeoutStmt.close();
3498
}
3499
}
3500
}
3501
}
3502
3503
/**
3504
* Tests fix for BUG#22290 - Driver issues truncation on write exception when
3505
* it shouldn't (due to sending big decimal incorrectly to server with
3506
* server-side prepared statement).
3507
*
3508
* @throws Exception if the test fails.
3509
*/
3510
public void testBug22290() throws Exception {
3511
if (!versionMeetsMinimum(5, 0)) {
3512
return;
3513
}
3514
3515
createTable(
3516
"testbug22290",
3517
"(`id` int(11) NOT NULL default '1',`cost` decimal(10,2) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
3518
assertEquals(
3519
this.stmt
3520
.executeUpdate("INSERT INTO testbug22290 (`id`,`cost`) VALUES (1,'1.00')"),
3521
1);
3522
3523
Connection configuredConn = null;
3524
3525
try {
3526
Properties props = new Properties();
3527
props.setProperty("sessionVariables", "sql_mode='STRICT_TRANS_TABLES'");
3528
3529
3530
configuredConn = getConnectionWithProps(props);
3531
3532
this.pstmt = configuredConn
3533
.prepareStatement("update testbug22290 set cost = cost + ? where id = 1");
3534
this.pstmt.setBigDecimal(1, new BigDecimal("1.11"));
3535
assertEquals(this.pstmt.executeUpdate(), 1);
3536
3537
assertEquals(this.stmt
3538
.executeUpdate("UPDATE testbug22290 SET cost='1.00'"), 1);
3539
this.pstmt = ((com.mysql.jdbc.Connection)configuredConn)
3540
.clientPrepareStatement("update testbug22290 set cost = cost + ? where id = 1");
3541
this.pstmt.setBigDecimal(1, new BigDecimal("1.11"));
3542
assertEquals(this.pstmt.executeUpdate(), 1);
3543
} finally {
3544
closeMemberJDBCResources();
3545
3546
if (configuredConn != null) {
3547
configuredConn.close();
3548
}
3549
}
3550
}
3551
3552
public void testClientPreparedSetBoolean() throws Exception {
3553
try {
3554
this.pstmt = ((com.mysql.jdbc.Connection)this.conn).clientPrepareStatement("SELECT ?");
3555
this.pstmt.setBoolean(1, false);
3556
assertEquals("SELECT 0",
3557
this.pstmt.toString().substring(this.pstmt.toString().indexOf("SELECT")));
3558
this.pstmt.setBoolean(1, true);
3559
assertEquals("SELECT 1",
3560
this.pstmt.toString().substring(this.pstmt.toString().indexOf("SELECT")));
3561
} finally {
3562
closeMemberJDBCResources();
3563
}
3564
}
3565
3566
/**
3567
* Tests fix for BUG#24360 .setFetchSize() breaks prepared
3568
* SHOW and other commands.
3569
*
3570
* @throws Exception if the test fails
3571
*/
3572
public void testBug24360() throws Exception {
3573
if (!versionMeetsMinimum(5, 0)) {
3574
return;
3575
}
3576
3577
Connection c = null;
3578
3579
Properties props = new Properties();
3580
props.setProperty("useServerPrepStmts", "true");
3581
3582
try {
3583
c = getConnectionWithProps(props);
3584
3585
this.pstmt = c.prepareStatement("SHOW PROCESSLIST");
3586
this.pstmt.setFetchSize(5);
3587
this.pstmt.execute();
3588
} finally {
3589
closeMemberJDBCResources();
3590
3591
if (c != null) {
3592
c.close();
3593
}
3594
}
3595
}
3596
3597
/**
3598
* Tests fix for BUG#24344 - useJDBCCompliantTimezoneShift with server-side prepared
3599
* statements gives different behavior than when using client-side prepared
3600
* statements. (this is now fixed if moving from server-side prepared statements
3601
* to client-side prepared statements by setting "useSSPSCompatibleTimezoneShift" to
3602
* "true", as the driver can't tell if this is a new deployment that never used
3603
* server-side prepared statements, or if it is an existing deployment that is
3604
* switching to client-side prepared statements from server-side prepared statements.
3605
*
3606
* @throws Exception if the test fails
3607
*/
3608
public void testBug24344() throws Exception {
3609
3610
if (!versionMeetsMinimum(4, 1)) {
3611
return; // need SSPS
3612
}
3613
3614
super.createTable("testBug24344",
3615
"(i INT AUTO_INCREMENT, t1 DATETIME, PRIMARY KEY (i)) ENGINE = MyISAM");
3616
3617
Connection conn2 = null;
3618
3619
try {
3620
Properties props = new Properties();
3621
props.setProperty("useServerPrepStmts", "true");
3622
props.setProperty("useJDBCCompliantTimezoneShift", "true");
3623
conn2 = super.getConnectionWithProps(props);
3624
this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)");
3625
Calendar c = Calendar.getInstance();
3626
this.pstmt.setTimestamp(1, new Timestamp(c.getTime().getTime()));
3627
this.pstmt.execute();
3628
this.pstmt.close();
3629
conn2.close();
3630
3631
props.setProperty("useServerPrepStmts", "false");
3632
props.setProperty("useJDBCCompliantTimezoneShift", "true");
3633
props.setProperty("useSSPSCompatibleTimezoneShift", "true");
3634
3635
conn2 = super.getConnectionWithProps(props);
3636
this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)");
3637
this.pstmt.setTimestamp(1, new Timestamp(c.getTime().getTime()));
3638
this.pstmt.execute();
3639
this.pstmt.close();
3640
conn2.close();
3641
3642
props.setProperty("useServerPrepStmts", "false");
3643
props.setProperty("useJDBCCompliantTimezoneShift", "false");
3644
props.setProperty("useSSPSCompatibleTimezoneShift", "false");
3645
conn2 = super.getConnectionWithProps(props);
3646
this.pstmt = conn2.prepareStatement("INSERT INTO testBug24344 (t1) VALUES (?)");
3647
this.pstmt.setTimestamp(1, new Timestamp(c.getTime().getTime()));
3648
this.pstmt.execute();
3649
this.pstmt.close();
3650
3651
Statement s = conn2.createStatement();
3652
this.rs = s.executeQuery("SELECT t1 FROM testBug24344 ORDER BY i ASC");
3653
3654
Timestamp[] dates = new Timestamp[3];
3655
3656
int i = 0;
3657
3658
while(rs.next()){
3659
dates[i++] = rs.getTimestamp(1);
3660
}
3661
3662
assertEquals( "Number of rows should be 3.", 3, i);
3663
assertEquals(dates[0], dates[1]);
3664
assertTrue(!dates[1].equals(dates[2]));
3665
} finally {
3666
closeMemberJDBCResources();
3667
3668
if (conn2 != null) {
3669
conn2.close();
3670
}
3671
}
3672
}
3673
3674
/**
3675
* Tests fix for BUG#25073 - rewriting batched statements leaks internal statement
3676
* instances, and causes a memory leak.
3677
*
3678
* @throws Exception if the test fails.
3679
*/
3680
public void testBug25073() throws Exception {
3681
if (isRunningOnJdk131()) {
3682
return;
3683
}
3684
3685
Properties props = new Properties();
3686
props.setProperty("rewriteBatchedStatements", "true");
3687
Connection multiConn = getConnectionWithProps(props);
3688
createTable("testBug25073", "(pk_field INT PRIMARY KEY NOT NULL AUTO_INCREMENT, field1 INT)");
3689
Statement multiStmt = multiConn.createStatement();
3690
multiStmt.addBatch("INSERT INTO testBug25073(field1) VALUES (1)");
3691
multiStmt.addBatch("INSERT INTO testBug25073(field1) VALUES (2)");
3692
multiStmt.addBatch("INSERT INTO testBug25073(field1) VALUES (3)");
3693
multiStmt.addBatch("INSERT INTO testBug25073(field1) VALUES (4)");
3694
multiStmt.addBatch("UPDATE testBug25073 SET field1=5 WHERE field1=1");
3695
multiStmt.addBatch("UPDATE testBug25073 SET field1=6 WHERE field1=2 OR field1=3");
3696
3697
int beforeOpenStatementCount = ((com.mysql.jdbc.Connection)multiConn).getActiveStatementCount();
3698
3699
multiStmt.executeBatch();
3700
3701
int afterOpenStatementCount = ((com.mysql.jdbc.Connection)multiConn).getActiveStatementCount();
3702
3703
assertEquals(beforeOpenStatementCount, afterOpenStatementCount);
3704
3705
3706
createTable("testBug25073", "(pk_field INT PRIMARY KEY NOT NULL AUTO_INCREMENT, field1 INT)");
3707
props.clear();
3708
props.setProperty("rewriteBatchedStatements", "true");
3709
props.setProperty("sessionVariables", "max_allowed_packet=1024");
3710
multiConn = getConnectionWithProps(props);
3711
multiStmt = multiConn.createStatement();
3712
3713
for (int i = 0; i < 1000; i++) {
3714
multiStmt.addBatch("INSERT INTO testBug25073(field1) VALUES (" + i + ")");
3715
}
3716
3717
beforeOpenStatementCount = ((com.mysql.jdbc.Connection)multiConn).getActiveStatementCount();
3718
3719
multiStmt.executeBatch();
3720
3721
afterOpenStatementCount = ((com.mysql.jdbc.Connection)multiConn).getActiveStatementCount();
3722
3723
assertEquals(beforeOpenStatementCount, afterOpenStatementCount);
3724
3725
createTable("testBug25073", "(pk_field INT PRIMARY KEY NOT NULL AUTO_INCREMENT, field1 INT)");
3726
3727
props.clear();
3728
props.setProperty("useServerPrepStmts", "false");
3729
props.setProperty("rewriteBatchedStatements", "true");
3730
multiConn = getConnectionWithProps(props);
3731
PreparedStatement pStmt = multiConn.prepareStatement("INSERT INTO testBug25073(field1) VALUES (?)",
3732
Statement.RETURN_GENERATED_KEYS);
3733
3734
for (int i = 0; i < 1000; i++) {
3735
pStmt.setInt(1, i);
3736
pStmt.addBatch();
3737
}
3738
3739
beforeOpenStatementCount = ((com.mysql.jdbc.Connection)multiConn).getActiveStatementCount();
3740
3741
pStmt.executeBatch();
3742
3743
afterOpenStatementCount = ((com.mysql.jdbc.Connection)multiConn).getActiveStatementCount();
3744
3745
assertEquals(beforeOpenStatementCount, afterOpenStatementCount);
3746
3747
createTable("testBug25073", "(pk_field INT PRIMARY KEY NOT NULL AUTO_INCREMENT, field1 INT)");
3748
props.setProperty("useServerPrepStmts", "false");
3749
props.setProperty("rewriteBatchedStatements", "true");
3750
props.setProperty("sessionVariables", "max_allowed_packet=1024");
3751
multiConn = getConnectionWithProps(props);
3752
pStmt = multiConn.prepareStatement("INSERT INTO testBug25073(field1) VALUES (?)",
3753
Statement.RETURN_GENERATED_KEYS);
3754
3755
for (int i = 0; i < 1000; i++) {
3756
pStmt.setInt(1, i);
3757
pStmt.addBatch();
3758
}
3759
3760
beforeOpenStatementCount = ((com.mysql.jdbc.Connection)multiConn).getActiveStatementCount();
3761
3762
pStmt.executeBatch();
3763
3764
afterOpenStatementCount = ((com.mysql.jdbc.Connection)multiConn).getActiveStatementCount();
3765
3766
assertEquals(beforeOpenStatementCount, afterOpenStatementCount);
3767
}
3768
3769
/**
3770
* Tests fix for BUG#25009 - Results from updates not handled correctly in multi-statement
3771
* queries.
3772
*
3773
* @throws Exception if the test fails.
3774
*/
3775
public void testBug25009() throws Exception {
3776
if (!versionMeetsMinimum(4, 1)) {
3777
return;
3778
}
3779
3780
Properties props = new Properties();
3781
props.setProperty("allowMultiQueries", "true");
3782
3783
Connection multiConn = getConnectionWithProps(props);
3784
createTable("testBug25009", "(field1 INT)");
3785
3786
try {
3787
Statement multiStmt = multiConn.createStatement();
3788
multiStmt.execute("SELECT 1;SET @a=1; SET @b=2; SET @c=3; INSERT INTO testBug25009 VALUES (1)");
3789
3790
assertEquals(-1, multiStmt.getUpdateCount());
3791
3792
this.rs = multiStmt.getResultSet();
3793
assertTrue(this.rs.next());
3794
assertEquals(multiStmt.getMoreResults(), false);
3795
3796
for (int i = 0; i < 3; i++) {
3797
assertEquals(0, multiStmt.getUpdateCount());
3798
assertEquals(multiStmt.getMoreResults(), false);
3799
}
3800
3801
assertEquals(1, multiStmt.getUpdateCount());
3802
3803
this.rs = multiStmt.executeQuery("SELECT field1 FROM testBug25009");
3804
assertTrue(this.rs.next());
3805
assertEquals(1, this.rs.getInt(1));
3806
3807
} finally {
3808
closeMemberJDBCResources();
3809
3810
if (multiConn != null) {
3811
multiConn.close();
3812
}
3813
}
3814
}
3815
3816
/**
3817
* Tests fix for BUG#25025 - Client-side prepared statement parser gets confused by
3818
* in-line (slash-star) comments and therefore can't rewrite batched statements or
3819
* reliably detect type of statements when they're used.
3820
*
3821
* @throws Exception if the test fails.
3822
*/
3823
public void testBug25025() throws Exception {
3824
3825
Connection multiConn = null;
3826
3827
createTable("testBug25025", "(field1 INT)");
3828
3829
try {
3830
Properties props = new Properties();
3831
props.setProperty("rewriteBatchedStatements", "true");
3832
props.setProperty("useServerPrepStmts", "false");
3833
3834
multiConn = getConnectionWithProps(props);
3835
3836
this.pstmt = multiConn.prepareStatement("/* insert foo.bar.baz INSERT INTO foo VALUES (?,?,?,?) to trick parser */ INSERT into testBug25025 VALUES (?)");
3837
this.pstmt.setInt(1, 1);
3838
this.pstmt.addBatch();
3839
this.pstmt.setInt(1, 2);
3840
this.pstmt.addBatch();
3841
this.pstmt.setInt(1, 3);
3842
this.pstmt.addBatch();
3843
3844
int[] counts = this.pstmt.executeBatch();
3845
3846
assertEquals(3, counts.length);
3847
assertEquals(1, counts[0]);
3848
assertEquals(1, counts[1]);
3849
assertEquals(1, counts[2]);
3850
assertEquals(true,
3851
((com.mysql.jdbc.PreparedStatement)this.pstmt).canRewriteAsMultivalueInsertStatement());
3852
} finally {
3853
closeMemberJDBCResources();
3854
3855
if (multiConn != null) {
3856
multiConn.close();
3857
}
3858
}
3859
}
3860
3861
public void testBug25606() throws Exception {
3862
if (!versionMeetsMinimum(5, 0)) {
3863
return;
3864
}
3865
3866
createTable("testtable", "(c0 int not null) engine=myisam");
3867
this.stmt
3868
.execute("alter table testtable add unique index testtable_i(c0)");
3869
3870
Properties props = new Properties();
3871
props.setProperty("allowMultiQueries", "true");
3872
3873
Connection multiConn = getConnectionWithProps(props);
3874
3875
multiConn
3876
.createStatement()
3877
.execute(
3878
"create temporary table testtable_td(_batch int not null,c0 int not null)");
3879
multiConn.createStatement().execute(
3880
"create unique index testtable_td_b on testtable_td(_batch)");
3881
3882
PreparedStatement ps;
3883
int num_changes = 0;
3884
3885
ps = multiConn.prepareStatement("insert into testtable(c0) values(?)");
3886
while (num_changes < 10000) {
3887
ps.setInt(1, num_changes);
3888
3889
ps.addBatch();
3890
3891
num_changes++;
3892
3893
if ((num_changes % 1000) == 0) {
3894
ps.executeBatch();
3895
}
3896
}
3897
3898
ps.close();
3899
3900
int num_deletes = 3646;
3901
3902
int i = 0;
3903
ps = multiConn
3904
.prepareStatement("insert into testtable_td(_batch,c0) values(?,?)");
3905
while (i < num_deletes) {
3906
ps.setInt(1, i);
3907
ps.setInt(2, i);
3908
3909
ps.addBatch();
3910
3911
i++;
3912
3913
if ((i % 1000) == 0) {
3914
ps.executeBatch();
3915
}
3916
}
3917
3918
ps.executeBatch();
3919
3920
ps.close();
3921
3922
String sql = "lock tables testtable write;\n"
3923
+ "delete testtable from testtable_td force index(testtable_td_b) straight_join testtable on testtable.c0=testtable_td.c0 where testtable_td._batch>=? and testtable_td._batch<?;\n"
3924
+ "unlock tables;";
3925
3926
ps = multiConn.prepareStatement(sql);
3927
3928
int bsize = 100;
3929
3930
for (int start_index = 0, end_index = Math.min(bsize, num_deletes); start_index < num_changes && start_index < 3646; start_index = end_index, end_index = Math
3931
.min(start_index + bsize, num_deletes)) {
3932
ps.clearParameters();
3933
ps.setInt(1, start_index);
3934
ps.setInt(2, end_index);
3935
ps.execute();
3936
3937
//ignore the results from the "lock_tables"
3938
int c1 = ps.getUpdateCount();
3939
boolean b1 = ps.getMoreResults();
3940
3941
//should always be the results from the "delete"
3942
int nrows_changed = ps.getUpdateCount();
3943
3944
if (nrows_changed == -1)
3945
throw new SQLException("nrows_changed==-1"); //????
3946
3947
bsize *= 4;
3948
}
3949
}
3950
3951
public void testBustedGGKWithPSExecute() throws Exception {
3952
if (isRunningOnJdk131()) {
3953
// no such method
3954
return;
3955
}
3956
3957
createTable("sequence", "(sequence_name VARCHAR(255) NOT NULL PRIMARY KEY, next_val BIGINT NOT NULL)");
3958
3959
// Populate with the initial value
3960
stmt.executeUpdate("INSERT INTO sequence VALUES ('test-sequence', 1234)");
3961
3962
// Atomic operation to increment and return next value
3963
PreparedStatement pStmt = null;
3964
3965
try {
3966
pStmt = this.conn.prepareStatement("UPDATE sequence SET next_val=LAST_INSERT_ID(next_val + ?) WHERE sequence_name = ?",
3967
Statement.RETURN_GENERATED_KEYS);
3968
3969
pStmt.setInt(1, 4);
3970
pStmt.setString(2, "test-sequence");
3971
pStmt.execute();
3972
3973
this.rs = pStmt.getGeneratedKeys();
3974
this.rs.next();
3975
assertEquals(1238, this.rs.getLong(1));
3976
} finally {
3977
closeMemberJDBCResources();
3978
3979
if (pStmt != null) {
3980
pStmt.close();
3981
}
3982
}
3983
}
3984
3985
/**
3986
* Tests fix for BUG#28469 - PreparedStatement.getMetaData()
3987
* for statements containing leading one-line comments
3988
* is not returned correctly.
3989
*
3990
* As part of this fix, we also overhauled detection of
3991
* DML for executeQuery() and SELECTs for executeUpdate() in
3992
* plain and prepared statements to be aware of the same
3993
* types of comments.
3994
*
3995
* @throws Exception
3996
*/
3997
public void testBug28469() throws Exception {
3998
PreparedStatement commentStmt = null;
3999
4000
try {
4001
String[] statementsToTest = {"-- COMMENT\nSELECT 1",
4002
"# COMMENT\nSELECT 1",
4003
"/* comment */ SELECT 1"};
4004
4005
for (int i = 0; i < statementsToTest.length; i++) {
4006
commentStmt = this.conn.prepareStatement(statementsToTest[i]);
4007
4008
assertNotNull(commentStmt.getMetaData());
4009
4010
try {
4011
commentStmt.executeUpdate();
4012
fail("Should not be able to call executeUpdate() on a SELECT statement!");
4013
} catch (SQLException sqlEx) {
4014
// expected
4015
}
4016
4017
this.rs = commentStmt.executeQuery();
4018
this.rs.next();
4019
assertEquals(1, this.rs.getInt(1));
4020
}
4021
4022
createTable("testBug28469", "(field1 INT)");
4023
4024
String[] updatesToTest = {"-- COMMENT\nUPDATE testBug28469 SET field1 = 2",
4025
"# COMMENT\nUPDATE testBug28469 SET field1 = 2",
4026
"/* comment */ UPDATE testBug28469 SET field1 = 2"};
4027
4028
for (int i = 0; i < updatesToTest.length; i++) {
4029
commentStmt = this.conn.prepareStatement(updatesToTest[i]);
4030
4031
assertNull(commentStmt.getMetaData());
4032
4033
try {
4034
commentStmt.executeQuery();
4035
fail("Should not be able to call executeQuery() on a SELECT statement!");
4036
} catch (SQLException sqlEx) {
4037
// expected
4038
}
4039
4040
try {
4041
this.stmt.executeQuery(updatesToTest[i]);
4042
fail("Should not be able to call executeQuery() on a SELECT statement!");
4043
} catch (SQLException sqlEx) {
4044
// expected
4045
}
4046
}
4047
} finally {
4048
closeMemberJDBCResources();
4049
4050
if (commentStmt != null) {
4051
commentStmt.close();
4052
}
4053
}
4054
}
4055
4056
/**
4057
* Tests error with slash-star comment at EOL
4058
*
4059
* @throws Exception if the test fails.
4060
*/
4061
public void testCommentParsing() throws Exception {
4062
createTable("PERSON", "(NAME VARCHAR(32), PERID VARCHAR(32))");
4063
4064
try {
4065
this.pstmt = this.conn.prepareStatement("SELECT NAME AS name2749_0_, PERID AS perid2749_0_ FROM PERSON WHERE PERID=? /*FOR UPDATE*/");
4066
} finally {
4067
closeMemberJDBCResources();
4068
}
4069
}
4070
4071
public void testBug28505() throws Exception {
4072
createTable("testBug28505", "(id int(10) PRIMARY KEY AUTO_INCREMENT NOT NULL, " +
4073
"name char(20) NOT NULL, " +
4074
"UNIQUE INDEX name (name))");
4075
4076
for (int i = 0; i < 2; i++) {
4077
4078
int changed = this.stmt.executeUpdate("INSERT INTO testBug28505 (id, name) " +
4079
"VALUES (NULL, 'test') ON DUPLICATE KEY UPDATE id = last_insert_id(id)");
4080
4081
ResultSet keysResultSet = this.stmt.getGeneratedKeys();
4082
4083
if (keysResultSet.next()) {
4084
assertEquals(keysResultSet.getInt(1), 1);
4085
} else {
4086
// THIS IS THE INCORRECT BEHAVIOP * * * *
4087
System.out.println(" No generated keys returned by this.stmt.getGeneratedKeys()");
4088
}
4089
keysResultSet.close();
4090
4091
System.out.println(getSingleIndexedValueWithQuery(1, "SELECT LAST_INSERT_ID()"));
4092
4093
}
4094
}
4095
4096
/**
4097
* Tests fix for BUG#28851 - parser in client-side prepared statements
4098
* eats character following '/' if it's not a multi-line comment.
4099
*
4100
* @throws Exception if the test fails.
4101
*/
4102
public void testBug28851() throws Exception {
4103
4104
try {
4105
this.pstmt = ((com.mysql.jdbc.Connection) this.conn)
4106
.clientPrepareStatement("SELECT 1/?");
4107
this.pstmt.setInt(1, 1);
4108
this.rs = this.pstmt.executeQuery();
4109
4110
assertTrue(this.rs.next());
4111
4112
assertEquals(1, this.rs.getInt(1));
4113
} finally {
4114
closeMemberJDBCResources();
4115
}
4116
}
4117
4118
/**
4119
* Tests fix for BUG#28596 - parser in client-side prepared statements
4120
* runs to end of statement, rather than end-of-line for '#' comments.
4121
*
4122
* Also added support for '--' single-line comments
4123
*
4124
* @throws Exception if the test fails.
4125
*/
4126
public void testBug28596() throws Exception {
4127
String query = "SELECT #\n" +
4128
"?, #\n" +
4129
"? #?\r\n" +
4130
",-- abcdefg \n" +
4131
"?";
4132
4133
try {
4134
this.pstmt = ((com.mysql.jdbc.Connection) this.conn)
4135
.clientPrepareStatement(query);
4136
this.pstmt.setInt(1, 1);
4137
this.pstmt.setInt(2, 2);
4138
this.pstmt.setInt(3, 3);
4139
4140
assertEquals(3, this.pstmt.getParameterMetaData().getParameterCount());
4141
this.rs = this.pstmt.executeQuery();
4142
4143
assertTrue(this.rs.next());
4144
4145
assertEquals(1, this.rs.getInt(1));
4146
assertEquals(2, this.rs.getInt(2));
4147
assertEquals(3, this.rs.getInt(3));
4148
} finally {
4149
closeMemberJDBCResources();
4150
}
4151
}
4152
}
b'\\ No newline at end of file'
Loggerhead is a web-based interface for Breezy
Version: 2.0.1