[SOLVED]General question - SQLiteStore Loading + Combo

[Jack_S]

Hello Pranke,

So I followed your instructions and my suspicions were right:

SQLError: 'Error #3115: SQL Error.', details:'no such table: 'vw_admins'', operation:'execute', detailID:'2013'
	at [Eval script](e) (iApplication/lib/ext/ext-all-debug.js:13981)
	at [Eval script](e) (iApplication/lib/ext/ext-all-debug.js:32611)
	at [Eval script]() (iApplication/lib/ext/ext-all-debug.js:32266)
	at [Eval script](forceAll, q) (iApplication/lib/ext/ext-all-debug.js:58267)
	at [Eval script]() (iApplication/lib/ext/ext-all-debug.js:58350)
	at h(e) (iApplication/lib/ext/ext-all-debug.js:2646)

As for rs, its showing we rs is undefined and I can't seem to catch what sql statement is being sent off to the SQLite database. I'm getting this suspicion that Ext.Air is doing something like the below, but I cna't prove it.

select * from sql_master where table_name= xyz

[PranKe01]

I see that you use "CREATE VIEW" to create the view. I am not sure, but I think, that the SQLiteStore does not support a real view. Try to use a normal table and the SQLiteStore with the property api. api got 4 propertys - set each one to false, so no changes are made to the database.

[Jack_S]

Hello Pranke,

This is odd, because Makana had previously stated that you could select from a view!?

Also a view is simply a predefined select statement. Therefore you can do a select * from my_view, which is exactly what happens in the code:

selectBy : function(clause, args){
		var sql = "SELECT * FROM " + this.name;
		
		if(clause){
			sql += ' ' + clause;
		}
		args = args || {};
		return this.conn.queryBy(sql, args);
	},

[makana]

This is odd, because Makana had previously stated that you could select from a view!?

You can!

SQLError: 'Error #3115: SQL Error.', details:'no such table: 'vw_admins'', operation:'execute', detailID:'2013'
	at [Eval script](e) (iApplication/lib/ext/ext-all-debug.js:13981)
	at [Eval script](e) (iApplication/lib/ext/ext-all-debug.js:32611)
	at [Eval script]() (iApplication/lib/ext/ext-all-debug.js:32266)
	at [Eval script](forceAll, q) (iApplication/lib/ext/ext-all-debug.js:58267)
	at [Eval script]() (iApplication/lib/ext/ext-all-debug.js:58350)
	at h(e) (iApplication/lib/ext/ext-all-debug.js:2646)

Seems like the view isn't created. It should not be a problem with the store. Or you have a quoting problem.... don't know exactly if the underlined single quotes are always in that type of error message...
But you quote a lot in qour sql strings. Why? Maybe there is a problem with this....

CREATE TABLE "ORI_ADMINISTRATORS" ("ADMINISTRATOR_ID" INTEGER,"ADMINISTRATOR_NAME" TEXT,"ADMINISTRATOR_TYPE" TEXT);

insert into ORI_ADMINISTRATORS values (1,'Jack Johnson','DATABASE');
insert into ORI_ADMINISTRATORS values (2,'Jamal Stevens','DATABASE');
insert into ORI_ADMINISTRATORS values (3,'Tom Brown','ONLINE');
insert into ORI_ADMINISTRATORS values (4,'Peter Smit','DATABASE');

CREATE VIEW "vw_admins" AS  SELECT ADMINISTRATOR_ID as 'id', ADMINISTRATOR_NAME as 'text' FROM ORI_ADMINISTRATORS WHERE ADMINISTRATOR_TYPE = 'DATABASE' ORDER BY UPPER(ADMINISTRATOR_NAME);

[Jack_S]

Hello Makana,

Could you provide me an example SQL CREATE VIEW whihc works for you. I've tried all the the possible combination, single quote, double quote, no quotes and I'll still getting thsi error. I've even tried thing ssuch as 'main.MYVIEW'

Thanks

Jack

[makana]

Is the view available in your database? With which name, with which column names? Use your SQLiteManager addon for firefox to inspect.

And then post your code you use to read from the db, please! Does this error appear on load method? or only on insert/update/delete?


Or wait a moment...
I think, I've an idea... is it possible, that you want to auto create the "table" on creating the store? Configure your store with createTable: false! Make sure, the Ext.sql.AirConnection.createTable method isn't called!
But that should be by default in current ext-air-debug file in this thread.

Just FYI, a new version with a completly reviewed SQLiteStore/connection etc. is coming soon...

[Jack_S]

Hello Makana,

I've followed all of you instructions. This is a read-only store, so I used your api extension as such:

store: new Ext.sql.SQLiteStore({
                     conn: iApplication.module.DB
					,api:{create:false
					     ,update:false
						 ,destroy:false
				    }
					,createTable: false
                                        ,tableName: 'VW_ADMINS'
					,key: 'id'
					,fields: [{name: 'id', type: 'int'}
                          	 ,{name: 'text', type: 'string'}
                    ]
      })

Yes my view is avaialble in the SQLiteManager add-on for firefox. When I do a select * from VW_ADMINS I get all the records inside that view. For the sake of avoid upper/lower case issues I've rewriten everything to upper case, therefore my view definition is as such

CREATE VIEW "VW_ADMINS" AS SELECT ADMINISTRATOR_ID as id, ADMINISTRATOR_NAME as text FROM ORI_ADMINISTRATORS WHERE ADMINISTRATOR_TYPE = "COUNTRY" ORDER BY UPPER(ADMINISTRATOR_NAME) ASC

Thanks Jack

ps - looking forward to the new Store :-)

[Jack_S]

Hello Makana,

I've followed all of you instructions. This is a read-only store, so I used your api extension as such:

store: new Ext.sql.SQLiteStore({
                     conn: iApplication.module.DB
					,api:{create:false
					     ,update:false
						 ,destroy:false
				    }
					,createTable: false
                                        ,tableName: 'VW_ADMINS'
					,key: 'id'
					,fields: [{name: 'id', type: 'int'}
                          	 ,{name: 'text', type: 'string'}
                    ]
      })

Yes my view is avaialble in the SQLiteManager add-on for firefox. When I do a select * from VW_ADMINS I get all the records inside that view. For the sake of avoid upper/lower case issues I've rewriten everything to upper case, therefore my view definition is as such

CREATE VIEW "VW_ADMINS" AS SELECT ADMINISTRATOR_ID as id, ADMINISTRATOR_NAME as text FROM ORI_ADMINISTRATORS WHERE ADMINISTRATOR_TYPE = "COUNTRY" ORDER BY UPPER(ADMINISTRATOR_NAME) ASC

Yes indeed the error originates from the load method:

load : function(options) {
        options = options || {};
        this.storeOptions(options);
        
		if(this.sortInfo && this.remoteSort){
            var pn = this.paramNames;
            options.params = options.params || {};
            options.params[pn.sort] = this.sortInfo.field;
            options.params[pn.dir] = this.sortInfo.direction;
        }
        try {
            return this.execute('read', null, options); // <-- null represents rs.  No rs for load actions.
        } catch(e) {
            this.handleException(e);
            return false;
        }
    },

And I'm using the basic selectBy as defined in the modified version:

selectBy : function(clause, args){
		var sql = "SELECT * FROM " + this.name;

		if(clause){
			sql += ' ' + clause;
		}
		args = args || {};
		return this.conn.queryBy(sql, args);

	},

Thanks Jack

ps - looking forward to the new Store :-)

[makana]

please do an

air.Introspector.Console.info(sql, args);

in your selectBy function and look, what sql string is generated.

[Jack_S]

Hello Makana,

Here is the ouput of the Instrospector from the view, which returns the error table cannot be found:

[app:/country.html?session_id=2176900351995318][13:57:56] SELECT * FROM VW_ADMINS, [Array 0],

[Arguments 2]
   0 "SELECT * FROM VW_ADMINS"
   1 [Array 0]

And here is the result of another store which access a table.

[app:/country.html?session_id=2176900351995318][13:59:41] SELECT * FROM ORI_DATABASES LIMIT 25, [Array 0],

[Arguments 2]
 0  "SELECT * FROM ORI_DATABASES LIMIT 25"
 1 [Array 0]

Ultimately, both are essential the same, and when copy/paste execute in SQLIteManager firefox add-on, it returns all the data as it should?

Thanks Jack