lizoft
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...
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...
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.
Can you help me with code for making own Ucase that also converts å -> Å, ä -> Ä and ö -> Ö?
Can't use PSQLv10 as it has no DOS-support.
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...
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...
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.
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.
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!
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!
ASKER
Hmm, the characters in my text is translated in the page. But the ascii codes is correct.
sshot-505.jpg
sshot-505.jpg
ASKER
And here the result as it looks in VB with OEM/ANSI Conversion.
sshot-506.jpg
sshot-506.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
- 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!
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.