parameterized sqlite query not working

Discussions and Tech Support related to the iMacros Firefox Add-on, including the built-in Javascript scripting interface for playing .js files.
Forum rules
Before asking a question or reporting an issue:
1. Please review the list of FAQ's.
2. Use the search box (at the top of each forum page) to see if a similar problem or question has already been addressed.
3. Try searching the iMacros Wiki - it contains the complete iMacros reference as well as plenty of samples and tutorials.
4. We can respond much faster to your posts if you include the following information:CLICK HERE FOR IMPORTANT INFORMATION TO INCLUDE IN YOUR POST
Post Reply
ravi_npatty
Posts: 22
Joined: Sat May 07, 2011 6:12 am

parameterized sqlite query not working

Post by ravi_npatty » Mon Apr 17, 2017 6:37 pm

I express my sincere thanks to blueX for providing nice library for keeping things simple through this post
http://forum.imacros.net/viewtopic.php? ... end#p73568

I am able to store my data into sqlite with simple query. When I try to use parameterized query, it fails with error message:

[Exception... "Component returned failure code: 0x8000ffff (NS_ERROR_UNEXPECTED) [mozIStorageStatement.execute]" nsresult: "0x8000ffff (NS_ERROR_UNEXPECTED)" location: "JS frame :: resource://gre/modules/RemoteAddonsParent.jsm line 780 > eval :: iimSqlite.prototype.exec :: line 89" data: no], line 89 (Error code: -991)

My code is here:

Code: Select all

	var db=new iimSqlite("test_v1.sqlite");
	//alert("file ok");
	db.exec('create table if not exists test ([ID] text primary key,[col 1] text,[col 2] number,[col 3] text);');
	//alert("create table ok");
	sql='insert or replace into test ([ID],[col 1],[col 3],[col 2]) values(:id, :col1, :col3, :col2);';
	param=[{name:"id",value:"255"},{name:"col1",value:"col1 text"},{name:"col2",value:458752},{name:"col3",value:"col3 text"},]
	alert(sql);
	db.exec(sql,param);
	alert("insert ok");
	db.close();
Somebody please help me in getting this working.
Thanks.
ravi_npatty
Posts: 22
Joined: Sat May 07, 2011 6:12 am

Re: parameterized sqlite query not working (solved)

Post by ravi_npatty » Thu Apr 20, 2017 2:51 pm

I have solved this issue. There was problem in binding parameter inside the library (iimext.js). I am posting the modified code here so that people need not solve it again.
I have commented old code for reference purpose.

A small change in approach now.
In earlier code, parameter was passed as param=[{name:"paramname1",value:"paramvalue1"},{name:"paramname2",value:"paramvalue2"},...]
In my modified code, I use param={paramname1:paramvalue1,paramname2:paramvalue2,...}

Similar change is required in query function also.

My code is here

Code: Select all

	iimSqlite.prototype.exec = function(sql, params) 
	{
		  //this.dbConn.executeSimpleSQL(sql);
		var stmt = this.dbConn.createStatement(sql);
		if (typeof(params) =="object") 
		{
			var bp;
			var _params = stmt.newBindingParamsArray();
			if(typeof(params.length)=="undefined")
			{				
				bp = _params.newBindingParams();  
				for(var prm in params) bp.bindByName(prm, params[prm]); 
				
				_params.addParams(bp); 	
			}
			else
			{
				
				alert("error");
			}
			I have solved this issue. There was problem in binding parameter inside the library (iimext.js). I am posting the modified code here so that people need not solve it again.
I have commented old code for reference purpose.

A small change in approach now.
In earlier code, parameter was passed as param=[{name:"paramname1",value:"paramvalue1"},{name:"paramname2",value:"paramvalue2"},...]
In my modified code, I use param={paramname1:paramvalue1,paramname2:paramvalue2,...}

Similar change is required in query function also.

My code is here
			
			//for (var i = 0, j = params.length; i < j; i++) 
			//{  
			//	var bp = _params.newBindingParams();  
			//  	bp.bindByName(params[i].name, params[i].value);  
		    //		_params.addParams(bp); 		    		
			//}			
			stmt.bindParameters(_params); 				
		}
		stmt.execute();		
		stmt.finalize();		
	}
	
	//params: {name1: value1,name2:value2,...}
	iimSqlite.prototype.query = function(sql, params) 
	{
		var stmt = this.dbConn.createStatement(sql);
		var results = new Array();
		if (typeof(params) =="object") 
		{
			var bp;
			var _params = stmt.newBindingParamsArray();
			if(typeof(params.length)=="undefined")
			{				
				bp = _params.newBindingParams();  
				for(var prm in params) bp.bindByName(prm, params[prm]); 
				
				_params.addParams(bp); 	
			}
			else
			{
				
			}
		}
		
		var colNames = new Array();
		var i = 0;
		while(1) 
		{
			try 
			{
				var colName = stmt.getColumnName(i++);
				colNames.push(colName);
			}	
			catch(e) 
			{
				break;
			}	
		}
					
		try 
		{
			while(stmt.executeStep()) 
			{
				var aRow = new Object();
			  	for (var i = 0, j = colNames.length; i < j; i++) 
			  	{
			  		aRow[colNames[i]] = stmt.row[colNames[i]];
				}
				results.push(aRow);
			}
		} 
		catch(aError) 
		{
			iimDisplay("Error: " + aError.message);
		}
		stmt.finalize();
		
		if (results.length == 0) 
		{
			return null;
		}
		else if (results.length == 1) 
		{
			return results[0];
		}
		return results;
	}
chivracq
Posts: 8430
Joined: Sat Apr 13, 2013 1:07 pm
Location: Amsterdam (NL)

Re: parameterized sqlite query not working

Post by chivracq » Thu Apr 20, 2017 9:27 pm

ravi_npatty wrote:I express my sincere thanks to blueX for providing nice library for keeping things simple through this post
http://forum.imacros.net/viewtopic.php? ... end#p73568

I am able to store my data into sqlite with simple query. When I try to use parameterized query, it fails with error message:

[Exception... "Component returned failure code: 0x8000ffff (NS_ERROR_UNEXPECTED) [mozIStorageStatement.execute]" nsresult: "0x8000ffff (NS_ERROR_UNEXPECTED)" location: "JS frame :: resource://gre/modules/RemoteAddonsParent.jsm line 780 > eval :: iimSqlite.prototype.exec :: line 89" data: no], line 89 (Error code: -991)

My code is here:

Code: Select all

	var db=new iimSqlite("test_v1.sqlite");
	//alert("file ok");
	db.exec('create table if not exists test ([ID] text primary key,[col 1] text,[col 2] number,[col 3] text);');
	//alert("create table ok");
	sql='insert or replace into test ([ID],[col 1],[col 3],[col 2]) values(:id, :col1, :col3, :col2);';
	param=[{name:"id",value:"255"},{name:"col1",value:"col1 text"},{name:"col2",value:458752},{name:"col3",value:"col3 text"},]
	alert(sql);
	db.exec(sql,param);
	alert("insert ok");
	db.close();
Somebody please help me in getting this working.
Thanks.
(F)CIM...! :mrgreen:
ravi_npatty wrote:I have solved this issue. There was problem in binding parameter inside the library (iimext.js). I am posting the modified code here so that people need not solve it again.
I have commented old code for reference purpose.

A small change in approach now.
In earlier code, parameter was passed as param=[{name:"paramname1",value:"paramvalue1"},{name:"paramname2",value:"paramvalue2"},...]
In my modified code, I use param={paramname1:paramvalue1,paramname2:paramvalue2,...}

Similar change is required in query function also.

My code is here

Code: Select all

	iimSqlite.prototype.exec = function(sql, params) 
	{
		  //this.dbConn.executeSimpleSQL(sql);
		var stmt = this.dbConn.createStatement(sql);
		if (typeof(params) =="object") 
		{
			var bp;
			var _params = stmt.newBindingParamsArray();
			if(typeof(params.length)=="undefined")
			{				
				bp = _params.newBindingParams();  
				for(var prm in params) bp.bindByName(prm, params[prm]); 
				
				_params.addParams(bp); 	
			}
			else
			{
				
				alert("error");
			}
			I have solved this issue. There was problem in binding parameter inside the library (iimext.js). I am posting the modified code here so that people need not solve it again.
I have commented old code for reference purpose.

A small change in approach now.
In earlier code, parameter was passed as param=[{name:"paramname1",value:"paramvalue1"},{name:"paramname2",value:"paramvalue2"},...]
In my modified code, I use param={paramname1:paramvalue1,paramname2:paramvalue2,...}

Similar change is required in query function also.

My code is here
			
			//for (var i = 0, j = params.length; i < j; i++) 
			//{  
			//	var bp = _params.newBindingParams();  
			//  	bp.bindByName(params[i].name, params[i].value);  
		    //		_params.addParams(bp); 		    		
			//}			
			stmt.bindParameters(_params); 				
		}
		stmt.execute();		
		stmt.finalize();		
	}
	
	//params: {name1: value1,name2:value2,...}
	iimSqlite.prototype.query = function(sql, params) 
	{
		var stmt = this.dbConn.createStatement(sql);
		var results = new Array();
		if (typeof(params) =="object") 
		{
			var bp;
			var _params = stmt.newBindingParamsArray();
			if(typeof(params.length)=="undefined")
			{				
				bp = _params.newBindingParams();  
				for(var prm in params) bp.bindByName(prm, params[prm]); 
				
				_params.addParams(bp); 	
			}
			else
			{
				
			}
		}
		
		var colNames = new Array();
		var i = 0;
		while(1) 
		{
			try 
			{
				var colName = stmt.getColumnName(i++);
				colNames.push(colName);
			}	
			catch(e) 
			{
				break;
			}	
		}
					
		try 
		{
			while(stmt.executeStep()) 
			{
				var aRow = new Object();
			  	for (var i = 0, j = colNames.length; i < j; i++) 
			  	{
			  		aRow[colNames[i]] = stmt.row[colNames[i]];
				}
				results.push(aRow);
			}
		} 
		catch(aError) 
		{
			iimDisplay("Error: " + aError.message);
		}
		stmt.finalize();
		
		if (results.length == 0) 
		{
			return null;
		}
		else if (results.length == 1) 
		{
			return results[0];
		}
		return results;
	}
Hum..., nice...!, and Thanks for sharing, and you've nicely updated the original Thread as well... :D

But you should mention in which Environment you encountered the Pb and tested your Solution, as new Versions of iMacros or of FF regularly break some Functionality... (And this is Required Info anyway to include in your Post when you open a new Thread or post for the first time in some existing Thread for me to react...)
=> Probable FCI(s) (April 2017):
- iMacros for FF v9.0.3, FF52, OS...?, + sqlite v3.18.0. (v9.0.3 = Latest (Version for iMacros for FF) but "unstable"...)
- iMacros for FF v8.9.7, FF52, OS...?, + sqlite v3.18.0. (v8.9.7 = Stable...)
- (F)CIM = (Full) Config Info Missing: iMacros + Browser + OS with all 3 Versions...
- I usually don't even read the Question if that (required) Info is not mentioned...
- Script & URL usually help a lot for a more "educated" Help...
ravi_npatty
Posts: 22
Joined: Sat May 07, 2011 6:12 am

Re: parameterized sqlite query not working

Post by ravi_npatty » Sun May 06, 2018 5:56 pm

Sorry for not posting my FCI.

My FCI was
iMacros for FF v8.9.7, FF52, Linux .
Not sure about sqlite version since it uses sqlite engine of firefox. No external database connection can be made. Version might be as you guessed sqlite v3.18.0

Since I knew the issue was of some logic, I skipped FCI. Anyway I should have given that. No excuse. Sorry.
Post Reply