SQLite Syntax

  1. 2 months ago

    Mike W

    is not verified Nov 10 Durban, South Africa

    I always seem to vave problems with the syntax for SQLite transactions. Thanks to you guys I've got the hang of SELECT operations. Now I'm looking for for the UPDATE and DELETE syntax.
    For UPDATE I want to be able to change a single record identified by the ID, but the ID would be cited by reference to a TextField which contains it -
    UPDATE [tablename] [field names] WHERE [ID = TF.text]
    For DELETE I want to delete the entire row referencing the record in the same way as above.

    What I find confusing is where to put the apostrophes and inverted commas.

    Also, how important is the db.Commit statement ??

  2. Jean-Yves P

    Nov 10 Pre-Release Testers, Xojo Pro Europe (France, Besançon)
    Edited 2 months ago

    easy good way to learn sql online : https://www.w3schools.com/sql/default.asp

    easy way to test live your sql queries online : http://sqlfiddle.com/

  3. Emile S

    Nov 10 Europe (France, Strasbourg)

    Read there too:
    https://www.sqlite.org/index.html

    Nota: Jean-Yves links are very good (especially https://www.sqlite.org/index.html )

  4. Tim S

    Nov 10 Canterbury, UK

    @Mike W For UPDATE I want to be able to change a single record identified by the ID, but the ID would be cited by reference to a TextField which contains it -
    UPDATE [tablename] [field names] WHERE [ID = TF.text]
    For DELETE I want to delete the entire row referencing the record in the same way as above.

    What I find confusing is where to put the apostrophes and inverted commas.

    Also, how important is the db.Commit statement ??

    What apostrophes and inverted commas?

    A simple UPDATE example might be:

    update mytable set myfield1=33, myfield2=44 where id=27

    If the id is in a textfield, you might build an sql string as in:

    Dim sql as string, dbh as sqlitedatabase
    
    sql = "update mytable set myfield1=33, myfield2=44 where id=" + clong (mytxtfield.text)
    
    dbh.SQLExecute (sql)

    (I've omitted code to open the db and check for errors.)

    This assumes that myfield is guaranteed to only contain a number, you may wish to read the value from the field and check it before generating the sql. If the values I've used (33 and 44) come from elsewhere then modify the sql generating statement as appropriate.

    You don't need the commit unless you have started a transaction. In SQLite, all single sql calls like the above are their own transaction.

  5. Emile S

    Nov 10 Europe (France, Strasbourg)
    Edited 2 months ago
     Dim sql as string, dbh as sqlitedatabase
      
      sql = "update mytable set myfield1=33, myfield2=44 where id=" + clong (mytxtfield.text)

    You cannot pass a clong [Klong ;-)] to a String variable. Use instead:

     Dim sql as string, dbh as sqlitedatabase
      
      sql = "update mytable set myfield1=33, myfield2=44 where id=" + mytxtfield.text
  6. Tim S

    Nov 10 Canterbury, UK

    Ah, sorry, I meant to write:

    sql = "update mytable set myfield1=33, myfield2=44 where id=" + clong(mytxtfield.text).ToText
  7. Emile S

    Nov 10 Europe (France, Strasbourg)
    Edited 2 months ago

    But (at first), I really liked Klong (phonetically) until I realisez it is CLong ;).

    Thank you for the smile.

  8. Emile S

    Nov 10 Europe (France, Strasbourg)

    @Tim S clong(mytxtfield.text).ToText

    BTW: do you really need to change the text contents to a long, then change it back to text ?

  9. Jean-Yves P

    Nov 10 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    @Emile S BTW: do you really need to change the text contents to a long, then change it back to text ?

    yes to filter only the integer, and disgard the eventual letters.

  10. Emile S

    Nov 10 Europe (France, Strasbourg)
    Edited 2 months ago

    @Jean-YvesPochez yes to filter only the integer, and disgard the eventual letters.

    There is a Mask Property available to avoid entering non digits characters in TextEdit.

    Edit:
    I asked because I am curious.

  11. Mike W

    is not verified Nov 10 Durban, South Africa

    Thanks chaps - got both working now. I really find this Forum most useful and some of the contributors, who are far more proficient than I can ever hope to be (!), really quite extraordinarily helpful. Mike W

  12. Tim S

    Nov 10 Canterbury, UK

    Good to hear. Emile's suggestion of the SQLite web site is a good one - I find that the docs there are most helpful. There's also an SQLite Users' Mailing List that I lurk on - most of what's there is too arcane for me but there's the occasional nugget - and - they're very helpful there, too.

or Sign Up to reply!