Re: Strange behavior of Derby database. SQL generation
Re: Strange behavior of Derby database. SQL generation
- Subject: Re: Strange behavior of Derby database. SQL generation
- From: Sergio Sánchez Maffet <email@hidden>
- Date: Tue, 5 Feb 2008 20:12:19 +0100
Hi David,
No, they are not reserved words. I have checked it, even when I prefix
them they do not work.
This was also my first guess. I have tried it with uppercase and
lowercase identifiers. Here ist the output of the following commands:
1. show tables; describe content; INSERT INTO TESTA(CREATED, start,
name, stop, id_r) VALUES ('2008-02-05 01:49:02', NULL, 'Test', NULL,
'00...[deleted]')
2. show tables; describe content; INSERT INTO testa(created, start,
name, stop, id_r) VALUES ('2008-02-05 01:49:02', NULL, 'Test', NULL,
'00...[deleted]')
both produces the same output:
derby:
[java] ij version 10.2
[java] CONNECTION0* - jdbc:derby:test
[java] * = current connection
[java] ij> TABLE_SCHEM |TABLE_NAME |
REMARKS
[java]
------------------------------------------------------------------------
[java] SYS |SYSALIASES |
[java] SYS |SYSCHECKS |
[java] SYS |SYSCOLPERMS |
[java] SYS |SYSCOLUMNS |
[java] SYS |SYSCONGLOMERATES |
[java] SYS |SYSCONSTRAINTS |
[java] SYS |SYSDEPENDS |
[java] SYS |SYSFILES |
[java] SYS |SYSFOREIGNKEYS |
[java] SYS |SYSKEYS |
[java] SYS |SYSROUTINEPERMS |
[java] SYS |SYSSCHEMAS |
[java] SYS |SYSSTATEMENTS |
[java] SYS |SYSSTATISTICS |
[java] SYS |SYSTABLEPERMS |
[java] SYS |SYSTABLES |
[java] SYS |SYSTRIGGERS |
[java] SYS |SYSVIEWS |
[java] SYSIBM |SYSDUMMY1 |
[java] APP |TESTA |
[java] 22 rows selected
[java] ij> COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|
COLUMN_DEF|CHAR_OCTE&|IS_NULL&
[java]
------------------------------------------------------------------------------
[java] created |TIMESTAMP|6 |10 |26 |NULL
|NULL |NO
[java] id_r |CHAR |NULL|NULL|24 |NULL
|48 |NO
[java] name |CHAR |NULL|NULL|254 |NULL
|508 |NO
[java] start |TIMESTAMP|6 |10 |26 |NULL
|NULL |YES
[java] stop |TIMESTAMP|6 |10 |26 |NULL
|NULL |YES
[java] 5 rows selected
[java] ij> ERROR 42X14: 'CREATED' is not a column in table or
VTI 'APP.CONTENT'.
[java] ij>
I have really no clue until now...
--
Sergio
On 05.02.2008, at 19:54, David Avendasora wrote:
I don't know about Derby, but table and field names like "created",
"start", "name" and "stop" give me the creepy-crawlies.
They either are, or are very similar to, reserved words. Could that
be causing any of your pain? It doesn't really seem like it here,
but crazier things have happened.
Also, since Java is case-sensitive, is Derby? Is created == CREATED?
Dave
On Feb 5, 2008, at 1:15 PM, Sergio Sánchez Maffet wrote:
Hi all,
I'm trying to get the derby database up and running with a new WO
application (a simple test). Trying to insert some simple records
including a 24 byte auto generated primary key, I encountered a
strange problem related to the SQL.
In contrast to the description in the derby documentation provided
with the JavaWebObjects examples, derby expects all external names
surrounded by double quotes. (I have done a dirty trick putting
double quotes into the external name in the model file itself, only
to test my guess and it does work)
Here is an example:
The following code will only run when you replace (created, start,
name, stop, id_r) with ("created", "start", "name", "stop", "id_r")
INSERT INTO tablea(created, start, name, stop, id_r) VALUES (?,
NULL, ?, NULL, ?)" withBindings: 1:2008-02-05 18:35:43(created),
2:"Test"(name), 3:<NSData: length=24, data=0... deleted...000>(idR)>:
Error:
Next exception:SQL State:42X14 -- error code: 20000 -- msg:
'CREATED' is not a column in table or VTI 'APP.TABLEA'.
But the table and the column exists! Checked with ij tool (show
tables and describe tablea).
There are three possibilities left:
1. Force the SQLExpression to use quotes on external names, but
this is deprecated, and a simple test did not work. (should I try
harder? ;-))
2. Repair, patch, configuration or what else to let derby accept
unquoted external names as described in the doc.
Other ideas?
My setup:
derby.system.home=/Library/Databases/Derby
derby.ui.codeset=UTF8
WO 5.4
Derby 10.2.2.0 (jars copied from the examples installation)
WOLips 4788
Any clues?
--
Sergio
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden