1
\$\begingroup\$

I want to insert parameters dynamically to statements at sqlite. I now write as follows:

            tx.executeSql('SELECT Data from Table Where something = "'+ anyvariable+ '"',[],successFn, errorCB);

But I guess there is a better (cleaner) method to do it.. Any ideas?

\$\endgroup\$

migrated from stackoverflow.com Jul 12 '12 at 17:19

This question came from our site for professional and enthusiast programmers.

  • \$\begingroup\$ No I said I want a better method. the one with "?", \$\endgroup\$ – Sana Joseph Jul 12 '12 at 15:58
  • \$\begingroup\$ Why in the world would you be using js to execute SQL statements... use prepared statements on the server to ensure proper sanitization. \$\endgroup\$ – rlemon Jul 12 '12 at 17:31
  • \$\begingroup\$ @rlemon: It might be WebSQL or server-side JS which is just wrongfully tagged with jquery \$\endgroup\$ – ThiefMaster Jul 12 '12 at 17:34
1
\$\begingroup\$

I would create an object to model my dynamic parameters using the following interfaces:

{ //reduced format
    clause: string,
    params: an array of parameters if more than one is used
}
{ //simple entry format
    clause: string
    param: the parameter if only one is used
}
{ //or format
    or: an array of dynamic parameter interfaces to be joined as part of an or clause
}
{ //and format
    and: an array of dynamic parameter interfaces to be joined as part of an and clause
}

From here I could use the following functions:

function reduce(param) {
    var clause = [], params = [], nest, nestType;
    if (param.clause !== undefined && param.params) {
        //already in reduced format 
        return param;
    }
    if (param.clause !== undefined && param.param !== undefined) {
        //convert simple format to reduced
        return { clause: param.clause, params: [param.param] };
    }
    if (param.clause) {
        //special case (clause without additional parameter)
        //parameters without clause would be done by using empty string
        return { clause: param.clause, params: [] };
    }
    //convert nested forms (and and or)
    if (param.and) {
        nest = param.and;
        nestType = ' AND ';
    } else if (param.or) {
        nest = param.or;
        nestType = ' OR ';
    } else {
        throw new Error('Invalid dynamic parameter found');
    }
    nest.forEach(function (p) {
        p = reduce(p);
        clause.push(p.clause);
        params.push.apply(params, p.params);
    });
    return {
        clause: '(' + clause.join(nestType) + ')',
        params: params
    };
}

function executeDynamicSql(tx, base, dynamicparameter, onSuccess, onError) {
    var reduction = reduce(dynamicparameter);
    tx.executeSql(base + reduction.clause, reduction.params, onSuccess, onError);
}

And call it (in your test case) like so:

executeDynamicSql(tx, 'SELECT Data from Table Where ', {
    clause: 'something = ?',
    param: anyvariable
}, successFn, errorCB);

What is nice about this function is that it can handle a far more complex where clause such as:

var dynamicwhere = {
        or: [
            {clause: 'something = ?', param: somethingid},
            {clause: 'somethingelse = ?', param: somethingelse},
            {clause: 'somedate between ? and ?', params: [d1, d2]},
            {
                and: [
                    {clause: 'amt > ?', param: min},
                    {clause: 'amt < ?', param: max},
                ]
            }
        ]
    };

executeDynamicSql(tx, 'SELECT Data from Table Where ', dynamicwhere, successFn, errorCB);

Ultimately executing:

SELECT Data from Table Where (something = ?
                           or somethingelse = ?
                           or somedate between ? and ?
                           or (amt > ? and amt < ?))

With the in order parameter list:

[somethingid, somethingelse, d1, d2, min, max]

You could do something more fancy using a regex replace or whatever, but I don't think it should be necessary.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy