wood burning stoves 2.0*
The moose likes JDBC and the fly likes default schema Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "default schema" Watch "default schema" New topic
Author

default schema

Jina Lu
Greenhorn

Joined: Jul 09, 2010
Posts: 25

The problem I faced with jdbc connection occurred when I needed to access object in different database schema when my new user is by default. StructDescriptor.createDescriptor adds scheme prefix which causes the problem that object is not found.
Is it possible to configure maybe Tomcat resource and set default current user scheme or the only solution would be to set default scheme on application start? I'm using Oracle in my project.

Thanks in advance

Lu
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Welcome to the Ranch!

I've run into similar issues with types in Oracle. My advice would be to use full name of the type (that is, SCHEMA.TYPE) in the call to the createDescriptor method. Even if you use different schemas in your application, create one separate schema which will contain all of the types and use full name of the type in stored procedures and packages. You could make the schema name configurable to increase the portability of your app into another database.

No doubt there are other arrangements possible, but this one worked quite well for me.

Edit: you'll need to grant appropriate permission - EXECUTE - on the type to the other schemas, of course
Jina Lu
Greenhorn

Joined: Jul 09, 2010
Posts: 25

Thanks for quick answer Martin. Adding "SCHEMA." to each call is quite complicated, but seams to work (checked one case). Are any other solutions to this problem or it is the only way to solve it?
Maybe there are other ways to stop Oracle adding default user schema to type name?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

I'm not sure I fully understand your need. You don't want to use a specific schema for the type, and you don't want to use the current schema. How do you want to specify which schema the type resides in?
Jina Lu
Greenhorn

Joined: Jul 09, 2010
Posts: 25

I currently have the old module, quite big one, which has lots of call without specifying concrete schema. Now I have requirement from the client to use different db user for connecting to db then objects owner.
After this user switch I need to be sure that the old code will work. All needed db grants for accessing objects are applied and the log on trigger to set current schema created.
Do I understand you correctly that oracle will add default user schema to any type which doesn't have it fully specified? Is any way to tell oracle to add current but not default schema to the type?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Ah, so the "default schema" is the "current (connected) user's schema", while "current schema" is the schema you set using alter session set current_schema=...?

Using this terminology, I do think that unqualified type name is resolved using default schema. I guess (just guess) this behavior is caused by the JDBC driver (since the JDBC driver generally cannot know about changes to the current schema you can do using database calls), perhaps there could be some connection properties that would have it behave otherwise. (I've found this interesting property of Oracle connection, whose name suggests it could do what you need, but I have really no experience with it. I'm afraid I won't be able to help any further on this, though.)

You can also try registering the type name with a dot in the front (that is, ".TYPENAME"). I believe this causes the type to be searched for in the current schema (I'm not 100% sure it will, as it's been some time I was experimenting with this and I've forgotten all the details in he meantime). Still, you'll have to review all these calls, so this might not be much of a help.
Jina Lu
Greenhorn

Joined: Jul 09, 2010
Posts: 25

Unfortunately the property you mentioned exists only for newer Oracle version like Oracle 11g. In my case it is 10g and ojdbc14.jar lib. I'll keep searching for solution and post it here when find out.
Thanks for trying to help anyway.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

You can use newer driver against older version of the database. Our Oracle FAQ has a link to a driver/database compatibility matrix.

ojdbc14.jar is actually too old for older than Oracle 10g.
Jina Lu
Greenhorn

Joined: Jul 09, 2010
Posts: 25

Even if I change ojdbc jar I still don't know how to set that property flag in my application. Seams like it is just constant...
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

The constant contains a name of a property. To activate it, you'll add the property into a property list with value equal to true. The details depend on how you're obtaining a connection. I'm not very skilled with setting connection properties, but if you post the details on how you create the connection, perhaps someone will be able to help.
Jina Lu
Greenhorn

Joined: Jul 09, 2010
Posts: 25

Application is running on tomcat 6. Connection pool is used from which application takes the connection. I tried to use connectionProperties, but it doesn't work:

<Resource auth="Container" name="jdbc/myDB"
type="oracle.jdbc.pool.OracleDataSource" factory="oracle.jdbc.pool.OracleDataSourceFactory"
connectionCachingEnabled="true" driverClassName="oracle.jdbc.OracleDriver"
url="jdbc: oracle:thin:@111.111.111.11:1521:dbsid"
user="user" password="psw" removeAbandoned="true"
removeAbandonedTimeout="30" maxActive="200" maxIdle="20" maxwait="600"
connectionProperties=";oracle.jdbc.createDescriptorUseCurrentSchemaForSchemaName=true;" />

Any ideas?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

This is really a grey area for me. But perhaps you could obtain the physical connection, cast it to OracleConnection and call its getProperties() to obtain the properties the connection was created with. If the property is set and it doesn't behave as expected, the search ends. If the property is not set, perhaps there could be a different way to slip it through.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: default schema
 
Similar Threads
How to access schema for db2 through java code
Encoding 8859_1 to 8859_7
character encodings
Problem with Session Facade
Database access through CMP, Setting Schema