sql - VB6 - Passing an Array as the ParamArray argument of a function -

sql - VB6 - Passing an Array as the ParamArray argument of a function -

we have utility method in vb6 com library executing parameterised sql:

public sub execsql(byval strsql string, paramarray varparams() variant) 'snip - adodb info access end sub

where varparams 2 dimensional array of sql parameter information. illustration usage be:

execsql("select * people name = ?", array("@p1", advarchar, 10, "smith"))

this tried , tested code , works fine in normal usage. in unusual situation sql string configurable value , contain number of parameters, need pass unknown number of arguments in paramarray. effort far (simplified) is:

function execconfigurablesql(sqlstring string, parametervalues() string) dim parameters() variant redim parameters(ubound(parametervalues)) variant = 0 ubound(parametervalues) parameters(i) = array("@p" + cstr(i), advarchar, 1000, parametervalues(i)) next execsql(sqlstring, parameters) 'type mismatch end function

the effort execute sql throws type mismatch error. there way pass array in function expects paramarray? or making altogether separate error somewhere?

this parameters dynamically built array:

, when passed comma-separated paramarray syntax (which works):

the construction looks same me.

first, never needed paramarray in execsql() in first place you're passing one argument on stack in add-on strsql, i.e. array of variants, is

array("@p1", advarchar, 10, "smith")

in sec listing. paramarray used pass undefined number or parameters on stack, i.e. able create calls like:

execsql "select * people name = ?", "@p1", advarchar execsql "select * people name = ?", "@p1", advarchar, 10, "smith" execsql "select * people name = ?", "@p1"

paramarray take arguments passed on stack , set them in array you.

so, have defined execsql() follow , have been same thing provided code adapted one-less array() layer around varparams:

public sub execsql(byval strsql string, varparams() variant) ' snip - adodb info access ' end sub

that beingness said:

currently, code in execconfigurablesql() transforms array of strings (i presume field names), format expected execsql(), except (outer) array can (and will) contain more 1 element of sort array("@p1", advarchar, 10, "smith").

can execsql() handle this? => problem #1

[by way, fields 1000-char long??]

problem #2: parameters fine when @ within execconfigurablesql(), 1 time pass if execsql(), paramarray wraps within array, end (once in execsql()) this:

now, have to set (unknown number of) parameters in array, 'cause, well, can't pass them on stack paramarray since don't know in advance number of them. cannot remove array() wrapping there.

you could rid of paramarray in execsql(), that break existing execsql() calls (for varparams wrapped 1 time in array() instead of twice).

knowing this, have 2 choices:

(1) maintain declares as-is, , have execconfigurablesql() create multiple execsql() calls within for loop (btw, declared sub presume doesn't homecoming value); e.g.

function execconfigurablesql(sqlstring string, parametervalues() string) = 0 ubound(parametervalues) phone call execsql(sqlstring, array("@p" + cstr(i), advarchar, 1000, parametervalues(i)) next end function

or

(2) other way around, improve logic & consistency

since can still play declaration & implementation of execconfigurablesql(), alter sec parameter expect array of array("@p1", advarchar, 10, "smith")-like members (and not field names). function execconfigurablesql(sqlstring string, varparamsarray() variant) dim varparams() variant = 0 ubound(varparamsarray) varparams = varparamsarray(i) ' snip - adodb info access ' next end function

take code within execsql() , set in execconfigurablesql() indicated - important: have update code business relationship fact parameters have 1 less array() wrapped around them.

for execsql(), remove paramarray keyword , treat method special case of execconfigurablesql() 1 fellow member provided, i.e.:

function execsql(sqlstring string, varparams() variant) phone call execconfigurablesql(sqlstring, array(varparams)) end function

sql arrays vb6 ado