help Using an unknown number a variables in a function

[SheriFF]

I tried to make a function which executes a dbExec like this

DATABASE_CONNECTION = dbConnect( "sqlite", "uhr.db" )

function db_executeCommand( COMMAND, ... )
	local VARS = table.concat({...}, ",") 
	local DB_EXEC 
	
	if ( VARS ) then 
		DB_EXEC = dbExec( DATABASE_CONNECTION, COMMAND, VARS )
	else 
		DB_EXEC = dbExec( DATABASE_CONNECTION, COMMAND )
	end 
	
	if not ( DB_EXEC ) then return false end 
	
	return true 
end 

The problem is that only the first column is filled with all the variables

For example : 

db_executeCommand( "INSERT INTO accounts( user, pass, word, staff, don, creatorSerial, saved, onSerial ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?)", user, pass, word, "Member", "false", serial, "false", "" )
--user column : all variables
--pass column empty
--word column empty
-- etc...

 

[Simple0x47]

You've got to handle each variable into the dbExec calling. So you could think of something like this making a function for each SQL Action:

local VARS = table.concat({...}, ",")

local SQL_ACTION = "INSERT INTO table ( 'key', 'key', 'key' ) VALUES ( "
local SQL_VALUES = ""
for value in string.gmatch(str, '([^,]+)') do
  SQL_VALUES = SQL_VALUES .. " '" .. value .. "',"
end

SQL_VALUES:sub( 1, -2 )

SQL_ACTION = SQL_ACTION .. SQL_VALUES .. ")"

dbExec( SQL_ACTION )

-> It's just an example it should work if it's right used.

[SheriFF]

I want this function to work with all dbExec commands ( INSERT, UPDATE etc... ) so i tried something like this 

DATABASE_CONNECTION = dbConnect( "sqlite", "uhr.db" )

function db_executeCommand( COMMAND, ... )
	local VARS_TABLE = { ... }
	local VARS_STRING = ""
	local VARS 
	local DB_EXEC 
	
	for _, VAR in pairs( VARS_TABLE ) do 
		if ( type( VAR ) == "string" ) then 
			VARS_STRING = VARS_STRING..", ".."\""..VAR.."\""
		else 
			VARS_STRING = VARS_STRING..","..tostring( VAR )
		end 
	end 

	if ( VARS_STRING and VARS_STRING ~= "" ) then 
		VARS = VARS_STRING:gsub( ",", "", 1 )
		DB_EXEC = dbExec( DATABASE_CONNECTION, COMMAND, loadstring( VARS ) )
	else 
		DB_EXEC = dbQuery( DATABASE_CONNECTION, COMMAND )
	end 
	if not ( DB_EXEC ) then return false end 
	
	return true 
end 

but when i use, for example

db_executeCommand( "INSERT INTO accounts( user,pass ) VALUES ( ?, ? )", theUser, thePass )

the value in the first column is nill and, in the second column are all the values stored

Edited April 22, 2017 by *BeaT*

[SheriFF]

I tried to change some little things in this function and, by using assert, i found out that there are errors

CODE:

DATABASE_CONNECTION = dbConnect( "sqlite", "uhr.db" )

function db_executeCommand( COMMAND, ... )
	local VARS_TABLE = { ... }
	local VARS_STRING = ""
	local VARS 
	local DB_EXEC 
	
	for INDEX, VAR in pairs( VARS_TABLE ) do 
		VARS_STRING = VARS_STRING..",".."VARS_TABLE["..tostring( INDEX ).."]"
	end 
	outputDebugString( "VARS_STRING : "..VARS_STRING:gsub( ",", "", 1 ) )

	if ( VARS_STRING and VARS_STRING ~= "" ) then 
		VARS = VARS_STRING:gsub( ",", "", 1 )
		DB_EXEC = dbExec( DATABASE_CONNECTION, COMMAND, assert( loadstring( VARS ) )() )
	else 
		DB_EXEC = dbQuery( DATABASE_CONNECTION, COMMAND )
	end 
	if not ( DB_EXEC ) then return false end 
	
	return true 
end

Function test

db_executeCommand( "INSERT INTO accounts( user, pass ) VALUES ( ?, ? )", "test", "test" )
--Output in debug panel : [string "VARS_TABLE[1],VARS_TABLE[2]"]:1:expected '=' near '<eof>'

 

Edited April 22, 2017 by *BeaT*
Sorry for the double post

[undefined]

DATABASE_CONNECTION = dbConnect( "sqlite", "uhr.db" )
function db_executeCommand( COMMAND, ... )
	local ARGUEMENTS = { ... }
	local VARS = {DATABASE_CONNECTION, COMMAND}
	local DB_EXEC 
	
	if #ARGUEMENTS == 0 then
		DB_EXEC = dbQuery(unpack(VARS))
		return DB_EXEC
	end
	
	for i, VAR in ipairs( ARGUEMENTS ) do
		local TYPE_COUNT = VAR:find("type=")
		local TYPE = VAR:sub(TYPE_COUNT, #VAR)
		TYPE = TYPE:gsub("type=")
		local VAR = VAR:sub(1, TYPE_COUNT - 1)
		VAR = TYPE == "INT" and tonumber (VAR) or TYPE == "loadstring" and loadstring(VAR)() or TYPE == "TEXT" and VAR
		table.insert(VARS, VAR)
	end 
	
	DB_EXEC = dbExec(unpack(VARS))
	return DB_EXEC
end

 

db_executeCommand( "INSERT INTO accounts( column1, column2, column3 ) VALUES ( ?, ?, ?)", "value1 type=INT", "value2 type=TEXT", "value3 type=loadstring" )

 

Try it.

[SheriFF]

Fixed, fully functional code( thanks @ZoRRoM for the idea ) here

function db_executeCommand( COMMAND, ... )
	local VARS_TABLE = { ... }
	local DB_EXEC 

	if ( VARS_TABLE and #VARS_TABLE > 0 ) then 
		DB_EXEC = dbExec( DATABASE_CONNECTION, COMMAND, unpack( VARS_TABLE ) )
	else 
		DB_EXEC = dbQuery( DATABASE_CONNECTION, COMMAND )
	end 
	if not ( DB_EXEC ) then return false end 
	
	return true 
end