Hi Jonathan,
Thanks for the detailed bug report. Please file a JIRA entry with all
these comments so that we can work on a patch and follow-up the
discussion within the entry.
Thanks again,
Emmanuel
I found the bug.
In a nutshell, what happens is that on the insert it parses the query
and it’s doing a case-insensitive lookup into the hash map of tables.
On the select it doesn’t do the parse. That’s why select works. The
parse fails because the hash map storing the tables is keyed off of
the table names. The key is all uppercase (because my DB has uppercase
table names) but the case-insensitive match it was trying to do put my
query into lowercase. The get() method on the hash map will always
fail because it uses .equals(), which is case sensitive.
The reason the insert works without the schema name is because of a
hack in the code. If the parse fails to find the matching key in the
hash map, it iterates through all the values and tries to match the
table name that way. This is very dangerous because if you have more
than one table with the same name under different schemas, it will
match the first table in the hash map, which might be the wrong one.
This can be fixed with either creating the hash map with lowercase
keys (when case-sensitivity is false) or by overloading the get()
method on the hash map to allow for case-insensitive comparisons on
the keys.
I’d be happy to write up a more descriptive e-mail with specific files
and methods if you’d like. Is there a better list to submit that to?
*From:* [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] *On Behalf Of
*Jonathan Stockho
*Sent:* Tuesday, August 19, 2008 1:13 PM
*To:* Sequoia general mailing list
*Subject:* [Sequoia] Can't Insert with schema name
I can SELECT from my tables using the schema name, but I can’t insert.
I have an Apache Derby database and I’m trying to incorporate Sequoia
into our product. However, when I try to do an insert with the
following syntax:
insert into SCHEMA_NAME.TABLE_NAME values (1, ‘test’);
I get the following error from Sequoia:
Error during command execution (Unknown table ‘schema_name.table_name’
in this INSERT statement: ‘insert into SCHEMA_NAME.TABLE_NAME values
(1, ‘test’)’)
If I query with the command:
select * from SCHEMA_NAME.TABLE_NAME;
it works fine.
I have gone in as admin and done a schema dump, which results in the
following output:
SCHEMA_NAME.TABLE_NAME
SCHEMA_NAME.TABLE_NAME2
I asked about this problem before, and it was suggested that it might
be a problem with case sensitivity. My tables and schema are defined
using all uppercase, which is what I supply in my query. However, the
error reports a lowercase schema and table name. Maybe Sequoia is
converting the case when doing an insert? Using the standard JDBC
driver I don’t have this problem. In fact, I can use any case I want
there. Between the schema dump and the select working, I don’t know
why this would fail.
Any ideas? I’m running Sequoia 2.10.10. I noticed there was a fix for
something like this in 2.5 against Postgres. Did the fix not apply to
Derby?
Jonathan Stockho
Software Developer
Swisslog Healthcare Solutions
10825 E. 47th Avenue
Denver, CO 80239
Phone: 303-373-7814
FAX: 303-373-7872
e-mail: [EMAIL PROTECTED]
Internet: www.swisslog.com <http://www.swisslog.com>
------------------------------------------------------------------------------------
*About Swisslog *
Swisslog designs solutions in logistics automation that greatly reduce
the manual flow of materials and associated information within
healthcare facilities.
This message may contain legally privileged or confidential
information and is therefore addressed to the named persons only. The
recipient should inform the sender and delete this message, if he/she
is not named as addressee. The sender disclaims any and all liability
for the integrity and punctuality of this message. The sender has
activated an automatic virus scanning, but does not guarantee the
virus free transmission of this message.
This message may contain legally privileged or confidential
information and is therefore addressed to the named persons only. The
recipient should inform the sender and delete this message, if he/she
is not named as addressee. The sender disclaims any and all liability
for the integrity and punctuality of this message. The sender has
activated an automatic virus scanning, but does not guarantee the
virus free transmission of this message.
------------------------------------------------------------------------
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: [EMAIL PROTECTED]
Skype: emmanuel_cecchet
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
