0

I am trying to add an object to the database like this:

DB.exec("INSERT INTO patients (name, birthdate) VALUES ('#{name}', '#{birthdate}');")

The problem is that if either of these values is nil, the data will be saved incorrectly or there will be a syntax error.

I can use if statements to send a different SQL statement depending on whether or not each value is nil, but this seems ugly and I'm wondering how to do this better.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
user3571908
  • 43
  • 5
  • What do you want to do if one or both values is `NULL`? Run the insert with placeholder values? Run a different statement entirely? No-op? – khampson Sep 14 '14 at 17:33

1 Answers1

0

Use prepare to build a prepared statement with placeholders:

DB.prepare('new_patient', 'insert into patients (name, birthdate) values ($1, $2)')

Note that this uses numbered parameters so

... bind parameters are represented as $1, $1, $2, etc., inside the SQL query.

rather than the positional ? parameters that you might be used to.

Then use exec_prepared execute the prepared statement and supply the parameter values then:

DB.exec_prepared('new_patient', [ name, birthdate ])

The interface to exec_prepared is a bit odd as it wants the parameters in an explicit array.

Now, if name.nil? then you'll get a NULL in the name column and if birthdate.nil? you'll get a NULL in the birthdate column.

If you ever find yourself writing #{...} inside an SQL statement, think again, there is almost always a better and safer way.

mu is too short
  • 426,620
  • 70
  • 833
  • 800