# New user learning .. experimenting with SQLite

**URL:** <https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851>\
**Category:** Getting Started\
**Created:** [September 5, 2026, 2:07pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851 "2026-09-05T14:07:39Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Marvin\_Clavey](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/43a26b/32.png) [@Marvin\_Clavey](https://forum.xojo.com/u/Marvin_Clavey)\
**Post date:** [September 5, 2026, 2:07pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/1 "2026-09-05T14:07:39Z")

</div>

I am learning and experimenting with SQLite syntax trying to learn the correct way to use it .

Experimenting with substitution … why does one line work properly and the first line give a sql syntax error ?

See question in comment below

 ![Screenshot From 2026-09-05 08-59-07](https://forum.xojo.com/uploads/default/original/3X/b/6/b684a2bdd7b30b2df6f14f9dcadffb291dd82992.png)

---

<div class="post-metadata">

**Author:** ![Anthony\_G\_Cyphers](https://forum.xojo.com/user_avatar/forum.xojo.com/anthony_g_cyphers/32/16862_2.png) [@Anthony\_G\_Cyphers](https://forum.xojo.com/u/Anthony_G_Cyphers)\
**Post date:** [September 5, 2026, 2:18pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/2 "2026-09-05T14:18:08Z")

</div>

IIRC you cannot use a prepared statement placeholder for the table name, only values. The table name is a known, as such you shouldn’t be accepting table names as input from users, so shouldn’t need to be sterilized.

---

<div class="post-metadata">

**Author:** ![Marvin\_Clavey](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/43a26b/32.png) [@Marvin\_Clavey](https://forum.xojo.com/u/Marvin_Clavey)\
**Post date:** [September 5, 2026, 2:20pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/3 "2026-09-05T14:20:18Z")

</div>

But it work fine in the lower line

```auto
rowsFound = app.db.SelectSQL("select * from ? where ? = ? ", fileName , selected , "accname" ) // this line will work fine 

```

---

<div class="post-metadata">

**Author:** ![Anthony\_G\_Cyphers](https://forum.xojo.com/user_avatar/forum.xojo.com/anthony_g_cyphers/32/16862_2.png) [@Anthony\_G\_Cyphers](https://forum.xojo.com/u/Anthony_G_Cyphers)\
**Post date:** [September 5, 2026, 2:21pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/4 "2026-09-05T14:21:24Z")

</div>

That’s interesting. Let me test. According to the SQLite docs, it shouldn’t, and I wouldn’t expect it to in Xojo as table and column names are an integral part of the schema that should be known.

---

<div class="post-metadata">

**Author:** ![Anthony\_G\_Cyphers](https://forum.xojo.com/user_avatar/forum.xojo.com/anthony_g_cyphers/32/16862_2.png) [@Anthony\_G\_Cyphers](https://forum.xojo.com/u/Anthony_G_Cyphers)\
**Post date:** [September 5, 2026, 2:25pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/5 "2026-09-05T14:25:05Z")

</div>

I can’t verify that the second line works. It certainly doesn’t for me.  
 ![image](https://forum.xojo.com/uploads/default/original/3X/5/f/5f3df5561dffbd87746b881fcd945849a2c9d93c.png)

---

<div class="post-metadata">

**Author:** ![Anthony\_G\_Cyphers](https://forum.xojo.com/user_avatar/forum.xojo.com/anthony_g_cyphers/32/16862_2.png) [@Anthony\_G\_Cyphers](https://forum.xojo.com/u/Anthony_G_Cyphers)\
**Post date:** [September 5, 2026, 2:29pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/6 "2026-09-05T14:29:55Z")

</div>

Maybe I’m overlooking something simple as I’m quite tired today:

```xojo
var db as new SQLiteDatabase
db.Connect
var filename as String = "bkaccts"
var selected as Boolean = true
var rows as RowSet = db.SelectSQL("select * from ? where ? = ? ", filename , selected , "accname" )

```

This results in the DatabaseException syntax error on the prepared statement placeholder in my testing.

This, however, works:

```xojo
var db as new SQLiteDatabase
db.Connect
var filename as String = "bkaccts"
var selected as boolean = true
var rows as RowSet = db.SelectSQL("SELECT * FROM bkaccts WHERE ? = ? " , selected , "accname" )

```

---

<div class="post-metadata">

**Author:** ![Marvin\_Clavey](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/43a26b/32.png) [@Marvin\_Clavey](https://forum.xojo.com/u/Marvin_Clavey)\
**Post date:** [September 5, 2026, 2:37pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/7 "2026-09-05T14:37:45Z")

</div>

I am trying to learn to make the reading a writing sub universal to use on any data base

and for me that second line works just fine … why the differance ???

---

<div class="post-metadata">

**Author:** ![Anthony\_G\_Cyphers](https://forum.xojo.com/user_avatar/forum.xojo.com/anthony_g_cyphers/32/16862_2.png) [@Anthony\_G\_Cyphers](https://forum.xojo.com/u/Anthony_G_Cyphers)\
**Post date:** [September 5, 2026, 2:38pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/8 "2026-09-05T14:38:31Z")

</div>

The second line shouldn’t work. How do you know it works? You hit a DatabaseException before the code ever executes the second line. Can you show me the test case where it does function?

---

<div class="post-metadata">

**Author:** ![Marvin\_Clavey](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/43a26b/32.png) [@Marvin\_Clavey](https://forum.xojo.com/u/Marvin_Clavey)\
**Post date:** [September 5, 2026, 2:40pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/9 "2026-09-05T14:40:01Z")

</div>

if I change the first line as shown in the code the program runs with out an error.

---

<div class="post-metadata">

**Author:** ![Anthony\_G\_Cyphers](https://forum.xojo.com/user_avatar/forum.xojo.com/anthony_g_cyphers/32/16862_2.png) [@Anthony\_G\_Cyphers](https://forum.xojo.com/u/Anthony_G_Cyphers)\
**Post date:** [September 5, 2026, 2:40pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/10 "2026-09-05T14:40:15Z")

</div>

Anyway, the point here is that you shouldn’t be accepting table names from users that need to be sterilized using a prepared statement. As long as you’re not doing that, you can use concatenation:

```xojo
var db as new SQLiteDatabase
db.Connect
var filename as String = "bkaccts"
var selected as boolean = true
var rows as RowSet = db.SelectSQL("SELECT * FROM " + filename + " WHERE ? = ? " , selected , "accname" )

```

---

<div class="post-metadata">

**Author:** ![Thom\_McGrath](https://forum.xojo.com/user_avatar/forum.xojo.com/thom_mcgrath/32/192_2.png) [@Thom\_McGrath](https://forum.xojo.com/u/Thom_McGrath)\
**Post date:** [September 6, 2026, 1:42pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/11 "2026-09-06T13:42:14Z")

</div>

If you absolutely have to use a variable identifier, wrap the identifier in quotation marks. Be sure to also escape quotation marks in the identifier.

```xojo
Var TableName As String = "table"
Var Rows As RowSet = db.SelectSQL("SELECT * FROM """ + TableName.ReplaceAll("""", """""") + """;")

```

You need all those quotation marks because Xojo escapes them the same way SQLite does.

I’ve got two other unrelated tips since you’re new to SQLite.

- Get into the habit of ending your statements with the `;` terminator. The way Xojo handles SQL, this isn’t a hard requirement, but it can help you catch some subtle syntax errors.
- SQLite supports numbered placeholders. So you can write both `INSERT INTO some_table (column_1, column_2) VALUES (?1, ?2);` and `UPDATE some_table SET column_2 = ?2 WHERE column_1 = ?1;’ and supply the parameters in the same order for both statements. Meaning you could store the statement in a variable depending on the required action and use `SelectSQL(Statement, Value1, Value2)`and it’ll work. Of course, this exact use case could be done with an`INSERT OR REPLACE INTO` statement too, but this feature is really handy should you ever need to duplicate placeholders. You _can_ use alphanumeric placeholders too, but things get weird and I find them not useful with Xojo’s positional values.

---

<div class="post-metadata">

**Author:** ![Marvin\_Clavey](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/43a26b/32.png) [@Marvin\_Clavey](https://forum.xojo.com/u/Marvin_Clavey)\
**Post date:** [September 6, 2026, 5:47pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/12 "2026-09-06T17:47:24Z")

</div>

deleted post

---

<div class="post-metadata">

**Author:** ![Marvin\_Clavey](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/43a26b/32.png) [@Marvin\_Clavey](https://forum.xojo.com/u/Marvin_Clavey)\
**Post date:** [September 6, 2026, 8:20pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/13 "2026-09-06T20:20:31Z")

</div>

Thanks for the suggestion, I think the line discussed earler works because I am using Linux and most people here are using Mac or Windows … and the compiler must be slightly different .

I learned a lot from refactoring my db query code about Xojo and SQLite.

This is my current db query … if you see anything wrong or a way to improve it .. comments requested

```auto

// Querydb(Action As String, fileName As String = "dataSqlite", tableName As String = "bkaccts", tableColumn As string = "accname")

Var filePath As string = "XojoData"     
Select Case Action
  
Case "Read"          
          
  ListBox1.RemoveAllRows
  App.db.DatabaseFile = SpecialFolder.Documents.Child(filePath).Child(fileName)
  app.db.Connect
  var rowsFound As RowSet
  rowsFound = App.db.SelectSQL("select * from " + tableName )
  For each row As DatabaseRow in rowsFound
    Listbox1.AddRow(row.Column(tableColumn).StringValue)
  Next
  GroupBox1.Visible = False
  EditSaveEdit.Enabled = False
  
Case "GetRecord" // get the data for the account and display it on the window
  
  var selected As String = ListBox1.SelectedRowText
  var rowsFound As RowSet
  rowsFound = app.db.SelectSQL("select * from ? where ? = ? " ,tableName, selected, tableColumn)
  
  For x as Integer = 0 to rowsFound.LastColumnIndex
    accinfo(x).Text = rowsFound.ColumnAt(x).stringValue
    accInfo(x).ReadOnly = True
  Next 
  // fill graphic box
  Var f As FolderItem = SpecialFolder.Documents.Child(filePath).Child(accinfo(7).Text)     
  If f <> Nil Then
    ImageViewer1.Image = Picture.Open(f)
  End If
  GroupBox1.Visible = True
  
Case "Write"
  
  Try
    Var columnSchema As RowSet = app.db.TableColumns(tableName)
    If columnSchema <> Nil Then
      var x as Integer = 0 
      var mySQL As String = " UPDATE " + tableName +" SET "
      For Each row As DatabaseRow In columnSchema
        mySQL = mySQL + row.Column("ColumnName").StringValue + " = " + "'"+ accInfo(x).Text +"'" + ", "
        x = x+1 
      Next
      mySQL = mySQL.left(mySQL.Length -3) // remove last comma and space from string 
      mySQL = mySQL + " Where accname = " + "'" + ListBox1.SelectedRowText + "'" 
      App.db.ExecuteSQL(mySQL) 
      
    End If
  Catch error As DatabaseException
    MessageBox("Error Editing columns: " + error.Message)
  End Try
  
Case "New" // add new record with default values 
  Try
    APP.db.ExecuteSQL("INSERT INTO bkaccts DEFAULT VALUES")
  Catch error As DatabaseException
    MessageBox("Error: " + error.Message)
  End Try
  
case "Delete"
  var alias As String = ListBox1.SelectedRowText // get the account alias 
  App.db.ExecuteSQL("DELETE FROM bkaccts WHERE " + tableColumn + " = ? ", alias )
end Select

```

---

<div class="post-metadata">

**Author:** ![Emily-Elizabeth\_Howard](https://forum.xojo.com/user_avatar/forum.xojo.com/emily-elizabeth_howard/32/14415_2.png) [@Emily-Elizabeth\_Howard](https://forum.xojo.com/u/Emily-Elizabeth_Howard)\
**Post date:** [September 6, 2026, 8:46pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/14 "2026-09-06T20:46:58Z")

</div>

I’d recommend Eugene Dakin’s book on SQLite [SQLite with Xojo – API 2 – Scientific Specialties](https://scispec.ca/books/sqlite-with-xojo-api-2/)

---

<div class="post-metadata">

**Author:** ![Marvin\_Clavey](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/43a26b/32.png) [@Marvin\_Clavey](https://forum.xojo.com/u/Marvin_Clavey)\
**Post date:** [September 6, 2026, 9:29pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/15 "2026-09-06T21:29:18Z")

</div>

thanks I will look at that 😀

---

<div class="post-metadata">

**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\
**Post date:** [September 7, 2026, 1:20pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/16 "2026-09-07T13:20:47Z")

</div>

> [@Marvin\_Clavey](#):
>
> `Var filePath As string = "XojoData"`

IS THAT REALLY A PATH ? (I am telling that for: **XojoData)**

---

<div class="post-metadata">

**Author:** ![Marvin\_Clavey](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/43a26b/32.png) [@Marvin\_Clavey](https://forum.xojo.com/u/Marvin_Clavey)\
**Post date:** [September 7, 2026, 2:41pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/17 "2026-09-07T14:41:48Z")

</div>

Yes it is the Path to my data files for Xojo.

 ![Screenshot From 2026-09-07 09-40-32](https://forum.xojo.com/uploads/default/original/3X/d/c/dca446e29790587af95c57ca0a0f7ace6e6418f5.png)

---

<div class="post-metadata">

**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\
**Post date:** [September 8, 2026, 8:20am UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/18 "2026-09-08T08:20:34Z")

</div>

So, XojoData is a name of a Folder.

Do you plan to use that same folder for your others applications ?

---

<div class="post-metadata">

**Author:** ![Jeff\_Tullin](https://forum.xojo.com/user_avatar/forum.xojo.com/jeff_tullin/32/693_2.png) [@Jeff\_Tullin](https://forum.xojo.com/u/Jeff_Tullin)\
**Post date:** [September 8, 2026, 9:07am UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/19 "2026-09-08T09:07:28Z")

</div>

> [@Marvin\_Clavey](#):
>
> Yes it is the Path to my data files for Xojo.

I can recommend you dont use that path for your data. After many years of ‘using a folder in documents’ myself, I can tell you that eventually iCloud will screw you over. If the files aren’t used for a while, they go to the cloud. And then if you try to open them using Xojo , the file open can fail/ stall.

Always put your data into specialfolder. applicationdata

---

<div class="post-metadata">

**Author:** ![MarkusR](https://forum.xojo.com/user_avatar/forum.xojo.com/markusr/32/685_2.png) [@MarkusR](https://forum.xojo.com/u/MarkusR)\
**Post date:** [September 8, 2026, 5:26pm UTC](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851/20 "2026-09-08T17:26:19Z")

</div>

> [@Marvin\_Clavey](#):
>
> why does one line work properly and the first line give a sql syntax error ?

i think this ? put a string content into the query and put single quote around  
`select * from mytable where myfield = 'xyz'`  
if it looks like this it is not a correct syntax, i guess  
`select * from 'mytable'`

according to web it could be

```auto
SELECT * FROM mytable
SELECT * FROM "mytable"
SELECT * FROM [mytable]
SELECT * FROM `mytable`

```

the last are backtick

[Next page](https://forum.xojo.com/t/new-user-learning-experimenting-with-sqlite/88851.md?page=2)