Trouble inserting row into sqlite3 database with ruby library

Question

I am a total newbie. I have a database with a table called OUTPUTS where all columns are of type integer. I am trying to insert a row into OUTPUTS with the following ruby script:

require 'rubygems'
require 'sqlite3'
...
db=SQLite3::Database.new("development.sqlite3")
db.execute( "INSERT into OUTPUTS (user_id,eac,pac,vac,iac,epv,ppv,vpv) VALUES (10,@eac,@pac,@vac,@iac,@epv,@ppv,@vpv);" )

Upon running this script, I do get a new row and the user_id column has a 10 in it as expected, but the rest of the columns are empty even though I verified that all of the variables (@eac, @pac, etc) do indeed contain values. What is wrong with my syntax?

figured it out. still not exactly sure why the above DOESN'T work but this does: — pitachip
– pitachip, Commented May 26, 2011 at 17:23
st=db.prepare("INSERT into OUTPUTS (user_id,eac,pac,vac,iac,epv,ppv,vpv) VALUES (?,?,?,?,?,?,?,?)")\nst.execute(10,@eac,@pac,@vac,@iac,@epv,@ppv,@vpv) — pitachip
– pitachip, Commented May 26, 2011 at 17:25
See below, but basically you can't drop variables into a string and get them to render without wrapping them in #{} -- but your solution is better anyway since binding is almost always the way to go. — muffinista
– muffinista, Commented May 27, 2011 at 13:13

muffinista · Accepted Answer · 2011-05-26 17:21:54Z

2

You're sending the names of the variables to sqlite, not their values. This is what you want:

db.execute( "INSERT into OUTPUTS (user_id,eac,pac,vac,iac,epv,ppv,vpv)
VALUES (10,#{@eac},#{@pac},#{@vac},#{@iac},#{@epv},#{@ppv},#{@vpv});" )

But even better would be to use variable binding like this:

db.execute( "INSERT into OUTPUTS (user_id,eac,pac,vac,iac,epv,ppv,vpv) 
VALUES (10,?,?,?,?,?,?,?)",
@eac,@pac,@vac,@iac,@epv,@ppv,@vpv)

(I may have messed up my count there).

Check out How do I use placeholders in an SQL statement? for some more details.

answered May 26, 2011 at 17:21

muffinista

6,7562 gold badges32 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

Trouble inserting row into sqlite3 database with ruby library

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related