Search in sources :

Example 66 with DatabaseMetaData

use of java.sql.DatabaseMetaData in project gerrit by GerritCodeReview.

the class QueryShell method listTables.

private void listTables() {
    final DatabaseMetaData meta;
    try {
        meta = connection.getMetaData();
    } catch (SQLException e) {
        error(e);
        return;
    }
    final String[] types = { "TABLE", "VIEW" };
    try (ResultSet rs = meta.getTables(null, null, null, types)) {
        if (outputFormat == OutputFormat.PRETTY) {
            println("                     List of relations");
        }
        showResultSet(rs, false, 0, Identity.create(rs, "TABLE_SCHEM"), Identity.create(rs, "TABLE_NAME"), Identity.create(rs, "TABLE_TYPE"));
    } catch (SQLException e) {
        error(e);
    }
    println("");
}
Also used : SQLException(java.sql.SQLException) ResultSet(java.sql.ResultSet) DatabaseMetaData(java.sql.DatabaseMetaData)

Example 67 with DatabaseMetaData

use of java.sql.DatabaseMetaData in project adempiere by adempiere.

the class PackInHandler method createImp_Sum_table.

// startElement
/**
     *	Check if Package History Table exists in database.  If not create
     *
     *      @param tablename
     *       	
     */
public void createImp_Sum_table(String tablename) {
    // Check if table exists.
    Connection conn = DB.getConnectionRW();
    DatabaseMetaData dbm;
    try {
        dbm = conn.getMetaData();
        //    	 check if table is there
        ResultSet tables = null;
        if (m_DatabaseType.equals("Oracle"))
            tables = dbm.getTables(null, null, tablename.toUpperCase(), null);
        else if (m_DatabaseType.equals("PostgreSQL"))
            tables = dbm.getTables(null, null, tablename.toLowerCase(), null);
        if (tables.next()) {
            log.info("Table Found");
        } else {
            if (tablename.equals("AD_Package_Imp")) {
                StringBuffer sqlB = new StringBuffer("CREATE TABLE " + tablename.toUpperCase() + "( ").append(tablename.toUpperCase() + "_ID   NUMBER(10) NOT NULL, ").append("AD_CLIENT_ID NUMBER(10) NOT NULL, ").append("AD_ORG_ID  NUMBER(10) NOT NULL, ").append("ISACTIVE CHAR(1) DEFAULT 'Y' NOT NULL, ").append("CREATED DATE DEFAULT SYSDATE NOT NULL, ").append("CREATEDBY NUMBER(10) NOT NULL, ").append("UPDATED DATE DEFAULT SYSDATE NOT NULL, ").append("UPDATEDBY NUMBER(10) NOT NULL, ").append("NAME NVARCHAR2(60) NOT NULL, ").append("PK_STATUS NVARCHAR2(22), ").append("RELEASENO NVARCHAR2(20), ").append("PK_VERSION NVARCHAR2(20), ").append("VERSION NVARCHAR2(20), ").append("DESCRIPTION NVARCHAR2(1000) NOT NULL, ").append("EMAIL NVARCHAR2(60), ").append("PROCESSED CHAR(1) DEFAULT 'N', ").append("PROCESSING CHAR(1) DEFAULT 'N', ").append("CREATOR VARCHAR2(60 ), ").append("CREATORCONTACT VARCHAR2(255), ").append(" CREATEDDATE  VARCHAR2(25), ").append("UPDATEDDATE VARCHAR2(25), ").append("PRIMARY KEY( " + tablename.toUpperCase() + "_ID)" + ")");
                try {
                    PreparedStatement pstmt = DB.prepareStatement(sqlB.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, null);
                    pstmt.executeUpdate();
                    MSequence.createTableSequence(m_ctx, "AD_Package_Imp", m_trxName);
                    pstmt.close();
                    pstmt = null;
                } catch (Exception e) {
                    log.info("createImp_Sum_table:" + e);
                }
            }
            if (tablename.equals("AD_Package_Imp_Inst")) {
                StringBuffer sqlB = new StringBuffer("CREATE TABLE " + tablename.toUpperCase() + "( ").append(tablename.toUpperCase() + "_ID   NUMBER(10) NOT NULL, ").append("AD_CLIENT_ID NUMBER(10) NOT NULL, ").append("AD_ORG_ID  NUMBER(10) NOT NULL, ").append("ISACTIVE CHAR(1) DEFAULT 'Y' NOT NULL, ").append("CREATED DATE DEFAULT SYSDATE NOT NULL, ").append("CREATEDBY NUMBER(10) NOT NULL, ").append("UPDATED DATE DEFAULT SYSDATE NOT NULL, ").append("UPDATEDBY NUMBER(10) NOT NULL, ").append("NAME NVARCHAR2(60) NOT NULL, ").append("PK_STATUS NVARCHAR2(22), ").append("RELEASENO NVARCHAR2(20), ").append("PK_VERSION NVARCHAR2(20), ").append("VERSION NVARCHAR2(20), ").append("DESCRIPTION NVARCHAR2(1000) NOT NULL, ").append("EMAIL NVARCHAR2(60), ").append("PROCESSED CHAR(1) DEFAULT 'N', ").append("PROCESSING CHAR(1) DEFAULT 'N', ").append("CREATOR VARCHAR2(60 ), ").append("CREATORCONTACT VARCHAR2(255), ").append(" CREATEDDATE  VARCHAR2(25), ").append("UPDATEDDATE VARCHAR2(25), ").append("PRIMARY KEY( " + tablename.toUpperCase() + "_ID)" + ")");
                try {
                    PreparedStatement pstmt = DB.prepareStatement(sqlB.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, null);
                    pstmt.executeUpdate();
                    MSequence.createTableSequence(m_ctx, "AD_Package_Imp_Inst", m_trxName);
                    pstmt.close();
                    pstmt = null;
                } catch (Exception e) {
                    log.info("createImp_Sum_table:" + e);
                }
            }
            if (tablename.equals("AD_Package_Imp_Detail")) {
                StringBuffer sqlB = new StringBuffer("CREATE TABLE " + tablename.toUpperCase() + "( ").append(tablename.toUpperCase() + "_ID   NUMBER(10) NOT NULL, ").append("AD_CLIENT_ID NUMBER(10) NOT NULL, ").append("AD_ORG_ID  NUMBER(10) NOT NULL, ").append("ISACTIVE CHAR(1) DEFAULT 'Y' NOT NULL, ").append("CREATED DATE DEFAULT SYSDATE NOT NULL, ").append("CREATEDBY NUMBER(10) NOT NULL, ").append("UPDATED DATE DEFAULT SYSDATE NOT NULL, ").append("UPDATEDBY NUMBER(10) NOT NULL, ").append("NAME NVARCHAR2(60), ").append("AD_PACKAGE_IMP_ID Number(10) NOT NULL, ").append("AD_ORIGINAL_ID Number(10) NOT NULL, ").append("AD_BACKUP_ID Number(10), ").append("ACTION NVARCHAR2(20), ").append("SUCCESS NVARCHAR2(20), ").append("TYPE NVARCHAR2(60), ").append("TABLENAME NVARCHAR2(60), ").append("AD_TABLE_ID NUMBER(10), ").append("UNINSTALL CHAR(1), ").append("PRIMARY KEY( " + tablename.toUpperCase() + "_ID)" + ")");
                try {
                    PreparedStatement pstmt = DB.prepareStatement(sqlB.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, null);
                    pstmt.executeUpdate();
                    MSequence.createTableSequence(m_ctx, "AD_Package_Imp_Detail", m_trxName);
                    pstmt.close();
                    pstmt = null;
                } catch (Exception e) {
                    log.info("createImp_Sum_table:" + e);
                }
            }
            if (tablename.equals("AD_Package_Imp_Backup")) {
                StringBuffer sqlB = new StringBuffer("CREATE TABLE " + tablename.toUpperCase() + "( ").append(tablename.toUpperCase() + "_ID NUMBER(10) NOT NULL, ").append("AD_CLIENT_ID NUMBER(10) NOT NULL, ").append("AD_ORG_ID  NUMBER(10) NOT NULL, ").append("ISACTIVE CHAR(1) DEFAULT 'Y' NOT NULL, ").append("CREATED DATE DEFAULT SYSDATE NOT NULL, ").append("CREATEDBY NUMBER(10) NOT NULL, ").append("UPDATED DATE DEFAULT SYSDATE NOT NULL, ").append("UPDATEDBY NUMBER(10) NOT NULL, ").append("AD_PACKAGE_IMP_ID Number(10) NOT NULL, ").append("AD_PACKAGE_IMP_DETAIL_ID Number(10) NOT NULL, ").append("AD_TABLE_ID NUMBER(10), ").append("AD_COLUMN_ID NUMBER(10), ").append("AD_REFERENCE_ID NUMBER(10), ").append("AD_PACKAGE_IMP_BCK_DIR NVARCHAR2(255), ").append("AD_PACKAGE_IMP_ORG_DIR NVARCHAR2(255), ").append("COLVALUE NVARCHAR2(2000), ").append("UNINSTALL CHAR(1), ").append("PRIMARY KEY( " + tablename.toUpperCase() + "_ID)" + ")");
                try {
                    PreparedStatement pstmt = DB.prepareStatement(sqlB.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, null);
                    pstmt.executeUpdate();
                    MSequence.createTableSequence(m_ctx, "AD_Package_Imp_Backup", m_trxName);
                    pstmt.close();
                    pstmt = null;
                } catch (Exception e) {
                    log.info("createImp_Sum_table:" + e);
                }
            }
        }
        tables.close();
    } catch (SQLException e) {
        log.info("createImp_Sum_table:" + e);
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
            }
        }
    }
}
Also used : SQLException(java.sql.SQLException) Connection(java.sql.Connection) ResultSet(java.sql.ResultSet) PreparedStatement(java.sql.PreparedStatement) DatabaseMetaData(java.sql.DatabaseMetaData) FileNotFoundException(java.io.FileNotFoundException) SAXException(org.xml.sax.SAXException) SQLException(java.sql.SQLException) TransformerConfigurationException(javax.xml.transform.TransformerConfigurationException)

Example 68 with DatabaseMetaData

use of java.sql.DatabaseMetaData in project aries by apache.

the class Connector method initialize.

public void initialize() throws SQLException {
    conn = connect();
    DatabaseMetaData dbmd = conn.getMetaData();
    ResultSet rs = dbmd.getTables(null, "", "TESTTABLE", null);
    if (!rs.next()) {
        executeUpdate("CREATE TABLE TESTTABLE (NAME VARCHAR(64), VALUE INTEGER, PRIMARY KEY(NAME, VALUE))");
    }
}
Also used : ResultSet(java.sql.ResultSet) DatabaseMetaData(java.sql.DatabaseMetaData)

Example 69 with DatabaseMetaData

use of java.sql.DatabaseMetaData in project adempiere by adempiere.

the class DB_Oracle method main.

/**************************************************************************
     *  Testing
     *  @param args ignored
     */
public static void main(String[] args) {
    Adempiere.startupEnvironment(true);
    CConnection cc = CConnection.get();
    DB_Oracle db = (DB_Oracle) cc.getDatabase();
    db.cleanup();
    try {
        Connection conn = null;
        //  System.out.println("Driver=" + db.getDriverConnection(cc));
        DataSource ds = db.getDataSource(cc);
        System.out.println("DS=" + ds.getConnection());
        conn = db.getCachedConnection(cc, true, Connection.TRANSACTION_READ_COMMITTED);
        System.out.println("Cached=" + conn);
        System.out.println(db);
        //////////////////////////
        System.out.println("JAVA classpath: [\n" + System.getProperty("java.class.path") + "\n]");
        DatabaseMetaData dmd = conn.getMetaData();
        System.out.println("DriverVersion: [" + dmd.getDriverVersion() + "]");
        System.out.println("DriverMajorVersion: [" + dmd.getDriverMajorVersion() + "]");
        System.out.println("DriverMinorVersion: [" + dmd.getDriverMinorVersion() + "]");
        System.out.println("DriverName: [" + dmd.getDriverName() + "]");
        System.out.println("ProductName: [" + dmd.getDatabaseProductName() + "]");
        System.out.println("ProductVersion: [\n" + dmd.getDatabaseProductVersion() + "\n]");
    //////////////////////////
    } catch (Exception e1) {
        e1.printStackTrace();
    }
    db.cleanup();
    System.out.println("--------------------------------------------------");
    /**
        DROP TABLE X_Test;
        CREATE TABLE X_Test
        (
            Text1   NVARCHAR2(2000) NULL,
            Text2   VARCHAR2(2000)  NULL
        );
        **/
    try {
        String myString1 = "123456789 12345678";
        String myString = "";
        for (int i = 0; i < 99; i++) myString += myString1 + (char) ('a' + i) + "\n";
        System.out.println(myString.length());
        System.out.println(Util.size(myString));
        //
        myString = Util.trimSize(myString, 2000);
        System.out.println(myString.length());
        System.out.println(Util.size(myString));
        //
        Connection conn2 = db.getCachedConnection(cc, true, Connection.TRANSACTION_READ_COMMITTED);
        /** **/
        PreparedStatement pstmt = conn2.prepareStatement("INSERT INTO X_Test(Text1, Text2) values(?,?)");
        // NVARCHAR2 column
        pstmt.setString(1, myString);
        // VARCHAR2 column
        pstmt.setString(2, myString);
        System.out.println(pstmt.executeUpdate());
        /** **/
        Statement stmt = conn2.createStatement();
        System.out.println(stmt.executeUpdate("INSERT INTO X_Test(Text1, Text2) values('" + myString + "','" + myString + "')"));
    } catch (Exception e) {
        e.printStackTrace();
    }
    db.cleanup();
    System.out.println("--------------------------------------------------");
    System.exit(0);
    System.out.println("--------------------------------------------------");
    try {
        Connection conn1 = db.getCachedConnection(cc, false, Connection.TRANSACTION_READ_COMMITTED);
        Connection conn2 = db.getCachedConnection(cc, true, Connection.TRANSACTION_READ_COMMITTED);
        Connection conn3 = db.getCachedConnection(cc, false, Connection.TRANSACTION_READ_COMMITTED);
        System.out.println("3 -> " + db);
        conn1.close();
        conn2.close();
        conn1 = db.getCachedConnection(cc, true, Connection.TRANSACTION_READ_COMMITTED);
        conn2 = db.getCachedConnection(cc, true, Connection.TRANSACTION_READ_COMMITTED);
        System.out.println("3 -> " + db);
        conn1.close();
        conn2.close();
        conn3.close();
        System.out.println("0 -> " + db);
    } catch (Exception e1) {
        e1.printStackTrace();
    }
    db.cleanup();
    //  System.exit(0);
    System.out.println("--------------------------------------------------");
    System.out.println(DB.getConnectionRO());
    System.out.println(DB.getConnectionRW());
    System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
    System.out.println(DB.getConnectionRO());
    System.out.println(DB.getConnectionRW());
    System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
    System.out.println(DB.getConnectionRO());
    System.out.println(DB.getConnectionRW());
    System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
    System.out.println(DB.getConnectionRO());
    System.out.println(DB.getConnectionRW());
    System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
    System.out.println(DB.getConnectionRO());
    System.out.println(DB.getConnectionRW());
    System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
    System.out.println(DB.getConnectionRO());
    System.out.println(DB.getConnectionRW());
    System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
    System.out.println(DB.getConnectionRO());
    System.out.println(DB.getConnectionRW());
    System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
    System.out.println(DB.getConnectionRO());
    System.out.println(DB.getConnectionRW());
    System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
    System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
    System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
    System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
    System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
    System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
    System.out.println(db);
    try {
        System.out.println("-- Sleeping --");
        Thread.sleep(60000);
        System.out.println(db);
        db.close();
        db.cleanup();
        System.out.println(db);
    } catch (InterruptedException e) {
    }
/**
        //  Connection option 1
        try
        {
            System.setProperty("oracle.jdbc.Trace", "true");
            DriverManager.registerDriver(new OracleDriver());
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@//dev:1521/dev", "adempiere", "adempiere");
            System.out.println("Catalog=" + con.getCatalog());
            DatabaseMetaData md = con.getMetaData();
            System.out.println("URL=" + md.getURL());
            System.out.println("User=" + md.getUserName());
            //
            System.out.println("Catalog");
            ResultSet rs = md.getCatalogs();
            while (rs.next())
                System.out.println("- " + rs.getString(1));
            //
            System.out.println("Table");
            rs = md.getTables(null, "ADEMPIERE", null, new String[] {"TABLE"});
            while (rs.next())
                System.out.println("- User=" + rs.getString(2) + " | Table=" + rs.getString(3)
                    + " | Type=" + rs.getString(4) + " | " + rs.getString(5));
            //
            System.out.println("Column");
            rs = md.getColumns(null, "ADEMPIERE", "C_ORDER", null);
            while (rs.next())
                System.out.println("- Tab=" + rs.getString(3) + " | Col=" + rs.getString(4)
                    + " | Type=" + rs.getString(5) + ", " + rs.getString(6)
                    + " | Size=" + rs.getString(7) + " | " + rs.getString(8)
                    + " | Digits=" + rs.getString(9) + " | Radix=" + rs.getString(10)
                    + " | Null=" + rs.getString(11) + " | Rem=" + rs.getString(12)
                    + " | Def=" + rs.getString(13) + " | " + rs.getString(14)
                    + " | " + rs.getString(15) + " | " + rs.getString(16)
                    + " | Ord=" + rs.getString(17) + " | Null=" + rs.getString(18)
                    );

            con.close();
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }
        **/
}
Also used : PreparedStatement(java.sql.PreparedStatement) Statement(java.sql.Statement) Connection(java.sql.Connection) PreparedStatement(java.sql.PreparedStatement) DatabaseMetaData(java.sql.DatabaseMetaData) DBException(org.adempiere.exceptions.DBException) SQLException(java.sql.SQLException) ComboPooledDataSource(com.mchange.v2.c3p0.ComboPooledDataSource) DataSource(javax.sql.DataSource)

Example 70 with DatabaseMetaData

use of java.sql.DatabaseMetaData in project adempiere by adempiere.

the class StatusInfo method doGet.

/**
	 * 	Get
	 *	@param request
	 *	@param response
	 *	@throws ServletException
	 *	@throws IOException
	 */
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    response.setContentType(CONTENT_TYPE);
    PrintWriter out = response.getWriter();
    out.println("<html>");
    out.println("<head><title>Status Info</title></head>");
    out.println("<body>");
    InitialContext context = null;
    try {
        context = new InitialContext();
    } catch (Exception ex) {
        out.println("<p><b>" + ex + "</b></p>");
    }
    try {
        Status status = (Status) context.lookup(Status.JNDI_NAME);
        out.println("<p>" + status.getStatus() + "</p>");
    } catch (Exception ex) {
        out.println("<p><b>" + ex + "</b></p>");
    }
    try {
        Server server = (Server) context.lookup(Server.JNDI_NAME);
        out.println("<p>" + server.getStatus() + "</p>");
    } catch (Exception ex) {
        out.println("<p><b>" + ex + "</b></p>");
    }
    try {
        out.println("<h2>-- /</h2>");
        NamingEnumeration ne = context.list("/");
        while (ne.hasMore()) out.println("<br>   " + ne.nextElement());
        out.println("<h2>-- java</h2>");
        ne = context.list("java:");
        while (ne.hasMore()) out.println("<br>   " + ne.nextElement());
        out.println("<h2>-- ejb</h2>");
        ne = context.list("ejb");
        while (ne.hasMore()) out.println("<br>   " + ne.nextElement());
        //
        out.println("<h2>-- DS</h2>");
        DataSource ds = (DataSource) context.lookup("java:/OracleDS");
        out.println("<br>  DataSource " + ds.getClass().getName() + " LoginTimeout=" + ds.getLoginTimeout());
        Connection con = ds.getConnection("adempiere", "adempiere");
        out.println("<br>  Connection ");
        getServletContext().log("Connection closed=" + con.isClosed());
        DatabaseMetaData dbmd = con.getMetaData();
        getServletContext().log("DB " + dbmd.getDatabaseProductName());
        getServletContext().log("DB V " + dbmd.getDatabaseProductVersion());
        getServletContext().log("Driver " + dbmd.getDriverName());
        getServletContext().log("Driver V " + dbmd.getDriverVersion());
        getServletContext().log("JDBC " + dbmd.getJDBCMajorVersion());
        getServletContext().log("JDBC mV " + dbmd.getJDBCMinorVersion());
        getServletContext().log("User " + dbmd.getUserName());
        getServletContext().log("ANSI 92 " + dbmd.supportsANSI92FullSQL());
        getServletContext().log("Connection Alter Table ADD" + dbmd.supportsAlterTableWithAddColumn());
        getServletContext().log("Connection Alter Table DROP " + dbmd.supportsAlterTableWithDropColumn());
        getServletContext().log("Connection DDL&DML " + dbmd.supportsDataDefinitionAndDataManipulationTransactions());
        getServletContext().log("Connection CatalogsIn DML " + dbmd.supportsCatalogsInDataManipulation());
        getServletContext().log("Connection Schema In DML " + dbmd.supportsSchemasInDataManipulation());
        con.close();
    } catch (Exception e) {
        out.println("<p><b>" + e + "</b></p>");
    }
    out.println("</body></html>");
}
Also used : Status(org.compiere.interfaces.Status) Server(org.compiere.interfaces.Server) Connection(java.sql.Connection) NamingEnumeration(javax.naming.NamingEnumeration) DatabaseMetaData(java.sql.DatabaseMetaData) InitialContext(javax.naming.InitialContext) ServletException(javax.servlet.ServletException) IOException(java.io.IOException) PrintWriter(java.io.PrintWriter) DataSource(javax.sql.DataSource)

Aggregations

DatabaseMetaData (java.sql.DatabaseMetaData)360 ResultSet (java.sql.ResultSet)215 Connection (java.sql.Connection)169 SQLException (java.sql.SQLException)154 Test (org.junit.Test)87 Statement (java.sql.Statement)49 ResultSetMetaData (java.sql.ResultSetMetaData)42 ArrayList (java.util.ArrayList)40 PreparedStatement (java.sql.PreparedStatement)38 IOException (java.io.IOException)26 Properties (java.util.Properties)25 HashSet (java.util.HashSet)16 PhoenixDatabaseMetaData (org.apache.phoenix.jdbc.PhoenixDatabaseMetaData)16 HashMap (java.util.HashMap)15 PhoenixConnection (org.apache.phoenix.jdbc.PhoenixConnection)15 PrintWriter (java.io.PrintWriter)10 Savepoint (java.sql.Savepoint)10 List (java.util.List)10 Map (java.util.Map)9 DataSource (javax.sql.DataSource)9