[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2008-11-19
Medium Priority
Last Modified: 2013-12-25

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...
Question by:lizoft
  • 5
  • 4
LVL 29

Expert Comment

by:Bill Bach
ID: 22999730
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.

Author Comment

ID: 23001716
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.
LVL 29

Expert Comment

by:Bill Bach
ID: 23005023
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...
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 29

Expert Comment

by:Bill Bach
ID: 23005325
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.

Author Comment

ID: 23006079
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!

Author Comment

ID: 23006136
Hmm, the characters in my text is translated in the page. But the ascii codes is correct.

Author Comment

ID: 23006194
And here the result as it looks in VB with OEM/ANSI Conversion.
LVL 29

Accepted Solution

Bill Bach earned 2000 total points
ID: 23006434
Hmm.  I must, then, stand by my original statement that I have no way to test this functionality for you.  However, here's the shell of the application that I wrote.

You can extend this by changing the characters to exactly what you need.  For more fine-grained control, change the characters inside the quotes to CHAR(134), CHAR(143), etc.

If you have a problem with the REPLACE function working with the OEM character sets, then another alternative is to build the string one character at a time within the loop, but this will be MUCH slower to execute.

Please test this & let me know what you get...
	DECLARE :result VARCHAR(250);
	-- Convert string to upper case first, which should reduce the workload
	SET :result = UCASE(:str);
	-- Next, find any special characters and fix them, too	
	SET :result = REPLACE(:result,'å','Å');
	SET :result = REPLACE(:result,'ä','Ä');
	SET :result = REPLACE(:result,'ö','Ö');
	RETURN :result;

Open in new window


Author Closing Comment

ID: 31518911
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!

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question