[Kepler-Project] Simple SQL parametrization

Serge mindrats at list.ru
Sat Nov 17 14:26:32 GMT+3 2007


Hello.
If anyone interested here goes simple SQL parametrization:
I myself found it pretty handy :).
table sql_formats controls formatting, makes it easy to switch between
databases and types representation.

usage example:
print (PSql ("SELECT FROM T WHERE N = {param_Name} AND x = {test}", {
        param_Name = {"string_or_null", ""},
        test = true,
        }))

print (PSql ("SELECT FROM T WHERE N = {param_Name} AND x = {test}", {
        param_Name = {"string_or_null", "string with 'single quotes' in it"},
        test = {"money", 123},
        }))
results:
SELECT FROM T WHERE N = NULL AND x = 1
SELECT FROM T WHERE N = 'string with ''single quotes'' in it' AND x = 123.00

-----------------------------------------------

function sprintf(...)
        return string.format(unpack(arg))
end

sql_formats = {
        ["money"] = function (val)
                return sprintf ("%.2f", val)
        end,
        ["string"] = function (val)
                return "'"..string.gsub (val, "'", "''").."'"
        end,
        ["string_or_null"] = function (val)
                if string.len (val) == 0 then
                        return "NULL"
                end
                return sql_formats["string"] (val)
        end,
        ["int"] = function (val)
                return sprintf ("%.0f", val)
        end,
}
function PSql (query, params)
        s = string.gsub (query, "{([%w_]+)}",
                function (capture)
                        print (capture)
                        local p = params[capture]
                        if type (p) == "table" then
                                return sql_formats[p[1]] (p[2])
                        elseif type (p) == "number" then
                                return tostring (p)
                        elseif type (p) == "boolean" then
                                return p and "1" or "0"
                        end
                        return sql_formats["string"](tostring (p))
                end)
        return s
end




More information about the Kepler-Project mailing list