none
Deleted RRS feed

Answers

  • Wendy,

    This is certainly doable - here is a simple example - any bit not sure about then ask.

    I think the stumbling block may be forming the SQL commands using variables in your code.

    The SQL is just a string and can be formed from literals (inside "") and variables,

    below i is replaced with its value, as is Controls.GetTextBocText[TB[i]]

    "INSERT INTO MyTable(Number,Data)VALUES('"+i+"','"+Controls.GetTextBoxText(TB[i])+"');"

    Perhaps put a TextWIndow.WriteLine to see how command is created.

    GraphicsWindow.Show()
    listView = LDDataBase.AddListView(GraphicsWindow.Width,GraphicsWindow.Height/2)
    Shapes.Move(listView,0,GraphicsWindow.Height/2)

    'Set a database file - it is created if it doesn't exist yet
    datFile = Program.Directory+"\database.db"
    database = LDDataBase.AddDataBase(datFile)
    command = "BEGIN TRANSACTION;"
    command = command + "DROP TABLE IF EXISTS MyTable;"
    command = command + "CREATE TABLE MyTable(Id INTEGER PRIMARY KEY, Number NUM, Data TEXT);"
    command = command + "COMMIT;"
    LDDataBase.Command(database,command)  

    'Some TextBoxes
    nBox = 5
    For i = 1 To nBox
      TB[i] = Controls.AddTextBox(30,30*i)
    EndFor

    'Button to put data in TextBoxes into database
    button = Controls.AddButton("Add",300,50)
    Controls.ButtonClicked = OnButtonClicked

    Sub OnButtonClicked
      command = "BEGIN TRANSACTION;"
      For i = 1 To nBox
        command = command + "INSERT INTO MyTable(Number,Data)VALUES('"+i+"','"+Controls.GetTextBoxText(TB[i])+"');"
      EndFor
      command = command + "COMMIT;"
      LDDataBase.Command(database,command)  
      results = LDDataBase.Query(database, "SELECT * FROM MyTable;", listView, "True")
      For i = 1 To Array.GetItemCount(results)
        TextWindow.WriteLine(results[i])
        TextWindow.WriteLine(results[i]["Data"])
      EndFor
    EndSub

    Sunday, May 11, 2014 5:40 PM
    Moderator

All replies

  • Wendy,

    The LDDataBase basically just calls standard SQLite functionality.  This does not include command line tools that come with SQLite3 for example.  Using LDDataBase you would normally store variables using Small Basic variables and enter these from an array in a loop as you suggest.  The SQLite lines can be created sequentially between BEGIN TRANSACTION; and COMMIT;.  See the sample LDDataBase.sb that come with LitDev extension.

    I don't think SQLite support SET or therefore @variables.

    http://stackoverflow.com/questions/7739444/declare-variable-in-sqlite-and-use-it

    Sunday, May 11, 2014 9:16 AM
    Moderator
  • Deleted
    Sunday, May 11, 2014 4:51 PM
  • Wendy,

    This is certainly doable - here is a simple example - any bit not sure about then ask.

    I think the stumbling block may be forming the SQL commands using variables in your code.

    The SQL is just a string and can be formed from literals (inside "") and variables,

    below i is replaced with its value, as is Controls.GetTextBocText[TB[i]]

    "INSERT INTO MyTable(Number,Data)VALUES('"+i+"','"+Controls.GetTextBoxText(TB[i])+"');"

    Perhaps put a TextWIndow.WriteLine to see how command is created.

    GraphicsWindow.Show()
    listView = LDDataBase.AddListView(GraphicsWindow.Width,GraphicsWindow.Height/2)
    Shapes.Move(listView,0,GraphicsWindow.Height/2)

    'Set a database file - it is created if it doesn't exist yet
    datFile = Program.Directory+"\database.db"
    database = LDDataBase.AddDataBase(datFile)
    command = "BEGIN TRANSACTION;"
    command = command + "DROP TABLE IF EXISTS MyTable;"
    command = command + "CREATE TABLE MyTable(Id INTEGER PRIMARY KEY, Number NUM, Data TEXT);"
    command = command + "COMMIT;"
    LDDataBase.Command(database,command)  

    'Some TextBoxes
    nBox = 5
    For i = 1 To nBox
      TB[i] = Controls.AddTextBox(30,30*i)
    EndFor

    'Button to put data in TextBoxes into database
    button = Controls.AddButton("Add",300,50)
    Controls.ButtonClicked = OnButtonClicked

    Sub OnButtonClicked
      command = "BEGIN TRANSACTION;"
      For i = 1 To nBox
        command = command + "INSERT INTO MyTable(Number,Data)VALUES('"+i+"','"+Controls.GetTextBoxText(TB[i])+"');"
      EndFor
      command = command + "COMMIT;"
      LDDataBase.Command(database,command)  
      results = LDDataBase.Query(database, "SELECT * FROM MyTable;", listView, "True")
      For i = 1 To Array.GetItemCount(results)
        TextWindow.WriteLine(results[i])
        TextWindow.WriteLine(results[i]["Data"])
      EndFor
    EndSub

    Sunday, May 11, 2014 5:40 PM
    Moderator
  • LitDev,

    I'm following this thread.  You mentioned a sample litdev database in the extension download.  I don't see that and I can't find any documentation for using it.  Can you tell me where they are? This is a good thread.


    JR

    Monday, May 12, 2014 2:31 AM
    Answerer
  • jricestk,

    The samples come in the LitDev zip along with the extension dll.  The example referred to above is in:

    other-samples/LDDataBase.sb

    Monday, May 12, 2014 8:19 AM
    Moderator