public void updateServiceType(
CFSecurityAuthorization Authorization, CFSecurityServiceTypeBuff Buff) {
final String S_ProcName = "updateServiceType";
ResultSet resultSet = null;
try {
int ServiceTypeId = Buff.getRequiredServiceTypeId();
String Description = Buff.getRequiredDescription();
int Revision = Buff.getRequiredRevision();
Connection cnx = schema.getCnx();
String sql = "exec sp_update_svctype ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?";
if (stmtUpdateByPKey == null) {
stmtUpdateByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtUpdateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtUpdateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtUpdateByPKey.setString(argIdx++, "SVCT");
stmtUpdateByPKey.setInt(argIdx++, ServiceTypeId);
stmtUpdateByPKey.setString(argIdx++, Description);
stmtUpdateByPKey.setInt(argIdx++, Revision);
resultSet = stmtUpdateByPKey.executeQuery();
if (resultSet.next()) {
CFSecurityServiceTypeBuff updatedBuff = unpackServiceTypeResultSetToBuff(resultSet);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
Buff.setRequiredDescription(updatedBuff.getRequiredDescription());
Buff.setRequiredRevision(updatedBuff.getRequiredRevision());
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected a single-record response, " + resultSet.getRow() + " rows selected");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public void testBoolean() throws Throwable {
// String crtab = "create table #testBigInt (a bigint)";
String crtab = "create table #testBit (a BIT NULL)";
dropTable("#testBit");
Statement stmt = con.createStatement();
stmt.executeUpdate(crtab);
stmt.executeUpdate("insert into #testBit values (NULL)");
stmt.executeUpdate("insert into #testBit values (0)");
stmt.executeUpdate("insert into #testBit values (1)");
ResultSet rs = stmt.executeQuery("select * from #testBit where a is NULL");
rs.next();
rs.getBoolean(1);
rs = stmt.executeQuery("select * from #testBit where a = 0");
rs.next();
rs.getBoolean(1);
rs = stmt.executeQuery("select * from #testBit where a = 1");
rs.next();
rs.getBoolean(1);
stmt.close();
PreparedStatement pstmt = con.prepareStatement("insert into #testBit values (?)");
pstmt.setBoolean(1, true);
assertTrue(!pstmt.execute());
assertTrue(pstmt.getUpdateCount() == 1);
pstmt.setBoolean(1, false);
assertTrue(!pstmt.execute());
assertTrue(pstmt.getUpdateCount() == 1);
pstmt.setNull(1, java.sql.Types.BIT);
assertTrue(!pstmt.execute());
assertTrue(pstmt.getUpdateCount() == 1);
pstmt.close();
}
private String getInfo(ResultSet rs) throws SQLException {
// position to first record
boolean moreRecords = rs.next();
// If there are no records, display a message
if (!moreRecords) {
return null;
}
Vector columnHeads = new Vector();
Vector rows = new Vector();
try {
// get column heads
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); ++i)
columnHeads.addElement(rsmd.getColumnName(i));
// get row data
do {
rows.addElement(getNextRow(rs, rsmd));
} while (rs.next());
String info;
String aux = rows.get(0).toString();
info = aux.substring(1, aux.length() - 1);
return info;
} catch (SQLException sqlex) {
sqlex.printStackTrace();
return null;
}
}
private void initFromDatabase() throws SQLException, BlockStoreException {
Statement s = conn.get().createStatement();
ResultSet rs;
rs = s.executeQuery("SELECT value FROM settings WHERE name = '" + CHAIN_HEAD_SETTING + "'");
if (!rs.next()) {
throw new BlockStoreException("corrupt Postgres block store - no chain head pointer");
}
Sha256Hash hash = new Sha256Hash(rs.getBytes(1));
rs.close();
this.chainHeadBlock = get(hash);
this.chainHeadHash = hash;
if (this.chainHeadBlock == null) {
throw new BlockStoreException("corrupt Postgres block store - head block not found");
}
rs =
s.executeQuery(
"SELECT value FROM settings WHERE name = '" + VERIFIED_CHAIN_HEAD_SETTING + "'");
if (!rs.next()) {
throw new BlockStoreException(
"corrupt Postgres block store - no verified chain head pointer");
}
hash = new Sha256Hash(rs.getBytes(1));
rs.close();
s.close();
this.verifiedChainHeadBlock = get(hash);
this.verifiedChainHeadHash = hash;
if (this.verifiedChainHeadBlock == null) {
throw new BlockStoreException("corrupt Postgres block store - verified head block not found");
}
}
@BeforeClass
public static void setUpBeforeClass() throws Exception {
con.addCustomer(
"Buddy", "Bear", "1520 Garnet Ave", "", "San Diego", "CA", "92109", "4766666656");
con.addPublication("Runner Magazine", "Sports", 9.80, "Monthly", 5);
ResultSet r = con.searchCustomer(0, "Buddy", "");
try {
while (r.next()) {
testCustID = r.getInt("CustomerID");
}
r.close();
} catch (Exception e) {
e.printStackTrace();
}
ResultSet rs = con.searchPublication(0, "", "Runner Magazine");
try {
while (rs.next()) {
testPubID = rs.getInt("PublicationID");
}
} catch (Exception e) {
e.printStackTrace();
}
subscriptions newSub = new subscriptions(testCustID, testPubID);
}
public void updateOptionPrice(
String modelName, String optionSetName, String optionName, float newPrice) {
try {
int autoid = 0;
String sql = "select id from automobile where name ='" + modelName + "';";
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
autoid = rs.getInt("id"); // get auto_id
}
int opsid = 0;
sql =
"select id from optionset where name ='"
+ optionSetName
+ "' and auto_id= "
+ autoid
+ ";";
rs = statement.executeQuery(sql);
while (rs.next()) {
opsid = rs.getInt("id"); // get option_id
}
sql =
"update options set price = "
+ newPrice
+ " where name= '"
+ optionName
+ "' and option_id= "
+ opsid
+ ";";
statement.executeUpdate(sql); // update it with name and option_id
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void testReadDomainsAndGuids() throws Exception {
CustomerDAO dao = new CustomerDAO();
IConfiguration configuration = EasyMock.createStrictMock(IConfiguration.class);
dao.setConfiguration(configuration);
ResultSet resultSet = EasyMock.createStrictMock(ResultSet.class);
Set<String> domains = new HashSet<String>();
Set<GlobalIdentifier> guids = new HashSet<GlobalIdentifier>();
int custId = 34;
int cloudService = 2;
int replicationZone = 453;
int cloudService2 = 1;
int replicationZone2 = 13;
int cloudService3 = 3;
// first exec of loop
EasyMock.expect(resultSet.getString(10)).andReturn("domain123");
EasyMock.expect(resultSet.getString(11)).andReturn("guid123");
EasyMock.expect(resultSet.getInt(12)).andReturn(cloudService);
EasyMock.expect(resultSet.getInt(13)).andReturn(replicationZone);
EasyMock.expect(resultSet.next()).andReturn(true);
EasyMock.expect(resultSet.getInt(1)).andReturn(custId);
// second exec of loop
EasyMock.expect(resultSet.getString(10)).andReturn("domain456");
EasyMock.expect(resultSet.getString(11)).andReturn("guid456");
EasyMock.expect(resultSet.getInt(12)).andReturn(cloudService2);
EasyMock.expect(resultSet.getInt(13)).andReturn(replicationZone2);
EasyMock.expect(resultSet.next()).andReturn(true);
EasyMock.expect(resultSet.getInt(1)).andReturn(custId);
// third exec of loop (guid not valid with no cloud service)
EasyMock.expect(resultSet.getString(10)).andReturn("domain456");
EasyMock.expect(resultSet.getString(11)).andReturn("guid789");
EasyMock.expect(resultSet.getInt(12)).andReturn(cloudService3);
EasyMock.expect(resultSet.next()).andReturn(true);
EasyMock.expect(resultSet.getInt(1)).andReturn(custId + 1); // ends loop with mismatched custid
EasyMock.replay(resultSet);
assertTrue(
"Should have another item even.",
dao.readDomainsAndGuids(resultSet, custId, domains, guids));
EasyMock.verify(resultSet);
assertEquals("Should have 2 domains.", 2, domains.size());
assertTrue("Domain123 not found.", domains.contains("domain123"));
assertTrue("Domain456 not found.", domains.contains("domain456"));
assertEquals("Should have 2 guids.", 2, guids.size());
for (GlobalIdentifier guid : guids) {
if (guid.getGuid().equals("guid123")) {
assertEquals("Wrong cloud service in guid123", CloudService.GOOGLE, guid.getService());
assertEquals("Wrong replication zone.", replicationZone, guid.getReplicationZone());
} else {
assertEquals("Wrong cloud service in guid456", CloudService.OFFICE365, guid.getService());
assertEquals("Wrong replication zone.", replicationZone2, guid.getReplicationZone());
}
}
}
/**
* Returns the normalized Authority value for an author based on the name passed in. If no
* authority exists, null will be returned.
*
* @param author the author to get the authority information for
* @return the normalized authority information or null if no authority exists.
*/
public static String getNormalizedAuthorAuthorityFromDatabase(String author) {
if (!connectToDatabase()) {
return null;
} else {
try {
getPreferredAuthorByOriginalNameStmt.setString(1, author);
// First check without normalization
ResultSet originalNameResults = getPreferredAuthorByOriginalNameStmt.executeQuery();
if (originalNameResults.next()) {
String authority = originalNameResults.getString("normalizedName");
// Found a match
originalNameResults.close();
return authority;
} else {
// No match, check alternate names for the author
String normalizedAuthor = AuthorNormalizer.getNormalizedName(author);
getPreferredAuthorByAlternateNameStmt.setString(1, normalizedAuthor);
ResultSet alternateNameResults = getPreferredAuthorByAlternateNameStmt.executeQuery();
if (alternateNameResults.next()) {
String authority = alternateNameResults.getString("normalizedName");
alternateNameResults.close();
return authority;
}
}
} catch (Exception e) {
logger.error("Error loading authority information from database", e);
}
}
return null;
}
@Test
public void testUpsertDateValues() throws Exception {
long ts = nextTimestamp();
Date now = new Date(System.currentTimeMillis());
ensureTableCreated(getUrl(), TestUtil.PTSDB_NAME, null, ts - 2);
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String dateString = "1999-01-01 02:00:00";
PreparedStatement upsertStmt =
conn.prepareStatement(
"upsert into ptsdb(inst,host,date) values('aaa','bbb',to_date('" + dateString + "'))");
int rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
upsertStmt =
conn.prepareStatement(
"upsert into ptsdb(inst,host,date) values('ccc','ddd',current_date())");
rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
conn.commit();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String select = "SELECT date,current_date() FROM ptsdb";
ResultSet rs = conn.createStatement().executeQuery(select);
Date then = new Date(System.currentTimeMillis());
assertTrue(rs.next());
Date date = DateUtil.parseDate(dateString);
assertEquals(date, rs.getDate(1));
assertTrue(rs.next());
assertTrue(rs.getDate(1).after(now) && rs.getDate(1).before(then));
assertFalse(rs.next());
}
/**
* This method gets the moments happening after the reference moments, in particular the moments
* that occur in the range ]referenceMoment, referenceMoment + range{
*
* @param referenceMoment the reference moment to be used to as boundary
* @param range how many minutes after we want to capture (remember the ranges are not inclusive
* of the boundaries)
* @return a vector of moments that happened after the referenceMoment
*/
public Vector<Moment> getMomentsAfter(Moment referenceMoment, int range) throws SQLException {
Vector<Moment> before = new Vector<Moment>();
String query =
"SELECT * FROM minute WHERE id > "
+ referenceMoment.getMinute()
+ "AND id < "
+ String.valueOf(Integer.parseInt(referenceMoment.getMinute()) + range);
Statement st = this.connection.createStatement();
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
Moment moment = new Moment();
moment.setMinute(rs.getString("id"));
moment.setDate(rs.getString("date"));
moment.setLocation(rs.getString("location"));
moment.setActivity(rs.getString("activity"));
moment.setImagepath(rs.getString("image_path"));
String queryimages = "SELECT `image-path` FROM image WHERE id='" + moment.getMinute() + "'";
Statement subst = this.connection.createStatement();
ResultSet subrs = subst.executeQuery(queryimages);
while (subrs.next()) {
moment.getImages().add(subrs.getString("image-path"));
}
}
return before;
}
public Moment queryMoment(String image_path) throws SQLException {
Moment moment = new Moment();
String queryminute = "SELECT id FROM image WHERE `image-path`='" + image_path + "'";
Statement st = this.connection.createStatement();
ResultSet rs = st.executeQuery(queryminute);
String minute = "";
while (rs.next()) {
minute = rs.getString("id");
}
String query = "SELECT * FROM minute WHERE id='" + minute + "'";
st = this.connection.createStatement();
rs = st.executeQuery(query);
int counter = 0;
while (rs.next()) {
moment.setMinute(rs.getString("id"));
moment.setDate(rs.getString("date"));
moment.setLocation(rs.getString("location"));
moment.setActivity(rs.getString("activity"));
moment.setImagepath(image_path);
String queryimages = "SELECT `image-path` FROM image WHERE id='" + moment.getMinute() + "'";
Statement subst = this.connection.createStatement();
ResultSet subrs = subst.executeQuery(queryimages);
while (subrs.next()) {
moment.getImages().add(subrs.getString("image-path"));
}
counter++;
}
if (counter > 1)
System.err.println(
"Attention! More than a moment associated to image "
+ image_path
+ ". Returning only the last moment");
return moment;
}
public static void main(String args[]) {
try {
String urlBD = "jdbc:odbc:FOSA";
String usuarioBD = "SYSDBA";
String passwordBD = "masterkey";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conexion = DriverManager.getConnection(urlBD, usuarioBD, passwordBD);
Statement select = conexion.createStatement();
ResultSet resultadoSelect = select.executeQuery("SELECT MAX(NUM_REG) AS numreg FROM FACT01");
System.out.println("COMANDO EXITOSO");
System.out.println("numreg");
System.out.println("------");
int col = resultadoSelect.findColumn("numreg");
for (boolean seguir = resultadoSelect.next(); seguir; seguir = resultadoSelect.next())
System.out.println(resultadoSelect.getInt(col));
resultadoSelect.close();
select.close();
conexion.close();
} catch (SQLException ex) {
System.out.println("Error: SQLException");
while (ex != null) {
System.out.println(
(new StringBuilder()).append("SQLState: ").append(ex.getSQLState()).toString());
System.out.println(
(new StringBuilder()).append("Mensaje: ").append(ex.getMessage()).toString());
System.out.println(
(new StringBuilder()).append("Vendedor: ").append(ex.getErrorCode()).toString());
ex = ex.getNextException();
System.out.println("");
}
} catch (Exception ex) {
System.out.println("Se produjo un error inesperado");
}
}
/** Test large batch behavior. */
public void testLargeBatch() throws Exception {
final int n = 5000;
getConnection().close();
Statement stmt = con.createStatement();
stmt.executeUpdate("create table #testLargeBatch (val int)");
stmt.executeUpdate("insert into #testLargeBatch (val) values (0)");
PreparedStatement pstmt = con.prepareStatement("update #testLargeBatch set val=? where val=?");
for (int i = 0; i < n; i++) {
pstmt.setInt(1, i + 1);
pstmt.setInt(2, i);
pstmt.addBatch();
}
int counts[] = pstmt.executeBatch();
// System.out.println(pstmt.getWarnings());
assertEquals(n, counts.length);
for (int i = 0; i < n; i++) {
assertEquals(1, counts[i]);
}
pstmt.close();
ResultSet rs = stmt.executeQuery("select count(*) from #testLargeBatch");
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertFalse(rs.next());
rs.close();
stmt.close();
}
public boolean include(String file, String folder) {
try {
rs = stmt.executeQuery("SELECT id from Folders where name='" + folder + "'");
rs.next();
tmpMov = rs.getInt(1);
rs =
stmt.executeQuery(
"SELECT folder_id from Files where name='"
+ file
+ "' AND folder_id='"
+ tmpMov
+ "'");
rs.next();
int tmpf = rs.getInt(1);
if (tmpMov == tmpf) {
rs = stmt.executeQuery("SELECT id from Files where name='" + file + "'");
rs.next();
fileID = rs.getInt(1);
return true;
} else {
folderID = tmpMov;
return false;
}
} catch (SQLException e) {
folderID = tmpMov;
return false;
}
}
public void actionPerformed(ActionEvent ae) {
try {
if (ae.getSource() == b7) {
rs = st.executeQuery("select count(*) from employeemaster");
if (rs.next()) {
t6.setText(rs.getString(1));
}
rs = st.executeQuery("select * from Head");
if (rs.next()) {
t1.setText(rs.getString(1));
t2.setText(rs.getString(2));
t7.setText(rs.getString(3));
t4.setText(rs.getString(4));
t5.setText(rs.getString(5));
t3.setText(rs.getString(6));
}
} else if (ae.getSource() == b6) {
dispose();
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, "Error is" + e);
}
}
@Test
public void testBooleanString() throws SQLException {
Statement stmt = sharedConnection.createStatement();
stmt.execute("insert into booleanvalue values(true)");
stmt.execute("insert into booleanvalue values(false)");
stmt.execute("insert into booleanvalue values(4)");
ResultSet rs = stmt.executeQuery("select * from booleanvalue");
if (rs.next()) {
assertTrue(rs.getBoolean(1));
assertEquals("1", rs.getString(1));
if (rs.next()) {
assertFalse(rs.getBoolean(1));
assertEquals("0", rs.getString(1));
if (rs.next()) {
assertTrue(rs.getBoolean(1));
assertEquals("4", rs.getString(1));
} else {
fail("must have a result !");
}
} else {
fail("must have a result !");
}
} else {
fail("must have a result !");
}
}
public void checkTables(consumer<Boolean> consumer) {
Connection connection = null;
ResultSet result = null;
try {
connection = HikariManager.getInstance().getConnection();
DatabaseMetaData dbm = connection.getMetaData();
boolean playersExists = false;
boolean periodicExists = false;
boolean allExist = false;
result = dbm.getTables(null, null, HikariManager.getInstance().prefix + "players", null);
if (result.next()) playersExists = true;
result = dbm.getTables(null, null, HikariManager.getInstance().prefix + "periodic", null);
if (result.next()) periodicExists = true;
if (playersExists && periodicExists) allExist = true;
if (consumer != null) consumer.accept(allExist);
} catch (SQLException e) {
e.printStackTrace();
} finally {
HikariManager.getInstance().close(connection, null, result);
}
}
public static Customer[] getCustomers() {
Logger.log("Database.getCustomers", CALL_FLAG);
Customer[] customers = new Customer[0];
try {
String name;
String phoneNumber;
int id;
ResultSet rs = query("SELECT COUNT(*) FROM kunder");
rs.next();
customers = new Customer[rs.getInt("COUNT(*)")];
rs.next();
rs = query("SELECT * FROM kunder");
while (rs.next()) {
name = rs.getString("navn");
phoneNumber = rs.getString("telefon");
id = rs.getInt("kunde_id");
customers[rs.getRow() - 1] = new Customer(name, phoneNumber, id);
}
} catch (SQLException exception) {
Logger.log(exception, "Database:getCustomers");
}
Logger.log("Database.getCustomer Finised", FINISHED_FLAG);
return customers;
}
@Test
public void testUpsertValuesWithExpression() throws Exception {
long ts = nextTimestamp();
ensureTableCreated(getUrl(), "IntKeyTest", null, ts - 2);
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String upsert = "UPSERT INTO IntKeyTest VALUES(-1)";
PreparedStatement upsertStmt = conn.prepareStatement(upsert);
int rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
upsert = "UPSERT INTO IntKeyTest VALUES(1+2)";
upsertStmt = conn.prepareStatement(upsert);
rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
conn.commit();
conn.close();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String select = "SELECT i FROM IntKeyTest";
ResultSet rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(-1, rs.getInt(1));
assertTrue(rs.next());
assertEquals(3, rs.getInt(1));
assertFalse(rs.next());
}
public static Show[] getShows() {
Logger.log("Database.getShows", CALL_FLAG);
Show[] shows = new Show[0];
try {
Hall[] halls = getHalls();
Movie[] movies = getMovies();
Timestamp time;
Hall hall;
Movie movie;
int ID;
ResultSet rs = query("SELECT COUNT(*) FROM forestillinger");
rs.next();
shows = new Show[rs.getInt("COUNT(*)")];
rs = query("SELECT * FROM forestillinger ORDER BY tid ASC");
while (rs.next()) {
hall = halls[rs.getInt("sal_id") - 1];
movie = movies[rs.getInt("film_id") - 1];
time = rs.getTimestamp("tid");
ID = rs.getInt("forestilling_id");
shows[rs.getRow() - 1] = new Show(hall, movie, time, ID);
}
} catch (Exception exception) {
Logger.log(exception, "Database:getShows");
}
Logger.log("Database.getShows", FINISHED_FLAG);
return shows;
}
public void deleteISOTimezone(
CFSecurityAuthorization Authorization, CFSecurityISOTimezoneBuff Buff) {
final String S_ProcName = "deleteISOTimezone";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
short ISOTimezoneId = Buff.getRequiredISOTimezoneId();
String sql =
"SELECT "
+ schema.getLowerDbSchemaName()
+ ".sp_delete_isotz( ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ " ) as DeletedFlag";
if (stmtDeleteByPKey == null) {
stmtDeleteByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByPKey.setShort(argIdx++, ISOTimezoneId);
stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision());
;
resultSet = stmtDeleteByPKey.executeQuery();
if (resultSet.next()) {
boolean deleteFlag = resultSet.getBoolean(1);
if (resultSet.next()) {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response");
}
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected 1 record result set to be returned by delete, not 0 rows");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
private void okActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_okActionPerformed
try {
if (txtuser.getText().equals("") || txtpassword.getText().equals("")) {
JOptionPane.showMessageDialog(this, "Please Enter Username & Password", "Message", WIDTH);
} else { // connect
Class.forName("com.mysql.jdbc.Driver");
Connection conn =
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/student_information", "root", "123");
Statement stm = conn.createStatement();
String qry = "select password from login where username = '******' ;";
ResultSet rst = stm.executeQuery(qry);
Statement stm2 = conn.createStatement();
String qry2 = "select state from login where username = '******'; ";
ResultSet rst2 = stm2.executeQuery(qry2);
if (!rst.next()) { // validate username
JOptionPane.showMessageDialog(this, "Invalid Username", "Error", WIDTH);
} else if (rst.getString("password").equals(txtpassword.getText())) { // check password
MainWindow m = new MainWindow(); // open main
m.setVisible(true);
m.lbluser.setText(txtuser.getText());
if (rst2.next() && rst2.getInt("State") == 1) { // block lecturer
m.lblState.setText("Admin");
} else {
m.lblState.setText("Lecturer");
/* MainWindow mw1 = new MainWindow();
mw1.btnStudentm.setVisible(false);
mw1.btnCoursem.setVisible(false);
mw1.btnLecturerm.setVisible(false);
mw1.btnAdmin.setVisible(false);
mw1.lbllec.setVisible(false);*/
}
this.setVisible(false);
txtuser.setText("");
txtpassword.setText("");
} else {
JOptionPane.showMessageDialog(this, "Invalid Password", "Error", WIDTH);
}
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, "Error in Excecution " + e, "Error", WIDTH);
}
} // GEN-LAST:event_okActionPerformed
@Test
public void testCompareLongGTEDecimal() throws Exception {
long ts = nextTimestamp();
initTableValues(null, ts);
String query = "SELECT l FROM LongInKeyTest where l >= 1.5";
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
/*
* Failing because we're not converting the constant to the type of the RHS
* when forming the start/stop key.
* For this case, 1.5 -> 1L
* if where l < 1.5 then 1.5 -> 1L and then to 2L because it's not inclusive
*
*/
assertTrue(rs.next());
assertEquals(2, rs.getLong(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
/**
* Remove a student from a particular course. Also Deletes all the quiz vizualisation files in the
* student's directory which relates to the course. Caution: vizualisation file will be deleted
* eventhough it also relates to another course if the student is also registered to that course.
* (FIX ME!) Throws InvalidDBRequestException if the student is not registered in the course,
* error occured during deletion, or other exception occured.
*
* @param username student's user name
* @param courseID course id (course number + instructor name)
* @throws InvalidDBRequestException
*/
public void deleteStudent(String username, String courseID) throws InvalidDBRequestException {
try {
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
ResultSet rs;
int count = 0;
// check if student registered to the course
rs =
stmt.executeQuery(
"select * from courseRoster where course_id = '"
+ courseID
+ "' and user_login = '******'");
if (!rs.next())
throw new InvalidDBRequestException("Student is not registered to the course");
// remove student from the course
count =
stmt.executeUpdate(
"delete from courseRoster where course_id = '"
+ courseID
+ "' and user_login = '******'");
if (count != 1) throw new InvalidDBRequestException("Error occured during deletion!");
// delete the quiz visualization files
rs =
stmt.executeQuery(
"select distinct unique_id, s.test_name from scores s, courseTest t "
+ "where s.test_name = t.test_name "
+ "and course_id = '"
+ courseID
+ "' "
+ "and user_login = '******'");
while (rs.next()) {
deleteVisualization(rs.getString(1), username, rs.getString(2));
count =
stmt.executeUpdate("delete from scores where unique_id = " + rs.getString(1).trim());
}
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in addstudent: " + e.getMessage());
throw new InvalidDBRequestException("???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
}
public void deleteOption(String modelName, String optionSetName, String option) {
try {
int autoid = 0;
String sql = "select id from automobile where name ='" + modelName + "';";
ResultSet rs;
rs = statement.executeQuery(sql);
while (rs.next()) {
autoid = rs.getInt("id"); // get auto_id
}
int opsid = 0;
sql =
"select id from optionset where name ='"
+ optionSetName
+ "' and auto_id= "
+ autoid
+ ";";
rs = statement.executeQuery(sql);
while (rs.next()) {
opsid = rs.getInt("id"); // get option_id
}
sql = "delete from options where name= '" + option + "' and option_id= " + opsid;
statement.executeUpdate(sql); // delete it using name and option_id
} catch (SQLException e) {
e.printStackTrace();
}
}
private List<User> doQuery(String sqlStmt) throws Exception {
ArrayList<User> resultList = new ArrayList<User>();
// Try-with-resources. Both Statement and ResultSet are AutoCloseable
try (Statement statement = connection.createStatement(); // forward-only, read-only result set,
// http://www.tutorialspoint.com/jdbc/jdbc-result-sets.htm
ResultSet rs = statement.executeQuery(sqlStmt); ) {
if (!rs.next()) { // positioned to first record, and then found there were none
// http://stackoverflow.com/questions/8292256/get-number-of-rows-returned-by-resultset-in-java
return null;
} else {
// get the rows
do {
User user =
new User(
rs.getString("firstname"),
rs.getString("lastname"),
rs.getString("username"),
rs.getString("email"));
resultList.add(user);
} while (rs.next());
}
} catch (SQLException se) {
se.printStackTrace();
throw (se); // rethrow
}
return resultList;
}
public CFSecurityTSecGroupBuff lockBuff(
CFSecurityAuthorization Authorization, CFSecurityTSecGroupPKey PKey) {
final String S_ProcName = "lockBuff";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
long TenantId = PKey.getRequiredTenantId();
int TSecGroupId = PKey.getRequiredTSecGroupId();
String sql =
"SELECT * FROM "
+ schema.getLowerDbSchemaName()
+ ".sp_lock_tsecgrp( ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ " )";
if (stmtLockBuffByPKey == null) {
stmtLockBuffByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtLockBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtLockBuffByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtLockBuffByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtLockBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtLockBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtLockBuffByPKey.setLong(argIdx++, TenantId);
stmtLockBuffByPKey.setInt(argIdx++, TSecGroupId);
resultSet = stmtLockBuffByPKey.executeQuery();
if (resultSet.next()) {
CFSecurityTSecGroupBuff buff = unpackTSecGroupResultSetToBuff(resultSet);
if (resultSet.next()) {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response");
}
return (buff);
} else {
return (null);
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
/**
* Helper function intended to be overwritten by subclasses. Thsi is where the real requiest for
* IDs happens
*/
protected void performIDRequest() throws Exception {
Connection dbConnection = null;
try {
try {
dbConnection = dataSource.getConnection();
Statement stmt =
dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet set = null;
set =
stmt.executeQuery(
"SELECT id, " + dbColumn + " FROM " + dbTable); // $NON-NLS-1$ //$NON-NLS-2$
if (!set.next()) {
set.moveToInsertRow();
set.insertRow();
set.updateLong(dbColumn, NUM_IDS_GRABBED);
set.moveToCurrentRow();
set.next();
}
long nextID = set.getLong(dbColumn);
long upTo = nextID + mCacheQuantity;
set.updateLong(dbColumn, upTo);
set.updateRow();
stmt.close();
setMaxAllowedID(upTo);
setNextID(nextID);
} finally {
if (dbConnection != null) {
dbConnection.close();
}
}
} catch (SQLException e) {
throw new NoMoreIDsException(e);
}
}
public String query7() {
String name, coach, output;
int budget;
String sqlText;
try {
sql = connection.createStatement();
sqlText =
"CREATE VIEW topScorer AS SELECT cid, MAX(goals) AS scores FROM player GROUP BY cid";
sql.executeUpdate(sqlText);
sqlText = "CREATE VIEW maxScore AS SELECT MAX(scores) AS maxscore FROM topScorer";
sql.executeUpdate(sqlText);
sqlText =
"CREATE VIEW topScorerTeam AS SELECT cid FROM topScorer JOIN maxScore ON (topScorer.scores = maxScore.maxscore)";
sql.executeUpdate(sqlText);
sqlText =
"CREATE VIEW budgetTeam AS SELECT cid, SUM(value) AS budget FROM player GROUP BY cid";
sql.executeUpdate(sqlText);
sqlText = "CREATE VIEW lowestBudget AS SELECT MIN(budget) AS minbudget FROM budgetTeam";
sql.executeUpdate(sqlText);
sqlText =
"CREATE VIEW lowestBudgetTeam AS SELECT cid, minbudget AS budget FROM lowestBudget JOIN budgetTeam ON (lowestBudget.minbudget = budgetTeam.budget)";
sql.executeUpdate(sqlText);
sqlText =
"CREATE VIEW countryLowestBudgetTopScorer AS SELECT topScorerTeam.cid AS cid, budget FROM topScorerTeam JOIN lowestBudgetTeam ON topScorerTeam.cid = lowestBudgetTeam.cid";
sql.executeUpdate(sqlText);
// We project Query7 over here.
sqlText =
"SELECT name, coach, budget FROM countryLowestBudgetTopScorer JOIN country ON (countryLowestBudgetTopScorer.cid = country.cid)";
rs = sql.executeQuery(sqlText);
if (!rs.isBeforeFirst()) {
return "";
} else {
rs.next();
// Query7 (String name, String coach, integer budget)
name = rs.getString("name");
coach = rs.getString("coach");
budget = rs.getInt("budget");
output = name + ":" + coach + ":" + budget;
while (rs.next()) {
name = rs.getString("name");
coach = rs.getString("coach");
budget = rs.getInt("budget");
output += "#" + name + ":" + coach + ":" + budget;
}
return output;
}
} catch (SQLException e) {
return "";
}
}
public static void main(String[] args) {
try {
Connection con = DBopsMySql.setuptradesConnection();
con.setAutoCommit(false);
String ul = "AUD";
PreparedStatement pstmt = DBopsMySql.getExpirysForUpdate(con, ul, 20090300, 20100700);
PreparedStatement upDateStmt = DBopsMySql.updateBeginEndDatesForExpiry(con);
ResultSet res = pstmt.executeQuery();
res.next(); // To get a lastexpiry for loop, so should be one extra early expiry
int lastexp = res.getInt("expiry");
while (res.next()) {
int exp = res.getInt("expiry");
String bdate = DateOps.dbShortFormatString(lastexp - 5);
String edate = DateOps.dbShortFormatString(exp - 6);
upDateStmt.setString(1, bdate);
if (!res.isLast()) {
upDateStmt.setString(2, edate);
}
upDateStmt.setString(3, ul);
upDateStmt.setInt(4, exp);
upDateStmt.addBatch();
lastexp = exp;
}
int[] updateCounts = upDateStmt.executeBatch();
upDateStmt.close();
con.close();
} catch (SQLException ex) {
MsgBox.err2(ex);
}
}
