Modifying ruby code to work with Postgresql

Question

I was previously using sqlite3 for my ruby code and it worked fine for the following code

def existsCheck( db, id )
    temp = db.exec( 'SELECT 1 WHERE EXISTS(
        SELECT 1
        FROM Products
        WHERE promoID = ?
    ) ', [id] ).length > 0
end


def writeDB( db, product )
    db.exec( 'INSERT INTO Products ( promoID, name, price, shipping, condition, grade, included, not_included, image, time_added )
                        VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [product.promoID, product.name, product.price, product.shipping, product.condition, product.grade, product.included, product.notIncluded, product.image, product.time] )
end

Does Postgresql not support the idea of "?" or am I doing something wrong?

Don't write directly to the gem/driver for a database, instead use an ORM like [Sequel](http://sequel.rubyforge.org/). In particular, read through the [Cheat Sheet](http://sequel.rubyforge.org/rdoc/files/doc/cheat_sheet_rdoc.html). Sequel works extremely well with PostgreSQL; The author is a big fan of that DBM. The benefit to using an ORM is you don't have to write SQL specific for the database, you let the ORM do it. — the Tin Man, Jan 21 '13 at 07:23

score 0 · Accepted Answer · answered Jan 21 '13 at 01:52

0

From the fine PG::Connection manual:

- (PG::Result) exec(sql[, params, result_format ])
- (Object) exec(sql[, params, result_format ]) {|pg_result| ... }
[...]
PostgreSQL bind parameters are represented as $1, $1, $2, etc., inside the SQL query. The 0th element of the params array is bound to $1, the 1st element is bound to $2, etc. nil is treated as NULL.

So you want to use numbered placeholders with PostgreSQL:

def existsCheck( db, id )
    temp = db.exec( 'SELECT 1 WHERE EXISTS(
        SELECT 1
        FROM Products
        WHERE promoID = $1
    ) ', [id] ).to_a.length > 0
    # You'll also need to .to_a the result before you can
    # treat it as an array...
end

answered Jan 21 '13 at 01:52

mu is too short

426,620
70
833
800

Maybe `SELECT 1 FROM Products WHERE promoID = $1 LIMIT 1` will be better. Instead of the odd `SELECT 1 WHERE EXISTS ...` – Ihor Romanchenko Jan 21 '13 at 08:10
@Igor: I think it is a variant of the more common `select exists (select 1 from ...)` construct which, AFAIK, some databases have special optimizations for. – mu is too short Jan 21 '13 at 19:26
`SELECT EXIST (...)` and `SELECT 1 ... LIMIT 1` will take equal time in PostgreSQL. But `SELECT ... LIMIT 1` is way easier to write and understand. Yet `SELECT EXIST` may be relevant if the project is for multiple databases. – Ihor Romanchenko Jan 21 '13 at 20:05

Modifying ruby code to work with Postgresql

1 Answers1