How can I pass text or strings using SQLite JDBC?

Question

I am having this problem using JDBC. I can create a table called food.db with text fields breakfast, lunch, dinner. When I call the following....

          statement.executeUpdate("create table food (breakfast string, lunch string, dinner string)");
          breakfast = JOptionPane.showInputDialog("What was your breakfast?");
            lunch = JOptionPane.showInputDialog("What was your lunch?");
            dinner = JOptionPane.showInputDialog("What was your dinner?");
         statement.executeUpdate("insert into food values(\'"+breakfast+"\', \'"+lunch+"\' ,\'"+dinner+"\')");

That last statement, however, results in an error. For whatever reason, it says that whatever I type in for "breakfast" (for example, oatmeal) is not a column, even though I know that I can use SQLite's syntax in this way to update columns.

Also I have checked the argument to executeUpdate(), and the syntax with single quotes and everything matches up...I have tried text and string column fields, get the same error for both.

Please check updated answer. You're escaping single quotes which isn't required. — Ravi K Thapliyal
– Ravi K Thapliyal, Commented May 10, 2013 at 15:06
Please use PreparedStatement with a parameterized query instead of concatenating user input into a query. You are currently open to SQL injection. — Mark Rotteveel
– Mark Rotteveel, Commented May 11, 2013 at 7:24

Nidhish Krishnan · Accepted Answer · 2013-05-10 14:50:42Z

2

Try this

change string to VARCHAR(SIZE) OR TEXT

 statement.executeUpdate("CREATE TABLE food (breakfast VARCHAR(25), lunch VARCHAR(25), dinner VARCHAR(25))");
 String breakfast = JOptionPane.showInputDialog("What was your breakfast?");
 String lunch = JOptionPane.showInputDialog("What was your lunch?");
 String dinner = JOptionPane.showInputDialog("What was your dinner?");
 statement.executeUpdate("INSERT INTO food (breakfast, lunch , dinner) VALUES ('"+breakfast+"', '"+lunch+"' ,'"+dinner+"')");

edited May 10, 2013 at 14:50

answered May 10, 2013 at 14:38

Nidhish Krishnan

20.8k6 gold badges67 silver badges78 bronze badges

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

4 Comments

john smith Over a year ago

Yes this worked, thank you! Is there any reason why I could not just skip the (breakfast,lunch,dinner) part after food?

Nidhish Krishnan Over a year ago

in your case there are only three field so it does'nt matter you put (breakfast,lunch,dinner) part after food ....you can simply write it as statement.executeUpdate("INSERT INTO food VALUES ('"+breakfast+"', '"+lunch+"' ,'"+dinner+"')");

john smith Over a year ago

Ok thats what I thought I was doing before, but either way, thanks!

Ravi K Thapliyal Over a year ago

@SaulBrodsky You were escaping ' with \ which wasn't required.

Ravi K Thapliyal · Accepted Answer · 2013-05-10 15:06:43Z

0

Change your CREATE TABLE to

statement.executeUpdate("create table food (breakfast text, lunch text, dinner text)");

Your INSERT INTO statement is escaping single ' quotes which isn't required. Change to

statement.executeUpdate("insert into food values('"+breakfast+"', '"+lunch+"' ,'"+dinner+"')");

There's no string data type in SQLite but it doesn't give an error because SQLite's SQL isn't strongly typed. The column affinity takes over and treats any unknown data type as NUMERIC. Better switch the column data type to TEXT or VARCHAR.

edited May 10, 2013 at 15:06

answered May 10, 2013 at 14:39

Ravi K Thapliyal

51.9k9 gold badges80 silver badges90 bronze badges

Collectives™ on Stack Overflow

How can I pass text or strings using SQLite JDBC?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related