Android Tutorial SQL tutorial

Discussion in 'Tutorials & Examples' started by Erel, Dec 2, 2010.

Thread Status:
Not open for further replies.
  1. nemethv

    nemethv Member Licensed User

    Hello,

    I'm struggling with reading stuff from an sqlite db file that I've already created outside the app. The file doesn't need to be edited/modified/etc in the app.
    Im trying to open the file, but the code SQL1.Initialize(File.DirAssets,"lineinfo.db",False) - fails, I get an errormessage on this when trying to do it saying "database not available". The file is in the files tab. SQL is enabled/ticked.

    The overall aim would be to create a list with information extracted from the SQL file.
    In theory, it would be something like:

    Code:
    Sub Globals

       
    Dim LineList As ListView
       
    Dim SQL1 As SQL
       
    Dim Cursor1 As Cursor ' not sure what to do with this?
    End Sub


    Sub Activity_Create(FirstTime As Boolean)
       
       
    Activity.Color = Colors.white
       
    If SQL1.IsInitialized = False Then 
          SQL1.Initialize(
    File.DirAssets,"lineinfo.db",False' this one fails
       End If   
       
        
       LineList.Initialize(
    "LineList")
       LineList.Visible=
    True:LineList.Clear
       
       LineList.AddSingleLine(SQL1.ExecQuerySingleResult(
    "SELECT DISTINCT LineName FROM lineinfo")) 'dont think this works either, but you get the gist of what i need hopefully...
       
       
        
    Activity.AddView(LineList, 00100%x100%y)
    End Sub
    At the end I'd like the list to return results like "Line A", "Line B"...
    Any thoughts?

    Thank you

    ps. i might not reply until the end of the coming long weekend, but I'll do my best to do so.
     
    Last edited: Apr 5, 2012
  2. Erel

    Erel Administrator Staff Member Licensed User

    You cannot open a database in the assets folder. This is a virtual read-only folder located inside the APK file. You will need to first copy the database to a writable location. You can use DBUtils for that task.
     
  3. nemethv

    nemethv Member Licensed User

    Thanks! Worked :)
     
    Last edited: Apr 5, 2012
  4. zekigultekin

    zekigultekin Member Licensed User

    How can add SQL library to my basic4android project

    Dear Erel,
    I want use SQL in my project. When I define Dim SQL1 as SQL then I cannot see SQL reference. ;)

    How can I add SQL library reference to my project.

    Best regards,
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    You should go the Libraries tab (right side of the IDE) and check SQL.
     
  6. zekigultekin

    zekigultekin Member Licensed User

    I found it and solved my problem. Thank you very much.
     
  7. McJaegs

    McJaegs Member Licensed User

    So I have my database created, but where in my project folder do I need to place it in order for it to be accessed?

    I am also trying to populate a spinner with unique values from one column of the database. What would be the code to do that? Right now I have this

    Cursor1 = RadioSQL.ExecQuery("SELECT DISTINCT Genre From Chicago")

    If CitySpinner.SelectedItem = "Chicago" Then
    GenreSpinner.AddAll(Cursor1)
     
    Last edited: May 4, 2012
  8. Erel

    Erel Administrator Staff Member Licensed User

    I recommend you to use DBUtils: Basic4android Search: DBUtils

    It will help you put the database in the correct folder and it also includes an ExecuteSpinner method.
     
  9. McJaegs

    McJaegs Member Licensed User

    Ok, thanks. I have that now, but now when I try to run my program I get the error: java.lang.ClassCastException:android.database.sqlite.SQLiteCursor

    I'm not sure what is causing this.
     
  10. Erel

    Erel Administrator Staff Member Licensed User

    Run your program in Debug mode and check the full error message in the logs.
     
  11. nemethv

    nemethv Member Licensed User

    Hi,

    I'm trying to simplify a piece of code that i've been using.
    The code I'm doing now is:
    Code:
    LocalLineCount = SQL1.ExecQuerySingleResult("SELECT count(DISTINCT LineName) FROM lineinfo Where StationName = '"&whatever&"')
       For i = 1 To LocalLineCount
          LocalLineList.AddSingleLine(SQL1.ExecQuerySingleResult(
    "SELECT DISTINCT LineName FROM lineinfo WHERE LocalLineID = " & i ))
       
    Next
    I was wondering if there is any way to replace the last part (&i) with something auto-incremental because at the moment I have to put zillions of various IDs into my data tables rather than just actually somehow saying, "select the next line after the one you've already selected".
    In other terms, what I'm trying to achieve is that there are certain stations on the underground network that serve more than one line. Rather than having to assign an ID for each line on each station and then call the LocalLineID as above, I want to avoid and automate that process somehow. Is it possible?
    Thanks
     
    Last edited: May 23, 2012
  12. poseidon

    poseidon Member Licensed User

    re

    I couldnt understand exactly... but you can make it one (?)

    LocalLineCount = SQL1.ExecQuerySingleResult("SELECT count(DISTINCT LineName) FROM lineinfo Where StationName = '"&whatever& "' and
    LocalLineID = " & i)

    or something like :

    LocalLineCount = SQL1.ExecQuerySingleResult("SELECT count(DISTINCT LineName) FROM lineinfo Where StationName = '"&whatever& "' and
    LocalLineID > " & i)

    (?)
     
  13. nemethv

    nemethv Member Licensed User

    I'm hoping avoid having having to use IDs in general. What I'd like is that if we know that the distinct count of stations is say 7 and then in a command I say select distinct stations, then I'd get 7 names. Then I'd want to return the first, second, nth. (rather having a prefixed ID value)
     
  14. poseidon

    poseidon Member Licensed User

    Code:
    LocalLineCount = SQL1.ExecQuerySingleResult("SELECT count(DISTINCT LineName) FROM lineinfo Where StationName = '"&whatever&"')


    'forget this    For i = 1 To LocalLineCount
           Cursor=SQL1.ExecQuery("SELECT DISTINCT LineName FROM lineinfo WHERE (LocalLineID > 0 and  LocalLineID <" & LocalLineCount+1 & ")")
    'forget this    Next

    ^so only 2
    SQL will be executed but then you have to make a loop to add at listview

    for i=0 to  Cursor.Count-1
     LocalLineList.AddSingleLine(Coursor.GetString2(
    0))
    next

    for sure is more faster.


    Generic Cursor Sample :
    Code:
    Dim SQLReader As Cursor
       
    Dim i As Int
        SQLReader = SQL1.ExecQuery(
    "SELECT id,Code, Name FROM CONTACTS order by Name")

        
    For i = 0 To SQLReader.RowCount - 1
            SQLReader.Position = i
          
          lstv.AddTwoLines2(SQLReader.GetString(
    "Code"),SQLReader.GetString("Name"),SQLReader.GetString("id"))
        
    Next
        SQLReader.Close
     
    Last edited: May 23, 2012
  15. nemethv

    nemethv Member Licensed User

    Found it

    Code:
    For a = 0 To LineCount -1
       CurrentLineName = SQL1.ExecQuerySingleResult(
    "SELECT DISTINCT LineName FROM LineInfo ORDER BY LineName LIMIT 1 OFFSET " & a)
    Next
     
  16. ltrebuchet

    ltrebuchet Member Licensed User

    Dir.Assets

    My DBFile is included within the Files of my project, but it is not found File.DirAssets.
    What did I miss?
    Thank You
     
  17. ltrebuchet

    ltrebuchet Member Licensed User

    Upper or Lower

    I found out the DBFileName has to be lowercase
    Thanks anyway
     
  18. jeeradate

    jeeradate Member Licensed User

    Question relate to Cursor read data

    From the code of the example as below

    Code:
    Sub LogTable1
        
    Dim Cursor1 As Cursor
        Cursor1 = SQL1.ExecQuery(
    "SELECT col1, col2, col3 FROM table1")
        
    For i = 0 To Cursor1.RowCount - 1
            Cursor1.Position = i
            
    Log("************************")
            
    Log(Cursor1.GetString("col1"))
            
    Log(Cursor1.GetInt("col2"))
            
    Log(Cursor1.GetInt("col3"))
        
    Next
        Cursor1.Close
    End Sub
    The code "Cursor1 = SQL1.ExecQuery("SELECT col1, col2, col3 FROM table1")" read all the records in the table and store in "Cursor1" or read nothing
    or read one record with this code "Cursor1.Position = i"

    Would be kind to advice me?
    :sign0104:
     
  19. klaus

    klaus Expert Licensed User

    Cursor1 = SQL1.ExecQuery("SELECT col1, col2, col3 FROM table1")
    Reads col1, col2 and col3 from all records in the database and stores them in the Cursor1 object.

    Cursor1.RowCount
    Holds the number of records (rows).

    Cursor1.Position = i
    Sets the index of the record (row), the first index = 0.

    Cursor1.GetString("col1")
    Gets the value of column col1 for the given row in Cursor1.Position = i.

    Did you have a look at chapter 3 SQLite Database in the User's Guide ?

    Best regards.
     
    SimpleSimon likes this.
  20. jeeradate

    jeeradate Member Licensed User

    Thank you for your kind response.

    Both Guide is very helpful for me.
    Thank you for your hard work of making these Guide.
    :sign0098:
     
Thread Status:
Not open for further replies.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice