SqlStore Test
SequelSphereDB / ExtJS Connector Example Page

The ExtJS 4.x application below provides an example of how to execute SQL against ExtJS 'Ext.data.Store's. A new 'SqlStore' can be used to execute SQL and use the query results as follows:

 
var store = Ext.create('Ext.ux.data.SqlStore', {
	storeId: 'emplList',
	sql: 'SELECT empl_id, e.name, age, d.name as dept_name ' +
             '  FROM empl e ' +
             '  JOIN dept d ' +
             '    ON e.dept_id = d.dept_id ' +
             ' WHERE age > 20'
});

When SQL is executed, it all occurs in the browser , using the SequelSphereDB engine to parse and execute the SQL. The ExtJS Data Stores are included in the SequelSphereDB engine using the TableProvider interface (Check the SequelSphereDB API documentation for more information.)

Two different Stores were created for this example, but any type of ExtJS store will work:

  • Empl (An 'Ext.data.ArrayStore')
  • Dept (An 'Ext.data.JsonStore')
Here is an EXT Query and Populate Table App: (Code Below)
Here is the source code for Ext.ux.data.SqlStore:
SqlStore.js
Here is code for creating the 'Empl' and 'Dept' Data Sources:
Click here to expand & contract code.
 
Ext.create('Ext.data.ArrayStore', {
	storeId: 'Empl',
	model: Ext.define('EmplModel', {
		extend: 'Ext.data.Model',
		idProperty: 'empl_id',
		fields: [
			{name: 'empl_id', type: 'int'},
			{name: 'name', type: 'string'},
			{name: 'age', type: 'int'},
			{name: 'dept_id', type: 'int' }
		]
	}),
	data: [
		[0,"Bob",32,0],
		[1,"John",37,2],
		[2,"Fred",28,1],
		[3,"Sue",26,3],
		[4,"Beth",22,99],
		[5,"Mary",27,3],
		[6,"Adrian",24,1]
	]
});

Ext.create('Ext.data.JsonStore', {
	storeId: 'Dept',
	model: Ext.define('DeptModel', {
		extend: 'Ext.data.Model',
		idProperty: 'dept_id',
		fields: [
			{name: 'dept_id', type: 'int' },
			{name: 'name', type: 'string'},
			{name: 'mgr_id', type: 'int'}
		]
	}),
	data: [
		{ dept_id: 0, name: "Finance", mgr_id: 0 },
		{ dept_id: 1, name: "Accounting", mgr_id: 4 },
		{ dept_id: 2, name: "I/T", mgr_id: 99 },
		{ dept_id: 3, name: "Marketing", mgr_id: 1 },
		{ dept_id: 4, name: "Janitorial Services", mgr_id: 5 }
	]
});

Here is the code for creating a 'SqlStore':
Click here to expand & contract code.
 
//	First, let's create our SqlStore.
//	The following will return whatever SQL is in the sqlArea textfield.
var query = Ext.getCmp("sqlArea").getValue();

//	Now, to create the store, we just provide the SQL.
var store = Ext.create('Ext.ux.data.SqlStore', {
	sql: query //  The sql is executed, creating 'queryResults'
});

Here is some of the relavent code for populating the Grid & Chart:
Click here to expand & contract code.
 
//	First, let's create our SqlStore.
var query = Ext.getCmp("sqlArea").getValue();
var store = Ext.create('Ext.ux.data.SqlStore', {
	sql: query //  The sql is executed, creating 'queryResults'
});

//	Now, let's create our Grid Columns
var res = store.queryResults;
var cols = [];
for (var i = 0; i < res.columnNames.length; ++i) {
	cols.push({
		text: res.columnNames[i],
		dataIndex: res.columnNames[i],
		flex: 1,
		renderer: function(val) {
			return (val != null) ? val : "<i style='color:#BBB'>NULL</i>";
		}
	});
}

//	Let's create our grid.
var grid = Ext.create('Ext.grid.Panel', {
	anchor: '100% 100%',
	autoScroll: true,
	store: store,
	columns: cols
});

//	Let's create our chart
var chart = Ext.create('Ext.chart.Chart', {
	width: 580,
	height: 190,
	store: store,
	axes: ...
	series: ...
});