/* * Copyright (C) 2000 - 2013 TagServlet Ltd * * This file is part of Open BlueDragon (OpenBD) CFML Server Engine. * * OpenBD is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * Free Software Foundation,version 3. * * OpenBD is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with OpenBD. If not, see http://www.gnu.org/licenses/ * * Additional permission under GNU GPL version 3 section 7 * * If you modify this Program, or any covered work, by linking or combining * it with any of the JARS listed in the README.txt (or a modified version of * (that library), containing parts covered by the terms of that JAR, the * licensors of this Program grant you additional permission to convey the * resulting work. * README.txt @ http://www.openbluedragon.org/license/README.txt * * http://openbd.org/ * $Id: metaDatabase.java 2327 2013-02-10 22:26:44Z alan $ */ package com.nary.db; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Map; import com.nary.util.FastMap; import com.naryx.tagfusion.cfm.sql.pool.WrappedConnection; /** * This class is an interface to information regarding a database * table or column */ public class metaDatabase extends Object { public static Map<String, metaColumn> getColumns( Connection _Connection, String _dbInst, String _tableName ){ Map<String, metaColumn> HT = new FastMap<String, metaColumn>(); try{ DatabaseMetaData metaData = _Connection.getMetaData(); // If it's an Oracle database then we need to convert the table name to all uppercase and // set _dbInst to null since DatabaseMetaData calls using the Oracle thin driver and the // JDBC-ODBC bridge connected to Oracle won't work if the table name isn't all uppercase // and if the catalog parameter isn't set to null. if ( isOracleDatabase( metaData ) ) { _tableName = _tableName.toUpperCase(); _dbInst = null; } else if ( isDb2Database( metaData ) || isPointBaseDatabase( metaData ) ) { // If it's a DB2 or PointBase database then we need to convert the table name to all uppercase // since DatabaseMetaData calls using the BEA WLS driver won't work if the table name // isn't all uppercase. _tableName = _tableName.toUpperCase(); } else if ( isInformixDatabase( metaData ) || isPostgreSQLDatabase( metaData ) ) { // If it's an Informix or PostgreSQL database then we need to convert the table name to all lowercase // since DatabaseMetaData calls using the BEA WLS driver or PostgreSQL driver won't work if the table name // isn't all lowercase. _tableName = _tableName.toLowerCase(); } ResultSet RES = metaData.getColumns( null, null, _tableName, null ); while ( RES.next() ){ metaColumn mC = new metaColumn(); mC.NAME = RES.getString( 4 ); mC.SQLTYPE = RES.getInt( 5 ); mC.DATATYPE = RES.getString( 6 ); mC.DEFAULTVALUE = RES.getString( 13 ); HT.put( mC.NAME, mC ); } RES.close(); if ( getPrimaryKeys(HT,metaData,_dbInst,_tableName) == null ) return getPrimaryKeysAccess(HT,metaData,_dbInst,_tableName); return HT; }catch(Exception E){ return null; } } private static Map<String, metaColumn> getPrimaryKeys( Map<String, metaColumn> HT, DatabaseMetaData metaData, String _dbInst, String _tableName ){ ResultSet RES; try{ RES = metaData.getPrimaryKeys( null, null, _tableName ); while ( RES.next() ){ String columnName = RES.getString(4); if ( HT.containsKey( columnName ) ){ metaColumn mC = HT.get( columnName ); mC.PRIMARYKEY = true; } } RES.close(); return HT; }catch(Exception E){ return null; } } private static Map<String, metaColumn> getPrimaryKeysAccess( Map<String, metaColumn> HT, DatabaseMetaData metaData, String _dbInst, String _tableName ){ ResultSet RES; try{ RES = metaData.getIndexInfo( null, null, _tableName, false, false ); metaColumn mC; while ( RES.next() ){ String columnName = RES.getString( "COLUMN_NAME" ); String indexName = RES.getString( "INDEX_NAME" ); if ( columnName == null ) continue; if ( HT.containsKey( columnName ) ){ mC = HT.get( columnName ); }else{ mC = new metaColumn(); mC.NAME = columnName; mC.DEFAULTVALUE = ""; HT.put( columnName, mC ); } // The check for indexName.startsWith( "Index_" ) was added to fix bug 1193 for // BD/Java connected to an Access database. if ( ( indexName != null ) && ( indexName.equals( "PrimaryKey" ) || indexName.startsWith( "Index_" ) ) ) mC.PRIMARYKEY = true; } RES.close(); return HT; }catch(Exception E){ com.nary.Debug.printStackTrace( E ); return null; } } public static boolean createTable( Connection _Connection, String table ){ Statement Statmt = null; try { Statmt = _Connection.createStatement(); if ( _Connection instanceof WrappedConnection ) ((WrappedConnection)_Connection).setLastQuery("STATEMENT: " + table); Statmt.executeUpdate(table); return true; } catch (Exception E) { } finally { if (Statmt != null) { try { Statmt.close(); } catch (SQLException sqlExc) { } } } return false; } public static boolean tableExist( Connection _Connection, String _dbInst, String _tableName ){ try{ DatabaseMetaData metaData = _Connection.getMetaData(); // If it's an Oracle database then we need to convert the table name to all uppercase and // set _dbInst to null since DatabaseMetaData calls using the Oracle thin driver and the // JDBC-ODBC bridge connected to Oracle won't work if the table name isn't all uppercase // and if the catalog parameter isn't set to null. if ( isOracleDatabase( metaData ) ) { _tableName = _tableName.toUpperCase(); _dbInst = null; } else if ( isDb2Database( metaData ) || isPointBaseDatabase( metaData ) ) { // If it's a DB2 or PointBase database then we need to convert the table name to all uppercase // since DatabaseMetaData calls using the BEA WLS driver won't work if the table name // isn't all uppercase. _tableName = _tableName.toUpperCase(); } else if ( isInformixDatabase( metaData ) || isPostgreSQLDatabase( metaData ) ) { // If it's an Informix or PostgreSQL database then we need to convert the table name to all lowercase // since DatabaseMetaData calls using the BEA WLS driver or PostgreSQL driver won't work if the table name // isn't all lowercase. _tableName = _tableName.toLowerCase(); } ResultSet RES = metaData.getTables( _dbInst, null, _tableName, null ); boolean bFound = false; if ( RES.next() ) bFound = true; RES.close(); return bFound; }catch(Exception E){} return false; } public static List<metaColumn> sortColumns( Map<String, metaColumn> HT ){ List<metaColumn> V = new ArrayList<metaColumn>( HT.size() ); Iterator<metaColumn> iter = HT.values().iterator(); while ( iter.hasNext() ) { metaColumn mC = iter.next(); if ( mC.PRIMARYKEY ) V.add( mC ); else V.add(0,mC); } return V; } public static boolean isMySQLDatabase(DatabaseMetaData metaData) throws SQLException { String dbName = metaData.getDatabaseProductName(); if (dbName.equals("MySQL")) return true; return false; } public static boolean isOracleDatabase(DatabaseMetaData metaData) throws SQLException { String dbName = metaData.getDatabaseProductName(); if (dbName.equals("Oracle")) return true; return false; } public static boolean isSybaseDatabase( DatabaseMetaData metaData ) throws SQLException { String dbName = metaData.getDatabaseProductName(); if ( dbName.equals( "Sybase" ) ) return true; return false; } private static boolean isDb2Database( DatabaseMetaData metaData ) throws SQLException { String dbName = metaData.getDatabaseProductName(); if ( dbName.startsWith( "DB2" ) ) return true; return false; } private static boolean isPointBaseDatabase( DatabaseMetaData metaData ) throws SQLException { String dbName = metaData.getDatabaseProductName(); if ( dbName.startsWith( "PointBase" ) ) return true; return false; } private static boolean isInformixDatabase( DatabaseMetaData metaData ) throws SQLException { String dbName = metaData.getDatabaseProductName(); if ( dbName.startsWith( "Informix" ) ) return true; return false; } private static boolean isPostgreSQLDatabase( DatabaseMetaData metaData ) throws SQLException { String dbName = metaData.getDatabaseProductName(); if ( dbName.startsWith( "PostgreSQL" ) ) return true; return false; } }