Search jQuery

jQuery Forum

Using jQuery

kallencon..

Deferred SQLite execution?

in Using jQuery • 9 years ago

Hey, folks.

I'm trying to integrate the some of the new deferred functionality into some SQLite db async transactions.

So far, I've had them colliding all over the place if I don't nest-nest-nest. What I want to do is something like this:

function readDB(p_sSQL) {
var Dfrd = $.Deferred();
g_oDeviceDB.transaction(function(tx) {
tx.executeSql(p_sSQL, [],
Dfrd.resolve,
function(tx, error) {
alert('Transaction with the device database failed - ' + error.message + '\nOffending SQL:\n"' + theSQL + "'");
}
)
});
return Dfrd.promise();
}
$.when(readDB).then(function(retval) {alert('Deferred Firing. retval = ' + retval)});

What gets returned to the "then" function is the contents (source code) of the readDB function. From what I've read of deferred processing in jQuery, it seems tailor-made for this sort of issue, I just need the resultset to be returned when the readDB function is done processing. The whole concept of deferred processing is on the outer edge of my understanding, so I probably have some of the basic concept wrong.

The plan is to call the readDB() function repeatedly and use the results to populate various areas on the page.

Any help would be very much appreciated. Thanks in advance.

Scott.

Replies(4)

kallencon..

Re: Deferred SQLite execution?

9 years ago

OK. Here's what I came up with:

function readDB(p_sSQL) {
var dfrd = $.Deferred();
g_oDeviceDB.transaction(function(tx) {
tx.executeSql(p_sSQL, [],
function(tx,result) {
dfrd.resolve(result);
},
function(tx, error) {
alert('Transaction with the device database failed - ' + error.message + '\nOffending SQL:\n"' + p_sSQL + "'");
}
)
});
return dfrd.promise();
}

Which is being called by (among others):

function loadSummary() {
$.when(readDB("SELECT * FROM tbl_summary")).done(function(theResultSet) {
theSummaryData = theResultSet.rows.item(0);
$.each(theSummaryData, function(theKey, theValue) {
$("#summary_"+theKey).text(theValue);
});
} );
}

This particular table has only one row. On the page in question, the summary data IDs are all named identically to the column names in the database, prefixed by "summary_". I am loading some other data from other tables in the DB and mapping them to other elements as well. Before I implemented this, the results were varied but never entirely successful. Generally the only result set returned was which ever one was called last. Now all are working in harmony! Yea!

It's not as elegant a solution as I was looking for but it seems to work and I'm under deadline.

Scott.

jaubourg

Re: Deferred SQLite execution?

9 years ago

I would have done something a little more Object-Oriented:

// Generic DB Object
function DB( dbNativeObject ) {
   this._native = dbNativeObject;
}

// Associated methods
jQuery.extend( DB.prototype, {
  exec: function( sql ) {
  return jQuery.Deferred(function( defer ) {
  this._native.transaction(function( transaction ) {
  transaction.executeSql( sql, [],
  function( transaction, result ) {
  defer.resolveWith( transaction, [ result ] );
  },
  function( transaction, error ) {
  defer.rejectWith( transaction, [ error, sql ] );
  }
  )
  })
  }).promise();
  }
} );

// More specific subclass
function MyDB( dbNativeObject ) {
  DB.call( this, dbNativeObject );
}

// Extend methods
jQuery.extend( MyDB.prototype, DB.prototype, {
   loadSummary: function() {
  return this.exec( "SELECT * FROM tbl_summary" );
  }
} );

// Actual code
var myDB = new MyDB( g_oDeviceDB );

myDB.loadSummary().done(function( resultSet ) {
  data = resultSet.rows.item( 0 );
  jQuery.each( data, function( key, value ) {
  jQuery( "#summary_" + key ).text( value );
  });
});

Just add all you app-specific methods into the MyDB prototype in the extend and you get quite an elegant solution imo.

kallencon..

Re: Re: Deferred SQLite execution?

9 years ago

OK. So I met *THAT* deadline and now can spend some time revisiting the code. I much prefer your solution, so I've started trying to implement it. Now, I'm the first to admit that callbacks tend to make my old-school brain fuzzy so I see this as a learning opportunity.

That being said, I'm getting an error that I think is scope-related but I'm not sure what to do about it:

TypeError: Result of expression 'this._native' [undefined] is not an object.

From this line 4 below:
jQuery.extend( DB.prototype, {
   exec: function( sql ) {
   return jQuery.Deferred(function( defer ) {
   this._native.transaction(function( transaction ) { // <-- error being thrown here.
   transaction.executeSql( sql, [],
   function( transaction, result ) {
   defer.resolveWith( transaction, [ result ] );
   },
   function( transaction, error ) {
   defer.rejectWith( transaction, [ error, sql ] );
   }
   )
   })
   }).promise();
   }
} );

Somehow the device database object isn't making it into the structure correctly, or so it seems.

The device db object (g_oDeviceDB) is arrived at via this code:
var g_oDeviceDB;

$(document).ready(function() {
try {
if (!window.openDatabase) {
alert('This device does not work with this application.');
} else {
g_oDeviceDB = window.openDatabase('appdb', '1.0', 'App Database', 65536);
g_oDeviceDB.transaction( // Checks for existence of our database
function (tx) {
tx.executeSql('SELECT name FROM sqlite_master WHERE type="table" AND name="tbl_summary";', [],
function (tx, result) {
if (result.rows.length == 0) {
alert("There is a problem with the database. Please contact your local Representative.");
return false;
}
}
)
}
)
}
} catch(e) {
// Error handling code goes here.
if (e == 2) {
// Version number mismatch.
alert("Invalid database version.");
} else {
alert("Unknown error "+e+", please contact your local Representative.");
}
}
loadMember();
loadSummary();
loadInstructions();
});

I've inserted your code within the "loadSummary()" function as a way to avoid introducing new errors while testing out this approach.

the new loadSummary() is as follows:

var atx_db = new MyDB( g_oDeviceDB );
function loadSummary() {
atx_db.loadSummary().done(function( resultSet ) {
data = resultSet.rows.item( 0 );
jQuery.each( data, function( theKey, theValue ) {
jQuery( "#summary_" + theKey ).text( theValue );
});
});
}

Any light you can shed here would be very much appreciated.

Thanks!

Scott.

P.S. When I tried to copy and paste the code from your comment above, it wouldn't let me save my response. For some strange reason, I kept getting "responseContent exceeds the maximum". When I pulled the code out and inserted it into a standard text editor, then re-inserted it as code, I was able to save just fine. Interesting.