[Middlegen-user] Oracle Hack Problem

[Jeff Finger]

I, too, am having the same problem trying to read an Oracle schema. I wonder if the hack that Michael sent for SchemaFactory is what
he intended? After piecing the lines back together, I get the appended snippet. But,....

1. The variable "types" is never used.
2. The presence of substring ":oracle" is checked inside a conditional that has already checked for this substring.
3. The method is not complete.

Sorry if I am being dense, but it looks like something is missing here.

Thanks a lot,
Jeff Finger

String[] types= null;
if( connectionSettings.databaseURL.indexOf(":oracle") > -1) {
// no significance for Oracle ? must me null ?
// alegedly significant for Sybase with
// multiple databases(catalogs) per instance
if( "".equals( connectionSettings.catalog ) ){
connectionSettings.catalog = null;
}
if( connectionSettings.catalog != null ) {
connectionSettings.catalog =
connectionSettings.catalog.toUpperCase();
}

if( "".equals( connectionSettings.schema ) ) {
// in case of oracle it will also cause retrieval of
// many other objects
// inlcuding dictionary views, thus processing slowing down
// "" may cause it not to retrieve anything
connectionSettings.schema = null;
}
if( connectionSettings.schema != null &&
connectionSettings.databaseURL.indexOf(":oracle") > -1) {
//usually the access rights are set up so that you can
// only query your schema
// ie. schema = username
connectionSettings.schema =
connectionSettings.schema.toUpperCase();
}

types = new String[] {"TABLE","VIEW"};
// null will also retrieve objects for which only
// synonyms exists, but this objects will not
// be successfully processed anyway - did not check why
// -probably columns not retrieved

System.out.println("connectionSettings.schema = " +
connectionSettings.schema );
}


----------------
/**
* @todo figure out how to pick driver at runtime and use it.
classloader
* problem. for now, must be on cp.
* @todo split into several methods
* @todo do smarter primkey guessing. Have a way to mark
fields/primkeys as
* "guessed"
* @todo Write javadocs for method parameter
* @todo Write javadocs for return value
* @todo Write javadocs for exception
* @param connectionSettings Describe what the parameter does
* @return Describe the return value
* @exception MiddlemanException Describe the exception
*/
public static Schema createSchema(ConnectionSettings
connectionSettings)
throws MiddlemanException {
Schema result = new Schema();
try {
Class.forName(connectionSettings.JDBCDriver).newInstance();

Connection connection =
DriverManager.getConnection(connectionSettings.databaseURL,
connectionSettings.username, connectionSettings.password);

DatabaseMetaData databaseMetaData = connection.getMetaData();

// get the tables
_log.debug("-- tables --");
String sse = databaseMetaData.getSearchStringEscape();
ArrayList tables = new ArrayList();


String[] types= null;

if(
connectionSettings.databaseURL.indexOf(":oracle") > -1) {

// no significance for Oracle ? must
me null ?
// alegedly significant for Sybase
with multiple databases(catalogs) per instance
if( "".equals(
connectionSettings.catalog ) ){
connectionSettings.catalog =
null;
}
if( connectionSettings.catalog !=
null ) {
connectionSettings.catalog =
connectionSettings.catalog.toUpperCase();
}

if( "".equals(
connectionSettings.schema ) ){
// in case of oracle it will
also cause retrieval of many other objects
// inlcuding dictionary views,
thus processing slowing down
// "" may cause it not to
retirieve anything
connectionSettings.schema =
null;
}
if( connectionSettings.schema !=
null && connectionSettings.databaseURL.indexOf(":oracle") > -1)
{
//usually the access rights are
set up so that you can only query your schema
// ie. schema = username
connectionSettings.schema =
connectionSettings.schema.toUpperCase();
}

types = new String[]
{"TABLE","VIEW"};
//null will also retrieve objects
for which only synonyms exists, but this objects will not
//be successfully processed anyway -
did not check why -probably columns not retrieved

System.out.println("connectionSettings.schema = " +
connectionSettings.schema );
}

----------------

[Michael Szlapa]

Sorry attached snipet got scrambled by email client. I will send you the
file directly.



----- Original Message -----
From: "Jeff Finger" <***@cs.stanford.edu>
To: <middlegen-***@lists.sourceforge.net>
Sent: Tuesday, December 18, 2001 9:18 AM
Subject: [Middlegen-user] Oracle Hack Problem

Post by Jeff Finger
I, too, am having the same problem trying to read an Oracle schema. I

wonder if the hack that Michael sent for SchemaFactory is what

Post by Jeff Finger
he intended? After piecing the lines back together, I get the appended snippet. But,....
1. The variable "types" is never used.
2. The presence of substring ":oracle" is checked inside a conditional

that has already checked for this substring.

Post by Jeff Finger
3. The method is not complete.
Sorry if I am being dense, but it looks like something is missing here.
Thanks a lot,
Jeff Finger
String[] types= null;
if( connectionSettings.databaseURL.indexOf(":oracle") > -1) {
// no significance for Oracle ? must me null ?
// alegedly significant for Sybase with
// multiple databases(catalogs) per instance
if( "".equals( connectionSettings.catalog ) ){
connectionSettings.catalog = null;
}
if( connectionSettings.catalog != null ) {
connectionSettings.catalog =
connectionSettings.catalog.toUpperCase();
}
if( "".equals( connectionSettings.schema ) ) {
// in case of oracle it will also cause retrieval of
// many other objects
// inlcuding dictionary views, thus processing slowing down
// "" may cause it not to retrieve anything
connectionSettings.schema = null;
}
if( connectionSettings.schema != null &&
connectionSettings.databaseURL.indexOf(":oracle")
-1) {
//usually the access rights are set up so that you can
// only query your schema
// ie. schema = username
connectionSettings.schema =
connectionSettings.schema.toUpperCase();
}
types = new String[] {"TABLE","VIEW"};
// null will also retrieve objects for which only
// synonyms exists, but this objects will not
// be successfully processed anyway - did not check why
// -probably columns not retrieved
System.out.println("connectionSettings.schema = " +
connectionSettings.schema );
}
----------------
/**
classloader
* problem. for now, must be on cp.
fields/primkeys as
* "guessed"
*/
public static Schema createSchema(ConnectionSettings
connectionSettings)
throws MiddlemanException {
Schema result = new Schema();
try {
Class.forName(connectionSettings.JDBCDriver).newInstance();
Connection connection =
DriverManager.getConnection(connectionSettings.databaseURL,
connectionSettings.username, connectionSettings.password);
DatabaseMetaData databaseMetaData = connection.getMetaData();
// get the tables
_log.debug("-- tables --");
String sse = databaseMetaData.getSearchStringEscape();
ArrayList tables = new ArrayList();
String[] types= null;
if(
connectionSettings.databaseURL.indexOf(":oracle") > -1) {
// no significance for Oracle ? must me null ?
// alegedly significant for Sybase
with multiple databases(catalogs) per instance
if( "".equals(
connectionSettings.catalog ) ){
connectionSettings.catalog =
null;
}
if( connectionSettings.catalog !=
null ) {
connectionSettings.catalog =
connectionSettings.catalog.toUpperCase();
}
if( "".equals(
connectionSettings.schema ) ){
// in case of oracle it will
also cause retrieval of many other objects
// inlcuding dictionary views,
thus processing slowing down
// "" may cause it not to
retirieve anything
connectionSettings.schema =
null;
}
if( connectionSettings.schema !=
null && connectionSettings.databaseURL.indexOf(":oracle") > -1)
{
//usually the access rights are
set up so that you can only query your schema
// ie. schema = username
connectionSettings.schema =
connectionSettings.schema.toUpperCase();
}
types = new String[]
{"TABLE","VIEW"};
//null will also retrieve objects
for which only synonyms exists, but this objects will not
//be successfully processed anyway -
did not check why -probably columns not retrieved
System.out.println("connectionSettings.schema = " +
connectionSettings.schema );
}
----------------
_______________________________________________
middlegen-user mailing list
https://lists.sourceforge.net/lists/listinfo/middlegen-user

[Jeff Finger]

1. In Oracle 8i (and others, too, I assume), you can see the number of
open cursors via

SELECT a.value
FROM v$mystat a, v$statname b
WHERE a.statistic# = b.statistic# AND
a.statistic#=3"

2. By executing the above in addRelationMaybe, you can see that the
md.getCrossReference() is leaving an open cursor each time it is called.

3. If you explicitly close the result set in addRelationMaybe, the
"ORA-01000 maximum open cursors exceeded" goes away. As usual, it is
best done in a "finally".

ResultSet relations = null;
try {
relations = md.getCrossReference( ... );
...
} finally {
if ( null != relations ) {
relations.close();
}
}

4. I do not understand whether this is an Oracle bug or a feature.

5. I do not understand how this relates to Michael's Szlapa's solution.

Thanks to all for sharing your code and help!

Jeff Finger
Molcho 5
Jerusalem 92185, Israel
[phone]

Post by Jeff Finger
_______________________________________________
middlegen-user mailing list
https://lists.sourceforge.net/lists/listinfo/middlegen-user