Source language: Translate to:

Query using other languages - Newbie question

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Query using other languages - Newbie question

Postby beno » Mon Sep 10, 2007 7:03 pm

Hi,

I´m doing my first tests with NBdbPRO.

I have this problem; let´s think in persons names.

For example, in Spanish I can have these names:

Juan Gómez
Pedro Gomez
Lupe Gutiérrez
Jim Gutierrez


As you can see some words are identical except the accent in certain cases.

I´m trying to query my database and do this query independent of accented characters but have no luck until now.

I have tried it with: dbpQuery and dbpExecSQL

For example:

Code: Select all
dbpQuery "MyDB" "Person" "Name LIKE [#34]%Gom%[#34]"


And this retrieves: Pedro Gomez but not Juan Gómez...

As far as I remember M$ Access does this accent/foreign characters trick with no problems.

Ideas?

Saludos,

beno
User avatar
beno
 
Posts: 678
Joined: Fri Apr 01, 2005 9:03 am
Location: México

Postby beno » Mon Sep 10, 2007 8:23 pm

Another idea on this.

This problem can be shown with the AddressBook NBdbPro sample publication.

Open it and add a person whose LastName contains extended characters.

For example:

Luis Méndez

(There is a Mendez in the sample database)

If you try to Find or Query on "Men" you will get only Mendez....

The real funny thing (well, in fact this is not funny), is that if you sort the database then NBdbPro does it very nice and does it aware of extended characters!!!

:shock:

Saludos,

beno
User avatar
beno
 
Posts: 678
Joined: Fri Apr 01, 2005 9:03 am
Location: México

Postby Neosoft Support » Tue Sep 11, 2007 10:09 am

I don't know the answer to this. DBPro doesn't actually perform the query itself. It simply converts it to a proper SQL command and passes it on to MS Access which does the actual query.

For example, the following:

dbpQuery "MyDB" "Person" "Name LIKE [#34]%Gom%[#34]"

is converted to:

SELECT * FROM Person WHERE Name LIKE "%Gom%";

MS Access then executes the query and returns the results which DBPro displays.

There may be some special command required to matching foreign language characters.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby beno » Tue Sep 11, 2007 1:24 pm

Hi Dave,

I have been trying to search for this problem.

It seems the trick resides in using unicode. Some info at:

http://doc.ddart.net/mssql/sql70/8_ar_da_10.htm

and

http://doc.ddart.net/mssql/sql70/8_con_03_20.htm

My question is: is there a difference or possibilty to create databases in NBdbPro using unicode?

Thanks for your time.

beno

P.S. wow!, the "globalization" idea is just a theory...
User avatar
beno
 
Posts: 678
Joined: Fri Apr 01, 2005 9:03 am
Location: México

Postby Neosoft Support » Wed Sep 12, 2007 10:15 am

My question is: is there a difference or possibilty to create databases in NBdbPro using unicode?


I'll have to look into that. Unicode isn't required for Spanish, French, Italian, German or English, but each of these languages have different requirements for sorting data. Almost all of DBPro's interaction with the database is done using SQL commands, so that's probably where we'll find the solution.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby beno » Wed Sep 12, 2007 12:06 pm

Hi Dave,

Thanks for your help.

I´m doing some tricks to deal with this in the meantime.

But of course it would be great if we can find a more serious solution.

Saludos,

beno
User avatar
beno
 
Posts: 678
Joined: Fri Apr 01, 2005 9:03 am
Location: México

Postby Neosoft Support » Thu Sep 13, 2007 10:41 am

From what I can tell, this is a limitation of the Access database engine. (It may be that the actual MS Access Application handles this differently.) Other database engines can automatically perform accent insensitive searches. For example, using MySQL, the following query:

dbpQuery "MySQLDB" "Contacts" "LastName=[#34]gomez[#34]"

will automatically find:

Gómez
Gomez

as well as

Góméz
Goméz

The same query with MS Access will find only:

Gomez

The apparent solution that MS Access users have found is to write a custom query function to search for all possible variations of a word. For example:

LastName="gomez"

would become:

LastName="gomez" OR LastName="gòmez" OR LastName="gömez" OR LastName="gómez" OR LastName="gõmez"

This can get quite involved when we also include the variations for a, e, i and u.

I think a much easier solution is to use the LIKE operator. For example:

LastName LIKE "g%m%z"

will find:

Gómez
Gomez
Góméz
Goméz

(The % character is a wildcard.)

It would be fairly easy to create a simple script to replace all vowels in the search string with % befor passing it on to dbpQuery.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5605
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby beno » Thu Sep 13, 2007 1:51 pm

Hi Dave,

Thanks a lot!!

Your suggestions are great!

Saludos,

beno
User avatar
beno
 
Posts: 678
Joined: Fri Apr 01, 2005 9:03 am
Location: México

Postby Gaev » Thu Sep 13, 2007 4:48 pm

Beno:
LastName="gomez" OR LastName="gòmez" OR LastName="gömez" OR LastName="gómez" OR LastName="gõmez"
This can get quite involved when we also include the variations for a, e, i and u.

Don't know if MS Access supports Regular Expressions (a.k.a Regex) in queries ... if it does, you might be able to specify something like ...
Code: Select all
SELECT * FROM Person WHERE Name LIKE "G[oó]m[eé]z";
... you may have to replace the sqare brackets with [#91] and [#93] ... depending on where you specify the SELECT command.

BTW, I don't think that MS Access is wrong when it returns just the record with content="Gomez" ... the unaccented and each of the accented vowels have a different (ascii) code ... so if you ask for records matching Gomez, you won't get ones with the different (ascii) codes ... same as if you ask for "son", you don't get "sun" ... for this you need to ask for words that sound like the specified word ... soundex('son') ? ... again if it is supported by MS Access.

Finally, if all else fails, you might consider having two columns/fields in your database ... one with the accented characters ... and another that is "dummed down" ... i.e. all accented vowels are replaced by their unaccented equivalents ... then, do the query on the second column/field ... making sure only unaccented characters are specified in the search words.

P.S. I believe later versions of mySQL support use of Regex in queries.
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby beno » Mon Sep 17, 2007 8:54 am

Hi Gaev,


Don't know if MS Access supports Regular Expressions (a.k.a Regex) in queries ... if it does, you might be able to specify something like ...
Code:

Code: Select all
SELECT * FROM Person WHERE Name LIKE "G[oó]m[eé]z";

... you may have to replace the sqare brackets with [#91] and [#93] ... depending on where you specify the SELECT command.



This one does the job exactly as needed.

1,000 Thanks!

Saludos,

beno
User avatar
beno
 
Posts: 678
Joined: Fri Apr 01, 2005 9:03 am
Location: México


Return to NeoBookDBPro

Who is online

Users browsing this forum: Bing [Bot] and 1 guest