org.compiere.db
Class DB_Oracle

java.lang.Object
  org.compiere.db.DB_Oracle

All Implemented Interfaces:

CompiereDatabase

public class DB_Oracle
extends Object
implements CompiereDatabase

Oracle Database Port

Version:

$Id: DB_Oracle.java,v 1.7 2006/09/22 23:35:19 jjanke Exp $

Author:

Jorg Janke

Field Summary

static int	DEFAULT_CM_PORT Default Connection Manager Port
static int	DEFAULT_PORT Default Port
static String	DRIVER Driver Class Name

Fields inherited from interface org.compiere.db.CompiereDatabase

CMD_CREATE_DATABASE, CMD_CREATE_USER, CMD_DROP_DATABASE

Constructor Summary

DB_Oracle()
Oracle Database

Method Summary

void	cleanup() Clean up
void	close() Close
String	convertStatement(String oraStatement) Convert an individual Oracle Style statements to target database statement syntax.
String	getAlternativeSQL(int reExNo, String msg, String sql) Check and generate an alternative SQL
Blob	getBlob(Connection con, byte[] bytes) Get the Database specific Blob data type
Connection	getCachedConnection(CConnection connection, boolean autoCommit, int transactionIsolation) Get new Connection from cache
String	getCatalog() Get JDBC Catalog
Clob	getClob(Connection con, String clobString) Get the Database specific Clob data type
String[]	getCommands(int cmdType) Get SQL Commands.
String	getConnectionURL(CConnection connection) Get Database Connection String.
String	getConnectionURL(String dbHost, int dbPort, String dbName, String userName) Get Connection URL.
String	getConnectionURL(String connectionURL, String userName) Get Database Connection String
String	getConstraintType(Connection conn, String tableName, String IXName) Get constraint type associated with the index
DataSource	getDataSource(CConnection connection) Create DataSource
String	getDataType(int displayType, int precision, boolean defaultValue) Get Data Type
String	getDescription() Get Database Description
Driver	getDriver() Get and register Database Driver
Connection	getDriverConnection(CConnection connection) Get new Connection from Driver
Connection	getDriverConnection(String dbUrl, String dbUid, String dbPwd) Get new Driver Connection
String	getName() Get Database Name
String	getSchema() Get JDBC Schema
int	getStandardPort() Get Standard JDBC Port
String	getStatus() Get Status
String	getSystemDatabase(String databaseName) Get Name of System Database
String	getSystemUser() Get Name of System User
String	getType() Return the Type
boolean	handleAbandonedConnection(oracle.jdbc.OracleConnection conn, Object userObject) Handle Abandoned Connection
boolean	isConnectionValid(Connection conn) Check if a connect is valid conn Connection
boolean	isSupported(String sql) Check if DBMS support the sql statement
static void	main(String[] args) Testing
String	nullValue(String sqlClause, int dataType) Get a string representation of literal used in SQL clause
void	releaseConnection(oracle.jdbc.OracleConnection conn, Object userObject) Release Connection
boolean	supportsBLOB() Supports BLOB
String	TO_CHAR(String columnName, int displayType, String AD_Language) Create SQL for formatted Date, Number
String	TO_DATE(Timestamp time, boolean dayOnly) Create SQL TO Date String from Timestamp
String	TO_NUMBER(BigDecimal number, int displayType) Return number as string for INSERT statements with correct precision
String	toString() String Representation
String	updateSetSelectList(String sql) jz change update set (...) = (select ... from ) standard format

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Detail

DRIVER

public static final String DRIVER

Driver Class Name

See Also:

Constant Field Values

DEFAULT_PORT

public static final int DEFAULT_PORT

Default Port

See Also:

Constant Field Values

DEFAULT_CM_PORT

public static final int DEFAULT_CM_PORT

Default Connection Manager Port

See Also:

Constant Field Values

Constructor Detail

DB_Oracle

public DB_Oracle()

Oracle Database

Method Detail

isConnectionValid

public boolean isConnectionValid(Connection conn)

Check if a connect is valid conn Connection

Specified by:

isConnectionValid in interface CompiereDatabase

Parameters:

conn - Connection

Returns:

true if connection is valid

getType

public String getType()

Return the Type

Specified by:

getType in interface CompiereDatabase

Returns:

database type Environment.DBTYPE_*

getName

public String getName()

Get Database Name

Specified by:

getName in interface CompiereDatabase

Returns:

database short name

getDescription

public String getDescription()

Get Database Description

Specified by:

getDescription in interface CompiereDatabase

Returns:

database long name and version

getStandardPort

public int getStandardPort()

Get Standard JDBC Port

Specified by:

getStandardPort in interface CompiereDatabase

Returns:

standard port

getDriver

public Driver getDriver()
                 throws SQLException

Get and register Database Driver

Specified by:

getDriver in interface CompiereDatabase

Returns:

Driver

Throws:

SQLException

getConnectionURL

public String getConnectionURL(CConnection connection)

Get Database Connection String.

  Timing:
  - CM with source_route not in address_list  = 28.5 sec
  - CM with source_route in address_list      = 58.0 sec
  - direct    = 4.3-8 sec  (no real difference if on other box)
  - bequeath  = 3.4-8 sec
  

Specified by:

getConnectionURL in interface CompiereDatabase

Parameters:

connection - Connection Descriptor

Returns:

connection String

getConnectionURL

public String getConnectionURL(String dbHost,
                               int dbPort,
                               String dbName,
                               String userName)

Get Connection URL. http://download-east.oracle.com/docs/cd/B14117_01/java.101/b10979/urls.htm#BEIDBFDF

Specified by:

getConnectionURL in interface CompiereDatabase

Parameters:

dbHost - db Host

dbPort - db Port

dbName - db Name

userName - user name

Returns:

connection

getConnectionURL

public String getConnectionURL(String connectionURL,
                               String userName)

Get Database Connection String

Specified by:

getConnectionURL in interface CompiereDatabase

Parameters:

connectionURL - Connection URL

userName - user name

Returns:

connection String

getCatalog

public String getCatalog()

Get JDBC Catalog

Specified by:

getCatalog in interface CompiereDatabase

Returns:

null - not used

getSchema

public String getSchema()

Get JDBC Schema

Specified by:

getSchema in interface CompiereDatabase

Returns:

user name

supportsBLOB

public boolean supportsBLOB()

Supports BLOB

Specified by:

supportsBLOB in interface CompiereDatabase

Returns:

true if BLOB is supported

toString

public String toString()

String Representation

Specified by:

toString in interface CompiereDatabase

Overrides:

toString in class Object

Returns:

info

getStatus

public String getStatus()

Get Status

Specified by:

getStatus in interface CompiereDatabase

Returns:

status info

convertStatement

public String convertStatement(String oraStatement)

Convert an individual Oracle Style statements to target database statement syntax.

Specified by:

convertStatement in interface CompiereDatabase

Parameters:

oraStatement - oracle statement

Returns:

converted Statement oracle statement

isSupported

public boolean isSupported(String sql)

Check if DBMS support the sql statement

Specified by:

isSupported in interface CompiereDatabase

Returns:

true: yes

getConstraintType

public String getConstraintType(Connection conn,
                                String tableName,
                                String IXName)

Get constraint type associated with the index

Specified by:

getConstraintType in interface CompiereDatabase

Returns:

String[0] = 0: do not know, 1: Primary Key 2: Foreign Key String[1] - String[n] = Constraint Name

getSystemUser

public String getSystemUser()

Get Name of System User

Specified by:

getSystemUser in interface CompiereDatabase

Returns:

system

getSystemDatabase

public String getSystemDatabase(String databaseName)

Get Name of System Database

Specified by:

getSystemDatabase in interface CompiereDatabase

Parameters:

databaseName - database Name

Returns:

e.g. master or database Name

TO_DATE

public String TO_DATE(Timestamp time,
                      boolean dayOnly)

Create SQL TO Date String from Timestamp

Specified by:

TO_DATE in interface CompiereDatabase

Parameters:

time - Date to be converted

dayOnly - true if time set to 00:00:00

Returns:

TO_DATE('2001-01-30 18:10:20',''YYYY-MM-DD HH24:MI:SS') or TO_DATE('2001-01-30',''YYYY-MM-DD')

TO_CHAR

public String TO_CHAR(String columnName,
                      int displayType,
                      String AD_Language)

Create SQL for formatted Date, Number

Specified by:

TO_CHAR in interface CompiereDatabase

Parameters:

columnName - the column name in the SQL

displayType - Display Type

AD_Language - 6 character language setting (from Env.LANG_*)

Returns:

TRIM(TO_CHAR(columnName,'9G999G990D00','NLS_NUMERIC_CHARACTERS='',.''')) or TRIM(TO_CHAR(columnName,'TM9')) depending on DisplayType and Language

See Also:

DisplayType, Env

TO_NUMBER

public String TO_NUMBER(BigDecimal number,
                        int displayType)

Return number as string for INSERT statements with correct precision

Specified by:

TO_NUMBER in interface CompiereDatabase

Parameters:

number - number

displayType - display Type

Returns:

number as string

getCommands

public String[] getCommands(int cmdType)

Get SQL Commands. The following variables are resolved:

Specified by:

getCommands in interface CompiereDatabase

Parameters:

cmdType - CMD_*

Returns:

array of commands to be executed

getDataSource

public DataSource getDataSource(CConnection connection)

Create DataSource

Specified by:

getDataSource in interface CompiereDatabase

Parameters:

connection - connection

Returns:

data dource

getCachedConnection

public Connection getCachedConnection(CConnection connection,
                                      boolean autoCommit,
                                      int transactionIsolation)
                               throws Exception

Get new Connection from cache

Specified by:

getCachedConnection in interface CompiereDatabase

Parameters:

connection - info

autoCommit - true if autocommit connection

transactionIsolation - Connection transaction level

Returns:

connection or null

Throws:

Exception

getDriverConnection

public Connection getDriverConnection(CConnection connection)
                               throws SQLException

Get new Connection from Driver

Specified by:

getDriverConnection in interface CompiereDatabase

Parameters:

connection - info

Returns:

connection or null

Throws:

SQLException

getDriverConnection

public Connection getDriverConnection(String dbUrl,
                                      String dbUid,
                                      String dbPwd)
                               throws SQLException

Get new Driver Connection

Specified by:

getDriverConnection in interface CompiereDatabase

Parameters:

dbUrl - URL

dbUid - user

dbPwd - password

Returns:

connection

Throws:

SQLException

close

public void close()

Close

Specified by:

close in interface CompiereDatabase

cleanup

public void cleanup()

Clean up

handleAbandonedConnection

public boolean handleAbandonedConnection(oracle.jdbc.OracleConnection conn,
                                         Object userObject)

Handle Abandoned Connection

Parameters:

conn - connection

userObject -

Returns:

true if close - false for keeping it

releaseConnection

public void releaseConnection(oracle.jdbc.OracleConnection conn,
                              Object userObject)

Release Connection

Parameters:

conn - connection

userObject -

getDataType

public String getDataType(int displayType,
                          int precision,
                          boolean defaultValue)

Get Data Type

Parameters:

displayType - display type

precision - precision

defaultValue - if true adds default value

Returns:

data type

getAlternativeSQL

public String getAlternativeSQL(int reExNo,
                                String msg,
                                String sql)

Check and generate an alternative SQL

Specified by:

getAlternativeSQL in interface CompiereDatabase

Returns:

String, the alternative SQL, null if no alternative

updateSetSelectList

public String updateSetSelectList(String sql)

jz change update set (...) = (select ... from ) standard format

Specified by:

updateSetSelectList in interface CompiereDatabase

Parameters:

sql - update clause

Returns:

new sql

nullValue

public String nullValue(String sqlClause,
                        int dataType)

Get a string representation of literal used in SQL clause

Specified by:

nullValue in interface CompiereDatabase

Parameters:

sqlClause - "S", "U","I", "W"

dataType - java.sql.Types

Returns:

db2: nullif(x,x)

getClob

public Clob getClob(Connection con,
                    String clobString)

Get the Database specific Clob data type

Specified by:

getClob in interface CompiereDatabase

Parameters:

connection - connection

clobString - clob string

Returns:

Clob

getBlob

public Blob getBlob(Connection con,
                    byte[] bytes)

Get the Database specific Blob data type

Specified by:

getBlob in interface CompiereDatabase

Parameters:

connection - connection

bytes - bytes

Returns:

Blob

main

public static void main(String[] args)

Testing

Parameters:

args - ignored