Mercurial > hg > monetdb-java

comparison tests/JDBC_API_Tester.java @ 967:5cc071c5c170

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Corrected the returned integer values of Statement methods executeUpdate(...), executeLargeUpdate(...), getUpdateCount() and getLargeUpdateCount() and PreparedStatement methods executeUpdate() and executeLargeUpdate(). They returned -2 for DDL statements, which was not in compliance with the JDBC API documentation.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 13 Feb 2025 21:16:20 +0100 (2 months ago)
parents 39b74cc688d2
children f90d811e97eb
comparison
equal deleted inserted replaced
966:39b74cc688d2 967:5cc071c5c170
808 } 808 }
809 809
810 private void handleExecuteDDL(Statement stmt, String action, String objtype, String objname, String sql) { 810 private void handleExecuteDDL(Statement stmt, String action, String objtype, String objname, String sql) {
811 try { 811 try {
812 int response = stmt.executeUpdate(sql); 812 int response = stmt.executeUpdate(sql);
813 if (response != Statement.SUCCESS_NO_INFO) 813 if (response != 0)
814 sb.append(action).append(" ").append(objtype).append(" ").append(objname).append(" failed to return -2!! It returned: ").append(response).append("\n"); 814 sb.append(action).append(" ").append(objtype).append(" ").append(objname).append(" failed to return 0!! It returned: ").append(response).append("\n");
815 } catch (SQLException e) { 815 } catch (SQLException e) {
816 sb.append("Failed to ").append(action).append(" ").append(objtype).append(" ").append(objname).append(": ").append(e.getMessage()).append("\n"); 816 sb.append("Failed to ").append(action).append(" ").append(objtype).append(" ").append(objname).append(": ").append(e.getMessage()).append("\n");
817 } 817 }
818 } 818 }
819 819
2540 sb.append("0. false\t").append(con.getAutoCommit()).append("\n"); 2540 sb.append("0. false\t").append(con.getAutoCommit()).append("\n");
2541 2541
2542 stmt = con.createStatement(); 2542 stmt = con.createStatement();
2543 int updates = 0; 2543 int updates = 0;
2544 updates = stmt.executeUpdate("CREATE TABLE table_Test_PSmetadata ( myint int, mydouble double, mybool boolean, myvarchar varchar(15), myclob clob )"); 2544 updates = stmt.executeUpdate("CREATE TABLE table_Test_PSmetadata ( myint int, mydouble double, mybool boolean, myvarchar varchar(15), myclob clob )");
2545 if (updates != Statement.SUCCESS_NO_INFO) 2545 if (updates != 0)
2546 sb.append("1. Expected -2 got ").append(updates).append(" instead\n"); 2546 sb.append("1. Expected 0 got ").append(updates).append(" instead\n");
2547 2547
2548 // all NULLs 2548 // all NULLs
2549 updates = stmt.executeUpdate("INSERT INTO table_Test_PSmetadata VALUES (NULL, NULL, NULL, NULL, NULL)"); 2549 updates = stmt.executeUpdate("INSERT INTO table_Test_PSmetadata VALUES (NULL, NULL, NULL, NULL, NULL)");
2550 if (updates != 1) 2550 if (updates != 1)
2551 sb.append("2a. Expected 1 got ").append(updates).append(" instead\n"); 2551 sb.append("2a. Expected 1 got ").append(updates).append(" instead\n");
3022 // >> false: auto commit should be off now 3022 // >> false: auto commit should be off now
3023 sb.append("0. false\t").append(con.getAutoCommit()).append("\n"); 3023 sb.append("0. false\t").append(con.getAutoCommit()).append("\n");
3024 3024
3025 stmt = con.createStatement(); 3025 stmt = con.createStatement();
3026 int updates = stmt.executeUpdate("CREATE TABLE table_Test_PSsqldata ( myinet inet, myurl url )"); 3026 int updates = stmt.executeUpdate("CREATE TABLE table_Test_PSsqldata ( myinet inet, myurl url )");
3027 if (updates != Statement.SUCCESS_NO_INFO) 3027 if (updates != 0)
3028 sb.append("1. Expected -2 got ").append(updates).append(" instead\n"); 3028 sb.append("1. Expected 0 got ").append(updates).append(" instead\n");
3029 3029
3030 pstmt = con.prepareStatement("INSERT INTO table_Test_PSsqldata VALUES (?, ?)"); 3030 pstmt = con.prepareStatement("INSERT INTO table_Test_PSsqldata VALUES (?, ?)");
3031 ParameterMetaData pmd = pstmt.getParameterMetaData(); 3031 ParameterMetaData pmd = pstmt.getParameterMetaData();
3032 sb.append(pmd.getParameterCount()).append(" parameters:\n"); 3032 sb.append(pmd.getParameterCount()).append(" parameters:\n");
3033 for (int parm = 1; parm <= pmd.getParameterCount(); parm++) { 3033 for (int parm = 1; parm <= pmd.getParameterCount(); parm++) {
3129 // >> false: auto commit should be off now 3129 // >> false: auto commit should be off now
3130 sb.append("0. false\t").append(con.getAutoCommit()).append("\n"); 3130 sb.append("0. false\t").append(con.getAutoCommit()).append("\n");
3131 3131
3132 stmt = con.createStatement(); 3132 stmt = con.createStatement();
3133 int updates = stmt.executeUpdate("CREATE TABLE Test_PStimedate (t time, ts timestamp, d date)"); 3133 int updates = stmt.executeUpdate("CREATE TABLE Test_PStimedate (t time, ts timestamp, d date)");
3134 if (updates != Statement.SUCCESS_NO_INFO) 3134 if (updates != 0)
3135 sb.append("1. Expected -2 got ").append(updates).append(" instead\n"); 3135 sb.append("1. Expected 0 got ").append(updates).append(" instead\n");
3136 3136
3137 pstmt = con.prepareStatement("INSERT INTO Test_PStimedate VALUES (?, ?, ?)"); 3137 pstmt = con.prepareStatement("INSERT INTO Test_PStimedate VALUES (?, ?, ?)");
3138 sb.append("1. empty call..."); 3138 sb.append("1. empty call...");
3139 try { 3139 try {
3140 // should fail (as no parameters set) 3140 // should fail (as no parameters set)
3226 // >> false: auto commit should be off now 3226 // >> false: auto commit should be off now
3227 sb.append("0. false\t").append(con.getAutoCommit()).append("\n"); 3227 sb.append("0. false\t").append(con.getAutoCommit()).append("\n");
3228 3228
3229 stmt = con.createStatement(); 3229 stmt = con.createStatement();
3230 int updates = stmt.executeUpdate("CREATE TABLE Test_PStimezone (ts timestamp, tsz timestamp with time zone, t time, tz time with time zone)"); 3230 int updates = stmt.executeUpdate("CREATE TABLE Test_PStimezone (ts timestamp, tsz timestamp with time zone, t time, tz time with time zone)");
3231 if (updates != Statement.SUCCESS_NO_INFO) 3231 if (updates != 0)
3232 sb.append("1. Expected -2 got ").append(updates).append(" instead\n"); 3232 sb.append("1. Expected 0 got ").append(updates).append(" instead\n");
3233 3233
3234 pstmt = con.prepareStatement("INSERT INTO Test_PStimezone VALUES (?, ?, ?, ?)"); 3234 pstmt = con.prepareStatement("INSERT INTO Test_PStimezone VALUES (?, ?, ?, ?)");
3235 sb.append("1. empty call..."); 3235 sb.append("1. empty call...");
3236 try { 3236 try {
3237 // should fail (as no parameters set) 3237 // should fail (as no parameters set)
3446 " type decimal(1,0)," + 3446 " type decimal(1,0)," +
3447 " imageurl url(100)," + 3447 " imageurl url(100)," +
3448 " comment varchar(100)," + 3448 " comment varchar(100)," +
3449 " CONSTRAINT htmtest_htmid_pkey PRIMARY KEY (htmid)" + 3449 " CONSTRAINT htmtest_htmid_pkey PRIMARY KEY (htmid)" +
3450 ")" ); 3450 ")" );
3451 if (updates != Statement.SUCCESS_NO_INFO) 3451 if (updates != 0)
3452 sb.append("1. Expected -2 got ").append(updates).append(" instead\n"); 3452 sb.append("1. Expected 0 got ").append(updates).append(" instead\n");
3453 3453
3454 // index is not used, but the original bug had it too 3454 // index is not used, but the original bug had it too
3455 updates = stmt.executeUpdate("CREATE INDEX htmid ON htmtest (htmid)"); 3455 updates = stmt.executeUpdate("CREATE INDEX htmid ON htmtest (htmid)");
3456 if (updates != Statement.SUCCESS_NO_INFO) 3456 if (updates != 0)
3457 sb.append("1. Expected -2 got ").append(updates).append(" instead\n"); 3457 sb.append("1. Expected 0 got ").append(updates).append(" instead\n");
3458 3458
3459 stmt.close(); 3459 stmt.close();
3460 3460
3461 pstmt = con.prepareStatement("INSERT INTO HTMTEST (HTMID,RA,DECL,FLUX,COMMENT) VALUES (?,?,?,?,?)"); 3461 pstmt = con.prepareStatement("INSERT INTO HTMTEST (HTMID,RA,DECL,FLUX,COMMENT) VALUES (?,?,?,?,?)");
3462 sb.append("1. inserting a record..."); 3462 sb.append("1. inserting a record...");
3692 int updates = stmt.executeUpdate( 3692 int updates = stmt.executeUpdate(
3693 "CREATE TABLE Test_Rbooleans (" + 3693 "CREATE TABLE Test_Rbooleans (" +
3694 " id int, tiny_int tinyint, small_int smallint, medium_int mediumint, \"integer\" int, big_int bigint," + 3694 " id int, tiny_int tinyint, small_int smallint, medium_int mediumint, \"integer\" int, big_int bigint," +
3695 " a_real real, a_float float, a_double double, a_decimal decimal(8,2), a_numeric numeric(8)," + 3695 " a_real real, a_float float, a_double double, a_decimal decimal(8,2), a_numeric numeric(8)," +
3696 " bool boolean, a_char char(4), b_char char(5), a_varchar varchar(20), PRIMARY KEY (id) )"); 3696 " bool boolean, a_char char(4), b_char char(5), a_varchar varchar(20), PRIMARY KEY (id) )");
3697 if (updates != Statement.SUCCESS_NO_INFO) 3697 if (updates != 0)
3698 sb.append("1a. Expected -2 got ").append(updates).append(" instead\n"); 3698 sb.append("1a. Expected 0 got ").append(updates).append(" instead\n");
3699 3699
3700 // all falses 3700 // all falses
3701 updates = stmt.executeUpdate("INSERT INTO Test_Rbooleans VALUES (1,0,0,0,0,0,0.0,0.0,0.0,0.0,0,false,'fals','false','false')"); 3701 updates = stmt.executeUpdate("INSERT INTO Test_Rbooleans VALUES (1,0,0,0,0,0,0.0,0.0,0.0,0.0,0,false,'fals','false','false')");
3702 // all trues 3702 // all trues
3703 updates += stmt.executeUpdate("INSERT INTO Test_Rbooleans VALUES (2,1,1,1,1,1,1.0,1.0,1.0,1.0,1,true,'true','true ','true')"); 3703 updates += stmt.executeUpdate("INSERT INTO Test_Rbooleans VALUES (2,1,1,1,1,1,1.0,1.0,1.0,1.0,1,true,'true','true ','true')");
4707 sb.append("0. false\t").append(con.getAutoCommit()).append("\n"); 4707 sb.append("0. false\t").append(con.getAutoCommit()).append("\n");
4708 4708
4709 stmt = con.createStatement(); 4709 stmt = con.createStatement();
4710 4710
4711 sb.append("1. create..."); 4711 sb.append("1. create...");
4712 if (stmt.executeUpdate("CREATE TABLE Test_Sbatching ( id int )") != Statement.SUCCESS_NO_INFO) 4712 if (stmt.executeUpdate("CREATE TABLE Test_Sbatching ( id int )") != 0)
4713 sb.append("Wrong return status\n"); 4713 sb.append("Wrong return status\n");
4714 else 4714 else
4715 sb.append("passed\n"); 4715 sb.append("passed\n");
4716 4716
4717 // for large batches of DML always set sys.optimizer = 'minimal_pipe'. It makes a big difference in performance. 4717 // for large batches of DML always set sys.optimizer = 'minimal_pipe'. It makes a big difference in performance.
4778 rs = stmt.executeQuery("SELECT COUNT(*) FROM Test_Sbatching"); 4778 rs = stmt.executeQuery("SELECT COUNT(*) FROM Test_Sbatching");
4779 rs.next(); 4779 rs.next();
4780 sb.append(rs.getInt(1)).append(" passed\n"); 4780 sb.append(rs.getInt(1)).append(" passed\n");
4781 4781
4782 sb.append("8. drop table..."); 4782 sb.append("8. drop table...");
4783 if (stmt.executeUpdate("DROP TABLE Test_Sbatching") != Statement.SUCCESS_NO_INFO) 4783 if (stmt.executeUpdate("DROP TABLE Test_Sbatching") != 0)
4784 sb.append("Wrong return status\n"); 4784 sb.append("Wrong return status\n");
4785 else 4785 else
4786 sb.append("passed\n"); 4786 sb.append("passed\n");
4787 4787
4788 // rs.close(); 4788 // rs.close();
5041 try { 5041 try {
5042 stmt = con.createStatement(); 5042 stmt = con.createStatement();
5043 sb.append("1. create table..."); 5043 sb.append("1. create table...");
5044 // create a simple table with an auto-generated key (id) 5044 // create a simple table with an auto-generated key (id)
5045 upd = stmt.executeUpdate("CREATE TABLE bogus_gen_keys (\n \"id\" serial,\n \"x\" varchar(12)\n);"); 5045 upd = stmt.executeUpdate("CREATE TABLE bogus_gen_keys (\n \"id\" serial,\n \"x\" varchar(12)\n);");
5046 if (upd != Statement.SUCCESS_NO_INFO) 5046 if (upd != 0)
5047 sb.append("Wrong return status: ").append(upd).append("\n"); 5047 sb.append("Wrong return status: ").append(upd).append("\n");
5048 else 5048 else
5049 sb.append("passed\n"); 5049 sb.append("passed\n");
5050 5050
5051 // perform an update, useless, but illustrates the bug, this time no 5051 // perform an update, useless, but illustrates the bug, this time no
5092 5092
5093 // cleanup 5093 // cleanup
5094 try { 5094 try {
5095 sb.append("7. drop table..."); 5095 sb.append("7. drop table...");
5096 upd = stmt.executeUpdate("DROP TABLE bogus_gen_keys"); 5096 upd = stmt.executeUpdate("DROP TABLE bogus_gen_keys");
5097 if (upd != Statement.SUCCESS_NO_INFO) 5097 if (upd != 0)
5098 sb.append("Wrong return status: ").append(upd).append("\n"); 5098 sb.append("Wrong return status: ").append(upd).append("\n");
5099 else 5099 else
5100 sb.append("passed\n"); 5100 sb.append("passed\n");
5101 } catch (SQLException e) { 5101 } catch (SQLException e) {
5102 sb.append("FAILED: ").append(e.getMessage()).append("\n"); 5102 sb.append("FAILED: ").append(e.getMessage()).append("\n");
6091 // >> true: auto commit should be on by default 6091 // >> true: auto commit should be on by default
6092 sb.append("0. true\t").append(con.getAutoCommit()).append("\n"); 6092 sb.append("0. true\t").append(con.getAutoCommit()).append("\n");
6093 6093
6094 stmt = con.createStatement(); 6094 stmt = con.createStatement();
6095 sb.append("1. Creating table ").append(tableName); 6095 sb.append("1. Creating table ").append(tableName);
6096 if (stmt.executeUpdate("CREATE TABLE " + tableName + " (myint INT, myvarchar VARCHAR(15), myjson JSON, myuuid UUID, myurl URL, myinet INET)") != Statement.SUCCESS_NO_INFO) 6096 if (stmt.executeUpdate("CREATE TABLE " + tableName + " (myint INT, myvarchar VARCHAR(15), myjson JSON, myuuid UUID, myurl URL, myinet INET)") != 0)
6097 sb.append("Wrong return status\n"); 6097 sb.append("Wrong return status\n");
6098 6098
6099 sb.append("\n2. Insert row 1, "); 6099 sb.append("\n2. Insert row 1, ");
6100 stmt.executeUpdate("INSERT INTO " + tableName + " VALUES (1, 'row 1', '{}', uuid '34c8deb5-e608-406b-beda-6a951f73d455', 'https://www.monetdb.org/', '128.0.0.1')"); 6100 stmt.executeUpdate("INSERT INTO " + tableName + " VALUES (1, 'row 1', '{}', uuid '34c8deb5-e608-406b-beda-6a951f73d455', 'https://www.monetdb.org/', '128.0.0.1')");
6101 sb.append("2, "); 6101 sb.append("2, ");
6445 ctsb.append(')'); 6445 ctsb.append(')');
6446 6446
6447 stmt = con.createStatement(); 6447 stmt = con.createStatement();
6448 sb.append("1. create table ").append(dqTblName).append("\n"); 6448 sb.append("1. create table ").append(dqTblName).append("\n");
6449 int ret = stmt.executeUpdate(ctsb.toString()); 6449 int ret = stmt.executeUpdate(ctsb.toString());
6450 if (ret != -2) 6450 if (ret != 0)
6451 sb.append(" returned: ").append(ret).append(" (expected -2)\n"); 6451 sb.append(" returned: ").append(ret).append(" (expected 0)\n");
6452 6452
6453 String tblName = dqTblName.substring(1, dqTblName.length() -1); // trim the leading and trailing double quote characters 6453 String tblName = dqTblName.substring(1, dqTblName.length() -1); // trim the leading and trailing double quote characters
6454 sb.append("2. show column names of this new table (").append(tblName).append(") via sys.columns query\n"); 6454 sb.append("2. show column names of this new table (").append(tblName).append(") via sys.columns query\n");
6455 rs = stmt.executeQuery("SELECT number, name, type from sys.columns where table_id in (select id from sys._tables where name = '" + tblName + "') order by number"); 6455 rs = stmt.executeQuery("SELECT number, name, type from sys.columns where table_id in (select id from sys._tables where name = '" + tblName + "') order by number");
6456 showResultAndClose_6183(rs); 6456 showResultAndClose_6183(rs);
6673 6673
6674 // cleanup 6674 // cleanup
6675 try { 6675 try {
6676 sb.append("Finally drop table ").append(dqTblName).append("\n"); 6676 sb.append("Finally drop table ").append(dqTblName).append("\n");
6677 int ret = stmt.executeUpdate("DROP TABLE " + dqTblName); 6677 int ret = stmt.executeUpdate("DROP TABLE " + dqTblName);
6678 if (ret != -2) 6678 if (ret != 0)
6679 sb.append(" returned: ").append(ret).append(" (expected -2)\n"); 6679 sb.append(" returned: ").append(ret).append(" (expected 0)\n");
6680 } catch (SQLException e) { 6680 } catch (SQLException e) {
6681 sb.append("FAILED: ").append(e.getMessage()).append("\n"); 6681 sb.append("FAILED: ").append(e.getMessage()).append("\n");
6682 } 6682 }
6683 6683
6684 closeStmtResSet(stmt, rs); 6684 closeStmtResSet(stmt, rs);
6869 closeStmtResSet(pstmt, null); 6869 closeStmtResSet(pstmt, null);
6870 6870
6871 compareExpectedOutput("Bug_PrepStmtManyParams_7337(" + nrParams + ")", 6871 compareExpectedOutput("Bug_PrepStmtManyParams_7337(" + nrParams + ")",
6872 "0. fetch size of new statement: 250\n" + 6872 "0. fetch size of new statement: 250\n" +
6873 "1. create table with " + (NR_COLUMNS+2) + " columns, sql has length: " + ((NR_COLUMNS * 23) -29) + "\n" + 6873 "1. create table with " + (NR_COLUMNS+2) + " columns, sql has length: " + ((NR_COLUMNS * 23) -29) + "\n" +
6874 "2. table created. ret = -2\n" + 6874 "2. table created. ret = 0\n" +
6875 "3. prepare insert statement (no params), sql has length: " + ((NR_COLUMNS * 25) -53) + "\n" + 6875 "3. prepare insert statement (no params), sql has length: " + ((NR_COLUMNS * 25) -53) + "\n" +
6876 " fetch size after prepare 1: 250\n" + 6876 " fetch size after prepare 1: 250\n" +
6877 " pmd. 0 parameters\n" + 6877 " pmd. 0 parameters\n" +
6878 "4. execute prepared insert\n" + 6878 "4. execute prepared insert\n" +
6879 "5. first execute returned: 1\n" + 6879 "5. first execute returned: 1\n" +