public DataSet getDataSet() throws Exception {
INode node = getNode();
if (node == null) {
return null;
}
if (node.getSession() == null) {
return null;
}
if (node instanceof TableNode) {
TableNode tableNode = (TableNode) node;
ITableInfo ti = tableNode.getTableInfo();
if (tableNode.getTableInfo() == null) {
return null;
}
ResultSet resultSet = null;
SessionTreeNode treeNode = node.getSession();
// For synonym table, should get the corresponding table.
if (ti.getType().equals("SYNONYM")) { // $NON-NLS-1$
String realTableName =
ExtractMetaDataFromDataBase.getTableNameBySynonym(
treeNode.getInteractiveConnection().getConnection(), ti.getSimpleName());
resultSet =
treeNode
.getMetaData()
.getJDBCMetaData()
.getColumns(
ti.getCatalogName(), ti.getSchemaName(), realTableName, "%"); // $NON-NLS-1$
} else {
// https://jira.talendforge.org/browse/TDI-28578
String tableName = ti.getSimpleName();
if (tableName.contains("/")) {
tableName = tableName.replaceAll("/", "//");
}
resultSet =
node.getSession()
.getMetaData()
.getJDBCMetaData()
.getColumns(ti.getCatalogName(), ti.getSchemaName(), tableName, "%");
// resultSet = node.getSession().getMetaData().getColumns(tableNode.getTableInfo());
}
DataSet dataSet =
new DataSet(
null, resultSet, new int[] {4, 5, 6, 7, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18});
resultSet.close();
return dataSet;
}
return null;
}
/** Get the full name of this table, creating that name the first time we are called */
public String getFullTableName() {
if (fullTableName == null) {
try {
final String name = ti.getQualifiedName();
fullTableName = getUnambiguousTableName(_session, name);
} catch (Exception e) {
s_log.error("getFullTableName: Unexpected exception - " + e.getMessage(), e);
}
}
return fullTableName;
}
/**
* Counts the number of affected rows, using this where clause.
*
* @param whereClauseParts where clause to use
* @param conn connection to use
* @return number of rows in the database, which will be selected by the given whereClauseParts
* @throws SQLException if an SQLExcetpion occurs.
*/
private int count(List<IWhereClausePart> whereClauseParts, final ISQLConnection conn)
throws SQLException {
int count;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
String whereClause = whereClausePartUtil.createWhereClause(whereClauseParts);
String countSql = "select count(*) from " + ti.getQualifiedName() + whereClause;
pstmt = conn.prepareStatement(countSql);
whereClausePartUtil.setParameters(pstmt, whereClauseParts, 1);
rs = pstmt.executeQuery();
rs.next();
count = rs.getInt(1);
} finally {
// We don't care if these throw an SQLException. Just squelch them
// and report to the user what the outcome of the previous statements
// were.
SQLUtilities.closeResultSet(rs);
SQLUtilities.closeStatement(pstmt);
}
return count;
}
/** Insert a row into the DB. If the insert succeeds this returns a null string. */
public String insertRow(Object[] values, ColumnDisplayDefinition[] colDefs) {
// if we could not identify which table to edit, tell user
if (ti == null) {
return TI_ERROR_MESSAGE;
}
final ISession session = _session;
final ISQLConnection conn = session.getSQLConnection();
int count = -1;
try {
// start the string for use in the prepared statment
StringBuilder buf = new StringBuilder("INSERT INTO ");
buf.append(ti.getQualifiedName());
// Add the list of column names we will be inserting into - be sure
// to skip the rowId column and any auto increment columns.
buf.append(" ( ");
for (int i = 0; i < colDefs.length; i++) {
if (i == _rowIDcol) {
continue;
}
if (colDefs[i].isAutoIncrement()) {
if (s_log.isInfoEnabled()) {
s_log.info("insertRow: skipping auto-increment column " + colDefs[i].getColumnName());
}
continue;
}
buf.append(colDefs[i].getColumnName());
buf.append(",");
}
buf.setCharAt(buf.length() - 1, ')');
buf.append(" VALUES (");
// add a variable position for each of the columns
for (int i = 0; i < colDefs.length; i++) {
if (i != _rowIDcol && !colDefs[i].isAutoIncrement()) buf.append(" ?,");
}
// replace the last "," with ")"
buf.setCharAt(buf.length() - 1, ')');
String pstmtSQL = buf.toString();
if (s_log.isInfoEnabled()) {
s_log.info("insertRow: pstmt sql = " + pstmtSQL);
}
final PreparedStatement pstmt = conn.prepareStatement(pstmtSQL);
try {
// We need to keep track of the bind var index separately, since
// the number of column defs may not be the number of bind vars
// (For example: auto-increment columns are excluded)
int bindVarIdx = 1;
// have the DataType object fill in the appropriate kind of value
// into the appropriate variable position in the prepared stmt
for (int i = 0; i < colDefs.length; i++) {
if (i != _rowIDcol && !colDefs[i].isAutoIncrement()) {
CellComponentFactory.setPreparedStatementValue(
colDefs[i], pstmt, values[i], bindVarIdx);
bindVarIdx++;
}
}
count = pstmt.executeUpdate();
} finally {
pstmt.close();
}
} catch (SQLException ex) {
// i18n[DataSetUpdateableTableModelImpl.error.duringInsert=Exception seen during check on DB.
// Exception was:\n{0}\nInsert was probably not completed correctly. DB may be corrupted!]
return s_stringMgr.getString(
"DataSetUpdateableTableModelImpl.error.duringInsert", ex.getMessage());
}
if (count != 1)
// i18n[DataSetUpdateableTableModelImpl.error.unknownerrorupdate=Unknown problem during
// update.\nNo count of inserted rows was returned.\nDatabase may be corrupted!]
return s_stringMgr.getString("DataSetUpdateableTableModelImpl.error.unknownerrorupdate");
// insert succeeded
try {
IObjectTreeAPI api = _session.getObjectTreeAPIOfActiveSessionWindow();
api.refreshSelectedTab();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* Delete a set of rows from the DB. If the delete succeeded this returns a null string. The
* deletes are done within a transaction so they are either all done or all not done.
*/
public String deleteRows(Object[][] rowData, ColumnDisplayDefinition[] colDefs) {
// if we could not identify which table to edit, tell user
if (ti == null) return TI_ERROR_MESSAGE;
// get the SQL session
final ISession session = _session;
final ISQLConnection conn = session.getSQLConnection();
// string used as error indicator and description of problems seen
// when checking for 0 or mulitple matches in DB
String rowCountErrorMessage = "";
// for each row in table, count how many rows match where clause
// if not exactly one, generate message describing situation
for (int i = 0; i < rowData.length; i++) {
// get WHERE clause for the selected row
// the -1 says to just use the contents of the values without
// any substitutions
List<IWhereClausePart> whereClauseParts = getWhereClause(rowData[i], colDefs, -1, null);
// count how many rows this WHERE matches
try {
int count = count(whereClauseParts, conn);
if (count != 1) {
if (count == 0) {
// i18n[DataSetUpdateableTableModelImpl.error.rownotmatch=\n Row {0} did not match
// any row in DB]
rowCountErrorMessage +=
s_stringMgr.getString(
"DataSetUpdateableTableModelImpl.error.rownotmatch", Integer.valueOf(i + 1));
} else {
// i18n[DataSetUpdateableTableModelImpl.error.rowmatched=\n Row {0} matched {1} rows
// in DB]
rowCountErrorMessage +=
s_stringMgr.getString(
"DataSetUpdateableTableModelImpl.error.rowmatched",
new Object[] {Integer.valueOf(i + 1), Integer.valueOf(count)});
}
}
} catch (Exception e) {
// some kind of problem - tell user
// i18n[DataSetUpdateableTableModelImpl.error.preparingdelete=While preparing for delete,
// saw exception:\n{0}]
return s_stringMgr.getString("DataSetUpdateableTableModelImpl.error.preparingdelete", e);
}
}
// if the rows do not match 1-for-1 to DB, ask user if they
// really want to do delete
if (rowCountErrorMessage.length() > 0) {
// i18n[DataSetUpdateableTableModelImpl.error.tabledbmismatch=There may be a mismatch between
// the table and the DB:\n{0}\nDo you wish to proceed with the deletes anyway?]
String msg =
s_stringMgr.getString(
"DataSetUpdateableTableModelImpl.error.tabledbmismatch", rowCountErrorMessage);
int option =
JOptionPane.showConfirmDialog(
null, msg, "Warning", JOptionPane.YES_NO_OPTION, JOptionPane.WARNING_MESSAGE);
if (option != JOptionPane.YES_OPTION) {
// i18n[DataSetUpdateableTableModelImpl.info.deletecancelled=Delete canceled at user
// request.]
return s_stringMgr.getString("DataSetUpdateableTableModelImpl.info.deletecancelled");
}
}
// for each row in table, do delete and add to number of rows deleted from DB
for (int i = 0; i < rowData.length; i++) {
// get WHERE clause for the selected row
// the -1 says to just use the contents of the values without
// any substitutions
List<IWhereClausePart> whereClauseParts = getWhereClause(rowData[i], colDefs, -1, null);
String whereClause = whereClausePartUtil.createWhereClause(whereClauseParts);
// try to delete
try {
// do the delete and add the number of rows deleted to the count
String sql = "DELETE FROM " + ti.getQualifiedName() + whereClause;
final PreparedStatement pstmt = conn.prepareStatement(sql);
whereClausePartUtil.setParameters(pstmt, whereClauseParts, 1);
try {
pstmt.executeUpdate();
} finally {
pstmt.close();
}
} catch (Exception e) {
// some kind of problem - tell user
// i18n[DataSetUpdateableTableModelImpl.error.deleteFailed=One of the delete operations
// failed with exception:\n{0}\nDatabase is in an unknown state and may be corrupted.]
return s_stringMgr.getString("DataSetUpdateableTableModelImpl.error.deleteFailed", e);
}
}
return null; // hear no evil, see no evil
}
/** link from fw to this for updating data */
public String updateTableComponent(
Object[] values,
ColumnDisplayDefinition[] colDefs,
int col,
Object oldValue,
Object newValue) {
// if we could not identify which table to edit, tell user
if (ti == null) return TI_ERROR_MESSAGE;
// get WHERE clause using original value
List<IWhereClausePart> whereClauseParts = getWhereClause(values, colDefs, col, oldValue);
String whereClause = whereClausePartUtil.createWhereClause(whereClauseParts);
if (s_log.isDebugEnabled()) {
s_log.debug("updateTableComponent: whereClause = " + whereClause);
}
final ISession session = _session;
final ISQLConnection conn = session.getSQLConnection();
int count = -1;
final String sql =
constructUpdateSql(ti.getQualifiedName(), colDefs[col].getColumnName(), whereClause);
if (s_log.isDebugEnabled()) {
s_log.debug("updateTableComponent: executing SQL - " + sql);
}
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
/*
* have the DataType object fill in the appropriate kind of value of the changed data
* into the first variable position in the prepared stmt
*/
CellComponentFactory.setPreparedStatementValue(colDefs[col], pstmt, newValue, 1);
// Fill the parameters of the where clause - start at position 2 because the data which is
// updated is at position 1
whereClausePartUtil.setParameters(pstmt, whereClauseParts, 2);
count = pstmt.executeUpdate();
} catch (SQLException ex) {
// i18n[DataSetUpdateableTableModelImpl.error.updateproblem=There
// was a problem reported during the update.
// The DB message was:\n{0}\nThis may or may not be serious depending
// on the above message.\nThe data was probably not changed in the
// database.\nYou may need to refresh the table to get an accurate
// view of the current data.]
String errMsg =
s_stringMgr.getString(
"DataSetUpdateableTableModelImpl.error.updateproblem", ex.getMessage());
s_log.error(
"updateTableComponent: unexpected exception - "
+ ex.getMessage()
+ " while executing SQL: "
+ sql);
return errMsg;
} finally {
SQLUtilities.closeStatement(pstmt);
}
if (count == -1) {
// i18n[DataSetUpdateableTableModelImpl.error.unknownupdateerror=Unknown problem during
// update.\nNo count of updated rows was returned.\nDatabase may be corrupted!]
return s_stringMgr.getString("DataSetUpdateableTableModelImpl.error.unknownupdateerror");
}
if (count == 0) {
// i18n[DataSetUpdateableTableModelImpl.info.norowsupdated=No rows updated.]
return s_stringMgr.getString("DataSetUpdateableTableModelImpl.info.norowsupdated");
}
// everything seems to have worked ok
return null;
}
/**
* Re-read the value for a single cell in the table, if possible. If there is a problem, the
* message has a non-zero length when this returns.
*/
public Object reReadDatum(
Object[] values, ColumnDisplayDefinition[] colDefs, int col, StringBuffer message) {
// if we could not identify which table to edit, tell user
if (ti == null) return TI_ERROR_MESSAGE;
// get WHERE clause
// The -1 says to ignore the last arg and use the contents of the values array
// for the column that we care about. However, since the data in
// that column has been limited, when getWhereClause calls that
// DataType with that value, the DataType will see that the data has
// been limited and therefore cannnot be used in the WHERE clause.
// In some cases it may be possible for the DataType to use the
// partial data, such as "matches <data>*", but that may not be
// standard accross all Databases and thus may be risky.
List<IWhereClausePart> whereClauseParts = getWhereClause(values, colDefs, -1, null);
String whereClause = whereClausePartUtil.createWhereClause(whereClauseParts);
final ISession session = _session;
final ISQLConnection conn = session.getSQLConnection();
Object wholeDatum = null;
try {
final String queryString =
"SELECT " + colDefs[col].getColumnName() + " FROM " + ti.getQualifiedName() + whereClause;
final PreparedStatement pstmt = conn.prepareStatement(queryString);
whereClausePartUtil.setParameters(pstmt, whereClauseParts, 1);
try {
ResultSet rs = pstmt.executeQuery(queryString);
// There should be one row in the data, so try to move to it
if (rs.next() == false) {
// no first row, so we cannot retrieve the data
// i18n[DataSetUpdateableTableModelImpl.error.nomatchingrow=Could not find any row in DB
// matching current row in table]
throw new SQLException(
s_stringMgr.getString("DataSetUpdateableTableModelImpl.error.nomatchingrow"));
}
// we have at least one row, so try to retrieve the object
// Do Not limit the read of this data
wholeDatum = CellComponentFactory.readResultSet(colDefs[col], rs, 1, false);
// There should not be more than one row in the DB that matches
// the table, and if there is we cannot determine which one to read,
// so check that there are no more
if (rs.next() == true) {
// multiple rows - not good
wholeDatum = null;
// i18n[DataSetUpdateableTableModelImpl.error.multimatchingrows=Muliple rows in DB match
// current row in table - cannot re-read data.]
throw new SQLException(
s_stringMgr.getString("DataSetUpdateableTableModelImpl.error.multimatchingrows"));
}
} finally {
pstmt.close();
}
} catch (Exception ex) {
// i18n[DataSetUpdateableTableModelImpl.error.rereadingdb=There was a problem reported while
// re-reading the DB. The DB message was:\n{0}]
message.append(
s_stringMgr.getString(
"DataSetUpdateableTableModelImpl.error.rereadingdb", ex.getMessage()));
// It would be nice to tell the user what happened, but if we try to
// put up a dialog box at this point, we run into trouble in some
// cases where the field continually tries to re-read after the dialog
// closes (because it is being re-painted).
}
// return the whole contents of this column in the DB
return wholeDatum;
};
/** 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();
}
}
