[TIMOB-26485] Android: Local database provides error in Alloy

GitHub Issue	n/a
Type	Bug
Priority	None
Status	Resolved
Resolution	Invalid
Resolution Date	2018-10-25T21:10:37.000+0000
Affected Version/s	n/a
Fix Version/s	n/a
Components	Android
Labels	n/a
Reporter	Rakhi Mitro
Assignee	Gary Mathews
Created	2018-10-25T07:21:46.000+0000
Updated	2018-10-26T19:59:29.000+0000

Description

Trying to install a local database(dumped form SQlite Manager) on Android using *Titanium.Database.Install()* and it provides error. *Testing Environment:* Appcelerator Command-Line Interface, version 7.0.6,Operating System Mac OS X 10.13.6, Node.js Version 8.9.1 SDK Version 7.4.1.GA,Device: Huawei GR3 (Android 7) *Test Steps:* 1. Create an alloy app 2.Paste the sample code to app directory and sample database to app->lib directory(sample db file is attached) 2. Run the project , click the label and check console *Test Code:* *index.js*

function doClick(e) {
	
    var db = Ti.Database.install('/lib/db', 'db');
    var db = Ti.Database.open('db');
    var importName='def';
    var imporCaptured='567';
    db.execute('INSERT INTO info (name,captured) VALUES (?,?)', importName, imporCaptured);

    var cityWeatherRS = db.execute('SELECT id,name,captured FROM info');
  while (cityWeatherRS.isValidRow()) {
    var cityId = cityWeatherRS.fieldByName('id');
    var cityName = cityWeatherRS.fieldByName('name');
    var citycaptured = cityWeatherRS.fieldByName('captured');
    Ti.API.info(cityId + ' ' + cityName + ' ' + citycaptured);
    
    
    cityWeatherRS.next();
}

db.close();

}

$.index.open();

*index.xml*


<Alloy>
	<Window class="container">
		<Label id="label" onClick="doClick">Hello, World</Label>
	</Window>
</Alloy>

*Test output:*

[ERROR] :  TiDB: (main) [7582,7583] Error executing sql: no such table: info (Sqlite code 1): , while compiling: INSERT INTO info (name,captured) VALUES (?,?), (OS error - 2:No such file or directory)
[ERROR] :  TiDB: android.database.sqlite.SQLiteException: no such table: info (Sqlite code 1): , while compiling: INSERT INTO info (name,captured) VALUES (?,?), (OS error - 2:No such file or directory)
[ERROR] :  TiDB: 	at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
[ERROR] :  TiDB: 	at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:910)
[ERROR] :  TiDB: 	at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:521)
[ERROR] :  TiDB: 	at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:603)
[ERROR] :  TiDB: 	at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:63)
[ERROR] :  TiDB: 	at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
[ERROR] :  TiDB: 	at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1965)
[ERROR] :  TiDB: 	at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1941)
[ERROR] :  TiDB: 	at ti.modules.titanium.database.TiDatabaseProxy.execute(TiDatabaseProxy.java:142)
[ERROR] :  TiDB: 	at org.appcelerator.kroll.runtime.v8.V8Object.nativeFireEvent(Native Method)
[ERROR] :  TiDB: 	at org.appcelerator.kroll.runtime.v8.V8Object.fireEvent(V8Object.java:63)
[ERROR] :  TiDB: 	at org.appcelerator.kroll.KrollProxy.doFireEvent(KrollProxy.java:969)
[ERROR] :  TiDB: 	at org.appcelerator.kroll.KrollProxy.handleMessage(KrollProxy.java:1194)
[ERROR] :  TiDB: 	at org.appcelerator.titanium.proxy.TiViewProxy.handleMessage(TiViewProxy.java:394)
[ERROR] :  TiDB: 	at android.os.Handler.dispatchMessage(Handler.java:101)
[ERROR] :  TiDB: 	at android.os.Looper.loop(Looper.java:156)
[ERROR] :  TiDB: 	at android.app.ActivityThread.main(ActivityThread.java:6523)
[ERROR] :  TiDB: 	at java.lang.reflect.Method.invoke(Native Method)
[ERROR] :  TiDB: 	at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:942)
[ERROR] :  TiDB: 	at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:832)
[ERROR] :  TiExceptionHandler: (main) [4,7587] /alloy/controllers/index.js:59
[ERROR] :  TiExceptionHandler: 		db.execute('INSERT INTO info (name,captured) VALUES (?,?)', importName, imporCaptured);
[ERROR] :  TiExceptionHandler:      ^
[ERROR] :  TiExceptionHandler: Error: no such table: info (Sqlite code 1): , while compiling: INSERT INTO info (name,captured) VALUES (?,?), (OS error - 2:No such file or directory)
[ERROR] :  TiExceptionHandler:     at Label.doClick (/alloy/controllers/index.js:59:6)
[ERROR] :  TiExceptionHandler:     at Label.value (ti:/events.js:49:21)
[ERROR] :  TiExceptionHandler:     at Label.value (ti:/events.js:101:19)
[ERROR] :  TiExceptionHandler:
[ERROR] :  TiExceptionHandler:     android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
[ERROR] :  TiExceptionHandler:     android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:910)
[ERROR] :  TiExceptionHandler:     android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:521)
[ERROR] :  TiExceptionHandler:     android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:603)
[ERROR] :  TiExceptionHandler:     android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:63)
[ERROR] :  TiExceptionHandler:     android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
[ERROR] :  TiExceptionHandler:     android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1965)
[ERROR] :  TiExceptionHandler:     android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1941)
[ERROR] :  TiExceptionHandler:     ti.modules.titanium.database.TiDatabaseProxy.execute(TiDatabaseProxy.java:142)
[ERROR] :  TiExceptionHandler:     org.appcelerator.kroll.runtime.v8.V8Object.nativeFireEvent(Native Method)
[INFO] :   HwPointEventFilter: do not support AFT because of no config
[ERROR] :  V8Exception: Exception occurred at /alloy/controllers/index

Attachments

File	Date	Size
db.sqlite	2018-10-25T07:21:00.000+0000	12288

Comments

Gary Mathews 2018-10-25

The file path is invalid. They need to specify the database like so:

   // you should only install the database once
   // this copies the database to internal storage
   // overwriting any previous internal database of the same name
   var db = Ti.Database.install('lib/db.sqlite', 'db'); // NOTE: I fixed the path
   
   // once the database is installed you should only use open()
   // this will open and use the internal database
   var db = Ti.Database.open('db');

They need to have a condition to only use open() after installing the database the first time the app is used.

Josh Magalich 2018-10-26

I have been having this same issue for days now. I have tried all kinds of things to get this to work. I just used the the above database and sample code and still get the error stating the table does not exist. I have read so many postings on other sites that have the same issue. This is such a simple thing to do. I have used this same approach years ago in Appcelerator on a classic app and it worked fine. I'm about ready to give up on Appcelerator even though I just purchased a Pro plan a couple weeks ago.
Gary Mathews 2018-10-26

[~josh.magalich@crompco.com] Could you provide a snippet of your database code?
Josh Magalich 2018-10-26

Below is the snippet of code I am using to install my database for the first time and insert a test record in the "user" table. I have even tried using your database file with the code and got the error. I have also tried to use a hypnosis.sqlite file and got the same results. Thank you. var db = Ti.Database.install('lib/hypnosis.db', 'hypnosis'); var db = Ti.Database.open('hypnosis'); db.execute('INSERT INTO user (user_name, password) VALUES (?,?)', "a", "a"); Below is the beginning part of the error I get that is the same as the original error of this ticket. [ERROR] : SQLiteLog: (1) no such table: user [ERROR] : TiDB: (main) [6266,6266] Error executing sql: no such table: user (code 1): , while compiling: INSERT INTO user (user_name, password) VALUES (?,?) [ERROR] : TiDB: ################################################################# [ERROR] : TiDB: Error Code : 1 (SQLITE_ERROR) [ERROR] : TiDB: Caused By : SQL(query) error or missing database. [ERROR] : TiDB: (no such table: user (code 1): , while compiling: INSERT INTO user (user_name, password) VALUES (?,?))
Gary Mathews 2018-10-26

Make sure your hypnosis.db file is placed under Resources/lib/hypnosis.db in your project. Also, as mentioned in my comment above. You should not call: var db = Ti.Database.open('hypnosis'); After install, you must only use one or the other. install should only be used the first time your app is launched. Every other time should use open as the database has already been installed. This is unless you want your internal database to be overwritten every time, which you would always use install. Make sure your database is the correct format to be used with SQLite. If possible, you could attach your hypnosis.db (or a similar database without sensitive information) that I could test.
Josh Magalich 2018-10-26

I have tried to attach my database file to these comments but cannot find anywhere to do it. When I click on the attachment icon in the tool bar I just see your db.sqlite file and cannot upload my own. I did send you an email with my db file attached but not sure if it went through or not. Thanks.

JSON Source