Error on SQL query: syntax error near "'"

Getting Started
#1

With this query:

rs = DB.SelectSQL(“SELECT * FROM cNotifiche WHERE cNotifiche MATCH ?”, txt5Cerca.Value )

if i insert only letters/numbers all works properly
when insert ’ or - or other similar characters i receive the error

syntax error near “’”

#2

I should use the SQLitePreparedStatement?
But why now i receive this error:

column index out of range

In the debugger

Var rs As RowSet = ps.SelectSQL
Is Nil
but the presence of

If rs <> Nil Then

Shouldn’t avoid that error ???

Try
ps = SQLitePreparedStatement(DB.Prepare(“SELECT * FROM cNotifiche WHERE cNotifiche MATCH ?”))
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
If rbParolaEsattaSI.value = true then
ps.Bind(0, txt5Cerca.Text)
End If
If rbParolaEsattaNO.value = true then
ps.Bind(0, txt5Cerca.Text + “*”)
End If

Var rs As RowSet = ps.SelectSQL
// Loop through each row, one-by-one, and add it to the ListBox.
If rs <> Nil Then

For Each row As DatabaseRow In rs
  If row.Column("Giorno").StringValue = "" or row.Column("Mese").StringValue = "" or row.Column("Anno").StringValue = "" Then
    ListaNotifiche.AddRow(row.Column("ID").StringValue,  row.Column("Pratica").StringValue, row.Column("Atto").StringValue, _
    row.Column("Cronologico").StringValue, "DA SPEDIRE", row.Column("Destinatario").StringValue, row.Column("Raccomandata").StringValue), _
    row.Column("Notificato").StringValue, row.Column("Duplicato").StringValue
    ListaNotifiche.RowTagAt(ListaNotifiche.LastAddedRowIndex) = row.Column("ID").IntegerValue
  Else
    ListaNotifiche.AddRow(row.Column("ID").StringValue,  row.Column("Pratica").StringValue, row.Column("Atto").StringValue, _
    row.Column("Cronologico").StringValue, row.Column("Giorno").StringValue + "/" + row.Column("Mese").StringValue + "/" + row.Column("Anno").StringValue, row.Column("Destinatario").StringValue, row.Column("Raccomandata").StringValue), _
    row.Column("Notificato").StringValue, row.Column("Duplicato").StringValue
    ListaNotifiche.RowTagAt(ListaNotifiche.LastAddedRowIndex) = row.Column("ID").IntegerValue
  End If
Next

rs.Close

End If

Catch err As DatabaseException
MessageDialog.Show("Errore DB: " + err.Message)
Return
End Try

#3

You can’t search for . or ’ directly, but you can put them in double quote:
IE to search un’altra you can insert in your txt5Cerca field: “un’altra”

#4

ps.Bind(0, ("""+ txt5Cerca.text + “”")

why that doesn’t work ?

#5 
ps.bind(0, """"+txtCerca.text+"""")

anyway I suggest to use it only when needed (you are telling to search the exact sequence)
Better to explain to the user the various combination he can use to search (AND, NEAR and when use double quote)
To avoid problems put the search in a try catch and use the exception to avoid problems in your code and help the user to use the right MATCH syntax (MATCH is not LIKE)

Moreover you can extend the FTS5 to accept other chars (like ’ for example) but you need to change the tokeniser

1 Like
#6

Thanks Antonio,
and why instead that not works ?

ps.Bind(0, “”""+txt5Cerca.text+"*"+"""")

(i’ve not insert the curly brackets only " but see that appear curly brackers, why ???)

#7

Probably for this is a strange mix: find all the variants of something (IE xxxx*) but with exact match

#8

I’ve solved in this way, thank you so much for your precious help.

If rbParolaEsattaSI.value = true then
rs = DB.SelectSQL(“SELECT * FROM cNotifiche WHERE cNotifiche MATCH ?”, “”""+txt5Cerca.text+"""" )
End If
If rbParolaEsattaNO.value = true then
rs = DB.SelectSQL(“SELECT * FROM cNotifiche WHERE cNotifiche MATCH ?”, “”""+txt5Cerca.text+"""" + “*” )
End If

#9