Search code examples
javasqlderbyembedding

Derby Embedded database strange behaviour, SQLSyntaxErrorException

I've got an embedded database in a Java project with a table TOOLS_IMAGES with two columns "TOOL_id" (int) and "TOOL_image" (blob). I try to get the blob with following code:

rs = stmt.executeQuery("SELECT * FROM 'TOOLS_IMAGES' WHERE 'TOOL_id' = " + id);

The program trys to get like 15 different images at this way one after another. When I run the program it sometimes manages to get one image, sometimes two, sometimes even six, but at one point it always throws following exception. And after that one fail, it throws this exception for every following image:

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "\'TOOLS_IMAGES\'" at line 1, column 15.
Caused by: ERROR 42X01: Syntax error: Encountered "\'TOOLS_IMAGES\'" at line 1, column 15.

Why do I use apostophes in the SQL query? I already tried it without them, but here sql converts all my lowercases in the query to uppercases and complains afterwars that it can't find that column -.- Looks like this:

rs = stmt.executeQuery("SELECT * FROM TOOLS_IMAGES WHERE TOOL_id = " + id);

and

java.sql.SQLSyntaxErrorException: Column 'TOOL_ID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'TOOL_ID' is not a column in the target table.
Caused by: ERROR 42X04: Column 'TOOL_ID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'TOOL_ID' is not a column in the target table.

Edit: Here's the script, which I used to create the table

create table TOOLS_IMAGES
(
    "TOOL_id"   INTEGER not null
        constraint TOOLS_IMAGES_PK
            primary key,
    "TOOL_mask" BLOB,
    "TOOL_img"  BLOB
);
Solution

  • Try to use:

    ResultSet rs = stm.executeQuery("SELECT * FROM TOOLS_IMAGES WHERE \"TOOL_id\" = " + 1);

    If you put:

    rs = stmt.executeQuery("SELECT * FROM TOOLS_IMAGES WHERE TOOL_id = " + id);

    It will be processed by Derby like :

    rs = stmt.executeQuery("SELECT * FROM TOOLS_IMAGES WHERE TOOL_ID = " + id);

    which are not the same

    For more info visit