New to basic4android and need Database information

[CD Tom]

I have been using MS Access for many years so I know a little about databases. I very new to android programming and need help. I'm trying to create a program that I can import data from my MS Access database into an android app that will allow my to search for an individual and display some information. I don't have any idea where to start, I see lots of tutorials on the forum, is there a tutorial that would show my how to set up a database and load it with data from my Access database.
Thanks for any help.

 

[mc73]

If there is no true need to read directly from msAccess, I would suggest exporting the accessDB to csv and then importing to sqlite using various utilities, as for e.g. sqliteManager for firefox. This way, you could then read directly sqlite using dbUtils or straight sqlite queries from b4a.
Another way is to create a pseudo-server on the pc hosting the accessDB and then share data through network with your android device. This requires socket and aStreams handling.
Finally, I think I've seen a library for mySQL reading, here in this forum, perhaps it could be used for access recordsets too, though not sure.

 

[tboy]

This may not be what your after, but there is a tutorial by Erel,

http://www.b4x.com/forum/basic4android-getting-started-tutorials/6736-sql-tutorial.html#post39108

also look at

http://www.b4x.com/forum/basic4android-share-your-creations/7284-access-converter-sqlite3-4.html

There is an Access to SQLite3 converter if your willing to change.

Hope it helps.

 

[CD Tom]

I think your suggestion is what I'm looking for it's easy to create a csv file from the access database. Now it's just learning how to import that into a android app. Like I mentioned this will be my first android app so this is going to be trial and error.

I will be looking at the two sql links and see how they look. Any further ideas and help would be appreciated.

Thanks

 

[CD Tom]

Now I'm going to show my newbee status, in the one tutorial is says to add the SQL reference library, and of course I can't find how to do that. As usual I need help again.
Sorry for being so slow on this.

 

[NJDude]

On the right side panel on the IDE you'll see a series of tabs at the bottom, click on LIBS, then search for the one you need and put a check on it.

 

[CD Tom]

Ok after looking more I did find the library tab. I good for now until I run into another problem.
Thanks

 

[CD Tom]

Ok, I've downloaded the SQLite and have created the database I want to use. I have exported the data from the Access Database to a csv file. Now my question is how do I get the database into the program and how do I update it with the csv file. What will happen is about once a month new people are added to the Access database so I will want to be able to clear the old database and input the new csv file. the database name is tcmembers and consists of first name, last name, address, city, state, zipcode, phone, datejoined, status. I want them to be able to search the last name I think that is setup as a spinner but would like it to show the last name followed by a , and first name. I can probably figure this all out what I really need is to know how to get the database update.
I know this sounds easy to everybody but as a newbe it isn't. Thanks for all your help.

 

[Dman]

I am a 6 month newbie but I have done this. The code below is a matrix that I have set up on my app. When one clicks the Import button, it checks to see if there is data in the table and if there is, it will prompt the user to overwrite. I'm sure there are better ways to do this but like I said I am a newbie also but it works.

   '***Check to see if the matrix is already loaded***
   matrixdata = Main.SQL1.ExecQuerySingleResult("SELECT count(*) FROM matrix")

   '***If it is, ask if we want to overwrite it***
   If matrixdata >1 Then
      Dim Answ As Int
         Answ = Msgbox2("Overwrite?", _
         "Matrix already exists. If you select YES, all changes will be overwritten.", "Yes", "", "No", Null)
            If Answ = DialogResponse.POSITIVE Then
               Try
                  Main.SQL1.ExecNonQuery("DELETE FROM matrix WHERE extra3 = 1") 'extra3 is the matrix number, there will eventually be more than one
               Catch
               End Try
                  Dim su As StringUtils
                  Dim Table As List
                     Table = su.LoadCSV(File.DirAssets, "matrix.csv", ";")
                  Dim Table2 As List
                  Dim Items() As String
                     Table2.Initialize
                        For i = 0 To Table.Size - 1
                            Items = Table.get(i)
                            Dim m As Map
                               m.Initialize
                               m.Put("panel", Items(0))
                                m.Put("cat", Items(1))
                                 m.Put("size", Items(2))
                               m.Put("price", Items(3))
                               m.Put("alum", Items(4))
                                m.Put("access", Items(5))
                                 m.Put("largepanel", Items(6))
                               m.Put("oversized", Items(7))
                               m.Put("oversizednum", Items(8))
                                m.Put("extra1", Items(9))
                                 m.Put("extra2", Items(10))
                               m.Put("extra3", Items(11))
                                  Table2.Add(m)
                        Next

                  DBUtils.InsertMaps(Main.SQL1, "matrix", Table2)
                  Msgbox("Overwritten","")
            Else
               Return
            End If
   Else
      Dim su As StringUtils
      Dim Table As List
         Table = su.LoadCSV(File.DirAssets, "matrix.csv", ";")
      Dim Table2 As List
      Dim Items() As String
         Table2.Initialize
            For i = 0 To Table.Size - 1
                Items = Table.get(i)
                Dim m As Map
                   m.Initialize
                   m.Put("panel", Items(0))
                    m.Put("cat", Items(1))
                     m.Put("size", Items(2))
                   m.Put("price", Items(3))
                   m.Put("alum", Items(4))
                   m.Put("access", Items(5))
                     m.Put("largepanel", Items(6))
                   m.Put("oversized", Items(7))
                   m.Put("oversizednum", Items(8))
                    m.Put("extra1", Items(9))
                     m.Put("extra2", Items(10))
                   m.Put("extra3", Items(11))
                      Table2.Add(m)
            Next

         DBUtils.InsertMaps(Main.SQL1, "matrix", Table2)
   End If

 

[CD Tom]

I'll give that a try but have a question on the first line what is Main when I type that in I don't get the dropdown list don't we have to Dim those somewhere?
Like I said I'm real new to this.

 

[CD Tom]

Well after some playing around I get this to run but I get an error when I try and do the update. Here's the code

Sub btnupdate_click
   Dim su As StringUtils
   Dim table As List
   Dim table2 As List
   Dim items() As String
   Dim Answ As Int
   Dim m As Map
   mbrdata = SQL1.ExecQuerySingleResult("Select count(*) from mbrs")
   If mbrdata > 1 Then
      Answ = Msgbox2("Overwrite?", "Load new members? If you select YES the new members will be loaded.", "Yes", "", "No", Null)
      If Answ = DialogResponse.POSITIVE Then
         Try
            SQL1.ExecNonQuery("Delete from mbrs") ' clear the members table
         Catch
         End Try
            table = su.LoadCSV(File.DirAssets, "MBRS.csv", ";")
            table2.Initialize
            For i = 0 To table.Size - 1
               items = table.Get(i)
                  m.Initialize
                  m.Put("mbr_number", items(0))
                  m.Put("mbr_first_name", items(1))
                  m.Put("mbr_last_name", items(2))
                  m.Put("address_1", items(3))
                  m.Put("city", items(4))
                  m.Put("state", items(5))
                  m.Put("zip_code", items(6))
                  m.Put("e_mail", items(7))
                  m.Put("Home_phone", items(8))
                  m.Put("datej", items(9))
                  m.Put("description", items(10))
                  table2.Add(m)
            Next
            DBUtils.InsertMaps(SQL1, "mbrs", table2)
            Msgbox("Overwritten","")
      Else
         Return
      End If
   Else
      table = su.LoadCSV(File.DirAssets, "mbrs.csv", ";")
      table2.Initialize
       For i = 0 To table.Size -1
               items = table.Get(i)
                  m.Initialize
                  m.Put("mbr_number", items(0))
                  m.Put("mbr_first_name", items(1))
                  m.Put("mbr_last_name", items(2))
                  m.Put("address_1", items(3))
                  m.Put("city", items(4))
                  m.Put("state", items(5))
                  m.Put("zip_code", items(6))
                  m.Put("e_mail", items(7))
                  m.Put("Home_phone", items(8))
                  m.Put("datej", items(9))
                  m.Put("description", items(10))
                  table2.Add(m)
            Next
       DBUtils.InsertMaps(SQL1, "mbrs", table2)
   End If
End Sub

when I reach the line m.put("mbr_first_name", items(1)) it give me an error about the length = 1 the mbr_first_name is a text field,
another question when I create the database I've set it to dirdefaultexternal when I look at the device I'm using (nexus 7) I can't locate the database. When you direct the file to dirdefaultexternal where does it go?
Thanks for any help

 

[Dman]

Main is the module that I used "Dim SQL1 as SQL" in the process globals

 

[CD Tom]

Ok, I've set up the build database and have put in what I've called an update button. When I run the code I get an error on the second m.put statement that says the length = 1 the code is as follows

Sub btnupdate_click
   Dim su As StringUtils
   Dim table As List
   Dim table2 As List
   Dim items() As String
   Dim Answ As Int
   Dim m As Map
   mbrdata = SQL1.ExecQuerySingleResult("Select count(*) from mbrs")
   If mbrdata > 1 Then
      Answ = Msgbox2("Overwrite?", "Load new members? If you select YES the new members will be loaded.", "Yes", "", "No", Null)
      If Answ = DialogResponse.POSITIVE Then
         Try
            SQL1.ExecNonQuery("Delete from mbrs") ' clear the members table
         Catch
         End Try
            table = su.LoadCSV(File.DirAssets, "MBRS.csv", ";")
            table2.Initialize
            For i = 0 To table.Size - 1
               items = table.Get(i)
                  m.Initialize
                  m.Put("mbr_number", items(0))
                  m.Put("mbr_first_name", items(1))
                  m.Put("mbr_last_name", items(2))
                  m.Put("address_1", items(3))
                  m.Put("city", items(4))
                  m.Put("state", items(5))
                  m.Put("zip_code", items(6))
                  m.Put("e_mail", items(7))
                  m.Put("Home_phone", items(8))
                  m.Put("datej", items(9))
                  m.Put("description", items(10))
                  table2.Add(m)
            Next
            DBUtils.InsertMaps(SQL1, "mbrs", table2)
            Msgbox("Overwritten","")
      Else
         Return
      End If
   Else
      table = su.LoadCSV(File.DirAssets, "mbrs.csv", ";")
      table2.Initialize
       For i = 0 To table.Size -1
               items = table.Get(i)
                  m.Initialize
                  m.Put("mbr_number", items(0))
                  m.Put("mbr_first_name", items(1))
                  m.Put("mbr_last_name", items(2))
                  m.Put("address_1", items(3))
                  m.Put("city", items(4))
                  m.Put("state", items(5))
                  m.Put("zip_code", items(6))
                  m.Put("e_mail", items(7))
                  m.Put("Home_phone", items(8))
                  m.Put("datej", items(9))
                  m.Put("description", items(10))
                  table2.Add(m)
            Next
       DBUtils.InsertMaps(SQL1, "mbrs", table2)
   End If
End Sub

the line I get the error on is m.put("mbr_first_name", items(1)) it's like the items(1) isn't long enough to hold the mbrs first name. I do have another questions
Here's the code where I build the database

Sub Activity_Create(FirstTime As Boolean)
   'Do not forget to load the layout file created with the visual designer. For example:
   'Activity.LoadLayout("Layout1")
   Activity.LoadLayout("Members")
   If FirstTime Then
      SQL1.Initialize(File.DirDefaultExternal,"Members.db",True)
   End If
   CreateTables
      
End Sub
Sub CreateTables
   SQL1.ExecNonQuery("DROP TABLE IF EXISTS MBRS")
   SQL1.ExecNonQuery("CREATE TABLE MBRS (Mbr_number integer, Mbr_first_name text, Mbr_Last_name text, Address_1 text, City text, State text, Zip_code text, e_mail text, Home_phone text, DateJ text, Status text)")
End Sub

Where does the database get built, in the code SQL1.Initialize(file.dirdefaultExternal,"members.db",true) where exactly does the database end up. I've tried looking on my test device (nexus 7) but don't seem to find a members.db anyplace.

Once again thanks for any help.

 

[klaus]

I suggest you to post your project as a zip file (IDE menu Files / Export As Zip) so we could test it.

Best regards.

 

[Dman]

There ya go. Now you have someone that knows what they are doing.

I do know that mbr_first_name is not the same as Mbr_first_name. I had that issue also.

 

[CD Tom]

Ok let see if I did this correct.

 

[Mahares]

Hi Tom:
1. In your SQL statement you have a field called Status. That should be Description. Below is the corrected:

SQL1.ExecNonQuery("CREATE TABLE MBRS (Mbr_number integer, Mbr_first_name text, Mbr_Last_name text, Address_1 text, City text, State text, Zip_code text, e_mail text, Home_phone text, DateJ text, Description text)")

2. The text file you are importing is a comma delimited text file, but you use semi-colon for separator:

table = su.LoadCSV(File.DirAssets, "MBRS.csv", ";")

Should be:

table = [su.LoadCSV(File.DirAssets, "MBRS.csv", ",")

You have 2 lines like that.
3. The database will be located in the internal memory under: Android\data\b4a.example\files\Members.db
I tested and it works.

 

[CD Tom]

Thank you, that seemed to work. But when I looked into the android/data I didn't find and b4a.example folder. When I followed the code through it worked.

Now I will work on displaying the data when the user select the spnLName

Question: does case make a difference?

Thanks again

 

[Mahares]

1. Case is important.
2. The path may be different depending on the package name you gave the project. It will be something like:
Android\data\yourpackagename\files\Members.db
3. To get you started and give you an idea to populate the last name spinner with last names:

Dim Cursor1 As Cursor     'in sub global
Dim txt As String                'in sub global
Dim i as int

Then below your code line: DBUtils.InsertMaps(SQL1, "mbrs", table2) you can add the below code:

txt="SELECT Mbr_Last_name FROM mbrs"
Cursor1=SQL1.ExecQuery(txt)
For i=0 To Cursor1.RowCount-1
   Cursor1.Position=i
   spnLName.Add(Cursor1.GetString("Mbr_Last_name"))  'populates the last name spinner
Next

 

[CD Tom]

No matter what I do I can't find the database in my nexus anyplace. It looks like it loads the data but when I run the code to populate the spinner I get an error that mrb_last_name mbr_first_name can't be found. Thank you for being patient with me, some day I'll learn this.

Thanks again.