Like Operator with Unicode

[Charax]

Using MS Access 2003 with ODBC to SQL Server 2005 on a Win XP US English
system. The database is Access 2000 format. I do not have "SQL Server
Compatible Syntax (ANSI 92)" selected.

Several fields of my database are nvarchar text with mixed English and
Greek.

Goal: I need a query to return all records that contain a specific Greek
word or phrase.

Whenever I use a Greek word in the Like criteria, the query returns no
records, for example,
Like "*???????*"
(If this doesn't come through properly it is the Greek name ARSAKOU in Greek
Unicode UTF-8: all uppercase Alpha Rho Sigma Alpha Kappa Omicron Upsilon)

However. I have found the query works when I place square brackets around
each letter:
Like "*[?][?][?][?][?][?][?]*"

There must be a better way! I must be missing the obvious. Can anyone help?

Cheers,

Charax

 

[Charax]

As I feared, the Greek doesn't show properly. The queries look like this:

Like "*ARSAKOU*" or Like "*[A][R][A][K][O]*" but, of course, using the
Greek letters.

Charax

 

[Sylvain Lafontaine]

First, you give no detail whatsoever on how this query is executed from the
MDB file (querydef over ODBC linked tables, passthrough queries over an ODBC
connection, ADO with ODBC, DAO call, anything else ??? Parameters used for
the connection string or DSN ??? How is the parameter for the LIKE statement
passed to the SQL-Server?). You don't give also any detail about the
collation of the database/table(s) on SQL-Server: english, greek?

Second, many older versions of ODBC drivers don't support Unicode; so you
should make sure of using one of the latest ODBC driver available on your
machine.

Finally, SQL-Server don't support UTF-8, only UTF-16 (and strictly speaking,
only a (big) subset of UTF-16).

 

[Charax]

Sylvain,

Thanks for your reply. These are just simple ad hoc queries initiated by
opening a simple New query from the Access MDB database window, no
parameters, the only criteria is a Like operator. The table is linked
through ODBC, SQL Server Native Client driver is 2005.90.3042.00. Database
sort order is General on a US English system.

The connect string:
ODBC;Driver={SQL
Server};Server=XPS;UID=Foobar;DATABASE=mylocaldb;Trusted_Connection=Yes

The objective is to return all records that contain ARSAKOU in the
txtSellwood field (where for this posting I've substituted Latin letters
ARSAKOU for the Greek letters, all uppercase Alpha Rho Sigma Alpha Kappa
Omicron Upsilon).

This query won't return data:
SELECT Coins.dwSellwood, Coins.txtSellwood
FROM Coins
WHERE (Coins.txtSellwood) Like "*ARSAKOU*"
WITH OWNERACCESS OPTION;

This query works:
SELECT dwSellwood, txtSellwood
FROM Coins
WHERE (txtSellwood) Like "*[A][R][A][K][O]*"
WITH OWNERACCESS OPTION;

Your thoughts appreciated,

Charax

 

[Sylvain Lafontaine]

The first thing to do would be to replace the old ODBC driver ({SQL Server})
with something more recent; preferably the SQL Native Client ODBC Driver;
see http://www.connectionstrings.com/?carrier=sqlserver2005

The second would be to make sure that the data is correct on the server side
by using the Unicode function to display what's you really have on the
server. You could also use the same Unicode function for testing what's you
are sending to the server as the string constant.

Finally, you should also take a look with the SQL-Server Profiler to see
what the ODBC Driver is sending exactly to the server.

 

[Charax]

Sylvain,

All is well with the driver, the connection and the data in the database.
Queries return correct data including the mixed English and Greek Unicode
text. I process a lot of Greek text in VBA and have no problems.

Let me restate: A simple query created from the database window using the
Like operator cannot differentiate an English "S" from a Greek "S" (sigma)
unless it is placed in square brackets. To find a Greek word, every letter
must be individually wrapped in a set of square brackets. For example,

Like "*[A][R][A][K][O]*"

where the letters are all uppercase Alpha Rho Sigma Alpha Kappa Omicron
Upsilon.

What is going on and how can I use Greek in the Like operator without the
brackets?

Thanks,

Charax

 

[Charax]

And some additional information. When I filter a form's recordset using
Filter By Selection, if any of the selected text includes any Greek
characters the filter returns unreliable results.

Charax

 

[Sylvain Lafontaine]

I've been able to reproduce your result using a database with the default
english collation (Latin1_General_CI_AS) but everything is ok if the default
collation for the database is Greek_CI_AS.


After some testing, it appears that the ODBC driver seems to be unable to
correctly translate the string constant as a Unicode string constant, so the
following command is sent to the server:

SELECT "dbo"."Table1_Latin"."IdTable1" FROM "dbo"."Table1_Latin" WHERE
("Greek1" LIKE 'a?%' )

instead of the correct form:

SELECT "dbo"."Table1_Latin"."IdTable1" FROM "dbo"."Table1_Latin" WHERE
("Greek1" LIKE N'a?%' )

Notice the N' before the Unicode string constant in the second (correct)
form. As the first version is not a unicode string constant, SQL-Server
translate it using the default Code page of the database; hence the wrong
result.

In the case of putting [] arround each characters, Access choose to retrieve
*all* records and make itself the filtering. So it works but at the expense
that the whole table must be retrieved.

I would suggest that you use a parameterised query - in order to avoid
passing a string constant - or a passthrough query.

 

[Charax]

Sylvain,

Many thanks for your research confirming my problem. As you suggest, I will
explore using parameterized and pass-thru queries to handle the problem.

Cheers,

Charax

 

[Charax]

I would suggest that you use a parameterised query - in order to avoid

passing a string constant - or a passthrough query.

Sylvain,

I tried a pass-through query. I thought I understood the N-prefix, but am
not able to implement it. Could you look at my code to find what is wrong? I
created a stored Access pass-through query and modify its SQL statement
real-time. The txtSellwood is an nvarchar field that holds mixed English and
Greek Unicode.

There is no problem passing the SQL statement. This portion of the code
snippet works OK so long as no Greek characters are in the txtSellwood
field:
strSql = "SELECT * FROM Coins WHERE txtSellwood LIKE '" & strSellwoodExt &
"';"
Profiler sees:
SELECT "dbo"."Coins"."CoinID" FROM "dbo"."Coins" WHERE ("txtSellwood" LIKE
'.1 variant' )

But when I attempt to prefix N to the field's contents with this statement:
strSql = "SELECT * FROM Coins WHERE txtSellwood LIKE N'" & strSellwoodExt &
";"
an error is popped:
Error: 3075 : Syntax error (missing operator) in query expression
'(txtSellwood LIKE N'.1 variant')'.
Profiler is not seeing this query. I've tried numerous combinations of
single and double quotation marks, but can't correct my error.

Dim strSql As String, strSellwoodExt As String
Dim db As Database, qryPassthrough As QueryDef
On Error GoTo error_handler
Set db = CurrentDb
Set qryPassthrough = db.QueryDefs("qsqlPassthru")
strSellwoodExt = [Forms]![frmCoins]![Sellwood Ext]
strSql = "SELECT * FROM Coins WHERE txtSellwood LIKE '" & strSellwoodExt
& "';"
qryPassthrough.SQL = strSql
Forms!frmCoins.RecordSource = strSql
qryPassthrough.Close
db.Close

If I load an SQL statement that includes Greek directly into the stored
pass-through query:
SELECT * FROM Coins WHERE txtSellwood LIKE N'.1 variant (??????? read from
inside)';
it returns records properly and the Profiler sees:
SELECT * FROM Coins WHERE txtSellwood LIKE N'.1 variant (??????? read from
inside)';
but Profiler highlights this part in red:
'.1 variant (??????? read from inside)'

Thanks for taking a look,

Charax

 

[Sylvain Lafontaine]

I absolutely don't understand from where your « '.1 variant' » is comng
from. If this is really a passthrough query, the exact same text that you
are setting as the sql string in your querydef should be passed to
SQL-Server; with no modification whatsoever; so I suppose that your
variable strSellwoodExt is not OK. Please display the sql string in a
msgbox or in the debug window (using Debut.Print) to see what's going on. A
correct version would be (final result as it should be displayed in your
msgbox):

SELECT * FROM Coins WHERE txtSellwood LIKE N'a%';

with, of course, the values inside the N'' written in Unicode values (and
not as ANSI values using the code page 1253).

Notice that I have replace the character * with % because this query will be
executed directly on the sql-server and as such, you must use the SQL-92
syntax; where the symbol % replace the symbol * in LIKE statements.

Are you sure that the querydef that you are using is really a passthrough
query? What version of Access and of SQL-Server are you using and did you
apply the latest service pack?

Also, passthrough queries are read-only. If you want to use them in a form
and make update to it, you will have to use another method; like passing the
value of your search string in hexadecimal and make an hexadecimal
comparaison. I've made some tests trying to use a parameter in an ordinary
querydef against ODBC linked tables and it didn't work; so in all cases, as
you want to use Unicode between a client and a database without the same
code page, I would suggest that you stop trying with ODBC linked tables and
switch to ADP instead. JET & ODBC doesn't seem to offer any real good
support for Unicode.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I would suggest that you use a parameterised query - in order to avoid
passing a string constant - or a passthrough query.

Sylvain,

I tried a pass-through query. I thought I understood the N-prefix, but am
not able to implement it. Could you look at my code to find what is wrong?
I created a stored Access pass-through query and modify its SQL statement
real-time. The txtSellwood is an nvarchar field that holds mixed English
and Greek Unicode.

There is no problem passing the SQL statement. This portion of the code
snippet works OK so long as no Greek characters are in the txtSellwood
field:
strSql = "SELECT * FROM Coins WHERE txtSellwood LIKE '" & strSellwoodExt &
"';"
Profiler sees:
SELECT "dbo"."Coins"."CoinID" FROM "dbo"."Coins" WHERE ("txtSellwood" LIKE
'.1 variant' )

But when I attempt to prefix N to the field's contents with this
statement:
strSql = "SELECT * FROM Coins WHERE txtSellwood LIKE N'" & strSellwoodExt
& ";"
an error is popped:
Error: 3075 : Syntax error (missing operator) in query expression
'(txtSellwood LIKE N'.1 variant')'.
Profiler is not seeing this query. I've tried numerous combinations of
single and double quotation marks, but can't correct my error.

Dim strSql As String, strSellwoodExt As String
Dim db As Database, qryPassthrough As QueryDef
On Error GoTo error_handler
Set db = CurrentDb
Set qryPassthrough = db.QueryDefs("qsqlPassthru")
strSellwoodExt = [Forms]![frmCoins]![Sellwood Ext]
strSql = "SELECT * FROM Coins WHERE txtSellwood LIKE '" &
strSellwoodExt & "';"
qryPassthrough.SQL = strSql
Forms!frmCoins.RecordSource = strSql
qryPassthrough.Close
db.Close

If I load an SQL statement that includes Greek directly into the stored
pass-through query:
SELECT * FROM Coins WHERE txtSellwood LIKE N'.1 variant (??????? read from
inside)';
it returns records properly and the Profiler sees:
SELECT * FROM Coins WHERE txtSellwood LIKE N'.1 variant (??????? read from
inside)';
but Profiler highlights this part in red:
'.1 variant (??????? read from inside)'

Thanks for taking a look,

Charax

 

[Sylvain Lafontaine]

BTW, why don't you set up the database to use a Greek collation instead?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I absolutely don't understand from where your « '.1 variant' » is comng
from. If this is really a passthrough query, the exact same text that you
are setting as the sql string in your querydef should be passed to
SQL-Server; with no modification whatsoever; so I suppose that your
variable strSellwoodExt is not OK. Please display the sql string in a
msgbox or in the debug window (using Debut.Print) to see what's going on.
A correct version would be (final result as it should be displayed in your
msgbox):

SELECT * FROM Coins WHERE txtSellwood LIKE N'a%';

with, of course, the values inside the N'' written in Unicode values (and
not as ANSI values using the code page 1253).

Notice that I have replace the character * with % because this query will
be executed directly on the sql-server and as such, you must use the
SQL-92 syntax; where the symbol % replace the symbol * in LIKE statements.

Are you sure that the querydef that you are using is really a passthrough
query? What version of Access and of SQL-Server are you using and did you
apply the latest service pack?

Also, passthrough queries are read-only. If you want to use them in a
form and make update to it, you will have to use another method; like
passing the value of your search string in hexadecimal and make an
hexadecimal comparaison. I've made some tests trying to use a parameter
in an ordinary querydef against ODBC linked tables and it didn't work; so
in all cases, as you want to use Unicode between a client and a database
without the same code page, I would suggest that you stop trying with ODBC
linked tables and switch to ADP instead. JET & ODBC doesn't seem to offer
any real good support for Unicode.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I would suggest that you use a parameterised query - in order to avoid
passing a string constant - or a passthrough query.

Sylvain,

I tried a pass-through query. I thought I understood the N-prefix, but am
not able to implement it. Could you look at my code to find what is
wrong? I created a stored Access pass-through query and modify its SQL
statement real-time. The txtSellwood is an nvarchar field that holds
mixed English and Greek Unicode.

There is no problem passing the SQL statement. This portion of the code
snippet works OK so long as no Greek characters are in the txtSellwood
field:
strSql = "SELECT * FROM Coins WHERE txtSellwood LIKE '" & strSellwoodExt
& "';"
Profiler sees:
SELECT "dbo"."Coins"."CoinID" FROM "dbo"."Coins" WHERE ("txtSellwood"
LIKE '.1 variant' )

But when I attempt to prefix N to the field's contents with this
statement:
strSql = "SELECT * FROM Coins WHERE txtSellwood LIKE N'" & strSellwoodExt
& ";"
an error is popped:
Error: 3075 : Syntax error (missing operator) in query expression
'(txtSellwood LIKE N'.1 variant')'.
Profiler is not seeing this query. I've tried numerous combinations of
single and double quotation marks, but can't correct my error.

Dim strSql As String, strSellwoodExt As String
Dim db As Database, qryPassthrough As QueryDef
On Error GoTo error_handler
Set db = CurrentDb
Set qryPassthrough = db.QueryDefs("qsqlPassthru")
strSellwoodExt = [Forms]![frmCoins]![Sellwood Ext]
strSql = "SELECT * FROM Coins WHERE txtSellwood LIKE '" &
strSellwoodExt & "';"
qryPassthrough.SQL = strSql
Forms!frmCoins.RecordSource = strSql
qryPassthrough.Close
db.Close

If I load an SQL statement that includes Greek directly into the stored
pass-through query:
SELECT * FROM Coins WHERE txtSellwood LIKE N'.1 variant (??????? read
from inside)';
it returns records properly and the Profiler sees:
SELECT * FROM Coins WHERE txtSellwood LIKE N'.1 variant (??????? read
from inside)';
but Profiler highlights this part in red:
'.1 variant (??????? read from inside)'

Thanks for taking a look,

Charax

 

[Charax]

BTW, why don't you set up the database to use a Greek collation instead?

Sylvain,

I could on my local server, but I am using an inexpensive ISP's SQL Server
for the web-based version. I don't believe they allow re-setting collation,
but I will check into it.

I'm studying your prior message and experimenting, and will get back to you
on these issues shortly.

Thanks,

Charax