B4J Question how to SQLite store single quotes(')?

[Dataverde]

Hey there,

im working on a SQLite db to store some Names.
When there is a single quote ' in the name there will be an error as ' is the text qualifier of SQLite.

This B4A thread and the SQLite documentation suggest to exit the single quote with another one.
Like this:

"INSERT INTO things(name) VALUES ('Neil O''Donnel')"

this works fine in SQLite DB editor but throws an error in B4J.

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "O": syntax error)

Is there any workaround for this? In wich ways do B4A and B4J SQL libs differ?

Thanks for your time,

Lennart

 

[billzhan]

try

Dim SQLa As SQL
Dim query As String="INSERT INTO things(name) VALUES (?)"
SQLa.ExecNonQuery2(query,Array As String("'Neil O''Donnel'"))  


'or use DBUtils.InsertMaps
Dim lom As List
lom.Initialize
Dim m As Map =CreateMap("name":"'Neil O''Donnel'")
lom.add(m)
DBUtils.InsertMaps(SQLa,"things",lom)

 

[Dataverde]

try

Dim SQLa As SQL
Dim query As String="INSERT INTO things(name) VALUES (?)"
SQLa.ExecNonQuery2(query,Array As String("'Neil O''Donnel'"))

The same error.
I will try DBUtils next.

 

[billzhan]

a line is missing , sqla should be initialized first. Make sure that .db file and table(things) and col(name) exists first.
And you don't need to escape quote(') in both case.

Dim SQLa As SQL

'missing line: init SQLa
SQLa.InitializeSQLite(File.DirApp,"test.db",False)


Dim query As String="INSERT INTO things(name) VALUES (?)"
SQLa.ExecNonQuery2(query,Array As String("Neil O'Donnel"))


'or use DBUtils.InsertMaps
Dim lom As List
lom.Initialize
Dim m As Map =CreateMap("name":"Neil O'Donnel")
lom.add(m)
DBUtils.InsertMaps(SQLa,"things",lom)

 

[Dataverde]

Ok, found my error. Dubble ' and everything works fine. Hand nothing to do with the SQL lib after all.

Thank you, billzhan, for your help!