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
