I'll have to
experiment some more with the JDBC metadata APIs to find out how they respond.
I'll see if I can get to that this afternoon.
-----Original Message-----
From: Jeff Butler [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 27, 2006 9:50 PM
To: dev@ibatis.apache.org
Subject: Re: Abator introspection patchFollowing your example...I'm assuming DatabaseMetaData.getColumns(null, null, 'BAR', null) would return columns for both the public alias BAR and PAT.BAR.However, could you try DatabaseMetaData.getColumns(null, '', 'BAR', null)? I'm wondering if only columns for the public alias would be returned in this case.If so, this could be an easy change to Abator. Currently, Abator does not distinguish between an empty string and null for schema/catalog - but the spec does make a distinction here.If this works, we could change Abator to allow this:<table tableName="BAR" schema="" />To mean a different thing than this:<table tableName="BAR" />Jeff Butler
On 9/27/06, [EMAIL PROTECTED] <[EMAIL PROTECTED] > wrote:Oracle schemas are equivalent to users. When you login to a database as user jeff, you have default access to all tables in schema 'JEFF'. You can change your current schema on a per-session basis.Thus, if there are tables JEFF.FOO and PAT.FOO and you login as jeff and execute "select * from foo", then you will be getting the data from JEFF.FOO. If you ran "ALTER SESSION SET CURRENT_SCHEMA = 'PAT'" and ran it again, then you'd see the data in PAT.FOO.The real trouble comes in if there's a table named PAT.BAR, and a synonym is created for it with "CREATE PUBLIC SYNONYM BAR FOR PAT.BAR". Then, if your current_schema was set to 'JEFF' and you ran "select * from bar", then you'd get the data from PAT.BAR. However, using the JDBC DatabaseMetaData.getColumns(null, 'JEFF', 'BAR', null) would return nothing. The BAR table is really in schema 'PAT', but there's no easy way to know that you can access it without using a schema qualifier without querying Oracle system views to know that a synonym exists, and having detailed knowledge of how Oracle resolves unqualified table names. However, you can just let Oracle take care of it for you by using the ResultSetMetaData API instead.The problem here is that I'm not sure how other databases and JDBC drivers will handle this, especially the "select * from tablename" part. I imagine that's a very expensive operation on some DBs. It's probably expensive on Oracle, too. I thought about trying "select * from tablewhere where 1 = 2", but I'm not sure if the ResultSetMetaData would have any data or not.-----Original Message-----
From: Jeff Butler [mailto: [EMAIL PROTECTED]]
Sent: Wednesday, September 27, 2006 2:30 PM
To: dev@ibatis.apache.org
Subject: Re: Abator introspection patchSome questions...How does the actual table get selected at run time? Is there a default schema specified on the db connection? Or, are the aliases not in a schema and there is some way to switch the aliases to the proper schema at runtime?Do you actually want to generate the artifacts based on a specific schema, but just not include the schema in the generated XML?Seems like it would be much easier to give you the option to generate against a specific schema, but then not include the schema in the generated code.Jeff Butler
On 9/27/06, [EMAIL PROTECTED] < [EMAIL PROTECTED] > wrote:I've had trouble with Abator's interaction with Oracle schemas. I've implemented and attached a patch to the DatabaseIntrospector that refactors it into a choice of using the DatabaseMetaData API and the ResultSetMetaData API to get information about a table.
The problem is that we have several schemas with the same tables as well as synonyms for a subset of these. Using DatabaseMetaData.getColumns(...) ends up returning data from an arbitrary schema, and in our case, it's never the one we want. The way to get around this is normally to specify a schema name in the config file, but we actually use different schemas in dev, test, and production. What we really want is the table that is found without any qualification.
The best way I know to do this is just run "select * from tablename" and let Oracle deal with the schema and synonym resolution, the examine the ResultSetMetaData. Luckily, the ResultSetMetaData gives all the information Abator actually uses from the DatabaseMetaData, so I've implemented this as a proof-of-concept.
I tried to refactor the code as little as possible, but it was not especially easy.
Let me know what you think. In particular, I'd like to know if we really need to support both techniques. Is there any case where the ResultSetMetaData approach would fail? Should it be the default? Can we remove the DatabaseMetaData.getColumns(...) code entirely?
--Patrick
