heyred heyred - 4 months ago 54
AngularJS Question

Inserting into SQLite database with parameters

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 + '" )');
}

Answer

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]);
  });
} 
Comments