You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Graham Leggett <mi...@sharp.fm> on 2008/08/23 16:55:29 UTC
Does derby support still work?
Hi all,
I have been trying to create a table generation script for derby using
the maven torque:sql goal, but the script produced doesn't seem to be
valid derby syntax.
Double checking within velocity.log, I see that the derby templates are
being used to create the database file. However, if I attempt to
manually run the create database script lines manually against a derby
database, I get a syntax error as follows:
ij> CREATE TABLE property ( property_id INTEGER NOT NULL, key VARCHAR
NOT NULL, value VARCHAR, PRIMARY KEY(property_id));
ERROR 42X01: Syntax error: Encountered "key" at line 1, column 55.
Is derby still supported by torque v3.3?
Regards,
Graham
--
RE: Does derby support still work?
Posted by Greg Monroe <Gr...@DukeCE.com>.
FWIW - I ran the V3.3RC3 Test project (same as release) against
Derby 10.3.2.1 (both embedded and network server versions).
This checks a LOT of standard functionality, including the
table creation and data type mappings.
In the table creation side of things, there were no issues in
creating the test tables (which include every Torque/JDBC
supported data type. There were some issues relating to
Torque/Derby not being able to drop some tables because they
contained circular foreign key references (part of what the
test suite check that Torque handles). This was handled
manually in running the tests by making sure the DB was
recreated from scratch each run.
The only functional test that failed was related to Derby not
allowing expressions in an order by clause where grouping or
distinct is used. E.g. ORDER BY UPPER(x.y) where x.y is in the
group fails because Derby does not consider UPPER(x.y) are
part of the group.
This caused the testJoinOrderDistinct test case to fail... but
everything else worked.
Here's the e-mail outlining the details:
http://mail-archives.apache.org/mod_mbox/db-torque-dev/200801.mbox/%3C8F
5843B903F59D4C8C6806BB49A39119051FC792@dukece-mail3.dukece.com%3E
> -----Original Message-----
> From: Graham Leggett [mailto:minfrin@sharp.fm]
> Sent: Monday, August 25, 2008 6:15 AM
> To: Apache Torque Users List
> Subject: Re: Does derby support still work?
>
> Thomas Vandahl wrote:
>
> > Graham Leggett wrote:
> >> ij> CREATE TABLE active ( active_id INTEGER NOT NULL, serial
VARCHAR
> NOT
> >> NULL, name VARCHAR NOT NULL, description VARCHAR NOT NULL,
> signature_id
> >> INTEGER NOT NULL, PRIMARY KEY(active_id), UNIQUE (serial));
> >> ERROR 42X01: Syntax error: Encountered "NOT" at line 1, column 66.
> >
> > I'm not exactly a Derby expert but in this case "serial" seems to be
> > responsible.
>
> Column 66 and the error message both point at the word "NOT", rather
> than the word serial.
>
> Some further digging over the weekend shows that Derby may not support
> the VARCHAR type properly (it seems to need to be VARCHAR(limit), not
> VARCHAR).
>
> Is it Torque's responsibility to decide on the VARCHAR type, or is the
> choice of type left to the end user?
>
> Regards,
> Graham
> --
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Does derby support still work?
Posted by Graham Leggett <mi...@sharp.fm>.
Thomas Vandahl wrote:
> Graham Leggett wrote:
>> ij> CREATE TABLE active ( active_id INTEGER NOT NULL, serial VARCHAR NOT
>> NULL, name VARCHAR NOT NULL, description VARCHAR NOT NULL, signature_id
>> INTEGER NOT NULL, PRIMARY KEY(active_id), UNIQUE (serial));
>> ERROR 42X01: Syntax error: Encountered "NOT" at line 1, column 66.
>
> I'm not exactly a Derby expert but in this case "serial" seems to be
> responsible.
Column 66 and the error message both point at the word "NOT", rather
than the word serial.
Some further digging over the weekend shows that Derby may not support
the VARCHAR type properly (it seems to need to be VARCHAR(limit), not
VARCHAR).
Is it Torque's responsibility to decide on the VARCHAR type, or is the
choice of type left to the end user?
Regards,
Graham
--
Re: Does derby support still work?
Posted by Thomas Vandahl <tv...@apache.org>.
Graham Leggett wrote:
> ij> CREATE TABLE active ( active_id INTEGER NOT NULL, serial VARCHAR NOT
> NULL, name VARCHAR NOT NULL, description VARCHAR NOT NULL, signature_id
> INTEGER NOT NULL, PRIMARY KEY(active_id), UNIQUE (serial));
> ERROR 42X01: Syntax error: Encountered "NOT" at line 1, column 66.
I'm not exactly a Derby expert but in this case "serial" seems to be
responsible.
> ij> CREATE TABLE company ( company_id INTEGER NOT NULL, serial VARCHAR
> NOT NULL, name VARCHAR NOT NULL, tradingas VARCHAR, registration_number
> VARCHAR NOT NULL, vat_number VARCHAR, fsp_number VARCHAR, bank_account
> VARCHAR, bank_sortcode VARCHAR, bank_name VARCHAR, bank_swift VARCHAR,
> bank_reference VARCHAR, description VARCHAR, active_id INTEGER NOT NULL,
> contact_id INTEGER NOT NULL, valid_from DATE, valid_to DATE,
> signature_id INTEGER NOT NULL, PRIMARY KEY(company_id), UNIQUE (serial,
> registration_number));
> ERROR 42X01: Syntax error: Encountered "NOT" at line 1, column 68.
Same thing here. My guess: "serial" is a reserved word in Derby. Try to
avoid reserved words as column names.
Bye, Thomas.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Does derby support still work?
Posted by Graham Leggett <mi...@sharp.fm>.
Thomas Vandahl wrote:
> What this tells you is that a column name "key" is not valid in Derby.
> You should choose another name. Although you might succeed creating a
> valid SQL file by patching some templates, Village will barf at you when
> you want to insert something.
I tried the next table down, and it also failed like so:
ij> CREATE TABLE active ( active_id INTEGER NOT NULL, serial VARCHAR NOT
NULL, name VARCHAR NOT NULL, description VARCHAR NOT NULL, signature_id
INTEGER NOT NULL, PRIMARY KEY(active_id), UNIQUE (serial));
ERROR 42X01: Syntax error: Encountered "NOT" at line 1, column 66.
The table below that failed as well, seemingly for the same reason:
ij> CREATE TABLE company ( company_id INTEGER NOT NULL, serial VARCHAR
NOT NULL, name VARCHAR NOT NULL, tradingas VARCHAR, registration_number
VARCHAR NOT NULL, vat_number VARCHAR, fsp_number VARCHAR, bank_account
VARCHAR, bank_sortcode VARCHAR, bank_name VARCHAR, bank_swift VARCHAR,
bank_reference VARCHAR, description VARCHAR, active_id INTEGER NOT NULL,
contact_id INTEGER NOT NULL, valid_from DATE, valid_to DATE,
signature_id INTEGER NOT NULL, PRIMARY KEY(company_id), UNIQUE (serial,
registration_number));
ERROR 42X01: Syntax error: Encountered "NOT" at line 1, column 68.
Is there a special version of derby required? I am using v10.4.1.3.
Regards,
Graham
--
Re: Does derby support still work?
Posted by Thomas Vandahl <tv...@apache.org>.
Graham Leggett wrote:
> ij> CREATE TABLE property ( property_id INTEGER NOT NULL, key VARCHAR
> NOT NULL, value VARCHAR, PRIMARY KEY(property_id));
> ERROR 42X01: Syntax error: Encountered "key" at line 1, column 55.
What this tells you is that a column name "key" is not valid in Derby.
You should choose another name. Although you might succeed creating a
valid SQL file by patching some templates, Village will barf at you when
you want to insert something.
> Is derby still supported by torque v3.3?
It sure is.
Bye, Thomas.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org