Sqlite: how to check if db is empty and how to use query result inside condition

[Gabriele Cozzolino]

I have some trouble understanding how function are used related to sql, I made a function (starting from a similar one) like this to know if db has records 

function DbIsEmpty( results )   

{  

    

    var len = results.rows.length;  

    console.log( len );

    

    if(len==0) PopulateDB();

    else return false;

}  

So if it has no record it calls PopulateDB function, if has records return false.

Then, my problem is that I'd like better that I can use the function result in an if condition, but right now I'm using it like this 

db.executeSql("SELECT * FROM Table;", [], DbIsEmpty);

So I don't know how can I use it in a condition. My enquiry is valid in this particular case but I wish to be able to create other functions usable inside the code like conditions, loops, etc.

Any help or documentation link will be much appreciated, thank you

[Chris Hopkin]

Hi Gabriele

If you have any more specific examples of what you would like to do, let us know so we can help you work out how to write the executeSql result callbacks.

The functions you pass to db.executeSql will just be called with the results of the query, anything you return from these functions will be ignored.

I would suggest breaking down your code a bit more. Your DbIsEmpty function should simply return true or false (and not be responsible for populating the database itself):

function DbIsEmpty( results )  
{    
    var len = results.rows.length;  
    console.log( len );

   
    return len == 0;
}

Add a new function that calls DbIsEmpty, and populates the database if it is empty:

function InitDB( results )
{
    If( DbIsEmpty( results ) )
    {
        PopulateDB();
    }
}

function PopulateDB()
{
...
}

Then pass the new function to db.executeSql instead:

db.executeSql("SELECT * FROM Table;", [], InitDB);

(You could of course drop the DbIsEmpty function and just check if results.rows.length == 0 directly if you wish).

Your DbIsEmpty function could then be re-used in other contexts if required, without causing the database to be populated when empty.

Hope that helps.

Thanks

Chris

[Gabriele Cozzolino]

Thank you for answering,
I'm pretty new to javascript but not to coding, so I understand general references but I'm having some trouble to understand specific matters.
What I meant is that I'd like to execute a query and use the result separately, something like (in code-like language :-P)

function myfunction()
{
query=select this from that where something = whatever
if query.result = something then
do this
else
do that
}

But right now I can use query result only in function nested inside the query itself, like in

  

db.executeSql("SELECT * FROM Table;", [], InitDB);

So, there's a way to do not include the InitDB part (or whatever function) and get somehow the query result? I have the feeling that managing the query like this is a bit restrictive.
For example, if what I ask is not doable, how can I pass other parameters to the InitDB? How can I get a result out of InitDB and use that inside of a condition?

[Chris Hopkin]

Hi Gabriele

The SQL queries are executed asynchronously, which is why we need to pass a callback to executeSql to receive the results, rather than simply returning the result from executeSql. The callback will be called at some point in the future with the results of the query, and this is the only way to get the results.

This means you need to handle the result in the result callback, and not the function you are executing the query from.

So something like your pseudo-code example....

function myfunction()
{
  result = db.executeSql("SELECT * FROM Table;", []);

  if result = something then

    do this
  else
    do that
}

... would need to be handled like this instead:

function dbquery()
{
  ...
  db.executeSql("SELECT * FROM Table;", [], myfunction);
  ...
}

function myfunction(result)
{
  if result = something then

    do this
  else
    do that
}

Thanks

Chris

[salvatore fusto]

If anyone is interested to callbacks and async js, imho this is a good, basic intro to the argument, even if referred to node,js.

[Gabriele Cozzolino]

Salvatore, link is missing :-)
Chris, so the answer is no, it's not possible handling querys differently. Such a pity, in this way if I want populate different list starting from different querys then I need to create a specific callback function for each query... because also i can't handle parameters in the callback function, so I can't say to it what to do and create a condition that execute different code starting from the input parameter.

[Warren Downs]

You should be able to use an anonymous inner callback, which also has access to variables in the calling function, so it can make the kind of decisions you describe:

function dbquery()
{
  ...
  var query1="SELECT * FROM Table;";
  var query2="SELECT * FROM Table2;";
  if condition then query=query1 else query=query2  // Pseudocode
  db.executeSql(query, [], function(result) {
    // Pseudocode
    if condition then

      if result = something then

        do this
      else
        do that
    else

      if result = something then

        do thisOther
      else
        do thatOther  

  });
  ...
}

[Gabriele Cozzolino]

Brilliant! This solves a part of the problem, then, if I understood correctly, I can use the "result" outside the callback function, right?
Because you wrote

db.executeSql(query, [], function(result) {
// Pseudocode
    if condition then

      if result = something then

But I tought that result could only be used inside the "function" function, if is this the case, then I can do what I want :-)

[salvatore fusto]

Excuse me please: the link is http://www.sitepoint.com/a-beginner-splurge-in-node-js/ . refer to the first part.

paisà, mi dispiace assai!!

[salvatore fusto]

The problem in callback is that results can be used in the callback itself: you cannot do actions between function calling the  callback and the callback for the results( ie testing errors etc); the evolution is async then unpredictable, so promises have been implemented, but i don't love them. 

remember that the callback operation is pushed in the stack of the callback instance, not in the stack of the calling outer function.