Null schema name when generating tables?

[Kevin Sutter <kw...@gmail.com>]

Hi,
Looking for a pointer in case somebody knows this code better than me...

We're running the CTS TCK against DB2 and we've hit a snag with the
SynchronizeMappings feature (I know this is not a required aspect of the
TCK, but that's what we have been using to create the tables for the
testing).

One of the tests in the TCK requires a table called DATATYPES.  The problem
is that DB2 has a system table by this name, so when OpenJPA attempts to
look up any existing table information, we accidentally find the system
table (SYSCAT) instead of the user's table (cts1).  The following trace
snippet shows the problem.

1000  JPATCK  TRACE  [main] openjpa.jdbc.Schema - Reading table information
for schema name "null", table name "DATATYPES".
1000  JPATCK  TRACE  [main] openjpa.jdbc.JDBC - <t 16515324, conn
1314410072> [0 ms] rollback
1000  JPATCK  TRACE  [main] openjpa.jdbc.JDBC - <t 16515324, conn
1314410072> getColumns: null, null, DATATYPES, null
1500  JPATCK  TRACE  [main] openjpa.jdbc.Schema - Reading column information
for table " SYSCAT.DATATYPES".

Of course, this goes on thinking that we have found an appropriate DATATYPES
table definition, but later when we attempt to alter it, we get an error:

1891  JPATCK  TRACE  [main] openjpa.jdbc.SQL - <t 16515324, conn 477240434>
executing stmnt 854733554 ALTER TABLE DATATYPES ADD id INTEGER
1891  JPATCK  TRACE  [main] openjpa.jdbc.SQL - <t 16515324, conn 477240434>
[0 ms] spent
1891  JPATCK  TRACE  [main] openjpa.jdbc.JDBC - <t 16515324, conn 477240434>
[0 ms] close
<openjpa-0.0.0-r420667:563705M nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: DB2 SQL error: SQLCODE:
-204, SQLSTATE: 42704, SQLERRMC: CTS1.DATATYPES {stmnt 854733554 ALTER TABLE
DATATYPES ADD id INTEGER} [code=-204, state=42704]

I started to debug the problem and am finding that we are relying on the
setting of a schema name either via annotation or xml to override the
default of "null".

The question I have is whether this could be changed to pull the userid from
the Connection object, if the configured schema name is null, of course.
This way, if the user has configured a schema name, it would be used.  But,
if no schema name is configured, then we would use the userid of the
Connection object.  If there is no security associated with a given database
(ie. Derby), this would still default to null and we'd be processing as we
do today.  But, in the case of secure databases (ie. DB2, Oracle, etc), we
would use the userid from the Connection object.

Would there be a problem with this approach?

Thanks,
Kevin

Re: Null schema name when generating tables?

[Kevin Sutter <kw...@gmail.com>]

OPENJPA-320 opened to address this problem.

On 8/16/07, Kevin Sutter <kw...@gmail.com> wrote:
>
> Good point, Mike.  And, I may have discovered that we already have a
> mechanism to skip these types of "system tables".  I see that we have
> defined systemSchemas and systemTables that will be compared against before
> allowing the use of a given table definition.  I haven't figured out all of
> the ins and outs of this approach, but it looks workable.  Thanks for the
> idea.
>
> Kevin
>
> On 8/16/07, Michael Dick <mi...@apache.org> wrote:
> >
> > <snip>
> >
> > The question I have is whether this could be changed to pull the userid
> > from
> > > the Connection object, if the configured schema name is null, of
> > course.
> > > This way, if the user has configured a schema name, it would be
> > > used.  But,
> > > if no schema name is configured, then we would use the userid of the
> > > Connection object.  If there is no security associated with a given
> > > database
> > > (ie. Derby), this would still default to null and we'd be processing
> > as we
> > > do today.  But, in the case of secure databases (ie. DB2, Oracle,
> > etc), we
> > > would use the userid from the Connection object.
> > >
> > > Would there be a problem with this approach?
> >
> >
> > I think this approach would work for DB2, but I'm not convinced it's the
> >
> > correct thing to do.
> >
> > I don't like the idea of assuming that the username is also the default
> > schema name. I believe that happens to be the case for many databases
> > but I
> > don't know whether it's mandated by the specification.
> >
> > Even if it is recommended / mandated by the SQL or JDBC spec(s) we'd be
> > ignoring any DB specific settings. MikesPhonyDB might allow users to
> > specify
> > a default in a properties file. This is a bad example, but in this case
> > we
> > should let the Database determine the appropriate schema to use.
> >
> > That being said I haven't looked at the code in this area and there
> > might be
> > a better way to query for a table.
> >
> > -Mike
> >
>
>

Re: Null schema name when generating tables?

[Kevin Sutter <kw...@gmail.com>]

Good point, Mike.  And, I may have discovered that we already have a
mechanism to skip these types of "system tables".  I see that we have
defined systemSchemas and systemTables that will be compared against before
allowing the use of a given table definition.  I haven't figured out all of
the ins and outs of this approach, but it looks workable.  Thanks for the
idea.

Kevin

On 8/16/07, Michael Dick <mi...@apache.org> wrote:
>
> <snip>
>
> The question I have is whether this could be changed to pull the userid
> from
> > the Connection object, if the configured schema name is null, of course.
> > This way, if the user has configured a schema name, it would be
> > used.  But,
> > if no schema name is configured, then we would use the userid of the
> > Connection object.  If there is no security associated with a given
> > database
> > (ie. Derby), this would still default to null and we'd be processing as
> we
> > do today.  But, in the case of secure databases (ie. DB2, Oracle, etc),
> we
> > would use the userid from the Connection object.
> >
> > Would there be a problem with this approach?
>
>
> I think this approach would work for DB2, but I'm not convinced it's the
> correct thing to do.
>
> I don't like the idea of assuming that the username is also the default
> schema name. I believe that happens to be the case for many databases but
> I
> don't know whether it's mandated by the specification.
>
> Even if it is recommended / mandated by the SQL or JDBC spec(s) we'd be
> ignoring any DB specific settings. MikesPhonyDB might allow users to
> specify
> a default in a properties file. This is a bad example, but in this case we
> should let the Database determine the appropriate schema to use.
>
> That being said I haven't looked at the code in this area and there might
> be
> a better way to query for a table.
>
> -Mike
>

Re: Null schema name when generating tables?

[Michael Dick <mi...@apache.org>]

<snip>

The question I have is whether this could be changed to pull the userid from
> the Connection object, if the configured schema name is null, of course.
> This way, if the user has configured a schema name, it would be
> used.  But,
> if no schema name is configured, then we would use the userid of the
> Connection object.  If there is no security associated with a given
> database
> (ie. Derby), this would still default to null and we'd be processing as we
> do today.  But, in the case of secure databases (ie. DB2, Oracle, etc), we
> would use the userid from the Connection object.
>
> Would there be a problem with this approach?


I think this approach would work for DB2, but I'm not convinced it's the
correct thing to do.

I don't like the idea of assuming that the username is also the default
schema name. I believe that happens to be the case for many databases but I
don't know whether it's mandated by the specification.

Even if it is recommended / mandated by the SQL or JDBC spec(s) we'd be
ignoring any DB specific settings. MikesPhonyDB might allow users to specify
a default in a properties file. This is a bad example, but in this case we
should let the Database determine the appropriate schema to use.

That being said I haven't looked at the code in this area and there might be
a better way to query for a table.

-Mike