001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: import java.sql.Connection;
034: import java.sql.DriverManager;
035: import java.sql.PreparedStatement;
036: import java.sql.SQLException;
037: import java.sql.Statement;
038: import java.sql.Timestamp;
039:
040: public class TestBatchBug {
041:
042: static final int DATASET_COUNT = 2;
043: static final int DECIMAL_FIELDS_PER_DATASET = 2;
044: static final String TABLE_ATTR_CACHED = "CACHED";
045: static final String IN_PROCESS_FILE_URL = "jdbc:hsqldb:/temp/hsqldb/perftest";
046: static final String HSQLDB_LOCALHOST_URL = "jdbc:hsqldb:hsql://localhost/yourtest";;
047: static final String TEST_TABLE_NAME = "CSBug";
048: static String FIELD_LIST_WITHOUT_ID = "Kennung, Last_Update ";
049:
050: // wird in static {} erweitert:
051: static String FIELD_LIST_WITH_ID = "ID, ";
052: static String SQL_SELECT_ALL_FIELDS = "SELECT ";
053:
054: // wird in static {} erweitert:
055: static {
056: for (int i = 1; i <= DECIMAL_FIELDS_PER_DATASET; i++) {
057: FIELD_LIST_WITHOUT_ID += ", Field_" + i;
058: }
059:
060: FIELD_LIST_WITH_ID += FIELD_LIST_WITHOUT_ID;
061: SQL_SELECT_ALL_FIELDS += FIELD_LIST_WITH_ID + " FROM "
062: + TEST_TABLE_NAME;
063: }
064:
065: static int ldfNrFuerKennung;
066:
067: public static void main(String[] arg) {
068:
069: try {
070:
071: // Load the HSQL Database Engine JDBC driver
072: Class.forName("org.hsqldb.jdbcDriver");
073:
074: String[] urls = { IN_PROCESS_FILE_URL,
075: HSQLDB_LOCALHOST_URL, };
076:
077: for (int i = 0; i < urls.length; i++) {
078: String url = urls[i];
079: String[] tableAttrs = { TABLE_ATTR_CACHED };
080:
081: for (int iAttr = 0; iAttr < tableAttrs.length; iAttr++) {
082: testURL(url, "CACHED");
083: }
084: }
085:
086: System.out.println("bye");
087: } catch (Exception e) {
088: e.printStackTrace();
089: }
090: }
091:
092: static void testURL(String url, String tableAttr)
093: throws SQLException {
094:
095: System.out.println(url);
096:
097: Connection con = DriverManager.getConnection(url, "sa", "");
098:
099: reCreateTable(con, "CACHED");
100: populateTable(con);
101: con.close();
102: }
103:
104: static void reCreateTable(Connection con, String tableAttr)
105: throws SQLException {
106:
107: String cvsFileName = TEST_TABLE_NAME + ".csv";
108:
109: // Create a statement object
110: Statement stmt = con.createStatement();
111:
112: // Try to drop the table
113: try {
114:
115: // stmt.executeUpdate("DROP TABLE " + TEST_TABLE_NAME + " IF EXISTS");
116: stmt.executeUpdate("DROP TABLE " + TEST_TABLE_NAME);
117: } catch (SQLException e) { // Ignore Exception, because the table may not yet exist
118: System.out.println(e.getMessage());
119: }
120:
121: StringBuffer sql = new StringBuffer();
122:
123: sql.append("CREATE ");
124: sql.append(tableAttr); // z.B. "CACHED"
125: sql.append(" TABLE ");
126: sql.append(TEST_TABLE_NAME);
127: sql.append(" (");
128: sql.append("Id integer IDENTITY");
129: sql.append(", ");
130: sql.append("Kennung varchar(20) NOT NULL");
131:
132: // etwas andere Schreibweise von CURRENT TIMESTAMP
133: sql.append(", last_update TIMESTAMP ");
134: sql.append("DEFAULT CURRENT_TIMESTAMP NOT NULL");
135:
136: for (int i = 1; i <= DECIMAL_FIELDS_PER_DATASET; i++) {
137: sql.append(", Field_" + i + " decimal");
138: }
139:
140: sql.append(", UNIQUE(Kennung)");
141: sql.append(")");
142: System.out.println(sql.toString());
143: stmt.executeUpdate(sql.toString());
144:
145: // CLEAR TABLE
146: sql = new StringBuffer();
147:
148: sql.append("DELETE FROM ");
149: sql.append(TEST_TABLE_NAME);
150: System.out.println(sql.toString());
151: stmt.executeUpdate(sql.toString());
152: stmt.close(); // is no longer used
153: }
154:
155: // tries to enter 2 records
156: static void populateTable(Connection con) throws SQLException {
157:
158: long startTime = System.currentTimeMillis();
159: Timestamp now = new Timestamp(startTime);
160:
161: con.setAutoCommit(false);
162:
163: String sql = createInsertSQL(true, false);
164: PreparedStatement prep = con.prepareStatement(sql);
165:
166: prep.clearParameters();
167: prep.setString(1, "xxx");
168: prep.setTimestamp(2, now); // last_update
169:
170: for (int ii = 0; ii < DECIMAL_FIELDS_PER_DATASET; ii++) {
171: prep.setDouble(ii + 3, 0.123456789); // Wert
172: }
173:
174: prep.addBatch();
175: prep.setString(1, "yyy");
176: prep.setTimestamp(2, now); // last_update
177:
178: for (int ii = 0; ii < DECIMAL_FIELDS_PER_DATASET; ii++) {
179: prep.setDouble(ii + 3, 0.123456789); // Wert
180: }
181:
182: prep.addBatch();
183:
184: int[] updateCounts = prep.executeBatch();
185:
186: con.setAutoCommit(true);
187: prep.close();
188: }
189:
190: static String createInsertSQL(boolean prepStmt,
191: boolean getIdAfterInsert) {
192:
193: StringBuffer sql = new StringBuffer();
194:
195: sql.append("INSERT INTO ");
196: sql.append(TEST_TABLE_NAME);
197: sql.append(" (");
198: sql.append(FIELD_LIST_WITHOUT_ID);
199: sql.append(") VALUES (");
200:
201: Timestamp now = new Timestamp(System.currentTimeMillis());
202: Object val = "?";
203:
204: if (prepStmt) {
205: sql.append(val + ", " + val);
206: } else {
207: long millis = System.currentTimeMillis();
208:
209: sql.append("'Ken");
210: sql.append((++ldfNrFuerKennung) + "'");
211:
212: val = new Double(0.123456789) + "";
213:
214: sql.append(", '" + now.toString() + "'");
215: }
216:
217: for (int i = 1; i <= DECIMAL_FIELDS_PER_DATASET; i++) {
218: sql.append(", " + val);
219: }
220:
221: sql.append(")");
222:
223: String ret = sql.toString();
224:
225: System.out.println(ret);
226:
227: return ret;
228: }
229: }
|