From: dmcnulty@... (Dan McNulty)
Date: 2002-04-11T01:33:34+09:00
Subject: Advice for best approach?

I am writing a relatively simple app that include Ruby::DBI access to
MySQL.  Currently, I have a file (autocert.sql) that contains all my
SQL statements like this:

insert_user_sql     = "INSERT INTO users 
                    (ntid, fname, lname, email, ou) 
                    VALUES
                    (\"#{somecert.ntid}\", \"#{somecert.fname}\", 
                     \"#{somecert.lname}\", 
                     \"#{somecert.email}\", \"#{somecert.ou}\");"

Then I have a Ruby program contained in a file (*.rb) and a file that
contains all my functions (*.rbh).  The .rb file "load"s the .rbh file
and I just call functions out of there, that works fine.  Here's an
example of a function that loads a new user into MySQL (it users the
SQL statement from above):

def addusertodb(somecert)
    load "autocert.sql"
    tmp = doqry(insert_user_sql)
    return tmp
end

"somecert" is an object with the appropriate members required by the
SQL statement.

When I load the file containing the SQL statements, Ruby complains
about an undefined local variable "somecert", which I kinda expected. 
Obviously I have scope issue.

My question is:  how should I organize this program so I can:
a) keep my sql statements in a single, separate file
b) use my sql statements and substitute values into them
c) not write an explicit function that passes the values into the
function scope and passes the completed SQL statement back (too much
work, but I know this will work)

Thanks for any suggestions.

-- Dan