protected Statement createStatement(Connection c) throws SQLException {
Statement statement =
c.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
statement.setFetchSize(batchSize);
statement.setMaxRows(maxRows);
return statement;
}
@SuppressWarnings({"unchecked"})
public static void close(Statement statement) {
log.tracef("Closing prepared statement [%s]", statement);
try {
// if we are unable to "clean" the prepared statement,
// we do not close it
try {
if (statement.getMaxRows() != 0) {
statement.setMaxRows(0);
}
if (statement.getQueryTimeout() != 0) {
statement.setQueryTimeout(0);
}
} catch (SQLException sqle) {
// there was a problem "cleaning" the prepared statement
if (log.isDebugEnabled()) {
log.debugf("Exception clearing maxRows/queryTimeout [%s]", sqle.getMessage());
}
// EARLY EXIT!!!
return;
}
statement.close();
} catch (SQLException e) {
log.debugf("Unable to release JDBC statement [%s]", e.getMessage());
} catch (Exception e) {
// try to handle general errors more elegantly
log.debugf("Unable to release JDBC statement [%s]", e.getMessage());
}
}
// 列表记录集
public Vector<User> list(String where, int startRow, int rowCount) {
Vector<User> beans = new Vector<User>();
try {
String sql = "SELECT * FROM " + strTableName + " ";
if (where.length() > 0) sql = String.valueOf(sql) + String.valueOf(where);
Statement s =
db.getConnection()
.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
if (startRow != 0 && rowCount != 0) s.setMaxRows((startRow + rowCount) - 1);
ResultSet rs = s.executeQuery(sql);
if (rs != null && rs.next()) {
if (startRow != 0 && rowCount != 0) rs.absolute(startRow);
do {
User theBean = new User();
theBean = load(rs, false);
beans.addElement(theBean);
} while (rs.next());
}
rs.close();
s.close();
} catch (Exception ee) {
ee.printStackTrace();
}
return beans;
}
@Test
public void testRemoteExecuteMaxRow() throws Exception {
Statement statement = remoteConnection.createStatement();
statement.setMaxRows(2);
ResultSet resultSet = statement.executeQuery("select * from \"hr\".\"emps\"");
int count = 0;
while (resultSet.next()) {
++count;
}
assertThat(count, equalTo(2));
}
@Override
public void test() throws Exception {
deleteDb("limit");
Connection conn = getConnection("limit");
stat = conn.createStatement();
stat.execute("create table test(id int) as " + "select x from system_range(1, 10)");
for (int maxRows = 0; maxRows < 12; maxRows++) {
stat.setMaxRows(maxRows);
for (int limit = -2; limit < 12; limit++) {
for (int offset = -2; offset < 12; offset++) {
int l = limit < 0 ? 10 : Math.min(10, limit);
for (int d = 0; d < 2; d++) {
int m = maxRows <= 0 ? 10 : Math.min(10, maxRows);
int expected = Math.min(m, l);
if (offset > 0) {
expected = Math.max(0, Math.min(10 - offset, expected));
}
String s =
"select "
+ (d == 1 ? "distinct " : "")
+ " * from test limit "
+ (limit == -2 ? "null" : limit)
+ " offset "
+ (offset == -2 ? "null" : offset);
assertRow(expected, s);
String union = "(" + s + ") union (" + s + ")";
assertRow(expected, union);
m = maxRows <= 0 ? 20 : Math.min(20, maxRows);
if (offset > 0) {
l = Math.max(0, Math.min(10 - offset, l));
}
expected = Math.min(m, l * 2);
union = "(" + s + ") union all (" + s + ")";
assertRow(expected, union);
for (int unionLimit = -2; unionLimit < 5; unionLimit++) {
int e = unionLimit < 0 ? 20 : Math.min(20, unionLimit);
e = Math.min(expected, e);
String u = union + " limit " + (unionLimit == -2 ? "null" : unionLimit);
assertRow(e, u);
}
}
}
}
}
assertEquals(0, stat.executeUpdate("delete from test limit 0"));
assertEquals(1, stat.executeUpdate("delete from test limit 1"));
assertEquals(2, stat.executeUpdate("delete from test limit 2"));
assertEquals(7, stat.executeUpdate("delete from test limit null"));
stat.execute("insert into test select x from system_range(1, 10)");
assertEquals(10, stat.executeUpdate("delete from test limit -1"));
conn.close();
deleteDb("limit");
}
public Pid getFirst() throws Exception {
Pid pid = new Pid();
Connection connection = DatabaseHelper.getConnection();
Statement statement = connection.createStatement();
statement.setMaxRows(1);
ResultSet resultSet = statement.executeQuery(DatabaseHelper.GET_QUEUE_FIRST_ELEMENT_STATEMENT);
if (resultSet.next()) {
pid.setIdentifier(resultSet.getString("identifier"));
pid.setUrl(resultSet.getString("url"));
connection.close();
} else {
connection.close();
return null;
}
statement.close();
return pid;
}
@Override
public void test() throws Exception {
deleteDb("resultSet");
conn = getConnection("resultSet");
stat = conn.createStatement();
testUnwrap();
testReuseSimpleResult();
testUnsupportedOperations();
testAmbiguousColumnNames();
testInsertRowWithUpdatableResultSetDefault();
testBeforeFirstAfterLast();
testParseSpecialValues();
testSubstringPrecision();
testSubstringDataType();
testColumnLabelColumnName();
testAbsolute();
testFetchSize();
testOwnUpdates();
testUpdatePrimaryKey();
testFindColumn();
testColumnLength();
testArray();
testLimitMaxRows();
trace("max rows=" + stat.getMaxRows());
stat.setMaxRows(6);
trace("max rows after set to 6=" + stat.getMaxRows());
assertTrue(stat.getMaxRows() == 6);
testInt();
testVarchar();
testDecimal();
testDoubleFloat();
testDatetime();
testDatetimeWithCalendar();
testBlob();
testClob();
testAutoIncrement();
conn.close();
deleteDb("resultSet");
}
public List<Pid> getFirstBlock(int size) throws Exception {
List<Pid> pids = new ArrayList<Pid>();
Connection connection = DatabaseHelper.getConnection();
Statement statement = connection.createStatement();
statement.setMaxRows(size);
ResultSet resultSet = statement.executeQuery(DatabaseHelper.GET_QUEUE_FIRST_ELEMENT_STATEMENT);
while (resultSet.next()) {
Pid pid = new Pid();
pid.setIdentifier(resultSet.getString("identifier"));
pid.setUrl(resultSet.getString("url"));
pids.add(pid);
}
statement.close();
connection.close();
logger.debug("getFirstBlock of queue returning " + pids.size() + " pids");
return pids;
}
private void dumpTo(RelationalProfile.Table table, int indent)
throws SQLException, XMLStreamException {
String key =
resultSets.isEmpty() ? null : resultSets.peek().getString(table.linkColumn.link.name);
if (table.cached) {
if (key == null) return;
Map<String, String> result = table.cache.get(key);
startWrapper(table.name, indent);
for (RelationalProfile.Column column : table.columns) {
putValue(column, result, indent + 1);
}
endWrapper(table.name, indent);
} else {
Statement statement = connection.createStatement();
if (maxRows > 0) statement.setMaxRows(maxRows);
String sql = table.toQuery(key);
resultSets.push(statement.executeQuery(sql));
int count = 0;
while (resultSets.peek().next()) {
if (resultSets.size() == 1) {
System.out.println("=== Record " + (++count));
}
startWrapper(table.name, indent);
for (RelationalProfile.Column column : table.columns) {
putValue(column, resultSets.peek(), indent + 1);
}
for (RelationalProfile.Table childTable : profile.childTables(table)) {
if (childTable.wrap != null) {
startWrapper(childTable.wrap, indent + 1);
dumpTo(childTable, indent + 2);
endWrapper(table.name, indent + 1);
} else {
dumpTo(childTable, indent + 1);
}
}
endWrapper(table.name, indent);
}
resultSets.pop();
statement.close();
}
}
private void checkStatementExecute(Connection connection, boolean prepare, int maxRowCount)
throws SQLException {
final String sql =
"select * from (\n" + " values (1, 'a'), (null, 'b'), (3, 'c')) as t (c1, c2)";
final Statement statement;
final ResultSet resultSet;
final ParameterMetaData parameterMetaData;
if (prepare) {
final PreparedStatement ps = connection.prepareStatement(sql);
statement = ps;
ps.setMaxRows(maxRowCount);
parameterMetaData = ps.getParameterMetaData();
assertTrue(ps.execute());
resultSet = ps.getResultSet();
} else {
statement = connection.createStatement();
statement.setMaxRows(maxRowCount);
parameterMetaData = null;
assertTrue(statement.execute(sql));
resultSet = statement.getResultSet();
}
if (parameterMetaData != null) {
assertThat(parameterMetaData.getParameterCount(), equalTo(0));
}
final ResultSetMetaData metaData = resultSet.getMetaData();
assertEquals(2, metaData.getColumnCount());
assertEquals("C1", metaData.getColumnName(1));
assertEquals("C2", metaData.getColumnName(2));
for (int i = 0; i < maxRowCount || (maxRowCount == 0 && i < 3); i++) {
assertTrue(resultSet.next());
}
assertFalse(resultSet.next());
resultSet.close();
statement.close();
connection.close();
}
/**
* <code>getUserSites</code> returns a vector of SiteView's meeting the criteria of the
* parameters.
*
* @param pStoreId a <code>int</code> ID value (from Main DB) of Store selected
* @param pUserId a <code>int</code> ID value (from Main DB) of Reporting User selected (appUser
* if user is not selected or 0 - if user assigned to All Accounts( has property RepOA^))
* @param pNameTempl <code>String</code> NANE filter value
* @param nameBeginsFl <code>int</code> NANE filter type (QueryRequest.BEGINS or
* QueryRequest.CONTAINS)
* @param pCity <code>String</code> filter value for CITY
* @param pState <code>String</code> filter value for STATE
* @param pAccountIdv <code>IdVector</code> filter of AccountDimIds (null if no filter)
* @return a <code>SiteViewVector</code> value
* <p>/**
* ========================================================================================
*/
public SiteViewVector getUserSites(
int pStoreId,
int pUserId,
Integer pSiteDimId,
String pNameTempl,
int nameBeginsFl,
String pCity,
String pState,
IdVector pAccountIdv,
boolean showInactiveFl,
int pResultLimit)
throws RemoteException {
Connection conn = null;
SiteViewVector siteV = new SiteViewVector();
try {
String storeDimFilter =
"(select STORE_DIM_ID from DW_STORE_DIM \n" + " where STORE_ID = " + pStoreId + " ) \n";
String userFilterForAccounts = ""; // if user is not RepOA^ and accounts where not selected
if (pUserId > 0 && (pAccountIdv == null || pAccountIdv.size() == 0)) {
userFilterForAccounts = getUserFilterForAccounts(pUserId);
}
String siteNameFilter = getSearchSqlByFilterName("SITE_NAME", pNameTempl, nameBeginsFl);
String accountIdsFilter =
(pAccountIdv != null && pAccountIdv.size() > 0)
? "and ACCOUNT_DIM_ID in (" + convertToString(pAccountIdv) + ")"
: "";
String cityFilter =
(Utility.isSet(pCity)) ? " and UPPER(SITE_CITY) LIKE UPPER('" + pCity + "%')" : "";
String stateFilter =
(Utility.isSet(pState)) ? " and UPPER(SITE_STATE) LIKE UPPER('" + pState + "%')" : "";
// conn = getConnection();
conn = getReportConnection();
String sql =
"select SITE_DIM_ID, \n"
+ " ACCOUNT_DIM_ID, ACCOUNT_ID, \n"
+ " (Select JD_ACCOUNT_NAME from DW_ACCOUNT_DIM a \n"
+ " where a.ACCOUNT_DIM_ID = s.ACCOUNT_DIM_ID) JD_ACCOUNT_NAME, \n"
+ " SITE_ID, \n"
+ " SITE_NAME, SITE_STREET_ADDRESS, SITE_CITY, SITE_STATE, SITE_STATUS_CD \n"
+ "from DW_SITE_DIM s\n"
+ "where \n"
+ " STORE_DIM_ID = "
+ storeDimFilter
+ " \n"
+ userFilterForAccounts
+ accountIdsFilter
+ siteNameFilter
+ cityFilter
+ stateFilter
+ "order by SITE_NAME ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (pResultLimit != QueryRequest.UNLIMITED) {
stmt.setMaxRows(Constants.MAX_SITES_TO_RETURN);
}
while (rs.next()) {
SiteView sv = SiteView.createValue();
sv.setId(rs.getInt("SITE_DIM_ID"));
sv.setName(rs.getString("SITE_NAME"));
sv.setAccountId(rs.getInt("ACCOUNT_ID"));
sv.setAccountName(rs.getString("JD_ACCOUNT_NAME"));
sv.setAddress(rs.getString("SITE_STREET_ADDRESS"));
sv.setCity(rs.getString("SITE_CITY"));
sv.setState(rs.getString("SITE_STATE"));
sv.setStatus(rs.getString("SITE_STATUS_CD"));
String targetS = null; // rs.getString("SITE_RANK");
int target = 0;
if (targetS != null) {
try {
target = Integer.parseInt(targetS);
} catch (NumberFormatException e) {
}
}
sv.setTargetFacilityRank(target);
siteV.add(sv);
}
stmt.close();
} catch (Exception exc) {
exc.printStackTrace();
throw processException(exc);
} finally {
closeConnection(conn);
}
return siteV;
}
@Test(expected = SQLFeatureNotSupportedException.class)
public void assertSetMaxRows() throws SQLException {
actual.setMaxRows(0);
}
/**
* DOCUMENT ME!
*
* @throws SQLException DOCUMENT ME!
*/
public void testAccessorsAndMutators() throws SQLException {
assertTrue(
"Connection can not be null, and must be same connection", stmt.getConnection() == conn);
// Set max rows, to exercise code in execute(), executeQuery() and executeUpdate()
Statement accessorStmt = null;
try {
accessorStmt = conn.createStatement();
accessorStmt.setMaxRows(1);
accessorStmt.setMaxRows(0); // FIXME, test that this actually affects rows returned
accessorStmt.setMaxFieldSize(255);
assertTrue("Max field size should match what was set", accessorStmt.getMaxFieldSize() == 255);
try {
accessorStmt.setMaxFieldSize(Integer.MAX_VALUE);
fail("Should not be able to set max field size > max_packet_size");
} /* ignore */ catch (SQLException sqlEx) {;
}
accessorStmt.setCursorName("undef");
accessorStmt.setEscapeProcessing(true);
accessorStmt.setFetchDirection(java.sql.ResultSet.FETCH_FORWARD);
int fetchDirection = accessorStmt.getFetchDirection();
assertTrue(
"Set fetch direction != get fetch direction",
fetchDirection == java.sql.ResultSet.FETCH_FORWARD);
try {
accessorStmt.setFetchDirection(Integer.MAX_VALUE);
fail("Should not be able to set fetch direction to invalid value");
} /* ignore */ catch (SQLException sqlEx) {;
}
try {
accessorStmt.setMaxRows(50000000 + 10);
fail("Should not be able to set max rows > 50000000");
} /* ignore */ catch (SQLException sqlEx) {;
}
try {
accessorStmt.setMaxRows(Integer.MIN_VALUE);
fail("Should not be able to set max rows < 0");
} /* ignore */ catch (SQLException sqlEx) {;
}
int fetchSize = stmt.getFetchSize();
try {
accessorStmt.setMaxRows(4);
accessorStmt.setFetchSize(Integer.MAX_VALUE);
fail("Should not be able to set FetchSize > max rows");
} /* ignore */ catch (SQLException sqlEx) {;
}
try {
accessorStmt.setFetchSize(-2);
fail("Should not be able to set FetchSize < 0");
} /* ignore */ catch (SQLException sqlEx) {;
}
assertTrue(
"Fetch size before invalid setFetchSize() calls should match fetch size now",
fetchSize == stmt.getFetchSize());
} finally {
if (accessorStmt != null) {
try {
accessorStmt.close();
} /* ignore */ catch (SQLException sqlEx) {;
}
accessorStmt = null;
}
}
}
/**
* execute sql query
*
* @throws Exception
*/
private void executeQuery() {
Connection con = null;
try {
InputStream catExtIs =
ScriptableMondrianDrillThroughTableModel.class
.getClassLoader()
.getResourceAsStream("/" + catalogExtension);
if (catExtIs != null) {
Digester catExtDigester = new Digester();
catExtDigester.setClassLoader(
ScriptableMondrianDrillThroughTableModel.class.getClassLoader());
catExtDigester.push(this);
catExtDigester.addSetProperties("extension");
catExtDigester.addObjectCreate(
"extension/script", "com.tonbeller.jpivot.mondrian.script.ScriptColumn");
catExtDigester.addSetProperties("extension/script");
catExtDigester.addSetNext("extension/script", "addScript");
catExtDigester.parse(catExtIs);
URL scriptsBaseURL =
Thread.currentThread().getContextClassLoader().getResource(scriptRootUrl);
scriptEngine = new GroovyScriptEngine(new URL[] {scriptsBaseURL});
}
con = getConnection();
Statement s = con.createStatement();
s.setMaxRows(maxResults);
ResultSet rs = s.executeQuery(sql);
ResultSetMetaData md = rs.getMetaData();
int numCols = md.getColumnCount();
List columnTitlesList = new ArrayList();
// set column headings
for (int i = 0; i < numCols; i++) {
// columns are 1 based
columnTitlesList.add(i, md.getColumnName(i + 1));
}
// loop on script columns
for (ListIterator sIt = scripts.listIterator(); sIt.hasNext(); ) {
final ScriptColumn sc = (ScriptColumn) sIt.next();
columnTitlesList.add(sc.getPosition() - 1, sc.getTitle());
}
columnTitles = (String[]) columnTitlesList.toArray(new String[0]);
// loop through rows
List tempRows = new ArrayList();
Map scriptInput = new HashMap();
Binding binding = new Binding();
while (rs.next()) {
List rowList = new ArrayList();
scriptInput.clear();
// loop on columns, 1 based
for (int i = 0; i < numCols; i++) {
rowList.add(i, rs.getObject(i + 1));
scriptInput.put(columnTitles[i], rs.getObject(i + 1));
}
binding.setVariable("input", scriptInput);
// loop on script columns
for (ListIterator sIt = scripts.listIterator(); sIt.hasNext(); ) {
final ScriptColumn sc = (ScriptColumn) sIt.next();
scriptEngine.run(sc.getFile(), binding);
final Object output = binding.getVariable("output");
if (output instanceof Map) {
Map outMap = (Map) output;
rowList.add(
sc.getPosition() - 1,
new DefaultCell((String) outMap.get("URL"), (String) outMap.get("Value")));
} else if (output instanceof String) {
rowList.add(sc.getPosition() - 1, (String) output);
} else {
throw new Exception("Unknown groovy script return type (not a Map nor String).");
}
}
tempRows.add(new DefaultTableRow(rowList.toArray()));
}
rs.close();
rows = (TableRow[]) tempRows.toArray(new TableRow[0]);
} catch (Exception e) {
e.printStackTrace();
logger.error("?", e);
// problem occured, set table model to zero size
rows = new TableRow[1];
columnTitles = new String[1];
columnTitles[0] = "An error occured";
Object[] row = new Object[1];
row[0] = e.toString();
rows[0] = new DefaultTableRow(row);
ready = false;
return;
} finally {
try {
con.close();
} catch (Exception e1) {
// ignore
}
}
ready = true;
}
@Override
public void setMaxRows(int max) throws SQLException {
stat.setMaxRows(max);
}
@Override
public void setMaxRows(int max) throws SQLException {
rawStatement.setMaxRows(max);
}
private void _runSelect(Connection conn, DaoStatement st) throws SQLException {
Object[][] paramMatrix = st.getParamMatrix();
// -------------------------------------------------
// 以下代码,就为了该死的游标分页!!
// -------------------------------------------------
int startRow = -1;
int lastRow = -1;
if (st.getContext().getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE) {
Pager pager = st.getContext().getPager();
if (pager != null) {
startRow = pager.getOffset();
lastRow = pager.getOffset() + pager.getPageSize();
}
}
// -------------------------------------------------
// 生成 Sql 语句
String sql = st.toPreparedStatement();
// 打印调试信息
ResultSet rs = null;
Statement stat = null;
try {
// 木有参数，直接运行
if (null == paramMatrix || paramMatrix.length == 0 || paramMatrix[0].length == 0) {
if (log.isDebugEnabled()) log.debug(st);
stat = conn.createStatement(st.getContext().getResultSetType(), ResultSet.CONCUR_READ_ONLY);
if (lastRow > 0) stat.setMaxRows(lastRow); // 游标分页,现在总行数
if (st.getContext().getFetchSize() > 0) stat.setFetchSize(st.getContext().getFetchSize());
rs = stat.executeQuery(sql);
}
// 有参数，用缓冲语句
else {
// 打印调试信息
if (paramMatrix.length > 1) {
if (log.isWarnEnabled())
log.warnf("Drop last %d rows parameters for:\n%s", paramMatrix.length - 1, st);
}
if (log.isDebugEnabled()) {
log.debug(st);
}
// 准备运行语句
ValueAdaptor[] adaptors = st.getAdaptors();
// 创建语句并设置参数
stat =
conn.prepareStatement(
sql, st.getContext().getResultSetType(), ResultSet.CONCUR_READ_ONLY);
if (lastRow > 0) stat.setMaxRows(lastRow);
for (int i = 0; i < paramMatrix[0].length; i++) {
adaptors[i].set((PreparedStatement) stat, paramMatrix[0][i], i + 1);
}
rs = ((PreparedStatement) stat).executeQuery();
}
if (startRow > 0) rs.absolute(startRow);
// 执行回调
st.onAfter(conn, rs);
} finally {
Daos.safeClose(stat, rs);
}
// 打印更详细的调试信息
if (log.isTraceEnabled()) log.trace("...DONE");
}
/** Executes the current statement, and handles any SQLException. */
public void execute() {
assert state == State.FRESH : "cannot re-execute";
state = State.ACTIVE;
String status = "failed";
Statement statement = null;
try {
this.jdbcConnection = dataSource.getConnection();
querySemaphore.enter();
haveSemaphore = true;
// Trace start of execution.
if (RolapUtil.SQL_LOGGER.isDebugEnabled()) {
StringBuilder sqllog = new StringBuilder();
sqllog.append(id).append(": ").append(locus.component).append(": executing sql [");
if (sql.indexOf('\n') >= 0) {
// SQL appears to be formatted as multiple lines. Make it
// start on its own line.
sqllog.append("\n");
}
sqllog.append(sql);
sqllog.append(']');
RolapUtil.SQL_LOGGER.debug(sqllog.toString());
}
// Execute hook.
RolapUtil.ExecuteQueryHook hook = RolapUtil.getHook();
if (hook != null) {
hook.onExecuteQuery(sql);
}
startTimeNanos = System.nanoTime();
startTimeMillis = System.currentTimeMillis();
if (resultSetType < 0 || resultSetConcurrency < 0) {
statement = jdbcConnection.createStatement();
} else {
statement = jdbcConnection.createStatement(resultSetType, resultSetConcurrency);
}
if (maxRows > 0) {
statement.setMaxRows(maxRows);
}
// First make sure to register with the execution instance.
locus.execution.registerStatement(locus, statement);
locus
.getServer()
.getMonitor()
.sendEvent(
new SqlStatementStartEvent(
startTimeMillis, id, locus, sql, getPurpose(), getCellRequestCount()));
this.resultSet = statement.executeQuery(sql);
// skip to first row specified in request
this.state = State.ACTIVE;
if (firstRowOrdinal > 0) {
if (resultSetType == ResultSet.TYPE_FORWARD_ONLY) {
for (int i = 0; i < firstRowOrdinal; ++i) {
if (!this.resultSet.next()) {
this.state = State.DONE;
break;
}
}
} else {
if (!this.resultSet.absolute(firstRowOrdinal)) {
this.state = State.DONE;
}
}
}
long timeMillis = System.currentTimeMillis();
long timeNanos = System.nanoTime();
final long executeNanos = timeNanos - startTimeNanos;
final long executeMillis = executeNanos / 1000000;
Util.addDatabaseTime(executeMillis);
status = ", exec " + executeMillis + " ms";
locus
.getServer()
.getMonitor()
.sendEvent(
new SqlStatementExecuteEvent(timeMillis, id, locus, sql, getPurpose(), executeNanos));
// Compute accessors. They ensure that we use the most efficient
// method (e.g. getInt, getDouble, getObject) for the type of the
// column. Even if you are going to box the result into an object,
// it is better to use getInt than getObject; the latter might
// return something daft like a BigDecimal (does, on the Oracle JDBC
// driver).
accessors.clear();
for (Type type : guessTypes()) {
accessors.add(createAccessor(accessors.size(), type));
}
} catch (Throwable e) {
status = ", failed (" + e + ")";
Util.close(resultSet, statement, jdbcConnection);
if (haveSemaphore) {
haveSemaphore = false;
querySemaphore.leave();
}
if (e instanceof Error) {
throw (Error) e;
} else {
throw handle(e);
}
} finally {
RolapUtil.SQL_LOGGER.debug(id + ": " + status);
if (RolapUtil.LOGGER.isDebugEnabled()) {
RolapUtil.LOGGER.debug(locus.component + ": executing sql [" + sql + "]" + status);
}
}
}
public void actionPerformed(ActionEvent ev) {
String s = ev.getActionCommand();
if (s == null) {
if (ev.getSource() instanceof JMenuItem) {
JMenuItem i;
s = ((JMenuItem) ev.getSource()).getText();
}
}
/*
// button replace by toolbar
if (s.equals("Execute")) {
execute();
} else
*/
if (s.equals("Exit")) {
windowClosing(null);
} else if (s.equals("Transfer")) {
Transfer.work(null);
} else if (s.equals("Dump")) {
Transfer.work(new String[] {"-d"});
} else if (s.equals("Restore")) {
Transfer.work(new String[] {"-r"});
} else if (s.equals("Logging on")) {
javaSystem.setLogToSystem(true);
} else if (s.equals("Logging off")) {
javaSystem.setLogToSystem(false);
} else if (s.equals("Refresh Tree")) {
refreshTree();
} else if (s.startsWith("#")) {
int i = Integer.parseInt(s.substring(1));
txtCommand.setText(sRecent[i]);
} else if (s.equals("Connect...")) {
connect(ConnectionDialogSwing.createConnection(fMain, "Connect"));
refreshTree();
} else if (s.equals("Results in Grid")) {
iResult = 0;
pResult.removeAll();
pResult.add(gScrollPane, BorderLayout.CENTER);
pResult.doLayout();
gResult.fireTableChanged(null);
pResult.repaint();
} else if (s.equals("Open Script...")) {
JFileChooser f = new JFileChooser(".");
f.setDialogTitle("Open Script...");
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setCurrentDirectory(new File(defDirectory));
}
int option = f.showOpenDialog(fMain);
if (option == JFileChooser.APPROVE_OPTION) {
File file = f.getSelectedFile();
if (file != null) {
StringBuffer buf = new StringBuffer();
ifHuge = DatabaseManagerCommon.readFile(file.getAbsolutePath());
if (4096 <= ifHuge.length()) {
buf.append("This huge file cannot be edited. Please execute\n");
txtCommand.setText(buf.toString());
} else {
txtCommand.setText(ifHuge);
}
}
}
} else if (s.equals("Save Script...")) {
JFileChooser f = new JFileChooser(".");
f.setDialogTitle("Save Script");
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setCurrentDirectory(new File(defDirectory));
}
int option = f.showSaveDialog(fMain);
if (option == JFileChooser.APPROVE_OPTION) {
File file = f.getSelectedFile();
if (file != null) {
DatabaseManagerCommon.writeFile(file.getAbsolutePath(), txtCommand.getText());
}
}
} else if (s.equals("Save Result...")) {
JFileChooser f = new JFileChooser(".");
f.setDialogTitle("Save Result...");
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setCurrentDirectory(new File(defDirectory));
}
int option = f.showSaveDialog(fMain);
if (option == JFileChooser.APPROVE_OPTION) {
File file = f.getSelectedFile();
if (file != null) {
showResultInText();
DatabaseManagerCommon.writeFile(file.getAbsolutePath(), txtResult.getText());
}
}
} else if (s.equals("Results in Text")) {
iResult = 1;
pResult.removeAll();
pResult.add(txtResultScroll, BorderLayout.CENTER);
pResult.doLayout();
showResultInText();
pResult.repaint();
} else if (s.equals("AutoCommit on")) {
try {
cConn.setAutoCommit(true);
} catch (SQLException e) {
}
} else if (s.equals("AutoCommit off")) {
try {
cConn.setAutoCommit(false);
} catch (SQLException e) {
}
} else if (s.equals("Commit")) {
try {
cConn.commit();
} catch (SQLException e) {
}
} else if (s.equals("Insert test data")) {
insertTestData();
} else if (s.equals("Rollback")) {
try {
cConn.rollback();
} catch (SQLException e) {
}
} else if (s.equals("Disable MaxRows")) {
try {
sStatement.setMaxRows(0);
} catch (SQLException e) {
}
} else if (s.equals("Set MaxRows to 100")) {
try {
sStatement.setMaxRows(100);
} catch (SQLException e) {
}
} else if (s.equals("SELECT")) {
showHelp(DatabaseManagerCommon.selectHelp);
} else if (s.equals("INSERT")) {
showHelp(DatabaseManagerCommon.insertHelp);
} else if (s.equals("UPDATE")) {
showHelp(DatabaseManagerCommon.updateHelp);
} else if (s.equals("DELETE")) {
showHelp(DatabaseManagerCommon.deleteHelp);
} else if (s.equals("CREATE TABLE")) {
showHelp(DatabaseManagerCommon.createTableHelp);
} else if (s.equals("DROP TABLE")) {
showHelp(DatabaseManagerCommon.dropTableHelp);
} else if (s.equals("CREATE INDEX")) {
showHelp(DatabaseManagerCommon.createIndexHelp);
} else if (s.equals("DROP INDEX")) {
showHelp(DatabaseManagerCommon.dropIndexHelp);
} else if (s.equals("CHECKPOINT")) {
showHelp(DatabaseManagerCommon.checkpointHelp);
} else if (s.equals("SCRIPT")) {
showHelp(DatabaseManagerCommon.scriptHelp);
} else if (s.equals("SHUTDOWN")) {
showHelp(DatabaseManagerCommon.shutdownHelp);
} else if (s.equals("SET")) {
showHelp(DatabaseManagerCommon.setHelp);
} else if (s.equals("Test Script")) {
showHelp(DatabaseManagerCommon.testHelp);
}
}
/**
* Method declaration
*
* @param ev
*/
public void actionPerformed(ActionEvent ev) {
String s = ev.getActionCommand();
if (s == null) {
if (ev.getSource() instanceof MenuItem) {
MenuItem i;
s = ((MenuItem) ev.getSource()).getLabel();
}
}
if (s.equals("Execute")) {
execute();
} else if (s.equals("Exit")) {
windowClosing(null);
} else if (s.equals("Transfer")) {
Transfer.work(null);
} else if (s.equals("Dump")) {
Transfer.work(new String[] {"-d"});
/* NB - 26052002 Restore is not implemented yet in the transfer tool */
/*
} else if (s.equals("Restore")) {
Transfer.work(new String[]{"-r"});
*/
} else if (s.equals("Logging on")) {
jdbcSystem.setLogToSystem(true);
} else if (s.equals("Logging off")) {
jdbcSystem.setLogToSystem(false);
} else if (s.equals("Refresh Tree")) {
refreshTree();
} else if (s.startsWith("#")) {
int i = Integer.parseInt(s.substring(1));
txtCommand.setText(sRecent[i]);
} else if (s.equals("Connect...")) {
connect(ConnectionDialog.createConnection(fMain, "Connect"));
refreshTree();
} else if (s.equals("Results in Grid")) {
iResult = 0;
pResult.removeAll();
pResult.add("Center", gResult);
pResult.doLayout();
} else if (s.equals("Open Script...")) {
FileDialog f = new FileDialog(fMain, "Open Script", FileDialog.LOAD);
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setDirectory(defDirectory);
}
f.show();
String file = f.getFile();
if (file != null) {
txtCommand.setText(DatabaseManagerCommon.readFile(f.getDirectory() + file));
}
} else if (s.equals("Save Script...")) {
FileDialog f = new FileDialog(fMain, "Save Script", FileDialog.SAVE);
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setDirectory(defDirectory);
}
f.show();
String file = f.getFile();
if (file != null) {
DatabaseManagerCommon.writeFile(f.getDirectory() + file, txtCommand.getText());
}
} else if (s.equals("Save Result...")) {
FileDialog f = new FileDialog(fMain, "Save Result", FileDialog.SAVE);
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setDirectory(defDirectory);
}
f.show();
String file = f.getFile();
if (file != null) {
showResultInText();
DatabaseManagerCommon.writeFile(f.getDirectory() + file, txtResult.getText());
}
} else if (s.equals("Results in Text")) {
iResult = 1;
pResult.removeAll();
pResult.add("Center", txtResult);
pResult.doLayout();
showResultInText();
} else if (s.equals("AutoCommit on")) {
try {
cConn.setAutoCommit(true);
} catch (SQLException e) {
}
} else if (s.equals("AutoCommit off")) {
try {
cConn.setAutoCommit(false);
} catch (SQLException e) {
}
} else if (s.equals("Enlarge Tree")) {
Dimension d = tTree.getMinimumSize();
d.width += 20;
tTree.setMinimumSize(d);
fMain.pack();
} else if (s.equals("Shrink Tree")) {
Dimension d = tTree.getMinimumSize();
d.width -= 20;
if (d.width >= 0) {
tTree.setMinimumSize(d);
}
fMain.pack();
} else if (s.equals("Enlarge Command")) {
txtCommand.setRows(txtCommand.getRows() + 1);
fMain.pack();
} else if (s.equals("Shrink Command")) {
int i = txtCommand.getRows() - 1;
txtCommand.setRows(i < 1 ? 1 : i);
fMain.pack();
} else if (s.equals("Commit")) {
try {
cConn.commit();
} catch (SQLException e) {
}
} else if (s.equals("Insert test data")) {
insertTestData();
} else if (s.equals("Rollback")) {
try {
cConn.rollback();
} catch (SQLException e) {
}
} else if (s.equals("Disable MaxRows")) {
try {
sStatement.setMaxRows(0);
} catch (SQLException e) {
}
} else if (s.equals("Set MaxRows to 100")) {
try {
sStatement.setMaxRows(100);
} catch (SQLException e) {
}
} else if (s.equals("SELECT")) {
showHelp(DatabaseManagerCommon.selectHelp);
} else if (s.equals("INSERT")) {
showHelp(DatabaseManagerCommon.insertHelp);
} else if (s.equals("UPDATE")) {
showHelp(DatabaseManagerCommon.updateHelp);
} else if (s.equals("DELETE")) {
showHelp(DatabaseManagerCommon.deleteHelp);
} else if (s.equals("CREATE TABLE")) {
showHelp(DatabaseManagerCommon.createTableHelp);
} else if (s.equals("DROP TABLE")) {
showHelp(DatabaseManagerCommon.dropTableHelp);
} else if (s.equals("CREATE INDEX")) {
showHelp(DatabaseManagerCommon.createIndexHelp);
} else if (s.equals("DROP INDEX")) {
showHelp(DatabaseManagerCommon.dropIndexHelp);
} else if (s.equals("CHECKPOINT")) {
showHelp(DatabaseManagerCommon.checkpointHelp);
} else if (s.equals("SCRIPT")) {
showHelp(DatabaseManagerCommon.scriptHelp);
} else if (s.equals("SHUTDOWN")) {
showHelp(DatabaseManagerCommon.shutdownHelp);
} else if (s.equals("SET")) {
showHelp(DatabaseManagerCommon.setHelp);
} else if (s.equals("Test Script")) {
showHelp(DatabaseManagerCommon.testHelp);
}
}
private void testInt() throws SQLException {
trace("Test INT");
ResultSet rs;
Object o;
stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY,VALUE INT)");
stat.execute("INSERT INTO TEST VALUES(1,-1)");
stat.execute("INSERT INTO TEST VALUES(2,0)");
stat.execute("INSERT INTO TEST VALUES(3,1)");
stat.execute("INSERT INTO TEST VALUES(4," + Integer.MAX_VALUE + ")");
stat.execute("INSERT INTO TEST VALUES(5," + Integer.MIN_VALUE + ")");
stat.execute("INSERT INTO TEST VALUES(6,NULL)");
// this should not be read - maxrows=6
stat.execute("INSERT INTO TEST VALUES(7,NULL)");
// MySQL compatibility (is this required?)
// rs=stat.executeQuery("SELECT * FROM TEST T ORDER BY ID");
// check(rs.findColumn("T.ID"), 1);
// check(rs.findColumn("T.NAME"), 2);
rs = stat.executeQuery("SELECT *, NULL AS N FROM TEST ORDER BY ID");
// MySQL compatibility
assertEquals(1, rs.findColumn("TEST.ID"));
assertEquals(2, rs.findColumn("TEST.VALUE"));
ResultSetMetaData meta = rs.getMetaData();
assertEquals(3, meta.getColumnCount());
assertEquals("resultSet".toUpperCase(), meta.getCatalogName(1));
assertTrue("PUBLIC".equals(meta.getSchemaName(2)));
assertTrue("TEST".equals(meta.getTableName(1)));
assertTrue("ID".equals(meta.getColumnName(1)));
assertTrue("VALUE".equals(meta.getColumnName(2)));
assertTrue(!meta.isAutoIncrement(1));
assertTrue(meta.isCaseSensitive(1));
assertTrue(meta.isSearchable(1));
assertFalse(meta.isCurrency(1));
assertTrue(meta.getColumnDisplaySize(1) > 0);
assertTrue(meta.isSigned(1));
assertTrue(meta.isSearchable(2));
assertEquals(ResultSetMetaData.columnNoNulls, meta.isNullable(1));
assertFalse(meta.isReadOnly(1));
assertTrue(meta.isWritable(1));
assertFalse(meta.isDefinitelyWritable(1));
assertTrue(meta.getColumnDisplaySize(1) > 0);
assertTrue(meta.getColumnDisplaySize(2) > 0);
assertEquals(null, meta.getColumnClassName(3));
assertTrue(rs.getRow() == 0);
assertResultSetMeta(
rs,
3,
new String[] {"ID", "VALUE", "N"},
new int[] {Types.INTEGER, Types.INTEGER, Types.NULL},
new int[] {10, 10, 1},
new int[] {0, 0, 0});
rs.next();
assertEquals(ResultSet.CONCUR_READ_ONLY, rs.getConcurrency());
assertEquals(ResultSet.FETCH_FORWARD, rs.getFetchDirection());
trace("default fetch size=" + rs.getFetchSize());
// 0 should be an allowed value (but it's not defined what is actually
// means)
rs.setFetchSize(0);
assertThrows(ErrorCode.INVALID_VALUE_2, rs).setFetchSize(-1);
// fetch size 100 is bigger than maxrows - not allowed
assertThrows(ErrorCode.INVALID_VALUE_2, rs).setFetchSize(100);
rs.setFetchSize(6);
assertTrue(rs.getRow() == 1);
assertEquals(2, rs.findColumn("VALUE"));
assertEquals(2, rs.findColumn("value"));
assertEquals(2, rs.findColumn("Value"));
assertEquals(2, rs.findColumn("Value"));
assertEquals(1, rs.findColumn("ID"));
assertEquals(1, rs.findColumn("id"));
assertEquals(1, rs.findColumn("Id"));
assertEquals(1, rs.findColumn("iD"));
assertTrue(rs.getInt(2) == -1 && !rs.wasNull());
assertTrue(rs.getInt("VALUE") == -1 && !rs.wasNull());
assertTrue(rs.getInt("value") == -1 && !rs.wasNull());
assertTrue(rs.getInt("Value") == -1 && !rs.wasNull());
assertTrue(rs.getString("Value").equals("-1") && !rs.wasNull());
o = rs.getObject("value");
trace(o.getClass().getName());
assertTrue(o instanceof Integer);
assertTrue(((Integer) o).intValue() == -1);
o = rs.getObject(2);
trace(o.getClass().getName());
assertTrue(o instanceof Integer);
assertTrue(((Integer) o).intValue() == -1);
assertTrue(rs.getBoolean("Value"));
assertTrue(rs.getByte("Value") == (byte) -1);
assertTrue(rs.getShort("Value") == (short) -1);
assertTrue(rs.getLong("Value") == -1);
assertTrue(rs.getFloat("Value") == -1.0);
assertTrue(rs.getDouble("Value") == -1.0);
assertTrue(rs.getString("Value").equals("-1") && !rs.wasNull());
assertTrue(rs.getInt("ID") == 1 && !rs.wasNull());
assertTrue(rs.getInt("id") == 1 && !rs.wasNull());
assertTrue(rs.getInt("Id") == 1 && !rs.wasNull());
assertTrue(rs.getInt(1) == 1 && !rs.wasNull());
rs.next();
assertTrue(rs.getRow() == 2);
assertTrue(rs.getInt(2) == 0 && !rs.wasNull());
assertTrue(!rs.getBoolean(2));
assertTrue(rs.getByte(2) == 0);
assertTrue(rs.getShort(2) == 0);
assertTrue(rs.getLong(2) == 0);
assertTrue(rs.getFloat(2) == 0.0);
assertTrue(rs.getDouble(2) == 0.0);
assertTrue(rs.getString(2).equals("0") && !rs.wasNull());
assertTrue(rs.getInt(1) == 2 && !rs.wasNull());
rs.next();
assertTrue(rs.getRow() == 3);
assertTrue(rs.getInt("ID") == 3 && !rs.wasNull());
assertTrue(rs.getInt("VALUE") == 1 && !rs.wasNull());
rs.next();
assertTrue(rs.getRow() == 4);
assertTrue(rs.getInt("ID") == 4 && !rs.wasNull());
assertTrue(rs.getInt("VALUE") == Integer.MAX_VALUE && !rs.wasNull());
rs.next();
assertTrue(rs.getRow() == 5);
assertTrue(rs.getInt("id") == 5 && !rs.wasNull());
assertTrue(rs.getInt("value") == Integer.MIN_VALUE && !rs.wasNull());
assertTrue(rs.getString(1).equals("5") && !rs.wasNull());
rs.next();
assertTrue(rs.getRow() == 6);
assertTrue(rs.getInt("id") == 6 && !rs.wasNull());
assertTrue(rs.getInt("value") == 0 && rs.wasNull());
assertTrue(rs.getInt(2) == 0 && rs.wasNull());
assertTrue(rs.getInt(1) == 6 && !rs.wasNull());
assertTrue(rs.getString(1).equals("6") && !rs.wasNull());
assertTrue(rs.getString(2) == null && rs.wasNull());
o = rs.getObject(2);
assertTrue(o == null);
assertTrue(rs.wasNull());
assertFalse(rs.next());
assertEquals(0, rs.getRow());
// there is one more row, but because of setMaxRows we don't get it
stat.execute("DROP TABLE TEST");
stat.setMaxRows(0);
}
/** Create the <TT>IDataSet</TT> to be displayed in this tab. */
protected IDataSet createDataSet() throws DataSetException {
final ISession session = getSession();
final ISQLConnection conn = session.getSQLConnection();
ISQLDatabaseMetaData md = session.getMetaData();
try {
final Statement stmt = conn.createStatement();
try {
final SessionProperties props = session.getProperties();
if (props.getContentsLimitRows()) {
try {
stmt.setMaxRows(props.getContentsNbrRowsToShow());
} catch (Exception ex) {
s_log.error("Error on Statement.setMaxRows()", ex);
}
}
final ITableInfo ti = getTableInfo();
/**
* When the SessionProperties are set to read-only (either table or text) but the user has
* selected "Make Editable" on the Popup menu, we want to limit the edit capability to only
* that table, and only for as long as the user is looking at that one table. When the user
* switches away to another table, that new table should not be editable.
*/
final String currentTableName = ti.getQualifiedName();
if (!currentTableName.equals(previousTableName)) {
previousTableName = currentTableName; // needed to prevent an infinite loop
_dataSetUpdateableTableModel.setEditModeForced(false);
/**
* Tell the GUI to rebuild itself. Unfortunately, this has the side effect of calling this
* same function another time. The second call does not seem to be a problem, but we need
* to have reset the previousTableName before makeing this call or we will be in an
* infinite loop.
*/
// props.forceTableContentsOutputClassNameChange();
}
/**
* If the table has a pseudo-column that is the best unique identifier for the rows (like
* Oracle's rowid), then we want to include that field in the query so that it will be
* available if the user wants to edit the data later.
*/
String pseudoColumn = "";
try {
BestRowIdentifier[] rowIDs = md.getBestRowIdentifier(ti);
for (int i = 0; i < rowIDs.length; ++i) {
short pseudo = rowIDs[i].getPseudoColumn();
if (pseudo == DatabaseMetaData.bestRowPseudo) {
pseudoColumn = " ," + rowIDs[i].getColumnName();
break;
}
}
}
// Some DBMS's (EG Think SQL) throw an exception on a call to
// getBestRowIdentifier.
catch (Throwable th) {
if (s_log.isDebugEnabled()) {
s_log.debug("getBestRowIdentifier not supported for table " + currentTableName, th);
}
}
// TODO: - Col - Add method to Databasemetadata that returns array
// of objects for getBestRowIdentifier. For PostgreSQL put this kludge in
// the new function. THis way all the kludges are kept in one place.
//
// KLUDGE!!!!!!
//
// For some DBs (e.g. PostgreSQL) there is actually a pseudo-column
// providing the rowId, but the getBestRowIdentifier function is not
// implemented. This kludge hardcodes the knowledge that specific
// DBs use a specific pseudo-column. Additionally, as of pg 8.1,
// you must create the table using "WITH OID" appended to the create
// statement. Otherwise, OID column is not available by default.
//
if (pseudoColumn.length() == 0) {
if (DialectFactory.isPostgreSQL(md)) {
pseudoColumn = ", oid";
}
if (DialectFactory.isOracle(md)) {
pseudoColumn = ", ROWID";
}
}
ResultSet rs = null;
try {
// Note. Some DBMSs such as Oracle do not allow:
// "select *, rowid from table"
// You cannot have any column name in the columns clause
// if you have * in there. Aliasing the table name seems to
// be the best way to get around the problem.
final StringBuffer buf = new StringBuffer();
buf.append("select tbl.*")
.append(pseudoColumn)
.append(" from ")
.append(ti.getQualifiedName())
.append(" tbl");
String clause =
_sqlFilterClauses.get(WhereClausePanel.getClauseIdentifier(), ti.getQualifiedName());
if ((clause != null) && (clause.length() > 0)) {
buf.append(" where ").append(clause);
}
clause =
_sqlFilterClauses.get(
OrderByClausePanel.getClauseIdentifier(), ti.getQualifiedName());
if ((clause != null) && (clause.length() > 0)) {
buf.append(" order by ").append(clause);
}
if (s_log.isDebugEnabled()) {
s_log.debug("createDataSet running SQL: " + buf.toString());
}
showWaitDialog(stmt);
rs = stmt.executeQuery(buf.toString());
} catch (SQLException ex) {
if (s_log.isDebugEnabled()) {
s_log.debug("createDataSet: exception from pseudocolumn query - " + ex, ex);
}
// We assume here that if the pseudoColumn was used in the query,
// then it was likely to have caused the SQLException. If not,
// (length == 0), then retrying the query won't help - just throw
// the exception.
if (pseudoColumn.length() == 0) {
throw ex;
}
// pseudocolumn query failed, so reset it. Otherwise, we'll
// mistake the last column for a pseudocolumn and make it
// uneditable
pseudoColumn = "";
// Some tables have pseudo column primary keys and others
// do not. JDBC on some DBMSs does not handle pseudo
// columns 'correctly'. Also, getTables returns 'views' as
// well as tables, so the thing we are looking at might not
// be a table. (JDBC does not give a simple way to
// determine what we are looking at since the type of
// object is described in a DBMS-specific encoding.) For
// these reasons, rather than testing for all these
// conditions, we just try using the pseudo column info to
// get the table data, and if that fails, we try to get the
// table data without using the pseudo column.
// TODO: Should we change the mode from editable to
// non-editable?
final StringBuffer buf = new StringBuffer();
buf.append("select *").append(" from ").append(ti.getQualifiedName()).append(" tbl");
String clause =
_sqlFilterClauses.get(WhereClausePanel.getClauseIdentifier(), ti.getQualifiedName());
if ((clause != null) && (clause.length() > 0)) {
buf.append(" where ").append(clause);
}
clause =
_sqlFilterClauses.get(
OrderByClausePanel.getClauseIdentifier(), ti.getQualifiedName());
if ((clause != null) && (clause.length() > 0)) {
buf.append(" order by ").append(clause);
}
rs = stmt.executeQuery(buf.toString());
}
final ResultSetDataSet rsds = new ResultSetDataSet();
// to allow the fw to save and reload user options related to
// specific columns, we construct a unique name for the table
// so the column can be associcated with only that table.
// Some drivers do not provide the catalog or schema info, so
// those parts of the name will end up as null. That's ok since
// this string is never viewed by the user and is just used to
// distinguish this table from other tables in the DB.
// We also include the URL used to connect to the DB so that
// the same table/DB on different machines is treated differently.
rsds.setContentsTabResultSet(
rs, _dataSetUpdateableTableModel.getFullTableName(), DialectFactory.getDialectType(md));
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
}
}
// KLUDGE:
// We want some info about the columns to be available for validating the
// user input during cell editing operations. Ideally we would get that
// info inside the ResultSetDataSet class during the creation of the
// columnDefinition objects by using various functions in ResultSetMetaData
// such as isNullable(idx). Unfortunately, in at least some DBMSs (e.g.
// Postgres, HSDB) the results of those calls are not the same (and are less accurate
// than) the SQLMetaData.getColumns() call used in ColumnsTab to get the column info.
// Even more unfortunate is the fact that the set of attributes reported on by the two
// calls is not the same, with the ResultSetMetadata listing things not provided by
// getColumns. Most of the data provided by the ResultSetMetaData calls is correct.
// However, the nullable/not-nullable property is not set correctly in at least two
// DBMSs, while it is correct for those DBMSs in the getColumns() info. Therefore,
// we collect the collumn nullability information from getColumns() and pass that
// info to the ResultSet to override what it got from the ResultSetMetaData.
TableColumnInfo[] columnInfos = md.getColumnInfo(getTableInfo());
final ColumnDisplayDefinition[] colDefs =
rsds.getDataSetDefinition().getColumnDefinitions();
// get the nullability information and pass it into the ResultSet
// Unfortunately, not all DBMSs provide the column number in object 17 as stated in the
// SQL documentation, so we have to guess that the result set is in column order
for (int i = 0; i < columnInfos.length; i++) {
boolean isNullable = true;
TableColumnInfo info = columnInfos[i];
if (info.isNullAllowed() == DatabaseMetaData.columnNoNulls) {
isNullable = false;
}
if (i < colDefs.length) {
colDefs[i].setIsNullable(isNullable);
}
}
// ?? remember which column is the rowID (if any) so we can
// ?? prevent editing on it
if (pseudoColumn.length() > 0) {
_dataSetUpdateableTableModel.setRowIDCol(rsds.getColumnCount() - 1);
}
return rsds;
} finally {
SQLUtilities.closeStatement(stmt);
}
} catch (SQLException ex) {
throw new DataSetException(ex);
} finally {
disposeWaitDialog();
}
}
public static void main(String args[]) {
try {
String url;
if (args.length == 0) url = "jdbc:virtuoso://localhost:1111";
else url = args[0];
Class.forName("virtuoso.jdbc3.Driver");
System.out.println("--------------------- Test of scrollable cursor -------------------");
System.out.print("Establish connection at " + url);
Connection connection = DriverManager.getConnection(url, "dba", "dba");
if (connection instanceof virtuoso.jdbc3.VirtuosoConnection) System.out.println(" PASSED");
else {
System.out.println(" FAILED");
System.exit(-1);
}
System.out.print("Create a Statement class attached to this connection");
Statement stmt =
connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
if (stmt instanceof virtuoso.jdbc3.VirtuosoStatement) System.out.println(" PASSED");
else {
System.out.println(" FAILED");
System.exit(-1);
}
try {
stmt.executeUpdate("drop table EX..DEMO");
} catch (Exception e) {
}
System.out.print("Execute CREATE TABLE");
if (stmt.executeUpdate("create table EX..DEMO (ID integer,FILLER integer,primary key(ID))")
== 0) System.out.println(" PASSED");
else {
System.out.println(" FAILED");
System.exit(-1);
}
System.out.print("Create a PStatement class attached to this connection");
PreparedStatement pstmt =
connection.prepareStatement("INSERT INTO EX..DEMO(ID,FILLER) VALUES (?,?)");
System.out.println(" PASSED");
System.out.print("Execute INSERT INTO");
for (int i = 0; i < 100; i++) {
pstmt.setInt(1, i);
pstmt.setInt(2, i);
if (pstmt.executeUpdate() != 1) {
System.out.println(" FAILED");
System.exit(-1);
}
}
System.out.println(" PASSED");
pstmt.close();
System.out.print("Execute SELECT");
stmt.setMaxRows(100);
stmt.setFetchSize(10);
stmt.execute("SELECT * from EX..DEMO");
System.out.println(" PASSED");
System.out.print("Get the result set");
ResultSet rs = stmt.getResultSet();
if (rs instanceof virtuoso.jdbc3.VirtuosoResultSet) {
System.out.println(" PASSED");
} else {
System.out.println(" FAILED");
System.exit(-1);
}
System.out.print("Execute the resultset.beforeFirst()");
rs.beforeFirst();
System.out.println(" PASSED");
System.out.print("Execute the resultset.next()");
for (int i = 0; i < 100; i++) {
rs.next();
if (rs.getInt(2) != i) {
System.out.println(" FAILED");
System.exit(-1);
}
}
System.out.println(" PASSED");
System.out.print("Execute the resultset.afterLast()");
rs.afterLast();
System.out.println(" PASSED");
System.out.print("Execute the resultset.previous()");
for (int i = 99; i >= 0; i--) {
rs.previous();
if (rs.getInt(2) != i) {
System.out.println(" FAILED");
System.exit(-1);
}
}
System.out.println(" PASSED");
System.out.print("Execute the resultset.first()");
rs.first();
System.out.println(" PASSED");
System.out.print("Execute the resultset.next()");
for (int i = 0; i < 100; i++) {
if (rs.getInt(2) != i) {
System.out.println(" FAILED");
System.exit(-1);
}
rs.next();
}
System.out.println(" PASSED");
System.out.print("Execute the resultset.last()");
rs.last();
System.out.println(" PASSED");
System.out.print("Execute the resultset.previous()");
for (int i = 99; i >= 0; i--) {
if (rs.getInt(2) != i) {
System.out.println(" FAILED");
System.exit(-1);
}
rs.previous();
}
System.out.println(" PASSED");
System.out.print("Execute the resultset.absolute(>0)");
for (int i = 0; i != 100; i++) {
rs.absolute(i + 1);
if (rs.getInt(2) != i) {
System.out.println(" FAILED");
System.exit(-1);
}
}
System.out.println(" PASSED");
System.out.print("Execute the resultset.absolute(<0)");
for (int i = -1, j = 99; i != -101; i--, j--) {
rs.absolute(i);
if (rs.getInt(2) != j) {
System.out.println(" FAILED");
System.exit(-1);
}
}
System.out.println(" PASSED");
System.out.print("Execute the resultset.absolute(50)");
rs.absolute(50);
System.out.println(" PASSED");
System.out.print("Execute the resultset.relative(>0)");
for (int i = 50; i != 90; i++) {
if (rs.getInt(2) != i - 1) {
System.out.println(" FAILED");
System.exit(-1);
}
rs.relative(1);
}
System.out.println(" PASSED");
System.out.print("Execute the resultset.absolute(50)");
rs.absolute(50);
System.out.println(" PASSED");
System.out.print("Execute the resultset.relative(<0)");
for (int i = 50; i != 10; i--) {
if (rs.getInt(2) != i - 1) {
System.out.println(" FAILED");
System.exit(-1);
}
rs.relative(-1);
}
System.out.println(" PASSED");
System.out.print("Execute the resultset.first()");
rs.first();
System.out.println(" PASSED");
System.out.print("Update rows in the table");
for (int i = 0; i != 2; i++) {
rs.updateInt("FILLER", i * 2);
rs.updateRow();
rs.refreshRow();
if (rs.getInt(2) != i * 2) {
System.out.println(" FAILED");
System.exit(-1);
}
rs.next();
}
System.out.println(" PASSED");
System.out.print("Execute DELETE");
pstmt = connection.prepareStatement("DELETE FROM EX..DEMO WHERE ID=?");
for (int i = 0; i < 100; i++) {
pstmt.setInt(1, i);
if (pstmt.executeUpdate() != 1) {
System.out.println(" FAILED");
System.exit(-1);
}
}
System.out.println(" PASSED");
pstmt.close();
stmt.close();
stmt =
connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
System.out.print("Execute DROP TABLE");
if (stmt.executeUpdate("DROP TABLE EX..DEMO") == 0) System.out.println(" PASSED");
else {
System.out.println(" FAILED");
System.exit(-1);
}
System.out.print("Close statement at " + url);
stmt.close();
System.out.println(" PASSED");
System.out.print("Close connection at " + url);
connection.close();
System.out.println(" PASSED");
System.out.println("-------------------------------------------------------------------");
System.exit(0);
} catch (Exception e) {
System.out.println(" FAILED");
e.printStackTrace();
System.exit(-1);
}
}
/**
* <code>getAccountsUIByCriteria</code> returns a vector of AccountUIView's meeting the criteria
* of the BusEntitySearchCriteria.
*
* @param pCrit a <code>BusEntitySearchCriteria</code> value the criteria
* @return a <code>AccountUIViewVector</code> value
* @exception SQLException if an error occurs
*/
public AccountUIViewVector getAccountsUIByCriteria(BusEntitySearchCriteria pCrit)
throws RemoteException {
Connection conn = null;
AccountUIViewVector acctVec = new AccountUIViewVector();
try {
// USER FILTER ==========================================================//
Integer userId = null;
String userFilterForAccounts = "";
if (pCrit.getUserIds() != null && pCrit.getUserIds().size() > 0) {
userId = (Integer) pCrit.getUserIds().get(0);
userFilterForAccounts = getUserFilterForAccounts(userId.intValue());
}
// STORE FILTER===========================================================//
Integer storeId = null;
if (pCrit.getStoreBusEntityIds() != null && pCrit.getStoreBusEntityIds().size() > 0) {
storeId = (Integer) pCrit.getStoreBusEntityIds().get(0);
} else {
throw new RemoteException("Store ID can't be null. ");
}
String storeFilter =
"(select STORE_DIM_ID from DW_STORE_DIM \n" + " where STORE_ID = " + storeId + " ) \n";
String storeFilterForAccounts = getStoreFilterForAccounts(storeId.intValue());
// ACCOUNT NAMES FILTER==================================================//
String accountNameFilter =
getSearchSqlByFilterName(
"JD_ACCOUNT_NAME", pCrit.getSearchName(), pCrit.getSearchNameType());
String accountIdFilter =
(Utility.isSet(pCrit.getSearchId())) ? " and JD_ACCOUNT_ID = " + pCrit.getSearchId() : "";
// ========================================================================//
conn = getReportConnection();
String sql =
"select \n "
+ " wm_concat(ACCOUNT_DIM_ID) ACCOUNT_DIM_IDS, \n"
+ " ACCOUNT_ID, \n"
+ " JD_ACCOUNT_NAME, JD_ACCOUNT_ID, \n"
+ " JD_ACCOUNT_CITY, JD_ACCOUNT_STATE, \n"
+ " JD_MARKET, JD_ACCOUNT_STATUS_CD \n"
+ "from DW_ACCOUNT_DIM where \n"
+ " STORE_DIM_ID = "
+ storeFilter
+ " \n"
+
/* " and account_ID in ( " + storeFilterForAccounts +") \n"
*/
userFilterForAccounts
+ accountIdFilter
+ accountNameFilter
+ " group by \n "
+ " ACCOUNT_ID, \n"
+ " JD_ACCOUNT_NAME, JD_ACCOUNT_ID, \n"
+ " JD_ACCOUNT_CITY, JD_ACCOUNT_STATE, \n"
+ " JD_MARKET, JD_ACCOUNT_STATUS_CD \n"
+ " order by JD_ACCOUNT_NAME ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (pCrit.getResultLimit() != QueryRequest.UNLIMITED) {
stmt.setMaxRows(2000);
}
int rowId = 0;
while (rs.next()) {
AccountUIView aui = AccountUIView.createValue();
rowId--;
String accountDimIds =
(rs.getString("ACCOUNT_DIM_IDS") != null) ? rs.getString("ACCOUNT_DIM_IDS") : "";
BusEntityData be = new BusEntityData();
be.setBusEntityId(rowId);
be.setShortDesc(
(rs.getString("JD_ACCOUNT_NAME") != null) ? rs.getString("JD_ACCOUNT_NAME") : "");
be.setBusEntityStatusCd(
(rs.getString("JD_ACCOUNT_STATUS_CD") != null)
? rs.getString("JD_ACCOUNT_STATUS_CD")
: "");
PropertyData pd = new PropertyData();
pd.setValue((rs.getString("JD_MARKET") != null) ? rs.getString("JD_MARKET") : "");
AddressData ad = new AddressData();
ad.setAddress1("");
ad.setCity(
(rs.getString("JD_ACCOUNT_CITY") != null) ? rs.getString("JD_ACCOUNT_CITY") : "");
ad.setStateProvinceCd(
(rs.getString("JD_ACCOUNT_STATE") != null) ? rs.getString("JD_ACCOUNT_STATE") : "");
aui.setAccountDimIds(accountDimIds);
aui.setBusEntity(be);
aui.setAccountType(pd);
aui.setPrimaryAddress(ad);
acctVec.add(aui);
}
stmt.close();
} catch (Exception exc) {
exc.printStackTrace();
throw processException(exc);
} finally {
closeConnection(conn);
}
return acctVec;
}
public void setMaxRows(int max) throws SQLException {
pst.setMaxRows(max);
};
public static void main(String args[]) {
try {
String url;
if (args.length == 0) url = "jdbc:virtuoso://localhost:1111";
else url = args[0];
Class.forName("virtuoso.jdbc4.Driver");
System.out.println("--------------------- Test of the fetch execute -------------------");
System.out.print("Establish connection at " + url);
Connection connection = DriverManager.getConnection(url, "dba", "dba");
if (connection instanceof virtuoso.jdbc4.VirtuosoConnection) System.out.println(" PASSED");
else {
System.out.println(" FAILED");
System.exit(-1);
}
System.out.print("Create a Statement class attached to this connection");
Statement stmt = connection.createStatement();
if (stmt instanceof virtuoso.jdbc4.VirtuosoStatement) System.out.println(" PASSED");
else {
System.out.println(" FAILED");
System.exit(-1);
}
System.out.print("Set fetch size attached to this statement");
stmt.setMaxRows(10);
stmt.setFetchSize(10);
if (stmt.getMaxRows() == 10 && stmt.getFetchSize() == 10) System.out.println(" PASSED");
else {
System.out.println(" FAILED");
System.exit(-1);
}
System.out.println("Execute select * from INFORMATION_SCHEMA.TABLES");
boolean more = stmt.execute("select * from INFORMATION_SCHEMA.TABLES");
ResultSetMetaData data = stmt.getResultSet().getMetaData();
for (int i = 1; i <= data.getColumnCount(); i++)
System.out.println(data.getColumnLabel(i) + "\t" + data.getColumnTypeName(i));
while (more) {
ResultSet rs = stmt.getResultSet();
while (rs.next())
for (int i = 1; i <= data.getColumnCount(); i++) {
if (i == 1 || i == 2) {
String s = stmt.getResultSet().getString(i);
if (stmt.getResultSet().wasNull()) System.out.print("NULL\t");
else System.out.print(s + "\t");
}
}
System.out.println();
more = stmt.getMoreResults();
}
System.out.println(" PASSED");
System.out.print("Close statement at " + url);
stmt.close();
System.out.println(" PASSED");
System.out.print("Close connection at " + url);
connection.close();
System.out.println(" PASSED");
System.out.println("-------------------------------------------------------------------");
System.exit(0);
} catch (Exception e) {
System.out.println(" FAILED");
e.printStackTrace();
System.exit(-1);
}
}
protected void append(LoggingEvent event) {
Connection connection = null;
try {
connection = connectionSource.getConnection();
connection.setAutoCommit(false);
PreparedStatement insertStatement;
if (cnxSupportsGetGeneratedKeys) {
insertStatement = connection.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS);
} else {
insertStatement = connection.prepareStatement(insertSQL);
}
/* insertStatement.setLong(1, event.getSequenceNumber());*/
insertStatement.setLong(1, 0);
insertStatement.setLong(2, event.getTimeStamp());
insertStatement.setString(3, event.getRenderedMessage());
insertStatement.setString(4, event.getLoggerName());
insertStatement.setString(5, event.getLevel().toString());
insertStatement.setString(6, event.getNDC());
insertStatement.setString(7, event.getThreadName());
insertStatement.setShort(8, DBHelper.computeReferenceMask(event));
LocationInfo li;
if (event.locationInformationExists() || locationInfo) {
li = event.getLocationInformation();
} else {
li = LocationInfo.NA_LOCATION_INFO;
}
insertStatement.setString(9, li.getFileName());
insertStatement.setString(10, li.getClassName());
insertStatement.setString(11, li.getMethodName());
insertStatement.setString(12, li.getLineNumber());
int updateCount = insertStatement.executeUpdate();
if (updateCount != 1) {
LogLog.warn("Failed to insert loggingEvent");
}
ResultSet rs = null;
Statement idStatement = null;
boolean gotGeneratedKeys = false;
if (cnxSupportsGetGeneratedKeys) {
try {
rs = (ResultSet) GET_GENERATED_KEYS_METHOD.invoke(insertStatement, null);
gotGeneratedKeys = true;
} catch (InvocationTargetException ex) {
Throwable target = ex.getTargetException();
if (target instanceof SQLException) {
throw (SQLException) target;
}
throw ex;
} catch (IllegalAccessException ex) {
LogLog.warn("IllegalAccessException invoking PreparedStatement.getGeneratedKeys", ex);
}
}
if (!gotGeneratedKeys) {
insertStatement.close();
insertStatement = null;
idStatement = connection.createStatement();
idStatement.setMaxRows(1);
rs = idStatement.executeQuery(sqlDialect.getSelectInsertId());
}
// A ResultSet cursor is initially positioned before the first row; the
// first call to the method next makes the first row the current row
rs.next();
int eventId = rs.getInt(1);
rs.close();
// we no longer need the insertStatement
if (insertStatement != null) {
insertStatement.close();
insertStatement = null;
}
if (idStatement != null) {
idStatement.close();
idStatement = null;
}
Set propertiesKeys = event.getPropertyKeySet();
if (propertiesKeys.size() > 0) {
PreparedStatement insertPropertiesStatement =
connection.prepareStatement(insertPropertiesSQL);
for (Iterator i = propertiesKeys.iterator(); i.hasNext(); ) {
String key = (String) i.next();
String value = event.getProperty(key);
// LogLog.info("id " + eventId + ", key " + key + ", value " + value);
insertPropertiesStatement.setInt(1, eventId);
insertPropertiesStatement.setString(2, key);
insertPropertiesStatement.setString(3, value);
if (cnxSupportsBatchUpdates) {
insertPropertiesStatement.addBatch();
} else {
insertPropertiesStatement.execute();
}
}
if (cnxSupportsBatchUpdates) {
insertPropertiesStatement.executeBatch();
}
insertPropertiesStatement.close();
insertPropertiesStatement = null;
}
String[] strRep = event.getThrowableStrRep();
if (strRep != null) {
LogLog.debug("Logging an exception");
PreparedStatement insertExceptionStatement =
connection.prepareStatement(insertExceptionSQL);
for (short i = 0; i < strRep.length; i++) {
insertExceptionStatement.setInt(1, eventId);
insertExceptionStatement.setShort(2, i);
insertExceptionStatement.setString(3, strRep[i]);
if (cnxSupportsBatchUpdates) {
insertExceptionStatement.addBatch();
} else {
insertExceptionStatement.execute();
}
}
if (cnxSupportsBatchUpdates) {
insertExceptionStatement.executeBatch();
}
insertExceptionStatement.close();
insertExceptionStatement = null;
}
connection.commit();
} catch (Throwable sqle) {
LogLog.error("problem appending event", sqle);
} finally {
DBHelper.closeConnection(connection);
}
}