Shorten Dynamic SQL Query For Loop

Question

I have a function that builds an sql query and then inserts it into the client-side light sql.

I think im using to many for loops, how would i make this shorter?

function insert(dataBase,table,row){
    var j = 0;

    var sqlQueryArray = [];

    sqlQueryArray[j++] = 'INSERT INTO ';
    sqlQueryArray[j++] = table
    sqlQueryArray[j++] = ' ('

    for (var i = row.length - 2; i >= 0; i--){
        sqlQueryArray[j++] = row[i].id + ',';
    };

    sqlQueryArray[j++] = row[(row.length - 1)].id + '';
    sqlQueryArray[j++] = ')'
    var sqlQueryString = ' VALUES ';

    for (var i = row.length - 2; i >= 0; i--){
        sqlQueryArray[j++] = '?, ';
    };

    sqlQueryArray[j++] = '?';
    sqlQueryArray[j++] = ');'

    for (var i = 0; i < sqlQueryArray.length; i++){
        sqlQueryString += sqlQueryArray[i];
    }
    var rowArray = []
    for (var i = row.length - 1; i >= 0; i--){
        rowArray[i] = row[i].val;
    };

    dataBase.openDatabase.transaction(
        function (transaction) {
            transaction.executeSql(sqlQueryString,
                rowArray,
                dataBase.nullSQLHandler, dataBase.QueryError);
        }
    );
}

harto · Accepted Answer · 2010-11-30 03:14:42Z

1

Assuming you're using a relatively recent browser, you could do something like this:

var insertStatement =
   'INSERT INTO ' + table +
   ' (' + rows.map(function (row) { return row.id; }).join(', ') + ')' +
   ' VALUES (' + rows.map(function () { return '?'; }).join(', ') + ')';

Whether you should or not is left as an exercise for the reader :)

Edit: I just noticed in a comment to another answer that you are sending this to the server for execution. You should definitely not do that, since a user could easily submit their own SQL and wreak all kinds of havoc on your database. You shouldn't trust any data received over the network.

edited Nov 30, 2010 at 3:14

answered Nov 30, 2010 at 3:04

harto

90.6k9 gold badges49 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim P. Over a year ago

Most of the info that is going into the client sql is from the server over an ssl socket. Im trying to make it very automated plus the client would be a mobile phone. I think sending most of the grunt work to the server might take a load of the client.

Bob Palmer · Accepted Answer · 2010-11-30 02:58:28Z

1

Whenver Dynamic SQL is created in an application, things are unleashed and children are eaten.

You should really use a parameterized SP for this one for a multitude of security and performance reasons (not to mention readability/maintainability).

answered Nov 30, 2010 at 2:58

Bob Palmer

4,7722 gold badges30 silver badges31 bronze badges

3 Comments

Tim P. Over a year ago

Your right I might more the query building to the server. It would make the sql more asynchronous.

harto Over a year ago

Oh... you're sending the query to the server for execution?

Tim P. Over a year ago

Right now Im doing it on the client side, but the server is a node.js server. So i might just send (table,row) and get a string with a value array.

Camilo Díaz Repka · Accepted Answer · 2010-11-30 03:03:37Z

0

First of all, I think you'd be better not tracking the index manually (j++). Use sqlQueryArray.push() to insert the elements at the end of sqlQueryArray.

Then, to simplify your code, you can create a function that returns you a custom SQL passing your row variable as a parameter, with that you can generate the INSERT INTO clause with all the ? markers.

answered Nov 30, 2010 at 3:03

Camilo Díaz Repka

4,8256 gold badges46 silver badges69 bronze badges

Collectives™ on Stack Overflow

Shorten Dynamic SQL Query For Loop

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related