SELECT error: SQLITE.ALL - Prepare Error 1 when migrating from {N} 6.8 to 8.1

[dlcole]

I'm migrating a large JavaScript project from NativeScript 6.8 to 8.1 and am receiving the subject error message. The database is the same and the code is the same (except for newer plugins). The nativescript-sqlite plugin moved from 2.6.6 to 2.8.6.

The SQL statements are simple enough. Here are two examples:

SELECT * FROM riders WHERE Bib LIKE '2'
SELECT * FROM riders WHERE [Participant First Name] LIKE 'David'

Here's the code in question:

if (!sqlite.exists(db.name)) {  // if not already in database folder
      sqlite.copyDatabase(db.name);  // copy shipped database from /app folder
    }

    new sqlite(db.name)
      .then((dbObj) => {
        dbObj.resultType(sqlite.RESULTSASOBJECT);
        dbObj.all(stmt)
          .then((result) => {

I can see that the database exists and is copied. My question now is what does this error mean, and how can I further isolate or debug the issue?

[NathanaelA]

To isolate / debug the issue, I would add a statement that queries and prints the metadata for the database after you copy it, just to verify that the you are shipping the proper database. Some files moved locations in NS 8, so it is possible that you aren't putting the DB in the correct location and so the copy isn't actually copying it. So instead of "opening" a db, you are creating a new db, which then has no riders table.

[dlcole]

@NathanaelA - thanks. I added this code:

    if (!sqlite.exists(db.name)) {  // if not already in database folder
      sqlite.copyDatabase(db.name);  // copy shipped database from /apps folder
    }

    if (!sqlite.exists(db.name)) {  // if not already in database folder
      console.log("riders-query-page.onQueryTap:  database does not exist.")
    } else {
      new sqlite(db.name)
      .then((dbObj) => {     
        console.log(JSON.stringify(dbObj, null, 4));    
        let stmt = "SELECT name FROM sqlite_schema WHERE type ='table';";
        dbObj.execSQL(stmt) 
        .then((result) => { 
          console.log("riders-query-page.onQueryTap result: " + JSON.stringify(result, null, 4));
        }) 
        .catch((e) => {
          console.log("riders-query-page.onQueryTap execSQL error: " + e );
        });
      });
      return;
    }

And I get this output:

CONSOLE LOG: {
"_messageHandlers": [],
"_isOpen": true,
"_resultType": 1,
"_valuesType": 4,
"_options": {},
"_dbnamePtr": {},
"_db": {}
}
CONSOLE LOG: riders-query-page.onQueryTap execSQL error: SQLITE.ExecSQL Failed Prepare: 1

Does _db equaling an empty object imply a new database was created?

[NathanaelA]

No, _db is just a pointer. Could the database be encrypted or corrupt?

[dlcole]

I opened the db file with DB Browser and saw what I was expecting. I edited a cell and saved. I deleted the app from the iOS emulator so as to clear the documents folder. When I ran the app I received the same results as posted above.

What does Prepare Error 1 usually indicate?

[dlcole]

I've been migrating other sections of the app, but will need to get back to this soon. @NathanaelA - what does Prepare Error 1 typically indicate?

[NathanaelA]

Prepare error 1 would be the error returned from Sqlite directly.
According to the Sqlite site it is: https://sqlite.org/rescode.html#error

(1) SQLITE_ERROR
The SQLITE_ERROR result code is a generic error code that is used when no other more specific error code is available.

Which is why I suggested that the Database is corrupt or that you are not using the Correct DB. I normally see this on a prepare if the table doesn't exist. However, based on your prior example where it failed on the schema table, I would guess it is a corrupt database (or perhaps a encrypted db, where the password opening it is wrong -- which then is treated as a corrupt db).

One thing you can double check is pull the demo from this repo and run it as-is. Make sure it works properly for you.

[dlcole]

@NathanaelA - thanks for the response. I returned to this part of the app for further debugging and now the queries are working as expected. This is perplexing, but then this code had worked before with this same database. I'll go ahead and close this issue.