List of usage examples for java.sql Connection setCatalog
void setCatalog(String catalog) throws SQLException;
Connection object's database in which to work. From source file:de.uniwue.info6.database.jdbc.ConnectionManager.java
/** * * * @param scenario/*www. j ava2s .co m*/ * @return * * @throws SQLException */ public synchronized Connection getConnection(Scenario scenario) throws SQLException { Connection connection = null; if (scenario != null) { if (pools.containsKey(scenario)) { try { ComboPooledDataSource pool = pools.get(scenario); connection = pool.getConnection(); boolean[] dbExists = checkIfDBExists(connection, scenario.getDbName()); if (!dbExists[0]) { this.createDatabase(connection, scenario.getDbName()); } connection.setCatalog(scenario.getDbName()); } catch (Exception exception) { errors.put(scenario, exception.getMessage()); throw exception; } } } return connection; }
From source file:de.uniwue.info6.database.jdbc.ConnectionManager.java
/** * * * @param dbUser//from w w w. j ava 2 s . c om * @param dbPass * @param dbName * @param url * @return */ public boolean[] checkIfDBExists(final Connection connection, final String dbName) { Statement statement = null; ResultSet resultSet = null; boolean catalogExists = false; boolean tableExists = false; try { Class.forName("org.mariadb.jdbc.Driver"); // Register JDBC Driver resultSet = connection.getMetaData().getCatalogs(); while (resultSet.next()) { String databaseName = resultSet.getString(1); if (databaseName.equalsIgnoreCase(dbName)) { catalogExists = true; break; } } connection.setCatalog(dbName); ResultSet rs = connection.getMetaData().getTables(null, null, "%", null); while (rs.next()) { tableExists = true; } } catch (Exception e) { return new boolean[] { false, false }; } finally { try { if (resultSet != null) { resultSet.close(); } if (statement != null) { statement.close(); } } catch (Exception e) { e.printStackTrace(); } } return new boolean[] { catalogExists, tableExists }; }
From source file:edu.ku.brc.specify.conversion.SpecifyDBConverter.java
/** * @return// ww w . j a v a 2s. co m * @throws SQLException */ public Pair<String, String> chooseTable(final String title, final String subTitle, final boolean doSp5DBs) throws SQLException { MySQLDMBSUserMgr mgr = new MySQLDMBSUserMgr(); Vector<DBNamePair> availPairs = new Vector<DBNamePair>(); if (mgr.connectToDBMS(itUsrPwd.first, itUsrPwd.second, hostName)) { BasicSQLUtils.setSkipTrackExceptions(true); //String sql = String.format("SELECT DISTINCT `COLUMNS`.TABLE_SCHEMA FROM `COLUMNS` WHERE `COLUMNS`.TABLE_NAME = '%s'", doSp5DBs ? "collectionobjectcatalog" : "taxon"); Connection conn = mgr.getConnection(); Vector<Object[]> dbNames = BasicSQLUtils.query(conn, "show databases"); for (Object[] row : dbNames) { String dbName = row[0].toString(); //System.out.print("Database Found ["+dbName+"] "); conn.setCatalog(dbName); boolean isSp5DB = false; Vector<Object[]> tables = BasicSQLUtils.query(conn, "show tables"); for (Object[] tblRow : tables) { String tableName = tblRow[0].toString(); if (tableName.equalsIgnoreCase("usysversion")) { isSp5DB = true; break; } } if ((!isSp5DB && doSp5DBs) || (isSp5DB && !doSp5DBs)) { continue; } // make all table names lowercase try { Integer count = BasicSQLUtils.getCount(conn, "select COUNT(*) FROM collection"); if (count == null) { for (Object[] tblRow : tables) { String tableName = tblRow[0].toString(); if (!tableName.equals(tableName.toLowerCase())) { BasicSQLUtils.update(conn, "RENAME TABLE " + tableName + " TO " + tableName.toLowerCase()); } } } } catch (Exception ex) { ex.printStackTrace(); } Vector<Object> tableDesc = BasicSQLUtils.querySingleCol(conn, "select CollectionName FROM collection"); if (tableDesc.size() > 0) { String collName = tableDesc.get(0).toString(); availPairs.add(new DBNamePair(collName, row[0].toString())); } } Collections.sort(availPairs, new Comparator<Pair<String, String>>() { @Override public int compare(Pair<String, String> o1, Pair<String, String> o2) { return o1.second.compareTo(o2.second); } }); mgr.close(); BasicSQLUtils.setSkipTrackExceptions(false); final JList list = new JList(availPairs); CellConstraints cc = new CellConstraints(); PanelBuilder pb = new PanelBuilder(new FormLayout("f:p:g", "p,4px,f:p:g")); pb.add(UIHelper.createLabel(subTitle), cc.xy(1, 1)); pb.add(UIHelper.createScrollPane(list, true), cc.xy(1, 3)); pb.setDefaultDialogBorder(); final CustomDialog dlg = new CustomDialog(null, title, true, pb.getPanel()); list.addListSelectionListener(new ListSelectionListener() { @Override public void valueChanged(ListSelectionEvent e) { if (!e.getValueIsAdjusting()) { dlg.getOkBtn().setEnabled(list.getSelectedIndex() > -1); } } }); list.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); list.addMouseListener(new MouseAdapter() { @Override public void mouseClicked(MouseEvent e) { if (e.getClickCount() == 2) { dlg.getOkBtn().setEnabled(list.getSelectedIndex() > -1); dlg.getOkBtn().doClick(); } } }); dlg.createUI(); dlg.pack(); Dimension d = dlg.getPreferredSize(); d.height = 700; dlg.setSize(d); UIHelper.centerAndShow(dlg); if (dlg.isCancelled()) { return null; } return namePairToConvert = (DBNamePair) list.getSelectedValue(); } return null; }
From source file:edu.ku.brc.specify.conversion.ConvertVerifier.java
/** * @return//from www. j av a 2s . c o m * @throws SQLException */ private Pair<String, String> chooseTable() throws SQLException { MySQLDMBSUserMgr mgr = new MySQLDMBSUserMgr(); final Vector<DBNamePair> availOldPairs = new Vector<DBNamePair>(); final Vector<DBNamePair> availNewPairs = new Vector<DBNamePair>(); try { if (mgr.connectToDBMS(itUsrPwd.first, itUsrPwd.second, hostName)) { BasicSQLUtils.setSkipTrackExceptions(true); Connection conn = mgr.getConnection(); Vector<Object[]> dbNames = BasicSQLUtils.query(conn, "show databases"); for (Object[] row : dbNames) { System.err.println("Setting [" + row[0].toString() + "] "); conn.setCatalog(row[0].toString()); boolean isSp5 = false; boolean isSp6 = false; Vector<Object[]> tables = BasicSQLUtils.query(conn, "show tables"); for (Object[] tblRow : tables) { if (row[0].toString().equals("debugdb")) { System.err.println(tblRow[0].toString()); } if (tblRow[0].toString().equals("usysversion")) { isSp5 = true; break; } else if (tblRow[0].toString().equals("gift")) { isSp6 = true; break; } } if (isSp5 || isSp6) { String collName = null; Vector<Object[]> tableDesc = BasicSQLUtils.query(conn, "SELECT CollectionName FROM collection"); if (tableDesc.size() > 0) { collName = tableDesc.get(0)[0].toString(); } if (collName == null) { continue; } if (isSp5) { availOldPairs.add(new DBNamePair(collName, row[0].toString())); } else { availNewPairs.add(new DBNamePair(collName, row[0].toString())); } } System.err.println("isSp5 [" + isSp5 + "] isSp6 [" + isSp6 + "] "); } Comparator<Pair<String, String>> comparator = new Comparator<Pair<String, String>>() { @Override public int compare(Pair<String, String> o1, Pair<String, String> o2) { return o1.second.compareTo(o2.second); } }; Collections.sort(availOldPairs, comparator); Collections.sort(availNewPairs, comparator); mgr.close(); BasicSQLUtils.setSkipTrackExceptions(false); final JList oldlist = new JList(availOldPairs); final JList newList = new JList(availNewPairs); CellConstraints cc = new CellConstraints(); PanelBuilder pb = new PanelBuilder(new FormLayout("f:p:g,10px,f:p:g", "p,2px,f:p:g,4px,p")); pb.addSeparator("Specify 5 Databases", cc.xy(1, 1)); pb.add(UIHelper.createScrollPane(oldlist), cc.xy(1, 3)); pb.addSeparator("Specify 6 Databases", cc.xy(3, 1)); pb.add(UIHelper.createScrollPane(newList), cc.xy(3, 3)); ArrayList<String> list = new ArrayList<String>(labels.length); for (String s : labels) { list.add(s); } chkPanel = new ToggleButtonChooserPanel<String>(list, Type.Checkbox); chkPanel.setUseScrollPane(true); chkPanel.createUI(); //pb.add(chkPanel, cc.xyw(1, 5, 3)); /*ActionListener al = new ActionListener() { @Override public void actionPerformed(ActionEvent e) { boolean isSelected = chkPanel.getButtons().get(0).isSelected(); int inx = chkPanel.getSelectedIndex(); if (inx == 0) { Vector<JToggleButton> btns = chkPanel.getButtons(); for (int i=1;i<btns.size();i++) { btns.get(i).setEnabled(!isSelected); } } } }; chkPanel.getButtons().get(0).addActionListener(al); chkPanel.getButtons().get(chkPanel.getButtons().size()-1).addActionListener(al);*/ ListSelectionListener oldDBListener = new ListSelectionListener() { @Override public void valueChanged(ListSelectionEvent e) { if (!e.getValueIsAdjusting()) { DBNamePair pair = (DBNamePair) oldlist.getSelectedValue(); if (pair != null) { int index = 0; for (DBNamePair p : availNewPairs) { if (p.second.startsWith(pair.second)) { final int inx = index; SwingUtilities.invokeLater(new Runnable() { @Override public void run() { newList.setSelectedIndex(inx); newList.ensureIndexIsVisible(inx); } }); } index++; } } } } }; oldlist.getSelectionModel().addListSelectionListener(oldDBListener); MouseAdapter ma = new MouseAdapter() { @Override public void mouseClicked(MouseEvent e) { super.mouseClicked(e); Vector<JToggleButton> btns = chkPanel.getButtons(); if (e.getSource() == btns.get(0)) { boolean isSelected = btns.get(0).isSelected(); for (int i = 1; i < btns.size(); i++) { btns.get(i).setEnabled(!isSelected); } } else if (e.getSource() == btns.get(btns.size() - 1)) { boolean isSelected = btns.get(btns.size() - 1).isSelected(); for (int i = 0; i < btns.size() - 1; i++) { if (i > 0) btns.get(i).setSelected(!isSelected); btns.get(i).setEnabled(!isSelected); } } } }; chkPanel.getButtons().get(0).addMouseListener(ma); chkPanel.getButtons().get(chkPanel.getButtons().size() - 1).addMouseListener(ma); /*ChangeListener cl = new ChangeListener() { @Override public void stateChanged(ChangeEvent e) { Vector<JToggleButton> btns = chkPanel.getButtons(); if (e.getSource() == btns.get(0)) { boolean isSelected = btns.get(0).isSelected(); System.out.println(isSelected); for (int i=1;i<btns.size();i++) { btns.get(i).setEnabled(!isSelected); } } else if (e.getSource() == btns.get(btns.size()-1)) { boolean isSelected = btns.get(0).isSelected(); System.out.println(isSelected); for (int i=0;i<btns.size()-1;i++) { btns.get(i).setEnabled(!isSelected); } } } }; chkPanel.getButtons().get(0).addChangeListener(cl); chkPanel.getButtons().get(chkPanel.getButtons().size()-1).addChangeListener(cl);*/ pb.setDefaultDialogBorder(); final CustomDialog dlg = new CustomDialog(null, "Select a DB to Verify", true, pb.getPanel()); ListSelectionListener lsl = new ListSelectionListener() { @Override public void valueChanged(ListSelectionEvent e) { if (!e.getValueIsAdjusting()) { dlg.getOkBtn().setEnabled(oldlist.getSelectedIndex() > -1); } } }; oldlist.addListSelectionListener(lsl); newList.addListSelectionListener(lsl); oldlist.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); newList.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); MouseAdapter listMA = new MouseAdapter() { @Override public void mouseClicked(MouseEvent e) { if (e.getClickCount() == 2) { dlg.getOkBtn() .setEnabled(oldlist.getSelectedIndex() > -1 && newList.getSelectedIndex() > -1); dlg.getOkBtn().doClick(); } } }; oldlist.addMouseListener(listMA); newList.addMouseListener(listMA); dlg.createUI(); dlg.pack(); //dlg.setSize(300, 800); dlg.pack(); dlg.setVisible(true); if (dlg.isCancelled()) { return null; } DBNamePair oldPair = (DBNamePair) oldlist.getSelectedValue(); namePairToConvert = (DBNamePair) newList.getSelectedValue(); namePairToConvert.first = oldPair.second; return namePairToConvert; } } catch (Exception ex) { } return null; }
From source file:edu.ku.brc.specify.web.SpecifyExplorer.java
/** * Drops, Creates and Builds the Database. * /* w w w . j av a2 s . c o m*/ * @throws SQLException * @throws IOException */ public boolean setupDatabase(final DatabaseDriverInfo driverInfo, final String hostName, final String dbName, final String username, final String password, final String firstName, final String lastName, final String email, final DisciplineType disciplineType) { log.info("Logging into " + dbName + "...."); //String connStr = driverInfo.getConnectionStr(DatabaseDriverInfo.ConnectionType.Create, hostName, dbName); //if (connStr == null) //{ String connStr = driverInfo.getConnectionStr(DatabaseDriverInfo.ConnectionType.Open, hostName, dbName); //} log.info(connStr); //Pair<String, String> usernamePassword = UserAndMasterPasswordMgr.getInstance().getUserNamePasswordForDB(); if (!UIHelper.tryLogin(driverInfo.getDriverClassName(), driverInfo.getDialectClassName(), dbName, connStr, username, password)) { log.info("Login Failed!"); throw new RuntimeException("Login failed."); //return false; } if (false) { try { Connection conn = DBConnection.getInstance().createConnection(); conn.setCatalog("webmember"); WebMemberDAO wm = new WebMemberDAO(conn); wm.createTable(); conn.close(); } catch (Exception ex) { ex.printStackTrace(); } } if (false) { try { Connection conn = DBConnection.getInstance().createConnection(); conn.setCatalog("webmember"); WebMemberDAO wm = new WebMemberDAO(conn); wm.addWebMember("Test", "db", "10"); conn.close(); } catch (Exception ex) { ex.printStackTrace(); } } queryHandler = new QueryReportHandler(); log.info("Creating database " + dbName + "...."); /* try { Thumbnailer thumb = Thumbnailer.getInstance(); thumb.registerThumbnailers("config/thumbnail_generators.xml"); thumb.setQuality(.5f); thumb.setMaxSize(128); thumb.setMaxWidth(128); AttachmentManagerIface attachMgr = new FileStoreAttachmentManager(UIRegistry.getAppDataSubDir("AttachmentStorage", true)); AttachmentUtils.setAttachmentManager(attachMgr); AttachmentUtils.setThumbnailer(thumb); } catch (Exception ex) { ex.printStackTrace(); return false; } */ if (true) { AppPreferences localPrefs = AppPreferences.getLocalPrefs(); localPrefs.setDirPath(UIRegistry.getAppDataDir()); AppContextMgr.CONTEXT_STATUS status = AppContextMgr.getInstance().setContext(DATABASE_NAME, "testuser", false, true, false); if (status == AppContextMgr.CONTEXT_STATUS.OK) { if (AppContextMgr.getInstance().getClassObject(Discipline.class) != null) { int disciplineeId = AppContextMgr.getInstance().getClassObject(Discipline.class) .getDisciplineId(); SchemaI18NService.getInstance().loadWithLocale(SpLocaleContainer.CORE_SCHEMA, disciplineeId, DBTableIdMgr.getInstance(), Locale.getDefault()); } } else { throw new RuntimeException("setContext was NOT OK!"); } } else { SpecifyUser user = null; String userName = "rods"; boolean debug = true; DataProviderSessionIFace session = null; try { session = DataProviderFactory.getInstance().createSession(); List<?> list = session.getDataList(SpecifyUser.class, "name", userName); if (list.size() == 1) { user = (SpecifyUser) list.get(0); user.getAgents(); // makes sure the Agent is not lazy loaded //session.evict( user.getAgents() ); AppContextMgr.getInstance().setClassObject(SpecifyUser.class, user); } else { //JOptionPane.showMessageDialog(null, // getResourceString("USER_NOT_FOUND"), // getResourceString("USER_NOT_FOUND_TITLE"), JOptionPane.WARNING_MESSAGE); return false; //throw new RuntimeException("The user ["+userName+"] could not be located as a Specify user."); } // First we start by getting all the Collection that the User want to // work with for this "Context" then we need to go get all the Default View and // additional XML Resources. // Ask the User to choose which Collection they will be working with Collection collection = setupCurrentCollection(session, user, "Fish"); if (collection == null) { // Return false but don't mess with anything that has been set up so far return false; } String userType = user.getUserType(); if (debug) log.debug("User[" + user.getName() + "] Type[" + userType + "]"); userType = StringUtils.replace(userType, " ", "").toLowerCase(); if (debug) log.debug("Def Type[" + userType + "]"); Discipline discipline = session.getData(Discipline.class, "disciplineId", collection.getDiscipline().getId(), DataProviderSessionIFace.CompareType.Equals); //discipline.getDeterminationStatuss().size(); // make sure they are loaded AppContextMgr.getInstance().setClassObject(Discipline.class, discipline); AppContextMgr.getInstance().setClassObject(Division.class, discipline.getDivision()); } catch (Exception ex) { log.error(ex); } finally { session.close(); } } // AppContextMgr.getInstance(). //SpecifyAppPrefs.initialPrefs(); AppPreferences localPrefs = AppPreferences.getLocalPrefs(); localPrefs.setDirPath(UIRegistry.getAppDataDir()); SpecifyAppPrefs.initialPrefs(); fmtMgr = new MyFmtMgr(); return true; }
From source file:edu.ku.brc.specify.dbsupport.SpecifySchemaUpdateService.java
/** * Fixes the Schema for Database Version 1.2 * @param conn/*from w w w.j ava 2 s . co m*/ * @throws Exception */ private boolean doFixesForDBSchemaVersions(final Connection conn, final String databaseName) throws Exception { ///////////////////////////// // PaleoContext ///////////////////////////// getTableNameAndTitleForFrame(PaleoContext.getClassTableId()); Integer len = getFieldLength(conn, databaseName, "paleocontext", "Text1"); alterFieldLength(conn, databaseName, "paleocontext", "Text1", 32, 64); alterFieldLength(conn, databaseName, "paleocontext", "Text2", 32, 64); len = getFieldLength(conn, databaseName, "paleocontext", "Remarks"); if (len == null) { int count = BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM paleocontext"); int rv = update(conn, "ALTER TABLE paleocontext ADD Remarks VARCHAR(60)"); if (rv != count) { errMsgList.add("Error updating PaleoContext.Remarks"); return false; } } frame.incOverall(); DBConnection dbc = DBConnection.getInstance(); ///////////////////////////// // FieldNotebookPage ///////////////////////////// getTableNameAndTitleForFrame(FieldNotebookPage.getClassTableId()); len = getFieldLength(conn, databaseName, "fieldnotebookpage", "PageNumber"); if (len != null && len == 16) { alterFieldLength(conn, databaseName, "fieldnotebookpage", "PageNumber", 16, 32); update(conn, "ALTER TABLE fieldnotebookpage ALTER COLUMN ScanDate DROP DEFAULT"); } frame.incOverall(); ///////////////////////////// // Project Table ///////////////////////////// alterFieldLength(conn, databaseName, "project", "projectname", 50, 128); frame.incOverall(); ///////////////////////////// // AttachmentImageAttribute Table ///////////////////////////// if (doesTableExist(databaseName, "attachmentimageattribute")) { alterFieldLength(conn, databaseName, "attachmentimageattribute", "CreativeCommons", 128, 500); frame.incOverall(); } ///////////////////////////// // LocalityDetail ///////////////////////////// String tblName = getTableNameAndTitleForFrame(LocalityDetail.getClassTableId()); boolean statusOK = true; String sql = String.format( "SELECT COUNT(*) FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = 'localitydetail' AND COLUMN_NAME = 'UtmScale' AND DATA_TYPE = 'varchar'", dbc.getDatabaseName()); int count = BasicSQLUtils.getCountAsInt(sql); if (count > 0) { Vector<Object[]> values = query("SELECT ld.LocalityDetailID, ld.UtmScale, l.LocalityName " + "FROM localitydetail ld INNER JOIN locality l ON ld.LocalityID = l.LocalityID WHERE ld.UtmScale IS NOT NULL"); update(conn, "ALTER TABLE localitydetail DROP COLUMN UtmScale"); addColumn(conn, databaseName, tblName, "UtmScale", "FLOAT", "UtmOrigLongitude"); addColumn(conn, databaseName, tblName, "MgrsZone", "VARCHAR(4)", "UtmScale"); HashMap<String, String> badLocalitiesHash = new HashMap<String, String>(); try { PreparedStatement pStmt = conn .prepareStatement("UPDATE localitydetail SET UtmScale=? WHERE LocalityDetailID=?"); for (Object[] row : values) { Integer locDetailId = (Integer) row[0]; String scale = (String) row[1]; String locName = (String) row[2]; scale = StringUtils.contains(scale, ',') ? StringUtils.replace(scale, ",", "") : scale; if (!StringUtils.isNumeric(scale)) { badLocalitiesHash.put(locName, scale); continue; } float scaleFloat = 0.0f; try { scaleFloat = Float.parseFloat(scale); } catch (NumberFormatException ex) { badLocalitiesHash.put(locName, scale); continue; } pStmt.setFloat(1, scaleFloat); pStmt.setInt(2, locDetailId); pStmt.execute(); } pStmt.close(); } catch (SQLException ex) { statusOK = false; } if (badLocalitiesHash.size() > 0) { try { File file = new File( UIRegistry.getUserHomeDir() + File.separator + "localitydetailerrors.html"); TableWriter tblWriter = new TableWriter(file.getAbsolutePath(), "Locality Detail Errors"); tblWriter.startTable(); tblWriter.logHdr(new String[] { "Locality Name", "Scale" }); for (String key : badLocalitiesHash.keySet()) { tblWriter.log(key, badLocalitiesHash.get(key)); } tblWriter.endTable(); tblWriter.flush(); tblWriter.close(); UIRegistry.showLocalizedError("LOC_DETAIL_ERRORS", badLocalitiesHash.size(), file.getAbsoluteFile()); badLocalitiesHash.clear(); if (file.exists()) { try { AttachmentUtils.openURI(file.toURI()); } catch (Exception ex) { ex.printStackTrace(); } } } catch (IOException ex) { ex.printStackTrace(); } } } else { addColumn(conn, databaseName, tblName, "UtmScale", "FLOAT", "UtmOrigLongitude"); } frame.incOverall(); ////////////////////////////////////////////// // collectingeventattribute Schema 1.3 ////////////////////////////////////////////// DBMSUserMgr dbmsMgr = DBMSUserMgr.getInstance(); if (dbmsMgr.connectToDBMS(itUserNamePassword.first, itUserNamePassword.second, dbc.getServerName())) { boolean status = true; Connection connection = dbmsMgr.getConnection(); try { // Add New Fields to Determination tblName = getTableNameAndTitleForFrame(Determination.getClassTableId()); addColumn(conn, databaseName, tblName, "VarQualifier", "ALTER TABLE %s ADD COLUMN %s VARCHAR(16) AFTER Qualifier"); addColumn(conn, databaseName, tblName, "SubSpQualifier", "ALTER TABLE %s ADD COLUMN %s VARCHAR(16) AFTER VarQualifier"); frame.incOverall(); // CollectingEventAttributes sql = String.format( "SELECT COUNT(*) FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = 'collectingeventattribute' AND COLUMN_NAME = 'CollectionMemberID'", dbc.getDatabaseName()); count = BasicSQLUtils.getCountAsInt(sql); connection.setCatalog(dbc.getDatabaseName()); //int numCEAttrs = BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM collectingeventattribute"); if (count > 0) { HashMap<Integer, Integer> collIdToDispIdHash = new HashMap<Integer, Integer>(); sql = "SELECT UserGroupScopeId, DisciplineID FROM collection"; for (Object[] cols : query(sql)) { Integer colId = (Integer) cols[0]; Integer dspId = (Integer) cols[1]; collIdToDispIdHash.put(colId, dspId); } count = BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM collectingeventattribute"); IdMapperMgr.getInstance().setDBs(connection, connection); IdTableMapper mapper = new IdTableMapper("ceattrmapper", "id", "SELECT CollectingEventAttributeID, CollectionMemberID FROM collectingeventattribute", true, false); mapper.setFrame(frame); mapper.mapAllIdsNoIncrement(count > 0 ? count : null); Statement stmt = null; try { getTableNameAndTitleForFrame(CollectingEventAttribute.getClassTableId()); stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); update(conn, "DROP INDEX COLEVATSColMemIDX on collectingeventattribute"); update(conn, "ALTER TABLE collectingeventattribute DROP COLUMN CollectionMemberID"); update(conn, "ALTER TABLE collectingeventattribute ADD COLUMN DisciplineID int(11)"); update(conn, "CREATE INDEX COLEVATSDispIDX ON collectingeventattribute(DisciplineID)"); double inc = count > 0 ? (100.0 / (double) count) : 0; double cnt = 0; int percent = 0; frame.setProcess(0, 100); frame.setProcessPercent(true); PreparedStatement pStmt = conn.prepareStatement( "UPDATE collectingeventattribute SET DisciplineID=? WHERE CollectingEventAttributeID=?"); ResultSet rs = stmt .executeQuery("SELECT CollectingEventAttributeID FROM collectingeventattribute"); while (rs.next()) { Integer ceAttrId = rs.getInt(1); Integer oldColId = mapper.get(ceAttrId); if (oldColId != null) { Integer dispId = collIdToDispIdHash.get(oldColId); if (dispId != null) { pStmt.setInt(1, dispId); pStmt.setInt(2, ceAttrId); pStmt.execute(); } else { log.debug("Error getting hashed DisciplineID from Old Collection ID[" + oldColId + "] ceAttrId[" + ceAttrId + "]"); } } else { log.debug("Error getting mapped Collection ID[" + oldColId + "] ceAttrId[" + ceAttrId + "]"); } cnt += inc; if (((int) cnt) > percent) { percent = (int) cnt; frame.setProcess(percent); } } rs.close(); pStmt.close(); frame.setProcess(100); } catch (SQLException ex) { ex.printStackTrace(); } finally { if (stmt != null) stmt.close(); } mapper.cleanup(); } frame.incOverall(); //----------------------------- // Collectors //----------------------------- sql = String.format( "SELECT COUNT(*) FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = 'collector' AND COLUMN_NAME = 'CollectionMemberID'", dbc.getDatabaseName()); count = BasicSQLUtils.getCountAsInt(sql); if (count > 0) { HashMap<Integer, Integer> collIdToDivIdHash = new HashMap<Integer, Integer>(); sql = "SELECT c.UserGroupScopeId, d.DivisionID FROM collection c INNER JOIN discipline d ON c.DisciplineID = d.UserGroupScopeId"; for (Object[] cols : query(sql)) { Integer colId = (Integer) cols[0]; Integer divId = (Integer) cols[1]; collIdToDivIdHash.put(colId, divId); } count = BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM collector"); IdMapperMgr.getInstance().setDBs(connection, connection); IdTableMapper mapper = new IdTableMapper("collectormap", "id", "SELECT CollectorID, CollectionMemberID FROM collector", true, false); mapper.setFrame(frame); mapper.mapAllIdsNoIncrement(count > 0 ? count : null); getTableNameAndTitleForFrame(Collector.getClassTableId()); Statement stmt = null; try { stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); update(conn, "DROP INDEX COLTRColMemIDX on collector"); update(conn, "ALTER TABLE collector DROP COLUMN CollectionMemberID"); update(conn, "ALTER TABLE collector ADD COLUMN DivisionID INT(11)"); update(conn, "CREATE INDEX COLTRDivIDX ON collector(DivisionID)"); double inc = count > 0 ? (100.0 / (double) count) : 0; double cnt = 0; int percent = 0; frame.setProcess(0, 100); frame.setProcessPercent(true); PreparedStatement pStmt = conn .prepareStatement("UPDATE collector SET DivisionID=? WHERE CollectorID=?"); ResultSet rs = stmt.executeQuery("SELECT CollectorID FROM collector"); while (rs.next()) { Integer coltrId = rs.getInt(1); Integer oldColId = mapper.get(coltrId); if (oldColId != null) { Integer divId = collIdToDivIdHash.get(oldColId); if (divId != null) { pStmt.setInt(1, divId); pStmt.setInt(2, coltrId); pStmt.execute(); } else { log.debug("Error getting hashed DisciplineID from Old Collection ID[" + oldColId + "]"); } } else { log.debug("Error getting mapped Collector ID[" + oldColId + "]"); } cnt += inc; if (((int) cnt) > percent) { percent = (int) cnt; frame.setProcess(percent); } } rs.close(); pStmt.close(); frame.setProcess(100); } catch (SQLException ex) { ex.printStackTrace(); } finally { if (stmt != null) stmt.close(); } mapper.cleanup(); frame.incOverall(); } } catch (Exception ex) { ex.printStackTrace(); } finally { frame.getProcessProgress().setIndeterminate(true); frame.setDesc("Loading updated schema..."); if (!status) { //UIRegistry.showLocalizedError("SCHEMA_UPDATE_ERROR", errMsgStr); JTextArea ta = UIHelper.createTextArea(); ta.setText(errMsgStr); CellConstraints cc = new CellConstraints(); PanelBuilder pb = new PanelBuilder(new FormLayout("f:p:g", "f:p:g")); pb.add(new JScrollPane(ta, JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED, JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED), cc.xy(1, 1)); pb.setDefaultDialogBorder(); CustomDialog dlg = new CustomDialog((Frame) UIRegistry.getTopWindow(), getResourceString("SCHEMA_UPDATE_ERROR"), true, pb.getPanel()); UIHelper.centerAndShow(dlg); } dbmsMgr.close(); } } return statusOK; }
From source file:net.sourceforge.squirrel_sql.fw.sql.SQLConnection.java
/** * @param catalogName/*from w w w .ja v a 2s. c o m*/ * @throws SQLException */ private void setGenericDbCatalog(String catalogName) throws SQLException { final Connection conn = getConnection(); conn.setCatalog(catalogName); }
From source file:net.sourceforge.squirrel_sql.fw.sql.SQLConnection.java
/** * MS SQL Server throws an exception if the catalog name contains a period without it being quoted. * //from w ww.ja v a 2s .c o m * @param catalogName * the catalog name to use * @throws SQLException * if an error occurs */ private void setMSSQLServerCatalog(final String catalogName) throws SQLException { final Connection conn = getConnection(); // Bug #1995728 // MS-SQL is inconsistent with regard to setting the current catalog. If you have a database with // periods or spaces, then in some cases you must surround the catalog with quotes. For example, // if you have a catalog named 'db with spaces' you must execute the following SQL: // // use "db with spaces" // // However, the same is not always true for the JDBC API method Connection.setCatalog. For some old // versions of Microsoft drivers, you must quote the catalog as well. But for newer versions of the // driver, you must not quote the catalog. So here, we attempt to use the unquoted version first, then // if that fails, we will try quoting it. try { conn.setCatalog(catalogName); return; } catch (SQLException e) { s_log.error("Connection.setCatalog yielded an exception for catalog (" + catalogName + ") :" + e.getMessage() + " - will try quoting the catalog next.", e); } conn.setCatalog(quote(catalogName)); }
From source file:net.sourceforge.squirrel_sql.fw.sql.SQLConnection.java
/** * Work-around for Informix catalog switching bugs. * /*w w w . j a v a 2 s.c o m*/ * @param catalogName * the catalog name to use * @throws SQLException * if an error occurs */ private void setInformixCatalog(String catalogName) throws SQLException { final Connection conn = getConnection(); Statement stmt = null; String sql = "DATABASE " + catalogName; try { stmt = conn.createStatement(); stmt.execute(sql); } catch (SQLException e) { s_log.error("setInformixCatalog: failed to change database with the database SQL directive: " + sql); } finally { SQLUtilities.closeStatement(stmt); } // finally, try to set the catalog, which appears to be a NO-OP in the Informix driver. conn.setCatalog(catalogName); }
From source file:org.apache.calcite.avatica.jdbc.JdbcMeta.java
protected void apply(Connection conn, ConnectionProperties connProps) throws SQLException { if (connProps.isAutoCommit() != null) { conn.setAutoCommit(connProps.isAutoCommit()); }/*from w w w.java2 s. com*/ if (connProps.isReadOnly() != null) { conn.setReadOnly(connProps.isReadOnly()); } if (connProps.getTransactionIsolation() != null) { conn.setTransactionIsolation(connProps.getTransactionIsolation()); } if (connProps.getCatalog() != null) { conn.setCatalog(connProps.getCatalog()); } if (connProps.getSchema() != null) { conn.setSchema(connProps.getSchema()); } }