Android Example SQLiteLight four simple SQLite projects

IslamQabel · Apr 28, 2014

Dear Klaus...
I used your program and i changed the header successfully, but i want to change later in the following line as follows
Query = "SELECT ID, Name As [Name], ID Number As [ID Number], City As [City] FROM persons"....so how to eliminate First name, last name and city ???
second issue, when i open the application for the first time a list is shown with database about US presidents.......I want an empty table without any data......How to do it?

Is the displayed US presidents table created from the first program you post?
I am really so confused and i want to understand
Thanks a lot

klaus · Apr 28, 2014

Sorry, but without seeing your code it's impossible to give you a concrete answer !

I tried to change Persons.db to another file name but an error occurs displayed also why?

What error do you get ?
You need to be more precice in your questions.

IslamQabel · Apr 28, 2014

Ok kalus, i will summarize my questions in the following points...
First of all, i am new in using SQL and try to answer my questions one by one.....with my apology for asking a lot of questions

Part 1 of Questions for the 1st attached program:

The ID is not updated automatically, I just click "Add" then add all data and then click on "Update" and then to add another name i click on "Add".....message appears "Entry already exits"...can not add any new entry...Why?
How to build a data base file have another name not persons.db as well as other parameters not like "first name,last name,....."?

Part 2 of Questions for the 2nd attached program:

When i run it, i see a table that has entered data for US presidents...why?
If i want to show an empty table, how to do that?
How to build a data base file have another name not persons.db as well as other parameters not like "first name,last name,.....?

Thanks

klaus · Apr 28, 2014

Have you already tried to modify these projects ?
If yes, what and how did you modify it ?

1.1 To add a new entry, modify the data in the EditTexts and click on Add, the new data will be added to the database.
If you want to update an entry, select it, modify it and click on Update.
In my programs I minimize the number of clicks to execute functions.
Why should I clicking on Add, modify the data in the EditTexts and then need to click Update.
1.2 You need to modify the program to fit your needs!
- replace all persons.db by your name
- modify the CreateDataBase routine to create your database
- modify everywhere the column names by yours.

2.1 As explained in the first post, this program has a default database to let the users 'play' with the filter functions.
2.2 In Activity_Create remove File.Copy(File.DirAssets, "persons.db", File.DirInternal, "persons.db")
2.3 You can define a database in another SQLite program and copy it to the project like persons.db.
Or create a CreateDatabase routine where you define your database like in SQLiteLight1.
You need to modify all database names, table names and column names everywhere in the program.

These two simple example programs are intended to show beginners what can be done and how.
The names of database, table and columns were intentionally 'hardcoded', instead of using variables, to keep the code easier to read.
The SQLiteDB example seemed to be too complicated for beginners therefore these two 'simple' programs.

netchicken · Apr 29, 2014

Hi Klaus, thats so much for your code, its great to update skills

I have a database calls class and am using functions to return the cursor. It works OK, but I saw in your example how you closed off the cursor after using it.

I can't do this with a function, as of course the return ends the code run, is this an issue with performance not closing off?

B4X:

Sub LoadAllEntries As Cursor
Dim cursor1 As Cursor
cursor1 = SQL1.ExecQuery("SELECT * FROM bikefit")
'cursor1.Close   
Return cursor1
End Sub

klaus · Apr 29, 2014

In the example programs I close the Cursor because I don't need it anymore.
What are you doing with the cursor which is returned by LoadAllEntries ?
I prefer using the Cusor directly in a routine to fill displays or other treatments, and therefore don't need it anymore and close it.
If I need other data from the database I launch a new query.
I don't know if there are any concerns not closing a Cursor.

IslamQabel · Apr 29, 2014

Dear Klaus
Thanks for your reply

I succeeded to to change and create my own database ( 5 columns ) and use the the program as you told me.....
Now, i am asking how to create database file in this program to be created n file folder in my project?
After creating it, i will replace your database file persons.db by my file Qabel.db....for the first project SQLite1

klaus · Apr 30, 2014

Not sure I understand what exactly you are asking for.
- If you have created a database with any other program on the PC you can add this one in the Files folder of your project (DirAssets folder)
and in the program copy it to another folder on the device. Like persons.db in SQLiteLight2.
- Or can create the database once in the program and use it like in SQLiteLight1.

IslamQabel · May 1, 2014

I know that i created a database by SQLight1....Now i want to create database file (.db) in file folder of my project , Later i will use this file in another project Got it?Just create it in SQLight1
Thanks

klaus · May 1, 2014

Where and with what name did you initialized the database file in SQLight1 ?
If you save it in DirRootExternal or another accessible folder you can find it on the device and either recuperate it or access it from the other program.
If, in SQLight1, you used SQL1.Initialize(File.DirInternal, "persons.db", True), I'm afraid you won't find it because DirInternal is a 'private' folder for the program and not accessible from other programs.
You might use SQL1.Initialize(File.DirRootExternal, "mydatabase.db", True)

IslamQabel · May 1, 2014

I already can not find the file in my device, so i will try your suggestion

Thanks

klaus · May 4, 2014

A third project has been added.

- SQLiteLight3
Same as SQLiteLight2, all functions are the same.
The differences are the database path, database name, table name, columb number, column names, column alias names and column data types are variables instead beeing hard coded.
It allows also to generate a new database by:
- changing in Globals the values of the variables listed above
- in Activity_Create
-- comment this line : 'File.Copy(File.DirAssets, SQLDateBaseName, SQLDataBasePath, SQLDateBaseName)
-- uncomment this line : CreateDataBase

Mahares · May 4, 2014

There is nothing light about what Klaus contributes to the forum. He is a powerhouse.
A very minuscule observation: Since you assign variables to all the database objects, this line in Activity_resume:

B4X:

SQL1.Initialize(File.DirInternal, SQLDateBaseName, True)

should be:

B4X:

SQL1.Initialize(SQLDataBasePath, SQLDateBaseName, True)

klaus · May 4, 2014

Thank you Mahares, I missed that one.
Project in the first post updated.

Watchkido1 · May 9, 2014

@ westerwäller: So geht's auch:
@ Klaus: Danke für diese einfache Version.

B4X:

Sub UpdateSelectedItem

    Dim Query As String
    Dim Curs As Cursor
  
    If IDList.Size = 0 Then
        ToastMessageShow("No Data",True)
    Else
  
        Query = "SELECT FirstName, LastName FROM persons WHERE ID = " & IDList.Get(CurrentIndex)
        Curs = SQL1.ExecQuery(Query)
        Curs.Position = 0
        lblSelectedItem.Text = Curs.GetString("FirstName") & " " & Curs.GetString("LastName")
        Curs.Close
    End If
  
End Sub

ginsh · Jul 1, 2014

Hi,

Any one please help me.
In the below query, how can I get the only the clicked cell value to lblSelected Item.text.

B4X:

Sub UpdateSelectedItem
 
    Dim Query As String
    Dim Curs As Cursor
 
    If IDList.Size = 0 Then
        ToastMessageShow("No Data",True)
    Else
 
        Query = "SELECT FirstName, LastName FROM persons WHERE ID = " & IDList.Get(CurrentIndex)
        Curs = SQL1.ExecQuery(Query)
        Curs.Position = 0
        lblSelectedItem.Text = Curs.GetString("FirstName") & " " & Curs.GetString("LastName")
        Curs.Close
    End If
 
End

klaus · Jul 1, 2014

Like this:

B4X:

'Routine from the DBUtils demo program
Sub wbvTable_OverrideUrl (Url As String) As Boolean
    'parse the row and column numbers from the URL
    Dim values() As String
    values = Regex.Split("[.]", Url.SubString(7))
    Dim col, row As Int
    col = values(0)
    row = values(1)
    CurrentIndex = row
'    UpdateSelectedItem
    UpdateSelectedItem1(col)
    Return True 'Don't try to navigate to this URL
End Sub

Sub UpdateSelectedItem1(col As Int)
    Dim Query As String
    Dim Curs As Cursor
  
    Query = "SELECT * FROM persons WHERE ID = " & IDList.Get(CurrentIndex)
    Curs = SQL1.ExecQuery(Query)
    Curs.Position = 0
    lblSelectedItem.Text = Curs.GetString2(col)
    Curs.Close
End Sub

ginsh · Jul 5, 2014

Hi Klaus,

Thanks for your reply. One more problem i am facing.

I have added a search option to SQLite3 example. Below is the code. The below code searches the entire table for a value and returns the matching rows. But when i click on one the cells of the searched rows, it returns the value of the cell of the original row on the same position on lblSelectedItem.Text not the searched row value. What could be wrong and how can i get the searched row value.

B4X:

Sub Search

Dim SeaVal As String
SeaVal = EditText1.Text.Trim

Dim Query As String
  
    Query = "SELECT "
    For i = 0 To ColNumber - 1
        If i < ColNumber - 1 Then
            Query = Query & ColNames(i) & " As [" & ColAliasNames(i) & "], "
        Else
            Query = Query & ColNames(i) & " As [" & ColAliasNames(i) & "] "
        End If
    Next
    Query = Query & " FROM " & SQLTabelName & " WHERE " & ColNames(1) & " LIKE " & "'%"  & SeaVal & "%'"  & " OR  " & ColNames(2) & " LIKE " & "'%"  & SeaVal & "%'"  & " OR  " & ColNames(3) & " LIKE " & "'%"  & SeaVal & "%'"  & " OR  " & ColNames(4) & " LIKE " & "'%"  & SeaVal & "%'"  '"
  
  
  
    wbvTable.LoadHtml(ExecuteHtml(SQL1, Query, Null, 0, True))
    ReadDataBaseIDs

End Sub

klaus · Jul 5, 2014

The problem is ReadDataBaseIDs.
This routine loads the IDs of the rows according to the filter included in the program.
You change the filter so calling ReadDataBaseIDs gives wrong results.
You need to either modify ReadDataBaseIDs to your needs or read the IDs specifically in your routine.

The principle in the original application is to filter only on existing entries.
The Spinners in the Filter activity are filled only with existing entries.

ginsh · Jul 5, 2014

Hi Klaus,

Can you help me please. i tried a lot and couldn't find a solution. Thanks in advance.

Android Example SQLiteLight four simple SQLite projects

Attachments

Active Member

Expert

Active Member

Expert

Active Member

Expert

Active Member

Expert

Active Member

Expert

Active Member

Expert

Expert

Expert

Active Member

Member

Expert

Member

Expert

Member

Similar Threads