SQLite3

[FabioG]

Hello,

how can I manage a database created with sqlite3?

Thanks to all

 

[mangojack]

Hi Fabio .. Just learning myself so this might get you started

I seriously suggest you read this .. SQL Tutorial

You can also build ,view and test your database with this program .... SQLite Database Browser

'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 SQL1 As SQL
   
   'initialize the database ..Create new file if does not exist
   SQL1.Initialize (File.DirDefaultExternal,"MyDataBase.db",True)
   
End Sub

Sub Activity_Create(FirstTime As Boolean)
   
   'Do not forget to load the layout file created with the visual designer. For example:
   Activity.LoadLayout("layout1")
        
    'remove table if needed
   SQL1.ExecNonQuery("DROP TABLE IF EXISTS stock")
   
    'create table
    SQL1.ExecNonQuery("CREATE TABLE IF NOT EXIST mytable (Firstname TEXT , LastName TEXT, age INTEGER)")
            
End Sub

Sub FillTable
      
    SQL1.ExecNonQuery ("INSERT INTO mytable VALUES ('John', 'Smith', 21)")      
   
End Sub

Cheers mj

 

[FabioG]

Hi Fabio .. Just learning myself so this might get you started

I seriously suggest you read this .. SQL Tutorial

You can also build ,view and test your database with this program .... SQLite Database Browser

'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 SQL1 As SQL
   
   'initialize the database ..Create new file if does not exist
   SQL1.Initialize (File.DirDefaultExternal,"MyDataBase.db",True)
   
End Sub

Sub Activity_Create(FirstTime As Boolean)
   
   'Do not forget to load the layout file created with the visual designer. For example:
   Activity.LoadLayout("layout1")
        
    'remove table if needed
   SQL1.ExecNonQuery("DROP TABLE IF EXISTS stock")
   
    'create table
    SQL1.ExecNonQuery("CREATE TABLE IF NOT EXIST mytable (Firstname TEXT , LastName TEXT, age INTEGER)")
            
End Sub

Sub FillTable
      
    SQL1.ExecNonQuery ("INSERT INTO mytable VALUES ('John', 'Smith', 21)")      
   
End Sub

Cheers mj

thank you very much

but this only works with sqlite sqlite3 or even?

thanks

 

[mangojack]

I'm little confused .... does not this work with SQLite3 anyway ?

Cheers mj

 

[FabioG]

I'm little confused .... does not this work with SQLite3 anyway ?

Cheers mj

does not work

Error: Unable to open database file (code 14)

the existing database is in the correct path and file permissions are correct
I gave chmod 666 so as to allow reading and writing to all users

are you sure this is also compatible with sqlite3?

 

[mangojack]

are you sure this is also compatible with sqlite3?

No i am not ... I will leave this thread for someone with more experience with SQLite ...

I Have no clue what chmod666 is.

Sorry i cant help.



Cheers mj

 

[FabioG]

Erel, please need your help....

 

[Erel]

SQL library work with SQLite3 databases.
Can you post the code that opens the database?

 

[FabioG]

SQL library work with SQLite3 databases.
Can you post the code that opens the database?

Tnks Erel

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

Sub Activity_Create(FirstTime As Boolean)

   Activity.LoadLayout("Main")
   
   SQL1.Initialize ("/sdcard/","contacts.db",False)
   
End Sub


Sub ButtonClearCall_Click
   
      ProgressDialogShow("Wait...")
      
      SQL1.ExecNonQuery("delete from data_usage_stat;")
      
   ProgressDialogHide
   
End Sub

the error occurs at this point in the code "SQL1.ExecNonQuery("delete from data_usage_stat;")"

 

[Erel]

Can you post the exact error message?

You should use SQL1.Initialize(File.DirRootExternal, "contacts.db", False) instead.

 

[FabioG]

Can you post the exact error message?

You should use SQL1.Initialize(File.DirRootExternal, "contacts.db", False) instead.

I apologize that it works (but it was a test I was doing)
the real database is located in "/data/data/com.android.providers.contacts/databases/contacts2.db"

while changing the permissions and then making the file contacts2.db writable to all users the operation fails

with with terminal command via root (via terminal emulator)
with the command sqlite3 can access to the db

 

[Erel]

This issue is not related to the databases. This folder is not accessible to non-system applications.

 

[FabioG]

This issue is not related to the databases. This folder is not accessible to non-system applications.

mmm
I access via terminal emulator

how can I fix this? :BangHead:

thank you very much

 

[Erel]

Do you plan to run your app on rooted devices only?

 

[FabioG]

Do you plan to run your app on rooted devices only?

Yes, my application works only on rootted devices

 

[Erel]

As far as I know, there is no way to run your application with root access. You can run a shell script with the Shell command (search the forum) that will copy the database to an accessible folder.

 

[FabioG]

As far as I know, there is no way to run your application with root access. You can run a shell script with the Shell command (search the forum) that will copy the database to an accessible folder.

I know the methods, but with the shell script the application does not work on all devices that did not install sqlite3

 

[Erel]

As I wrote above you will need to copy the database and then open it from your application.

 

[FabioG]

As I wrote above you will need to copy the database and then open it from your application.

I solved it by making a copy in DirInternalCache and replacing the original after the change.

thank you very much :sign0098: