You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Patrick Meyer <me...@gmail.com> on 2014/04/19 03:34:53 UTC

CREATE TABLE lexical error

I have an application that allows users to import data into Derby. As such, the users specify the column names. A user encountered a lexical error message that I have been able to reproduce with the following CREATE TABLE statements. Can anyone explain why the column names appear to be causing a lexical error and how to avoid it? I am using 10.9.1.

 

These two statements result in errors:

 

CREATE TABLE TBLPAT1 (xb1x DOUBLE, xb2x DOUBLE) 

Error: Lexical error at line 1, column 24.  Encountered: "\ufeff" (65279), after : "".

 

CREATE TABLE TBLPAT2 (xb1x VARCHAR(50), xb2x VARCHAR(50)) 

Error: Lexical error at line 1, column 24.  Encountered: "\ufeff" (65279), after : "".

SQLState:  42X02

ErrorCode: 30000

 

 

These statements work just fine.

 

CREATE TABLE TBLPAT3 (xvar1x DOUBLE, xvar2x DOUBLE) 

 

CREATE TABLE TBLPAT4 (xvar1x VARCHAR(50), xvar2x DOUBLE) 

 

CREATE TABLE TBLPAT5 (xvar1x VARCHAR(50), xvar2x VARCHAR(50))

 

 

Thanks,

Patrick

 


RE: CREATE TABLE lexical error

Posted by Patrick Meyer <me...@gmail.com>.
The users create the files, so I don't have control over that part. However, in this particular case the file was created in SPSS and edited in Excel. I'll look into using a hex editor to explore the file.

I modified the statement so that column names are in double quotes and that solves the problem when reading the names from a delimited text file.

Thanks again!


-----Original Message-----
From: John English [mailto:john.foreign@gmail.com] 
Sent: Saturday, April 19, 2014 11:47 AM
To: Derby Discussion
Subject: Re: CREATE TABLE lexical error

On 19/04/2014 18:18, Patrick Meyer wrote:
> Thanks John. Yes, I was cutting and pasting from Notepad, but I was 
> doing that for all four lines. I don't understand why it worked for some lines but not others.
>
> Nevertheless,  the cutting and pasting is not my real problem. I 
> created these four statements to reproduce a problem I encountered 
> with my program. I get the same lexical error when my program obtains 
> the column names from a delimited text file. This error only occurs 
> with some (actually, very few) files and I cannot figure out why. Any ideas why?

What are you using to create these files? Presumably whatever created the files for you decided you would like some extra line breaks that you didn't ask for (like all the too-clever-by-half autocorrections that MS Office wants to do if you give it a chance). You could always try using something like "od -cx" or a hex editor to see what the file really contains.

Good luck,
--
John English


Re: CREATE TABLE lexical error

Posted by John English <jo...@gmail.com>.
On 19/04/2014 18:18, Patrick Meyer wrote:
> Thanks John. Yes, I was cutting and pasting from Notepad, but I was doing that
> for all four lines. I don't understand why it worked for some lines but not others.
>
> Nevertheless,  the cutting and pasting is not my real problem. I created these
> four statements to reproduce a problem I encountered with my program. I get the
> same lexical error when my program obtains the column names from a delimited
> text file. This error only occurs with some (actually, very few) files and I
> cannot figure out why. Any ideas why?

What are you using to create these files? Presumably whatever created the files 
for you decided you would like some extra line breaks that you didn't ask for 
(like all the too-clever-by-half autocorrections that MS Office wants to do if 
you give it a chance). You could always try using something like "od -cx" or a 
hex editor to see what the file really contains.

Good luck,
-- 
John English

Re: CREATE TABLE lexical error

Posted by Rick Hillegas <ri...@oracle.com>.
On 4/19/14 8:18 AM, Patrick Meyer wrote:
>
> Thanks John. Yes, I was cutting and pasting from Notepad, but I was 
> doing that for all four lines. I don't understand why it worked for 
> some lines but not others.
>
> Nevertheless,  the cutting and pasting is not my real problem. I 
> created these four statements to reproduce a problem I encountered 
> with my program. I get the same lexical error when my program obtains 
> the column names from a delimited text file. This error only occurs 
> with some (actually, very few) files and I cannot figure out why. Any 
> ideas why?
>
Not your immediate problem, but it sounds as though you may have a SQL 
injection issue if you are creating tables with user-supplied column 
names. Make sure that you correctly quote and escape the user-supplied 
column names.

Hope this helps,
-Rick
>
> Thanks
>
> On Apr 19, 2014 10:53 AM, "John English" <john.foreign@gmail.com 
> <ma...@gmail.com>> wrote:
>
>     On 19/04/2014 04:34, Patrick Meyer wrote:
>
>         I have an application that allows users to import data into
>         Derby. As such, the
>         users specify the column names. A user encountered a lexical
>         error message that
>         I have been able to reproduce with the following CREATE TABLE
>         statements. Can
>         anyone explain why the column names appear to be causing a
>         lexical error and how
>         to avoid it? I am using 10.9.1.
>
>
>     \uFEFF is a Unicode "zero-width line break" character. Presumably
>     you're cutting and pasting from somewhere; try pasting into a
>     plain-text editor and then cut & paste from there to get rid of it?
>
>     HTH,
>     -- 
>     John English
>


Re: CREATE TABLE lexical error

Posted by Patrick Meyer <me...@gmail.com>.
Thanks John. Yes, I was cutting and pasting from Notepad, but I was doing
that for all four lines. I don't understand why it worked for some lines
but not others.

Nevertheless,  the cutting and pasting is not my real problem. I created
these four statements to reproduce a problem I encountered with my program.
I get the same lexical error when my program obtains the column names from
a delimited text file. This error only occurs with some (actually, very
few) files and I cannot figure out why. Any ideas why?

Thanks
On Apr 19, 2014 10:53 AM, "John English" <jo...@gmail.com> wrote:

> On 19/04/2014 04:34, Patrick Meyer wrote:
>
>> I have an application that allows users to import data into Derby. As
>> such, the
>> users specify the column names. A user encountered a lexical error
>> message that
>> I have been able to reproduce with the following CREATE TABLE statements.
>> Can
>> anyone explain why the column names appear to be causing a lexical error
>> and how
>> to avoid it? I am using 10.9.1.
>>
>
> \uFEFF is a Unicode "zero-width line break" character. Presumably you're
> cutting and pasting from somewhere; try pasting into a plain-text editor
> and then cut & paste from there to get rid of it?
>
> HTH,
> --
> John English
>

Re: CREATE TABLE lexical error

Posted by John English <jo...@gmail.com>.
On 19/04/2014 04:34, Patrick Meyer wrote:
> I have an application that allows users to import data into Derby. As such, the
> users specify the column names. A user encountered a lexical error message that
> I have been able to reproduce with the following CREATE TABLE statements. Can
> anyone explain why the column names appear to be causing a lexical error and how
> to avoid it? I am using 10.9.1.

\uFEFF is a Unicode "zero-width line break" character. Presumably you're cutting 
and pasting from somewhere; try pasting into a plain-text editor and then cut & 
paste from there to get rid of it?

HTH,
-- 
John English