A Storage Example in XUL using SQL Select and an XUL Grid

From MDC

As of Sunday, May 27, I have decided to "delete" this page and just link to the source in the mozstorage article. This page is redundant .

A work in progress as of April 26, 2007

To be done

Check out templates, and re review style guides

Here is a link Image:TTRW2.zip to the complete source.

The following is a long snippet of the example JavaScript source. It is self explanatory, if you know JavaScript, the DOM and have used the Mozilla interfaces. This is a working, annotated source example, my favorite way to get started on a new API.

/*
see http://www.sqlite.org/  for more information on sqlite
see http://developer.mozilla.org for more information on the storage layer

see the TTRW2.xul file for additional comments

Logging
NSPR_LOG_MODULES mozStorage:5 

Change log
 Apr 21 2007  

	use  storageService   lastErrorString  for more information

 Apr 26 2007  

	changed to gjSQLonerror(e, strDB.value ," Error on Open "); for  openDatabase
 
*/

// a class to hold a few variables
function gjSQLHelper()
{
	this.colNameArray = new Array();
	this.resultsArray = new Array();
	this.statsArray   = new Array(2); // 0 = Rows, 1 = columnCount
}

// this function is called from TTRW2, the xul page that shows input boxes for an SQL query and 
// an SQL databse

function runStorage()
{
	theResults = new gjSQLHelper;
 
	 //get the query string from an xul page
	 var strQuery=document.getElementById('testgj53runSQL');
		
	 if(strQuery.value.length == 0)
	 {
			document.getElementById("testgj53runSQL").value = 'Please Enter Something and Hit Search';
			return;
	 }

	 // get the file name and path to the database 
	 var strDB=document.getElementById('testgj53runDB');
		
	 if(strDB.value.length == 0)
	 {
			document.getElementById("testgj53runDB").value = 'Please Enter Something and Hit Search';
			return;
	 }

	// nsILocalFile extends nsIFile, the input into Storage Service Open Data Base call
	var ifile = Components.classes["@mozilla.org/file/local;1"].
	createInstance(Components.interfaces.nsILocalFile);

	//if the database does not exist, storageService.openDatabase creates it
	ifile.initWithPath(strDB.value);

	//Warning,  if the file does not exist, openDatabase below will create if for you 
	if(!(ifile.exists()))
	{
		// addIt shows messages on the XUL page.  function addIt(where, what) 
		// It adds the what to whatever is already there on id where
		addIt('tb2', "File "   strDB.value   " does not exist, You are creating a new Database");
	}
	else
	{
		addIt('tb2', "File "   strDB.value   " exists");
	}

	// get an interface to MOZSTORAGE
	var storageService = Components.classes["@mozilla.org/storage/service;1"]
	.getService(Components.interfaces.mozIStorageService);
 
	try
	{
		var mDBConn = storageService.openDatabase(ifile);

		// if the db does not exist it does not give us any indication
		//	mDBConn.lastErrorString returns "not an error"

	}
	catch (e)
	{
			gjSQLonerror(e, strDB.value ," Error on Open ");
	}

	// After and Open or Create of a DB above doSQL gets the column names and  
	// the data from the SQLite DB and stores it the helper object  (theResults = new gjSQLHelper;) 

	if(doSQL(mDBConn,theResults, strQuery.value))
	{
			// display whatever results we have
			doDisplay (theResults);
	}

}

function doSQL(theConn, theHelper, thesql)
{
	// if there is a problem with in the Select, Replace, Insert, Count, Delete  etc SQL statement 
	// if the table does not exist 
	// createStatement throws JS error.name == NS_ERROR_FAILURE, there is no return,the statement is undefined
	// theConn.lastErrorString contains the SQL Syntax error hint 

	// use the Step (Fetch a row at a time) method of using SQLite, so we keep track rows
	try
	{
		var myRows = 0;  
		var statement = theConn.createStatement(thesql);
	}
	catch (e)
	{
			// statement will be undefined because it throws error);
			gjSQLonerror(e, "Likely SQL syntax error: "   thesql, theConn.lastErrorString );
			//statement.reset;  //if it gets here and a statement does not exist, it says "Statement has no properties"
			return false;
	}

		// executeStep once to look at stats and get colnames etc (the first fetch)
		// store column names in colNameArray
		
	try
	{
		rc =  statement.executeStep();
	
		if(rc)
		{
			myRows  ;
                        // if you use statement.columnCount in the for loops, it looks like it 
                        // goes over to C   land to get this already known value 
	                var cols = statement.columnCount; 
			for (var i = 0; i < cols; i  )
			{
				theHelper.colNameArray.push(statement.getColumnName(i));
			}
		}
		else
		{
				// might as well bail 
				addIt('tb2', "No Results from query "   thesql);
				statement.reset;
				return;
		}

			// store the results row wise, column wise in resultsArray vector AKA array of one dimension
			// row1 col1, row1, col2, row1 colx.. , row2 col1, row2 col2

			for (var i = 0; i < cols; i  )
			{
				theHelper.resultsArray.push(statement.getString(i));
			}

			// executeStep to get the rest of the results a row at a time

			while (statement.executeStep())
			{
				myRows  ;
				for (var i = 0; i < cols; i  )
				{
					theHelper.resultsArray.push(statement.getString(i));
				}
			}

			addIt('tb2', " statement.numEntries "   statement.numEntries );  //? 
			addIt('tb2', " statement.columnCount "   cols );
			addIt('tb2', " myRows "   myRows );  // step walks through stuff so I do not think we know the count until the end

			addIt('tb2', " lastErrorString "   theConn.lastErrorString);

			//store the counts and stats in stats arrray

			theHelper.statsArray[1] = cols;
			theHelper.statsArray[0] = myRows;
	}
	catch (e)
	{
		gjSQLonerror(e, "Database Error ", theConn.lastErrorString );
	}

// what about cleaning up these arrays?
// the database is closed on html or xul page close 
// I think this is safe to reset here, if we do not get here, we are probably too hosed to do anything anyways

statement.reset;
return true;
}