Newbie questions

["Williamson, Nick" <ni...@mentor.com>]

Hi all,
 
I'm brand new to Derby. Having been through the tutorial exercises, I've
started on the joyous task of migrating my Oracle schema to Derby and
I'm finding quite a few things in the DDL that it doesn't like. I've
been able to re-phrase / remove most of them, but one in particular is
causing me a problem: I have many column names that include the
characters "##", and Derby doesn't like them. For example:
 
CREATE TABLE componen
  (u##intepn varchar (30) NOT NULL);
 
gives me a lexical error. I can work around it by adding speech marks:
 
CREATE TABLE componen
  ("u##intepn" varchar (30) NOT NULL);
 
...but it's a bit of a pain because I also have to:
 
select "u##intepn" from componen;
 
to avoid further lexical errors.
 
Much of the SQL in my app is generated by Java and it is supposed to be
database-independant. In Oracle, doing that last select with the speech
marks around the column name would cause it to return the literal string
"u##intepn" rather than the column value. Is there a way to get Derby to
be a bit more chilled about this particular character, so that I don't
have to use quotes everywhere?
 
Oh, and while I'm at it: is there a Derby equivalent to Oracle's SPOOL
command? I want to record all the errors from my DDL script in a
logfile, and they're not getting written to derby.log...
 
TIA
Nick
 
 

Re: Newbie questions

[Stanley Bradbury <St...@gmail.com>]

Williamson, Nick wrote:
> Hi all,
>  
> I'm brand new to Derby. Having been through the tutorial exercises, 
> I've started on the joyous task of migrating my Oracle schema to Derby 
> and I'm finding quite a few things in the DDL that it doesn't like. 
> I've been able to re-phrase / remove most of them, but one in 
> particular is causing me a problem: I have many column names that 
> include the characters "##", and Derby doesn't like them. For example:
>  
> CREATE TABLE componen
>   (u##intepn varchar (30) NOT NULL);
>  
> gives me a lexical error. I can work around it by adding speech marks:
>  
> CREATE TABLE componen
>   ("u##intepn" varchar (30) NOT NULL);
>  
> ...but it's a bit of a pain because I also have to:
>  
> select "u##intepn" from componen;
>  
> to avoid further lexical errors.
>  
> Much of the SQL in my app is generated by Java and it is supposed to 
> be database-independant. In Oracle, doing that last select with the 
> speech marks around the column name would cause it to return the 
> literal string "u##intepn" rather than the column value. Is there a 
> way to get Derby to be a bit more chilled about this particular 
> character, so that I don't have to use quotes everywhere?
>  
> Oh, and while I'm at it: is there a Derby equivalent to Oracle's SPOOL 
> command? I want to record all the errors from my DDL script in a 
> logfile, and they're not getting written to derby.log...
>  
> TIA
> Nick
>  
>  
Database independent code via strict adherence to standards is a key 
part of the Derby charter.  Use of quoted identifiers is the way to 
accommodate special characters in column names.  Derby follows the Java 
standard that single quotes are character string delimiters ('speech 
marks') and double quotes delimit special characters.  If allowing hash 
marks (#) in non-quoted SQL92Identifiers (like columnnames) is important 
to you please file a JIRA feature request for this.  The bug/feature 
request process can be found at: 
http://db.apache.org/derby/DerbyBugGuidelines.html.

Am not familiar with the the SPOOL command but you can get get all 
messages of any severity written to the derby.log  using  the property 
....logSeverityLevel to zero...  A good reference to some basic 
development properties can be found at:

  http://wiki.apache.org/db-derby/DebugPropertiesTmpl