Derby Embedded database strange behaviour, SQLSyntaxErrorException

Question

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
);

I can‘t. I created the database with IntelliJ‘s integrated database tool — user5406389
– user5406389, Commented Apr 28, 2022 at 20:19
then the script with which you created it, but the database, should be accessible other wise — nbk
– nbk, Commented Apr 28, 2022 at 20:23
Ah yeah, I found that script, I'll edit my question, since I can't post code here — user5406389
– user5406389, Commented Apr 28, 2022 at 22:11
Do you create the table with the executeUpdate() function? — Zeuzif
– Zeuzif, Commented Apr 28, 2022 at 22:24

Zeuzif · Accepted Answer · 2022-04-28 22:42:28Z

1

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

answered Apr 28, 2022 at 22:42

Zeuzif

3174 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5406389 Over a year ago

Thats it, fixed my problem. Didn't know there was a difference between '' and ""

Collectives™ on Stack Overflow

Derby Embedded database strange behaviour, SQLSyntaxErrorException

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related