best way to select sql with accentued text ?

Databases
1

Hi,
I have an sqlite database, with text fields in it
I import records in it and I search for an already present record to import it or not

it works when I have normal text, but not when I have accentuated (french) text like “Hrisson”

select * from table1 where name='Hrisson' tells me there is no record althought there is already one (or more)

select * from table1 where name='Bouteille' is ok and tells me there is already one

the database is encoded in UTF8 (default) and the text to import is also in UTF8
how can I search for accentued text precisely ?

thanks.

2

Try using a PreparedStatement.
Something like this:

Dim ps As SQLitePreparedStatement
ps = SQLitePreparedStatement(db.Prepare("SELECT * FROM table1 WHERE name = ? "))

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, "Hrisson")
Dim rs As RecordSet = ps.SQLSelect

http://documentation.xojo.com/index.php/SQLitePreparedStatement

3

Make sure the import has been from a text file encoded in utf8.

4

yes the database is in UTF8 and the imported text is also in UTF8
I only tried in sqlite but is there the same problem with postgres ?

5

[quote=149261:@Albin Kiland]Try using a PreparedStatement.
Something like this:

Dim ps As SQLitePreparedStatement
ps = SQLitePreparedStatement(db.Prepare("SELECT * FROM table1 WHERE name = ? "))
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, "Hérisson")
Dim rs As RecordSet = ps.SQLSelect

http://documentation.xojo.com/index.php/SQLitePreparedStatement[/quote]

well I have to import some files with 10k+ records in each, and records with same names that must merge and not create another record.
so this is a one time and debug only work, I’m not sure I need to use the PreparedStatements ?
or does the PreparedStatements make some filterings on the input text ?

6

Yes … Prepared statements allow you to use characters that otherwise would have to be “escaped”

7

Have you tried executing the same query in a db editor like Sequel (MySQL) or SQLiteManage (SQLite) ? If you get a result when searching for “Hrisson”, you know the problem is on the query side in your app. If you don’t get a result, the imported data was not encoded in utf8.

8

sqlitemanagers gives a good answer when searching for ‘Hérisson’ so it’s definitely in my app.

9

just for the records,

well the preparedstatements did not lead to a solution…
I used select * from table1 where upper(name)=upper('Hrisson')
and it did not work.
even with the preparedstatement : select * from table1 where upper(name)=upper( ? )
then I used the terms : select * from table1 where name='Hrisson' collation nocase
and everything worked fine !