I want to use non-ascii character string literals in a firebird sql query.
So I used FlameRobin to see if it works first:
I used something like this:
SELECT NAME FROM TABLE1 WHERE NAME = 'العربية'
I also tried:
SELECT NAME FROM TABLE1 COLLATE UNICODE_CI_AI WHERE NAME = 'العربية'
with no success. Because I have declared NAME using UTF8 Character Set and UNICODE_CI_AI Collation.
from this referential url link: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-appx06-charsets.html
FlameRobin throws an error about the syntax, it actually don't understand : 'العربية' string.
So I decided to change the database administration tool with Firebird Maestro to do the test, this tool now accepts these kind of non-ascii character string literals, but it is not doing the filter properly.
How can this issue be solved?
NB: I am using firebird v2.5
Firebird uses a connection character set to know how string values need to be encoded. The default is NONE which means that no specific character set conversion is applied, and exact behavior varies per tool/client, but in general clients will apply the system default character set to convert between byte values and strings.
You need to specify connection character set UTF8 when connecting from FlameRobin, and your query will work
SELECT NAME FROM TABLE1 WHERE NAME = 'العربية'
If you don't specify a connection character set, then connection character set NONE is applied, and as a result string values will be interpreted in the platform default encoding (which on Windows is not utf8, but for example windows-1252).
Do not use literals. Don't put data into the query text, put it outside of query as "parameters".
It has a number of benefits, like more reliable parsing, more type-checking, more safety and often more speed (you can prepare query once, and then run it many times only changing parameters value).
How you code the parameters in SQL queries depends upon the library you use in your programming language for connecting to Firebird. See http://bobby-tables.com/ for some examples. The following are three often used conventions to try:
SELECT NAME FROM TABLE1 WHERE NAME = ? -- natively supported by Firebird, index-based access to parametersSELECT NAME FROM TABLE1 WHERE NAME = :NAME_PARAM -- BDE / Delphi styleSELECT NAME FROM TABLE1 WHERE NAME = @NAME_PARAM -- MS SQL / .Net style
I do not know which flavours are supported in Flame Robin and Maestro.
IB Expert uses Delphi libraries, hence using #2 option.
Java-written programs tend to use #1 option.
For ADO.NET connections, include "Charset=UTF8" (by default "NONE") in the connection string. As in:
"User=SYSDBA;Password=masterkey;Database=SampleDatabase.fdb;DataSource=localhost;Port=3050;Dialect=3;Charset=UTF8;Role=;Connection lifetime=15;Pooling=true;MinPoolSize=0;MaxPoolSize=50;Packet Size=8192;ServerType=0;"
