Re: when I insert table, value is a string contains apostrophe - it throws an error there - how to fix

[Petite Abeille <petite.abeille@...>]

On Feb 3, 2010, at 4:36 PM, Joshua Jensen wrote:

> I've always found it bothersome that LuaSQL doesn't support prepared statements.  That has forced me to find alternatives.

True, but even without proper prepared statements support, you can get by with LuaSQL binding for 'escape':

http://github.com/keplerproject/luasql/blob/master/src/ls_firebird.c#L563
http://github.com/keplerproject/luasql/blob/master/src/ls_mysql.c#L351
http://github.com/keplerproject/luasql/blob/master/src/ls_postgres.c#L373
http://github.com/keplerproject/luasql/blob/master/src/ls_sqlite3.c#L319

FWIW, here is an usage example, using DB.lua [1]:

local DB = require( 'DB' )

aDB( 'create table contact( name varchar( 32 ) primary key, email varchar( 32 ), phone number )' )

local someContacts = {
    { name = 'Jose das Couves', email = 'jose@couves.com' },
    { name = 'Manoel Joaquim', email = 'manoel.joaquim@cafundo.com' },
    { name = 'Maria das Dores', email = 'maria@dores.com' },
    { name = nil, email = 'foo@bar.com' },
    { name = nil, email = nil },
    { name = 'Foo', email = nil }
}

for _, aContact in ipairs( someContacts ) do
    aDB( 'insert into contact( name, email ) values( %s, %s )', aContact.name, aContact.email )
end

Each argument is transparently escaped using the appropriate 'escape' function provided by the connection:

http://dev.alt.textdrive.com/browser/HTTP/DB.lua#L133

[1] http://dev.alt.textdrive.com/browser/HTTP/DB.lua