001: /*
002: * UpdatingCommandTest.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.sql.commands;
013:
014: import java.io.File;
015: import java.io.FileOutputStream;
016: import java.io.OutputStream;
017: import java.io.Writer;
018: import java.sql.ResultSet;
019: import java.sql.Statement;
020: import junit.framework.TestCase;
021: import workbench.TestUtil;
022: import workbench.db.WbConnection;
023: import workbench.sql.DefaultStatementRunner;
024: import workbench.sql.StatementRunnerResult;
025: import workbench.storage.ResultInfo;
026: import workbench.storage.RowData;
027: import workbench.util.EncodingUtil;
028: import workbench.util.SqlUtil;
029:
030: /**
031: * @author support@sql-workbench.net
032: */
033: public class UpdatingCommandTest extends TestCase {
034: private TestUtil util;
035: private WbConnection connection;
036: private DefaultStatementRunner runner;
037:
038: public UpdatingCommandTest(String testName) {
039: super (testName);
040: try {
041: util = new TestUtil(testName);
042: util.prepareEnvironment();
043: } catch (Exception e) {
044: e.printStackTrace();
045: }
046: }
047:
048: public void setUp() throws Exception {
049: super .setUp();
050: util.emptyBaseDirectory();
051: runner = util.createConnectedStatementRunner();
052: connection = runner.getConnection();
053: }
054:
055: public void tearDown() throws Exception {
056: connection.disconnect();
057: super .tearDown();
058: }
059:
060: public void testInsertBlob() {
061: try {
062: Statement stmt = this .connection.createStatement();
063: stmt
064: .executeUpdate("CREATE MEMORY TABLE blob_test(nr integer, blob_data BINARY)");
065: stmt.close();
066:
067: final byte[] blobData = new byte[] { 1, 2, 3, 4, 5, 6 };
068: File blobFile = new File(util.getBaseDir(),
069: "blob_data.data");
070: OutputStream out = new FileOutputStream(blobFile);
071: out.write(blobData);
072: out.close();
073:
074: String sql = "-- read blob from file\ninsert into blob_test(nr, blob_data)\nvalues\n(1,{$blobfile='"
075: + blobFile.getName() + "'})";
076: runner.runStatement(sql, -1, -1);
077: StatementRunnerResult result = runner.getResult();
078: if (!result.isSuccess())
079: System.out
080: .println(result.getMessageBuffer().toString());
081: assertEquals("Insert not executed", true, result
082: .isSuccess());
083:
084: stmt = this .connection.createStatement();
085: ResultSet rs = stmt
086: .executeQuery("select nr, blob_data from blob_test");
087: if (rs.next()) {
088: ResultInfo info = new ResultInfo(rs.getMetaData(),
089: this .connection);
090: RowData data = new RowData(2);
091: data.read(rs, info);
092:
093: Object value = data.getValue(0);
094: int nr = ((Integer) value).intValue();
095: assertEquals("Wrong id inserted", 1, nr);
096:
097: value = data.getValue(1);
098: assertTrue(value instanceof byte[]);
099:
100: byte[] blob = (byte[]) value;
101: assertEquals("Wrong blob size retrieved",
102: blobData.length, blob.length);
103:
104: for (int i = 0; i < blob.length; i++) {
105: assertEquals("Wrong blob contents", blobData[i],
106: blob[i]);
107: }
108: } else {
109: fail("No data in table");
110: }
111: SqlUtil.closeAll(rs, stmt);
112:
113: } catch (Exception e) {
114: e.printStackTrace();
115: fail(e.getMessage());
116: }
117: }
118:
119: public void testInsertClob() {
120: try {
121: Statement stmt = this .connection.createStatement();
122: stmt
123: .executeUpdate("CREATE MEMORY TABLE clob_test(nr integer, clob_data LONGVARCHAR)");
124: stmt.close();
125:
126: final String clobData = "Clob data to be inserted";
127: File clobFile = new File(util.getBaseDir(),
128: "clob_data.data");
129: Writer w = EncodingUtil.createWriter(clobFile, "UTF8",
130: false);
131: w.write(clobData);
132: w.close();
133:
134: String sql = "-- read clob from file\ninsert into clob_test(nr, clob_data)\nvalues\n(1,{$clobfile='"
135: + clobFile.getName() + "' encoding='UTF-8'})";
136: runner.runStatement(sql, -1, -1);
137: StatementRunnerResult result = runner.getResult();
138: if (!result.isSuccess())
139: System.out
140: .println(result.getMessageBuffer().toString());
141: assertEquals("Insert not executed", true, result
142: .isSuccess());
143:
144: stmt = this .connection.createStatement();
145: ResultSet rs = stmt
146: .executeQuery("select nr, clob_data from clob_test");
147: if (rs.next()) {
148: int nr = rs.getInt(1);
149: assertEquals("Wrong id inserted", 1, nr);
150:
151: String value = rs.getString(2);
152: assertEquals("Wrong clob inserted", clobData, value);
153: } else {
154: fail("No data in table");
155: }
156: SqlUtil.closeAll(rs, stmt);
157: } catch (Exception e) {
158: e.printStackTrace();
159: fail(e.getMessage());
160: }
161: }
162:
163: public void testUpdate() {
164: try {
165: Statement stmt = this .connection.createStatement();
166: stmt
167: .executeUpdate("CREATE MEMORY TABLE update_test(nr integer primary key, some_data VARCHAR(10))");
168: stmt
169: .executeUpdate("insert into update_test (nr, some_data) values (1, 'one')");
170: stmt
171: .executeUpdate("insert into update_test (nr, some_data) values (2, 'two')");
172: stmt
173: .executeUpdate("insert into update_test (nr, some_data) values (3, 'three')");
174: stmt.close();
175:
176: String sql = "-- udpate one row\nupdate update_test set some_data = 'THREE' where nr = 3";
177: runner.runStatement(sql, -1, -1);
178: StatementRunnerResult result = runner.getResult();
179: if (!result.isSuccess())
180: System.out
181: .println(result.getMessageBuffer().toString());
182: assertEquals("Update not executed", true, result
183: .isSuccess());
184:
185: stmt = this .connection.createStatement();
186: ResultSet rs = stmt
187: .executeQuery("select some_data from update_test where nr = 3");
188: if (rs.next()) {
189: String value = rs.getString(1);
190: assertEquals("Wrong value updated", "THREE", value);
191: } else {
192: fail("No data in table");
193: }
194: rs.close();
195: rs = stmt
196: .executeQuery("select count(*) from update_test where nr = 3");
197: if (rs.next()) {
198: int count = rs.getInt(1);
199: assertEquals("Wrong row count", 1, count);
200: } else {
201: fail("No data in table");
202: }
203: SqlUtil.closeAll(rs, stmt);
204: } catch (Exception e) {
205: e.printStackTrace();
206: fail(e.getMessage());
207: }
208: }
209:
210: }
|