private void testFetchSize() throws SQLException {
if (!config.networked || config.memory) {
return;
}
ResultSet rs = stat.executeQuery("SELECT * FROM SYSTEM_RANGE(1, 100)");
int a = stat.getFetchSize();
int b = rs.getFetchSize();
assertEquals(a, b);
rs.setFetchSize(b + 1);
b = rs.getFetchSize();
assertEquals(a + 1, b);
}
private void validateSQLdata(boolean hasdata) throws NamingException, SQLException {
Connection con = h2Datasource.getConnection();
Statement stmt = null;
try {
stmt = con.createStatement();
ResultSet resultSet = null;
try {
resultSet = stmt.executeQuery("SELECT * FROM TEST");
} catch (SQLException e) {
}
if (hasdata) {
assertNotNull(resultSet);
assertTrue(resultSet.first());
assertEquals("txt", resultSet.getString("A"));
} else {
assertTrue(resultSet == null || resultSet.getFetchSize() == 0);
}
} finally {
if (stmt != null) {
stmt.close();
}
con.close();
}
}
@Override
public User retrieveUser(User user) {
final String SQL = "SELECT * FROM users WHERE username = ?";
PreparedStatement preparedStatement = PreparedStatementCreator.createPreparedStatement(SQL);
ResultSet resultSet = null;
try {
preparedStatement.setString(1, user.getUsername());
resultSet = preparedStatement.executeQuery();
rowAffected = resultSet.getFetchSize();
System.out.println("Row: " + rowAffected);
} catch (SQLException e) {
e.printStackTrace();
}
User retrievedUser = null;
try {
while (resultSet.next()) {
retrievedUser = new User();
retrievedUser.setFirstName(resultSet.getString("first_name"));
retrievedUser.setLastName(resultSet.getString("last_name"));
retrievedUser.setUsername(resultSet.getString("username"));
retrievedUser.setEmail(resultSet.getString("email"));
retrievedUser.setPassword(resultSet.getString("password"));
retrievedUser.setType(resultSet.getString("user_type"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return retrievedUser;
}
public List<DataTransferObject> select(String sql, DataTransferObject record)
throws SQLException, NoSuchMethodException, InstantiationException, IllegalAccessException,
Exception {
checkConnection();
// Connection conn = DButil.getConnection();
prglog.debug("[PRG] selection SQL: " + sql);
lastSQL = sql;
Statement stmt = null;
ResultSet result = null;
List<DataTransferObject> records = new ArrayList<DataTransferObject>();
try {
stmt = conn.createStatement();
result = stmt.executeQuery(sql);
prglog.debug("[PRG] FetchSize: " + result.getFetchSize());
while (result.next()) {
records.add(result2record(result, record.getClass().newInstance()));
}
} finally {
if (result != null) result.close();
if (stmt != null) stmt.close();
}
// DButil.closeConnection(conn);
return records;
}
@Override
public int getFetchSize() throws SQLException {
try {
return _res.getFetchSize();
} catch (SQLException e) {
handleException(e);
return 0;
}
}
@Override
public Collection<Long> extractData(ResultSet rs) throws SQLException, DataAccessException {
List<Long> goodIds = new ArrayList<>(rs.getFetchSize());
while (rs.next()) {
incGoodCount(/* article id */ rs.getLong(2), /* timestamp */ rs.getTimestamp(3).getTime());
goodIds.add(/* good id*/ rs.getLong(1));
}
return goodIds;
}
/**
* This method queries the database to validate the passed in FRN for the selected Funding Year
* has been invoiced or not
*
* @exception SQLException, if query fails
* @author
*/
public boolean checkFRNinv(String frn, String year) {
String query;
boolean validfrn = false;
Statement stmt = null;
ResultSet rs = null;
USFEnv.getLog().writeCrit("RhccDinvview: FRN Invoiced" + frn, this, null);
USFEnv.getLog().writeCrit("RhccDinvview: FRN Invoiced" + year, this, null);
query = " SELECT DISTINCT rhcc_inv_id_fk ";
query = query + " FROM wrk_ordr, wrk_ordr_dets, wo_det_hsties ";
query = query + " WHERE wo_id = wo_id_fk ";
query = query + " AND wod_id = wod_id_fk ";
query = query + " AND wdh_stat = 'P' ";
query = query + " AND inv_stat = 'I' ";
query = query + " AND wrk_ordr_no ='" + frn + "' ";
query = query + " AND wrk_ordr.yr_fk =" + year;
USFEnv.getLog().writeCrit("RhccDinvview: FRN Invoiced" + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
USFEnv.getLog().writeCrit("RhccDinvview: FRN Invoiced" + rs.getFetchSize(), this, null);
if (rs.next()) {
USFEnv.getLog()
.writeCrit("RhccDinvview: FRN Invoiced" + rs.getString("rhcc_inv_id_fk"), this, null);
validfrn = true;
if (rs != null) rs.close();
if (stmt != null) stmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("RhccDinvview: FRN not Invoiced", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return false;
}
/**
* Search a PID from his URL in the {@link Queue}: - return the PID if found - return null if not
* found
*
* @param url
* @return
* @throws Exception
*/
public Pid search(String url) throws Exception {
Pid pid = null;
Connection connection = DatabaseHelper.getConnection();
PreparedStatement stmt =
connection.prepareStatement(DatabaseHelper.GET_QUEUE_ELEMENT_URL_STATEMENT);
stmt.setString(1, url);
ResultSet resultSet = stmt.executeQuery();
if (resultSet.getFetchSize() > 1) {
throw new RuntimeException("Duplicate detected for URI: " + url);
}
if (resultSet.next()) {
pid = new Pid(resultSet.getString("identifier"), resultSet.getString("url"));
stmt.close();
connection.close();
}
stmt.close();
connection.close();
return pid;
}
public List<CharacterDTO> findAll() {
ArrayList<CharacterDTO> cast = new ArrayList<CharacterDTO>();
try {
// Class.forName("com.mysql.jdbc.Driver");
// Properties dbProps = new Properties();
// dbProps.put("user", "srikumarv");
// dbProps.put("password", "comp9321");
// Connection connection =
// DriverManager.getConnection("jdbc:mysql://srikumarv.srvr:3306/cs9321",dbProps);
Statement stmnt = connection.createStatement();
String query_cast = "SELECT MVCHAR_ID, MVCHAR_NAME, DIET, SOUNDS FROM TBL_CHARACTERS";
ResultSet res = stmnt.executeQuery(query_cast);
logger.info("The result set size is " + res.getFetchSize());
while (res.next()) {
int id = res.getInt("MVCHAR_ID");
logger.info(" " + id);
String name = res.getString("MVCHAR_NAME");
logger.info(name);
String diet = res.getString("DIET");
logger.info(diet);
String soundsArr = res.getString("SOUNDS");
logger.info(soundsArr);
logger.info(name + " " + diet + " " + soundsArr);
String[] sounds = soundsArr.split(",");
cast.add(new CharacterDTO(id, name, diet, sounds));
}
res.close();
stmnt.close();
} catch (Exception e) {
System.out.println("Caught Exception");
e.printStackTrace();
}
return cast;
}
/**
* return the {@link Operation}s whose type matches specified string
*
* @param typeDescription - a type description
* @return the list of {@link Operation}s. It could be <code>empty</code> or <code>null</code>.
* @throws OHException
*/
public ArrayList<Operation> getOperation(String typeDescription) throws OHException {
DbQueryLogger dbQuery = new DbQueryLogger();
ArrayList<Operation> operationList = null;
ResultSet resultSet;
if (typeDescription == null) {
String sqlString =
"SELECT * FROM OPERATION JOIN OPERATIONTYPE ON OPE_OCL_ID_A = OCL_ID_A ORDER BY OPE_DESC";
resultSet = dbQuery.getData(sqlString, true);
} else {
String sqlString =
"SELECT * FROM OPERATION JOIN OPERATIONTYPE ON OPE_OCL_ID_A = OCL_ID_A WHERE OCL_DESC LIKE CONCAT('%', ? , '%') ORDER BY OPE_DESC";
List<Object> parameters = Collections.<Object>singletonList(typeDescription);
resultSet = dbQuery.getDataWithParams(sqlString, parameters, true);
}
try {
operationList = new ArrayList<Operation>(resultSet.getFetchSize());
while (resultSet.next()) {
Operation operation =
new Operation(
resultSet.getString("OPE_ID_A"),
resultSet.getString("OPE_DESC"),
new OperationType(
resultSet.getString("OPE_OCL_ID_A"), resultSet.getString("OCL_DESC")),
resultSet.getInt("OPE_STAT"),
resultSet.getInt("OPE_LOCK"));
operationList.add(operation);
}
} catch (SQLException e) {
throw new OHException(
MessageBundle.getMessage("angal.sql.problemsoccurredwiththesqlistruction"), e);
} finally {
dbQuery.releaseConnection();
}
return operationList;
}
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);
}
public int getFetchSize() throws SQLException {
return rs.getFetchSize();
}
public static void fixUserCommon(int base) {
if (Configuration.getInstance() == null) {
Configuration.setInstance(ComptaPropsConfiguration.create());
}
Configuration instance = Configuration.getInstance();
SQLTable tableSociete = Configuration.getInstance().getBase().getTable("SOCIETE_COMMON");
String baseName = tableSociete.getRow(base).getString("DATABASE_NAME");
instance.getBase().getDBSystemRoot().getRootsToMap().clear();
try {
Set<String> s = new HashSet<String>();
s.add(baseName);
instance.getBase().fetchTables(s);
} catch (SQLException e) {
throw new IllegalStateException("could not access societe base", e);
}
System.err.println("baseName" + baseName);
instance.getSystemRoot().prependToRootPath("Common");
instance.getSystemRoot().prependToRootPath(baseName);
SQLSchema baseSQL = instance.getBase().getSchema(baseName);
DatabaseMetaData dbMetaDataSociete;
try {
dbMetaDataSociete = baseSQL.getBase().getDataSource().getConnection().getMetaData();
String[] type = new String[1];
type[0] = "TABLE";
ResultSet rs = dbMetaDataSociete.getTables("", baseSQL.getName(), "%", null);
System.err.println("Start " + rs.getFetchSize());
int i = 0;
while (rs.next()) {
if (rs.getString("TABLE_TYPE") != null
&& rs.getString("TABLE_TYPE").equalsIgnoreCase("TABLE")) {
// System.err.println("FIND TABLE");
// baseSQL.getTableNames();
final SQLTable table = baseSQL.getTable(rs.getString("TABLE_NAME"));
Set<SQLField> s = table.getFields();
for (SQLField field : s) {
if (field.getName().equalsIgnoreCase("ID_USER_COMMON_CREATE")
|| field.getName().equalsIgnoreCase("ID_USER_COMMON_MODIFY")) {
Object o = field.getDefaultValue();
if (o == null || (o instanceof Integer && ((Integer) o) == 0)) {
System.err.println("Bad default on " + field);
baseSQL
.getBase()
.execute(
"ALTER TABLE \""
+ field.getTable().getSchema().getName()
+ "\".\""
+ field.getTable().getName()
+ "\" ALTER COLUMN \""
+ field.getName()
+ "\" SET DEFAULT 1;");
baseSQL
.getBase()
.execute(
"UPDATE \""
+ field.getTable().getSchema().getName()
+ "\".\""
+ field.getTable().getName()
+ "\" SET \""
+ field.getName()
+ "\"=1 WHERE \""
+ field.getName()
+ "\"=0 OR \""
+ field.getName()
+ "\" IS NULL;");
}
}
}
}
// System.err.println(i++ + " " + rs.getString("TABLE_TYPE"));
}
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public int getFetchSize() throws SQLException {
return current.getFetchSize();
}
