Android Question Database load into listview how?

[schemer]

Ok, I am a noob at SQLite but managed to create a db. Now even though I have read a lot of stuff I am not sure I found the answer. My db will be "read only" and lots of examples are using the db in the more normal way of adding and deleting records. I will not be adding or deleting records nor will the end user. I also want to be sure I can even use the listview instead of maybe a better choice to load my records and data into. But for starters, how do I load the db and fill the list? I have tried the Dim SQL1 AS SQL etc but I am having trouble reading the data into the listview. What I want to do is be able to click on a list item and in turn access related data from another column to further process.
Thanks in advance,
schemer

 

[lemonisdead]

Hello,
Perhaps could you take a look at the excellent DBUtils : https://www.b4x.com/android/forum/threads/dbutils-android-databases-are-now-simple.8475/
and the FlexibleTable: https://www.b4x.com/android/forum/threads/class-flexible-table.30649/

 

[Rob Rendle]

DBUtils is very useful.

However try something like this (some of this was taken from a help guide posted somewhere on these forums)

Note that DBFileDir, DBFileName have been set in my process_globals, like this :-

Dim DBFileName As String                : DBFileName = "rdb.sql"
          Dim DBTableName As String                : DBTableName = "Rec"
          Dim DBFileDir As String                    : DBFileDir = File.DirInternal

Create two subs (ListViewInit & ListViewFill)

And in Activity_Create, loadlayout, initialize SQL1 and then underneath put ListViewInit and ListViewFill (example below)

Then in your Init sub, create the listview (i.e your single line layout color and so on), then in your ListViewFill query your DB and send the string to the ListView.

Example :-

Sub Activity_Create(FirstTime As Boolean)
 
    Activity.LoadLayout("Indian")
 
    SQL1.Initialize(DBFileDir, DBFileName, True)
 
 
    ListViewInit
    ListViewFill
 

End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub ListViewInit
    rlv.TwoLinesLayout.Label.Left = 0
    rlv.TwoLinesLayout.Label.Width = 100%x
    rlv.SingleLineLayout.Label.Color = Colors.White
    rlv.SingleLineLayout.Label.TextColor = Colors.Black
 
End Sub

Sub ListViewFill

    Dim txt As String
    Dim cur As Cursor
 

    txt = "SELECT Name FROM Rec WHERE Nationality ='Indiann" & currname & "'"

    rlv.Clear
    cur = SQL1.ExecQuery(txt)
 
    For i = 0 To cur.RowCount -1
        cur.Position = i
        rlv.AddSingleLine(cur.GetString("Name"))

Next
End Sub

 

[schemer]

Hello,
Perhaps could you take a look at the excellent DBUtils : https://www.b4x.com/android/forum/threads/dbutils-android-databases-are-now-simple.8475/
and the FlexibleTable: https://www.b4x.com/android/forum/threads/class-flexible-table.30649/

I have looked at DBUtils but it seems I didn't understand what to do in my case as most or all the examples were creating a db, writing to a db, or deleting records etc. Of course I am a noob at database stuff so I may have had the answer in there but couldn't see it. For some reason I am struggling with database stuff and have given up in the past on trying to figure it out, but then come back to it and try again as I really want to understand and use them. I think the syntax scared me. Anyhow, I am going to learn this stuff if it kills me.
Thanks,
schemer

 

[schemer]

DBUtils is very useful.

However try something like this (some of this was taken from a help guide posted somewhere on these forums)

Note that DBFileDir, DBFileName have been set in my process_globals, like this :-

Dim DBFileName As String                : DBFileName = "rdb.sql"
          Dim DBTableName As String                : DBTableName = "Rec"
          Dim DBFileDir As String                    : DBFileDir = File.DirInternal

Create two subs (ListViewInit & ListViewFill)

And in Activity_Create, loadlayout, initialize SQL1 and then underneath put ListViewInit and ListViewFill (example below)

Then in your Init sub, create the listview (i.e your single line layout color and so on), then in your ListViewFill query your DB and send the string to the ListView.

Example :-

Sub Activity_Create(FirstTime As Boolean)

    Activity.LoadLayout("Indian")

    SQL1.Initialize(DBFileDir, DBFileName, True)


    ListViewInit
    ListViewFill


End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub ListViewInit
    rlv.TwoLinesLayout.Label.Left = 0
    rlv.TwoLinesLayout.Label.Width = 100%x
    rlv.SingleLineLayout.Label.Color = Colors.White
    rlv.SingleLineLayout.Label.TextColor = Colors.Black

End Sub

Sub ListViewFill

    Dim txt As String
    Dim cur As Cursor


    txt = "SELECT Name FROM Rec WHERE Nationality ='Indiann" & currname & "'"

    rlv.Clear
    cur = SQL1.ExecQuery(txt)

    For i = 0 To cur.RowCount -1
        cur.Position = i
        rlv.AddSingleLine(cur.GetString("Name"))

Next
End Sub

Rob,
Thanks for your most informative reply. I will see how it goes in a little while after I test the code. I really appreciate the jump start. Which example in DBUtils is the closest to what I am trying to figure out? And who knows, in the beginning (only have had B4A for about 3 weeks now) I was using the emulators and the more I learn I realize using a real device makes a big difference. Maybe I was closer than I thought (doubt it) and the emulator hosed me. From now on I am going to use a device. So that leaves the question...What good it the emulator? Does it have its uses like for layout only?
Thanks again,
schemer

 

[Rob Rendle]

In all honesty I found the student b4a course pdf the most helpful, there is a good sql example in there. Also, once you're familiar with that, have a look for erels and klaus's sql examples.

Likewise in reference to not having b4a for long, im still learning myself and I've not used the emulator.

The app I've been working on is heavily sql dependant so if you have any more questions please do ask. I'm no expert mind you

 

[schemer]

In all honesty I found the student b4a course pdf the most helpful, there is a good sql example in there. Also, once you're familiar with that, have a look for erels and klaus's sql examples.

Likewise in reference to not having b4a for long, im still learning myself and I've not used the emulator.

The app I've been working on is heavily sql dependant so if you have any more questions please do ask. I'm no expert mind you

I appreciate the offer and I will be sure to take you up on it. Sometimes you can read and study until you are blue in the face then all of a sudden it clicks. Still waiting for that part. I am working on the example now and have my first error:

Where is the proper location of the db besides adding it to the files tab? The project folder?
Thanks,
schemer

 

[Rob Rendle]

I'm answering this away from my PC so no code snippets.

Make sure in process globals you have -

Dim SQL1 As SQL

Ok so all files you add to b4a will be in DirAssets once you've compiled. However as that is part of the APK you wouldn't be able to use your app to update your db. So its recommended that you run a copy sub so that when your app is opened, your sql db is copied from assets to either the sdcard or the phones internal memory.

Search these forums for 'copy from DirAssets to dirinternal' for some good code snippets.

Rob

 

[schemer]

I'm answering this away from my PC so no code snippets.

Make sure in process globals you have -

Dim SQL1 As SQL

Ok so all files you add to b4a will be in DirAssets once you've compiled. However as that is part of the APK you wouldn't be able to use your app to update your db. So its recommended that you run a copy sub so that when your app is opened, your sql db is copied from assets to either the sdcard or the phones internal memory.

Search these forums for 'copy from DirAssets to dirinternal' for some good code snippets.

Rob

Yeah, I figured out the missing Dim on the SQL1 already...I am down to the editing of the SELECT statement. I will work on it and try to figure the rest out but will be back later if I don't. Thanks for the added info on the copy sub.
schemer

 

[schemer]

Yeah, I figured out the missing Dim on the SQL1 already...I am down to the editing of the SELECT statement. I will work on it and try to figure the rest out but will be back later if I don't. Thanks for the added info on the copy sub.
schemer

I am back. I am getting an error here in the
Sub ListViewFill:

txt = "SELECT * FROM 'my_names'";

B4A version 4.30
Parsing code. Error
Error parsing program.
Error description: Input string was not in a correct format.
Occurred on line: 64
txt = "SELECT * FROM my_names";

What is the correct syntax or is the underscore illegal in a column name?

Thanks,
schemer

 

[eurojam]

schemer,
the select statement should be user like this: "SELECT * FROM tablename;" which selects all colums from the table tablename or "SELECT column1, column2 FROM tablename;"
In your case you wrote: txt = "SELECT * FROM 'my_names'"; you can not quote the tablename like you did: 'my_names'. This will cause an error. Quotes are only used in the WHERE Part if you want to compare strings like: "SELECT * FROM tablename WHERE name='stefan';"

good luck
stefan

 

[Rob Rendle]

It would be good to know what exactly you're trying to display.

Usually the query will be something like

"SELECT * tablenamehere etc etc"

So in the query I had in my code I wanted to display all names in my table called rec where the column nationality had the word Indiann which ended up like this

"SELECT Name FROM Rec WHERE Nationality ='Indiann" & currname & "'"


Sorry I'm away from my PC for the night. I belive in that college b4a course pdf you'll find the exact query that you're looking for.

Rob

 

[schemer]

schemer,
the select statement should be user like this: "SELECT * FROM tablename;" which selects all colums from the table tablename or "SELECT column1, column2 FROM tablename;"
In your case you wrote: txt = "SELECT * FROM 'my_names'"; you can not quote the tablename like you did: 'my_names'. This will cause an error. Quotes are only used in the WHERE Part if you want to compare strings like: "SELECT * FROM tablename WHERE name='stefan';"

good luck
stefan

Good catch stefan. I missed it by this much! Still does not work but it gets me past the error and then I get a blank screen. So I am getting closer. I will do some more reading as I probably am missing something simple.
Thanks,
schemer

 

[schemer]

It would be good to know what exactly you're trying to display.

Usually the query will be something like

"SELECT * tablenamehere etc etc"

So in the query I had in my code I wanted to display all names in my table called rec where the column nationality had the word Indiann which ended up like this

"SELECT Name FROM Rec WHERE Nationality ='Indiann" & currname & "'"


Sorry I'm away from my PC for the night. I belive in that college b4a course pdf you'll find the exact query that you're looking for.

Rob

Rob,
I am just trying to display some text strings. Nothing special, just words. I will look at that college B4A course and do some reading. Thanks for all the help.
schemer

p.s. I found the college b4a course and downloaded it. I had not heard of or seen that before. I will be reading it for sure. Thanks again.

 

[Rob Rendle]

Look for page 22. It has pretty much exactly what you're trying to do.

Glad I could be of help.

Rob

 

[DonManfred]

txt = "SELECT * FROM 'my_names'";

try

txt = "SELECT * FROM 'my_names';"

 

[klaus]

@schemer
Did you have a look at chapter 4 SQLite Database in the User's Guide ?
Did you have a look at the three SQLiteLight examples, links in my signature ?

 

[schemer]

try

txt = "SELECT * FROM 'my_names';"

Hi Don,
Tried that and it still does not work. I just woke up after dreaming about db programming all night so maybe today I will have better luck. My problem may end up in my listview setup. I will drink some coffee and walk the dog, and eat some breakfast before I start today.
Thanks,
schemer

 

[schemer]

@schemer
Did you have a look at chapter 4 SQLite Database in the User's Guide ?
Did you have a look at the three SQLiteLight examples, links in my signature ?

Hi Klaus,
I just looked and I already downloaded the examples the other day but did not check them out as they are still zipped. I will be looking at those and also reading chapter 4 today. I will get this yet.
Thanks,
schemer

 

[schemer]

Well, I am making some progress. It seems I had tried to initialize some stuff like the listview in code so that gave me some trouble. But I am making progress. Now I just have to figure out why I get this error:

android.database.sqlite.SQLiteException: no such table: mytable (code 1): , while compiling: SELECT * From 'mytable';

I may just quickly create a new small database for testing. I also think I have a few questions on making a copy of a database. Can you just copy a database file in Windows and put it in another folder then rename it?
Thanks,
schemer