Android Tutorial SQL tutorial

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

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

    eps Well-Known Member Licensed User

    Okay... I have a database, all is well.... I feel the need to change the database structure. Is there a way to handle a DB alteration? At the moment users get a DB error and have to work out that they should delete data and start again...

    What's the most elegant solution?

    The only one I can think of is to have a variable which is set to the App version when the DB last changed, i.e. 0.8. Then compare this with the version, if it's different, the database has changed, 0.9 for instance. Then when the DB doesn't change make them the same, so that normal service is resumed..

    Although I'm sure someone will come along and open my eyes to a much better solution!!
     
  2. Erel

    Erel Administrator Staff Member Licensed User

  3. eps

    eps Well-Known Member Licensed User

    Hi Erel


    I thought that might be the answer.. I was slightly hoping to avoid having to pick up another Lib, but will do so, thanks!
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    DBUtils is a code module, not a library. This allows you to modify it if needed.
     
  5. MaxApps

    MaxApps Active Member Licensed User

    How to delete an index?
    How to modify an index?

    Kind regards
    Jmich
     
  6. Erel

    Erel Administrator Staff Member Licensed User

  7. MaxApps

    MaxApps Active Member Licensed User

    Does not help.
    I have tried what is says, but get a syntax error

    SQL1.ExecNonQuery("DROP INDEX table1 1")

    or

    SQL1.ExecNonQuery("DROP INDEX table1.1")

    Kind regards
    Jmich
     
  8. Erel

    Erel Administrator Staff Member Licensed User

    How did you create the index? What is the index name?
     
  9. MaxApps

    MaxApps Active Member Licensed User

    SQL1.ExecNonQuery("CREATE TABLE table1 (item TEXT, brand TEXT, model TEXT, seno TEXT, barc TEXT, date TEXT, price INTEGER)")

    I would like to be able to delete and modify each line of data
     
  10. Erel

    Erel Administrator Staff Member Licensed User

    This is not related to an index. You should use DELETE FROM. It is recommended to create a primary key when you create the table.
     
  11. MaxApps

    MaxApps Active Member Licensed User

    So I should do like this:

    SQL1.ExecNonQuery("CREATE TABLE table1 (ID INTEGER, item TEXT, brand TEXT, model TEXT, seno TEXT, barc TEXT, date TEXT, price INTEGER)")

    And then
    SQL1.ExecNonQuery("DELETE FROM table1 WHERE ID=X")

    X= the index number, that should be deleted

    ?

    And what about REPLACE?


    Kind regards
    Jmich
     
  12. Erel

    Erel Administrator Staff Member Licensed User

    The query should be:
    Code:
    CREATE TABLE table1 (ID INTEGER PRIMARY KEY, item TEXT, brand TEXT, model TEXT, seno TEXT, barc TEXT, date TEXT, price INTEGER)
    Yes.

    There are many general SQL and SQLite tutorials available online which will help you get started.
     
  13. darkuni

    darkuni Member Licensed User

    What is everyone's preferred method of checking for the existence of a table? I don't want to drop and re-add the table each time it's run - and there won't necessarily be any data in there if the table already exists.

    Example:

    The app has a login. They may elect to store the login - or not. The login username and password will be stored if they elect to do so.

    So I have several possibilities to handle:

    1) First run: no database, no table, no data.
    2) Subsequent run: no data (because they didn't elect to store it)
    3) Subsequent run: data retrieved (because they elected to store it)

    First one is kinda handled - if the database isn't there - it will create it. BUT, I don't know it was done (unless I missed a return value from initialize that says it was created). So I don't know if I need to create the table or not.

    Let's say they run it, login but don't save. I could query the table for data - but if it is empty ...? I don't know if there simply was no table - or if there was just no data.

    Questions:

    Can I tell if Initialize created the database or just opened it?
    Can I tell if a table exists or not?

    If I cannot, is TRY/CATCH my best means of handling this? If I try to query a table that doesn't exist, can I CATCH the exception?

    Thanks!
     
  14. darkuni

    darkuni Member Licensed User

    Apparently, the answer here is "no". The initialize method returns void - so there isn't a value passed back.
     
  15. Erel

    Erel Administrator Staff Member Licensed User

  16. darkuni

    darkuni Member Licensed User

  17. paskuale

    paskuale Member

    Hi Erel great article ;) if my app start all from a csv file ? How can read and then insert data from csv in sqlLite db ? thanks
     
  18. hdtvirl

    hdtvirl Active Member Licensed User

  19. iabros

    iabros New Member

    Generate Error "InputStream1 Not Initialized"

    InputStream1 = File.OpenInput(File.DirAssets, "smiley.gif")
    The above statement Generate Error "InputStream1 Not Initialized"
     
  20. Erel

    Erel Administrator Staff Member Licensed User

    Are you sure that this line raised the error?
     
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