asked on

Ucase and OEM to ANSI, won't work with Swedish åäö

Hi

I have i Pervasive table that is in DOS-format. Now building a program in VB 6. For a while the tables data need to stay in DOS-format.

1) Auto Translate=True in the connection string fixes the presentation of data.
2) CharToOemBuff converts fine when I'm writing data
3) The fields are not Case Insensitive
4) Did try PSQL 8.x and 9.5
5) Function CharToOem() uses API CharToOemBuff()

My problem is when using search on words with i.e. å, ä och ö in.

This works fine:
1) SELECT myfield FROM mytable WHERE Ucase(myfield) LIKE '%" & CharToOem("BIKE") & "%'"
Will find all fileds with Bike, BIKE and bike etc.

This will not work:
2) SELECT myfield FROM mytable WHERE Ucase(myfield) LIKE '%" & CharToOem("SÖNDAG") & "%'"
Will find words like SÖNDAG, sÖndag but not söndag.

3) SELECT myfield FROM mytable WHERE Lcase(myfield) LIKE '%" & CharToOem("söndag") & "%'"
Will find words like söndag but not SÖNDAG

The problem seams to be that SQL command Lcase() and Ucase() only convert a-z, not åäö etc.

Is my only option to adjust the 10 year old kernel in the dos-code to handle åäö?? That would be a pain...

Bill Bach

I know this isn't the answer that you're looking for, but with PSQLv9 and higher, you can write yourself a new LCASE or UCASE function to manually convert those special characters. (See the manual onder "CREATE FUNCTION" for more information. I am guessing that Pervasive is using the standard "C" string conversion functions, which explains why the built-in functions won't convert those special characters at all.

If you have an option of running PSQLv10, then I would strongly recommend that you contact Pervasive and open a trouble ticket with them. This sounds like a bug that could be fixed.

You may also wish to investigate the International Sorting Rules (ISR's), which might also be able to handle this. You'd have to specify a different collating sequence on your file, though, which might confuse the DOS app.

lizoft

ASKER

Sounds greate with Create Function. Did find info on http://www.pervasive.com/library/docs/PSQL/950/sqlref/sqlref-04-19.html.

Can you help me with code for making own Ucase that also converts å -> Å, ä -> Ä and ö -> Ö?

Can't use PSQLv10 as it has no DOS-support.

Bill Bach

Are you still running on Native DOS machines? PSQLv10 DOES work with DOS applications running on Win32 platforms (Windows 2000 and above).

Unfortunately, I have no way to easily test anything that I create (being a US user working with an inferior character set). As such, I can give you the core of the function, but you'll have to try to fix it and do some testing on your own. I'll work up something and post it shortly...

Bill Bach

I wrote the function and actually found that I could cut&paste the characters from your post into the PCC. When I run a simple query of SELECT First_Name, UCASE(First_Name) FROM Person and include some of the special characters, I *do* get the special characters converted to Upper case:

First_Name EXPR_1
=============== ===============
Jåmes JÅMES
Kånagae KÅNAGAE

I was also able to issue the following query:
SELECT First_Name, UCASE(First_Name) FROM Person WHERE UCASE(First_Name) LIKE '%Å%'
This also returned for me the two same records.

I ran these tests on both PSQLv10.13.015 and PSQLv9.52.056, the current versions available from Pervasive. If you have a test system with an older v9.50.077, then I would urge you to patch it to 9.52.056 from the free download on Pervasive's web site, and re-test. If it STILL doesn't work, then the problem may be in the string that your application is providing via the OEM conversion, and not in the SQL engine.

lizoft

ASKER

I run my Dos program on Windows XP in a dos-window, using PSQL v8.x (test customer) and PSQL v9.50.077 (my test enviroment). Will download and patch to 9.52.056 and the try again.

It works fine for me to when READING data. It is when using WHERE UCASE(First_Name) LIKE '%Å%' it doesn't work. I have checked Use OEM/ANSI Conversion.

Thanks so far for your help! I will come back with my result.

** 2 min later: **
Aha, YOUR data is with Windows "å". The Dos-format for "å" (Chr(134)) is in Windows. In Dos: "Båstad", shows "B stad" in PCC. But with OEM/ANSI Conversion will it work fine in VB as it uses ODBC. What I want is Ucase() to convert " " to "ý". Chr(134) to Chr(143) in ASCII code. That would convert å to Å when OEM/ANSI Conversion is on. The big problem seams to be that my tables data is in Dos-format. The solution (?) is to use CREATE FUNCTION and make an own Ucase() that changes my selected ascii-codes, i.e. 134 to 143, 148 to 153 and some more. I sample code how to do that would help me a lot!

lizoft

ASKER

Hmm, the characters in my text is translated in the page. But the ascii codes is correct.
sshot-505.jpg

lizoft

ASKER

And here the result as it looks in VB with OEM/ANSI Conversion.
sshot-506.jpg

ASKER CERTIFIED SOLUTION

Bill Bach

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lizoft

ASKER

This did finally work! Needed to change SET :result = REPLACE(:result,'å','Å'); to SET :result = REPLACE(:result,Char(134),Char(143)); as you wrote. The sat thing is that this is really slow. Using it in a Super Seach with some 20 textfields and all of them with LIKE.
- WHERE myField LIKE 'mytext' = 0,5 sec
- WHERE Ucase(myField) LIKE 'mytext' = 2,5 sec (built in function that doesnt work well with OEM/ANSI Convertion)
- WHERE My_Ucase(myField) LIKE 'mytext' = 10 sec (UDT)
But I did for sure learn something today; It's better to use real Windows tables than to convert Dos tables on the fly.

Again, thanks a lot BillBach!