Parameter binding not working.

[joshuapinter]

Hi there,

Love the gem. Running into a strange parameter binding issue I was hoping I could get some insight on.

Works

db.query_single( "SELECT * FROM `dbo_CYLINDER` WHERE CYLINDER_ID = '#{ 11202 }'" )
#=> { CYLINDER_ID: "11202", ... }

Does Not Work

db.query_single( "SELECT * FROM `dbo_CYLINDER` WHERE CYLINDER_ID = '?'", 11202 )
#=> nil
db.query_single( "SELECT * FROM `dbo_CYLINDER` WHERE CYLINDER_ID = ':id'", id: 11202 )
#=> nil

What am I missing here with parameter binding that the above queries would be different than the String interpolation version?

I tried using db.trace{ |sql| puts sql } to find out what's happening but it doesn't show the final query with the values replaced.

Any idea?

[noteflakes]

Running #trace you won't be able to see the "final query" as you call it, since the parameter binding is done at the level of SQLite.

If you're looking to bind a string parameter, you need to get rid of the single quotes in the query. Otherwise SQLite won't bind the parameter:

db.query_single( "SELECT * FROM `dbo_CYLINDER` WHERE CYLINDER_ID = ?", "11202" )

[joshuapinter]

@noteflakes That's the one! Thanks for that.

As you suggested, I removed the single quotes from the query String and then I had to convert my Integer to a String for it to work. In your example, you wrapped it in double quotes but it's actually a variable in my use case so I just called .to_s on it.

db.query_single( "SELECT * FROM `dbo_CYLINDER` WHERE CYLINDER_ID = ?", 11202 )
#=> nil

db.query_single( "SELECT * FROM `dbo_CYLINDER` WHERE CYLINDER_ID = ?", 11202.to_s )
#=> { CYLINDER_ID: "11202", ... }

Thanks for the clarity around that and the super quick response. 🙏