Inserting into SQLite database with parameters

Question

I am building a cross-platform app using AngularJS, Monaca and OnsenUI.

I have implemented a SQLite database to save data to be able to work offline. My implementation is based on the answer found HERE

I have a view where the user can select any number of options and those option values should then be saved to the SQLite database. Each option value can be saved to the same database table or separate tables - depending on the option value selected.

With this I am trying to refactor my insert statement to make it more efficient as it may be called many times. In my app.js controller I have a function that checks which option value was selected in the view, and then calls the goInsert() function that inserts the selected option value into the SQLite Database. Below is a sample of my function.

$scope.selectedIDOne = ""; // Variable to hold selected options value

$scope.changedValue = function (item, identifier) // item = selected option value; identifier = identifies table name to insert into
{
    switch (identifier)
    {
        case "Square":
            $scope.selectedIDOne = item; 
            db.transaction(goInsert(identifier), errorCB, successCB); // Error on refactored goInsert(identifier) function trying to pass identifier
            break;

            default:
                // TODO
    }   
}

Then I try the following in my goInsert() function.

function goInsert(identifier) // Error here
{
    switch (identifier)
    {
        case "Square":
            db.transaction(insertSquareDB, errorCB, successCB);
            break;
    }
}

function insertSquareDB(tx) 
{
    tx.executeSql('INSERT OR IGNORE INTO tb_square (square_id) VALUES ("' + $scope.selectedIDOne + '" )');
}

When I run the code I get an error where indicated but the value is nonetheless inserted int the database. The error is thrown at the goInsert(identifier) function call. The error is:

TypeError: Failed to execute 'Transaction' on 'Database'. The callback provided as parameter 1 is not a function.

How can I implement this solution please? Or is there a better way? I would also ideally not like to create multiple insertSquareDB(tx) functions e.g. insertCircleDB(tx), insertROundDB(tx) etc. Is there a way I can have 1 function defined that inserts values dynamically e.g. (hypothetical)

function insertSquareDB(tx, tableName, columnName, optionValues) 
{
    tx.executeSql('INSERT OR IGNORE INTO tableName (columnName) VALUES ("' + optionValues + '" )');
}

score 1 · Accepted Answer · 2016-07-12 22:53:33Z

1

You don't need to wrap your goInsert(identifier) call inside a transaction:

switch (identifier)
{
  case "Square":
    $scope.selectedIDOne = item; 
    goInsert(identifier);
    ...
}

If you want to be able to call one function to insert any shape into the database, your best bet is to dynamically generate the SQL statement:

function insertShapeDB(shape, value) 
{
  var tableName = '';
  var columnName = '';
  if (shape === 'Square') {
    tableName = 'tb_square';
    columnName = 'square_id';
  }
  else if (shape === 'Circle') {
    tableName = 'tb_circle';
    columnName = 'circle_id';
  }
  else if (shape === 'Round') {
    tableName = 'tb_round';
    columnName = 'round_id';
  }
  var sql = 'INSERT OR IGNORE INTO ' + tableName + ' (' + columnName + ') VALUES (?)';
  db.transaction(function(tx) {
      tx.executeSql(sql, [value]);
  });
}

edited Jul 12, 2016 at 22:53

answered Jul 12, 2016 at 22:13

user6470655

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

2 Comments

heyred Over a year ago

Excellent, thanks. When I run my insertShapeDB(tx, shape, value) function I get error tx.executeSql is not a function. I call the method like insertShapeDB(identifier, 50);

user6470655 Over a year ago

See updated answer. You would call the insertShapeDB inside your goInsert method. And based on the identifier (square, circle, whatever), you would call insertShapeDB('square', 50) or insertShapeDB('circle', 50). Hope that helps.

Collectives™ on Stack Overflow

Inserting into SQLite database with parameters

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related