Results 1 to 7 of 7
  1. spdqbr's Avatar
    Posts
    78 Posts
    Global Posts
    80 Global Posts
       #1  
    I'm making a few updates to PreCT and have run into a bit of a snag. Anyone's input is appreciated.

    I'm trying to add persistent times by use of a simple database:

    Code:
    CREATE TABLE IF NOT EXISTS 'SOLVES' (DATE_ENTERED TIMESTAMP PRIMARY KEY,
    		                     SESSION_ID INT NOT NULL,
    				     PUZZLE_TYPE VARCHAR NOT NULL,
    				     SCRAMBLE VARCHAR,
    				     SOLVE_TIME_MILLIS INT NOT NULL,
    				     COMMENTS VARCHAR)
    The database creates just fine with the above SQL. The problem comes when I try to insert into it:

    Code:
    PreCTAssistant.prototype.addTime = function(){
    	var db = openDatabase(this.dbName, this.version);
    	
    	Mojo.Log.info("Inserting values %s %s %s %s %s null",new Date().getTime(), this.sessionId, this.puzzle, this.currentScramble, this.currentTime);
    		
    	var sql = "INSERT INTO 'SOLVES' (DATE_ENTERED, SESSION_ID, PUZZLE_TYPE, SCRAMBLE, SOLVE_TIME_MILLIS, COMMENTS) VALUES (?, ?, ?, ?, ?, ?)";
    	Mojo.Log.info(sql);
    	
    	db.transaction( function (transaction) {
    	  transaction.executeSql(sql,  [new Date().getTime(), this.sessionId, this.puzzle, this.currentScramble, this.currentTime ,null], 
    	                         function(transaction, results) {    // success handler
    	                           Mojo.Log.info("Successfully inserted record"); 
    	                         },
    	                         function(transaction, error) {      // error handler
    	                           Mojo.Log.error("Could not insert record: " + error.message);
    	                         }
    	  );
    	});
    
    }
    I get the log statements I would expect:
    Code:
    [20100215-20:34:53.615056] info: Inserting values 1266291293614 1266291278871 3x3x3 R2 D' L R B D' B2 L' F D2 B2 R U B F2 R2 D' R' B2 R' D' U B' D' U L' R2 B R2 D2 R D' R2 D R2 940 null
    [20100215-20:34:53.615760] info: INSERT INTO 'SOLVES' (DATE_ENTERED, SESSION_ID, PUZZLE_TYPE, SCRAMBLE, SOLVE_TIME_MILLIS, COMMENTS) VALUES (?, ?, ?, ?, ?, ?)
    [20100215-20:34:53.644520] info: Successfully inserted record
    But the values in the database do not appear to be correct (I copied the db file via usb to my machine and used sqlite3 to poke around at it):
    Code:
    sqlite> select * from SOLVES;
    1266290534187|undefined|undefined|undefined|undefined|
    1266290536508|undefined|undefined|undefined|undefined|
    1266290538124|undefined|undefined|undefined|undefined|
    1266290571254|undefined|undefined|undefined|undefined|
    1266290581094|undefined|undefined|undefined|undefined|
    1266291293614|undefined|undefined|undefined|undefined|
    I am, however, able to insert the logged data without a problem if I do it via sqlite3:
    Code:
    sqlite> INSERT INTO 'SOLVES' (DATE_ENTERED, SESSION_ID, PUZZLE_TYPE, SCRAMBLE, SOLVE_TIME_MILLIS, COMMENTS) VALUES (1266291293616, 1266291278871, '3x3x3', 'R2 D'' L R B D'' B2 L'' F D2 B2 R U B F2 R2 D'' R'' B2 R'' D'' U B'' D'' U L'' R2 B R2 D2 R D'' R2 D R2', 940, null);
    sqlite> 
    sqlite> select * from SOLVES;
    1266290534187|undefined|undefined|undefined|undefined|
    1266290536508|undefined|undefined|undefined|undefined|
    1266290538124|undefined|undefined|undefined|undefined|
    1266290571254|undefined|undefined|undefined|undefined|
    1266290581094|undefined|undefined|undefined|undefined|
    1266291293614|undefined|undefined|undefined|undefined|
    1266291293616|1266291278871|3x3x3|R2 D' L R B D' B2 L' F D2 B2 R U B F2 R2 D' R' B2 R' D' U B' D' U L' R2 B R2 D2 R D' R2 D R2|940|
    Any suggestions as to why all of my inserts are logging correctly, but showing up as undefined?

    Thanks!
  2. #2  
    It's an issue with your 'this' reference, which is local to your function. Add .bind(this) to your function declaration like this:

    Code:
    PreCTAssistant.prototype.addTime = function(){
    	var db = openDatabase(this.dbName, this.version);
    	
    	Mojo.Log.info("Inserting values %s %s %s %s %s null",new Date().getTime(), this.sessionId, this.puzzle, this.currentScramble, this.currentTime);
    		
    	var sql = "INSERT INTO 'SOLVES' (DATE_ENTERED, SESSION_ID, PUZZLE_TYPE, SCRAMBLE, SOLVE_TIME_MILLIS, COMMENTS) VALUES (?, ?, ?, ?, ?, ?)";
    	Mojo.Log.info(sql);
    	
    	db.transaction( function (transaction) {
    	  transaction.executeSql(sql,  [new Date().getTime(), this.sessionId, this.puzzle, this.currentScramble, this.currentTime ,null], 
    	                         function(transaction, results) {    // success handler
    	                           Mojo.Log.info("Successfully inserted record"); 
    	                         },
    	                         function(transaction, error) {      // error handler
    	                           Mojo.Log.error("Could not insert record: " + error.message);
    	                         }
    	  );
    	});
    
    }.bind(this);
  3. spdqbr's Avatar
    Posts
    78 Posts
    Global Posts
    80 Global Posts
       #3  
    Binding this in the function declaration didn't seem to work, but I did manage to get success by binding this to the db.transaction method:

    Code:
    PreCTAssistant.prototype.addTime = function(){
    	var db = openDatabase(this.dbName, this.version);
    	
    	Mojo.Log.info("Inserting values %s %s %s %s %s null",new Date().getTime(), this.sessionId, this.puzzle, this.currentScramble, this.currentTime);
    		
    	var sql = "INSERT INTO 'SOLVES' (DATE_ENTERED, SESSION_ID, PUZZLE_TYPE, SCRAMBLE, SOLVE_TIME_MILLIS, COMMENTS) VALUES (?, ?, ?, ?, ?, ?)";
    	Mojo.Log.info(sql);
    	
    	db.transaction( function (transaction) {
    	  transaction.executeSql(sql,  [new Date().getTime(), this.sessionId, this.puzzle, this.currentScramble, this.currentTime ,null], 
    	                         function(transaction, results) {    // success handler
    	                           Mojo.Log.info("Successfully inserted record"); 
    	                         },
    	                         function(transaction, error) {      // error handler
    	                           Mojo.Log.error("Could not insert record: " + error.message);
    	                         }
    	  );
    	}.bind(this));
    
    };
    I'm still getting used to the language for sure! Thanks for your help!
  4. #4  
    Whoops, yeah, you're correct. I meant to put it in the anonymous function you declared in the transaction like you did.
  5. spdqbr's Avatar
    Posts
    78 Posts
    Global Posts
    80 Global Posts
       #5  
    Close enough to get me there, and that's all I ask. Thanks!
  6. spdqbr's Avatar
    Posts
    78 Posts
    Global Posts
    80 Global Posts
       #6  
    New problem I'm coming up with now. I'm trying to make a series of tables, indexes, and triggers all at once and it just doesn't seem to want to do it.

    The code amounts to this:

    Code:
    var sql =
     " Create table1 (col1, col2);" +
     " Create table2 (col3, col4);" +
     " Create index t1_c1 on table1 (col1);" +
     " Create index t2_c3 on table2 (col3);" +
     " Create trigger t1_insert after update on table 1 "+
     " begin " +
     " insert into table2 ('Hello','World');" +
     " end;" +
     " create trigger t1_update after insert on table1 " +
     " begin " +
     " update t1 set col2 = 'updated'; " +
     " end;";
    
    	this.db.transaction(function(transaction){
    			transaction.executeSql(sql, // SQL to execute
    	 [], // array of substitution values
    	 	function(transaction, results){ // success handler
    					Mojo.Log.info("Successfully created tables");
    				}, function(transaction, error){ // error handler
    					Mojo.Log.error("Could not create tables");
    				});
    			}
    	.bind(this));
    The sql has been made simpler here for demonstration purposes. But the schema that I've come up with has been tested on my machine with sqlite3 and seems to work correctly.

    The problem is, when I execute the above code, I get the "Successfully created tables" message, but none of the indexes show up, and only one of the triggers is created.

    Any tips on how to get webos to efficiently create my db structure?
  7. spdqbr's Avatar
    Posts
    78 Posts
    Global Posts
    80 Global Posts
       #7  
    Giving a minor bump here as all my efforts have proven ineffective. I have tried surrounding the database creation code in "Begin transaction; ... commit;" statements, but that gets me even less far. The query fails with the error message: Could execute sql with name : not authorized.

    If anyone has any insight, I'd really appreciate it!

Posting Permissions