no such table

cirollo · Nov 29, 2011

Hi!
I've created a new db with 1 table using SQLite Database Browser 2.0 b1

I've added the .db file in b4a files

this is my code:

'Activity module
Sub Process_Globals
'These global variables will be declared once when the application starts.
'These variables can be accessed from all modules.
Dim SQL As SQL
End Sub

Sub Globals
'These global variables will be redeclared each time the activity is created.
'These variables can only be accessed from this module.
Dim lblCliente As Label
Dim spnrClienteId As Spinner
Dim lstSerials As ListView
Dim WebView1 As WebView
End Sub

Sub Activity_Create(FirstTime As Boolean)
If FirstTime Then
SQL.Initialize(File.DirInternal, "elenco.db", True)
DBUtils.CopyDBFromAssets("elenco.db")
End If
Activity.LoadLayout("elenco")
'Fill the id spinner (aka combo box).
DBUtils.ExecuteSpinner(SQL, "SELECT nominativo FROM clienti", Null, 0, spnrClienteId)
spnrClienteIdItem_Click(0, spnrClienteId.GetItem(0))
'ExportToJSON
'ShowTableInWebView
End Sub

but when launching:SELECT nominativo FROM clienti it raise an error:
no such table:clienti

why???
regards,
cirollo

klaus · Nov 29, 2011

Before initializing the database you must check if the file does exist in File.DirInternal.
And if not copy it from File.DirAssets to File.DirInternal.

Have a look here.

Best regards.

cirollo · Nov 30, 2011

ok but ...

Ok
but how can i add a db file to My app because maybe i made something wrong

klaus · Nov 30, 2011

You can add the db file with the file manager in the IDE, but then as I mentioned in my previous post, you must copy it to a writable folder.
Have a look at page 152 in the Beginner's Guide.

Best regards.

cirollo · Nov 30, 2011

tried...but

this is the updated code.....but still doesn't work
(on emulator)

this is the logcat:
android.database.sqlite.SQLiteException: no such table: clienti: , while compiling: SELECT nominativo FROM clienti

and this the code:

Sub Process_Globals
Dim SQL As SQL
End Sub

Sub Activity_Create(FirstTime As Boolean)
If FirstTime Then
If File.Exists(File.DirInternal,"elenco.db") = False Then
File.Copy(File.DirAssets,"elenco.db",File.DirInternal,"elenco.db")
End If
If SQL.IsInitialized = False Then
SQL.Initialize(File.DirInternal, "elenco.db", False)
End If
' SQL.Initialize(File.DirInternal, "elenco.db", True)
' DBUtils.CopyDBFromAssets("elenco.db")
End If
Activity.LoadLayout("elenco")
'Fill the id spinner (aka combo box).
DBUtils.ExecuteSpinner(SQL, "SELECT nominativo FROM clienti", Null, 0, spnrClienteId)
spnrClienteIdItem_Click(0, spnrClienteId.GetItem(0))
End Sub

klaus · Nov 30, 2011

There is an extra space in this line ?
File.Copy(File.DirAssets,"elenco.db",File.DirInter nal,"elenco.db")

What error do you get ?
Do you perhaps already have a wrong database file in File.DirInternal ?
In that case there will be no update.
For test try to delete an existing file in File.DirInternal.

Otherwise post your project or at least a smaller one that shows the problem.

Best regards.

cirollo · Nov 30, 2011

well

no there's the space, it appears after copy/paste from notepad! strange...

the problem is on this command:

DBUtils.ExecuteSpinner(SQL1, "select id from clienti", Null, 0, spnrClienteId)

if i comment it, everything seems to work, i've also modified this:

If File.Exists(File.DirInternal,"elenco.db") = False Then
Msgbox("DB Inesistente!","Attenzione")
File.Copy(File.DirAssets,"elenco.db",File.DirInternal,"elenco.db")
End If
If SQL1.IsInitialized = False Then
SQL1.Initialize(File.DirInternal, "elenco.db", False)
End If

and no messagebox appears, so the file is in the correct position!
I don't know what else to check!?!?!?!

klaus · Nov 30, 2011

Are you shure that your db file is OK ?

Best regards.

cirollo · Nov 30, 2011

another problem

finally I made it, deleting the db and recreating copying from assets

but now, I'm experiencing this problem:

android.database.sqlite.SQLiteException: bind or column index out of range: handle 0x25c268

the code in question is:

Sub Globals
'These global variables will be redeclared each time the activity is created.
'These variables can only be accessed from this module.
Dim lblCliente As Label
Dim spnrClienteId As Spinner
Dim lstSerials As ListView
Dim WebView1 As WebView
End Sub

Sub Activity_Create(FirstTime As Boolean)
If FirstTime Then
'If File.Delete(File.DirInternal,"elenco.db") = True Then
If File.Exists(File.DirInternal,"elenco.db") = False Then
Msgbox("DB Inesistente!","Attenzione")
File.Copy(File.DirAssets,"elenco.db",File.DirInternal,"elenco.db")
End If
' End If
If SQL1.IsInitialized = False Then
SQL1.Initialize(File.DirInternal, "elenco.db", False)
End If
End If
Activity.LoadLayout("elenco")
'Fill the id spinner (aka combo box).
' DBUtils.ExecuteSpinner(SQL1, "select id || ' ' || nominativo from clienti", Null, 0, spnrClienteId)
DBUtils.ExecuteSpinner(SQL1, "select id from clienti", Null, 0, spnrClienteId)
spnrClienteIdItem_Click(0, spnrClienteId.GetItem(0))
'ExportToJSON
'ShowTableInWebView
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub spnrClienteIdItem_Click (Position As Int, Value As Object)
FindSerials(Value)
End Sub
Sub FindSerials(ClienteId As String)
'Note that we use SQLite concatenation operator to add 'Grade: ' before each grade.
DBUtils.ExecuteListView(SQL1, "SELECT licenza, 'Seriale: ' || seriale, ' Expiration:' || expiration FROM clienti WHERE id = 1", _
Array As String(ClienteId), 0, lstSerials, True)
End Sub

the istruction that raises the error is:

DBUtils.ExecuteListView(SQL1, "SELECT licenza, 'Seriale: ' || seriale, ' Expiration:' || expiration FROM clienti WHERE id = 1", _
Array As String(ClienteId), 0, lstSerials, True)

I don't understand why!!!!

eps · Nov 30, 2011

Is this in the AVD? If so you may well need to give it some dataspace to play with.. I would also clear the device before installing, as sometimes the old data hangs around and new tables won't show...

I'll try and post my code, which I know works and can be used to force a DB refresh, when the structure changes.

I would also check this : http://www.b4x.com/forum/basic4android-getting-started-tutorials/6736-sql-tutorial.html#post42606

As there are some fields and tables that SQLLite DBs on Android expect to exist... I use the Firefox SQLLite Add-on/Extension, which works well

cirollo · Nov 30, 2011

yes....

is in the AVD.

the db is made from scratch with SQLite Database Browser 2.0b1

the db doesn't have metadata table, neither the id is called _id

do you think this should be the problem (and I have to follow the tutorial)???

klaus · Dec 1, 2011

Cold you post such a db so we could test it ?

Best regards.

eps · Dec 1, 2011

You need to ensure that the DB is actually created on the AVD and that it is being copied to the correct filespace..

I had some issues with tables and ids, but can't remember now, that was about 5 months ago!! It's one of those problems that once you're over it, you never look back again!

If you do resolve it, please post here how you resolved it.

I would wipe data on the AVD in question and then step through the code and ensure that each step, concerning the DB existence is working...

I would guess that it's looking at a different version of the DB than you think it is and the table doesn't exist there.. but has since been created.

eps · Dec 1, 2011

I do something like this....

db_ver is analogous to the App version.. If the DB is likely to change then I keep it one back from the current App version...

Code:

   db_ver = "0.6"
   
'Log(DateTime.Time(DateTime.Now))
   
   'Database hasn't been copied to read/write area
   If File.Exists(File.DirInternal,"the_database.db") = False Then
      File.Copy(File.DirAssets,"the_database.db",File.DirInternal,"the_database.db")
   Else
      'Database is in the read/write area, check the db_ver matches the app_ver
      If VersionLabel.Text <> db_ver Then
         ListViewSaveFavs.Initialize("ListViewSaveFavs")
         Dim number_of_favs As Int
         number_of_favs = 0
            If SQL1.IsInitialized = False Then
                 SQL1.Initialize(File.DirInternal, "the_database.db", True)
            End If
         'DB version is greater than the app version, we need to store favourites, update the DB and restore favourites

                       'code excerpt removed

         'remove the current database file
         File.Delete(File.DirInternal,"theolympics.db")
         'put the new database file in place
         File.Copy(File.DirAssets,"the_database.db",File.DirInternal,"the_database.db")
         SQL1.Initialize(File.DirInternal, "the_database.db", True)
         'put the favourites back in the database

                       'code excerpt removed

      End If   
   End If
   
   If SQL1.IsInitialized = False Then
        SQL1.Initialize(File.DirInternal, "the_database.db", True)
   End If

Does your code look, possibly in part, something like the above? Esp. the copy and then the initialize steps.

no such table

cirollo

Active Member

klaus

Expert

cirollo

Active Member

klaus

Expert

cirollo

Active Member

klaus

Expert

cirollo

Active Member

klaus

Expert

cirollo

Active Member

eps

Expert

cirollo

Active Member

klaus

Expert

eps

Expert

eps

Expert

Similar Threads