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...
lizoftAsked:
Who is Participating?
 
Bill BachConnect With a Mentor PresidentCommented:
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...
CREATE FUNCTION "My_UCASE" ( IN :str VARCHAR(250)) RETURNS VARCHAR(250) AS
BEGIN
	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;
END;

Open in new window

0
 
Bill BachPresidentCommented:
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.
0
 
lizoftAuthor Commented:
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.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Bill BachPresidentCommented:
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...
0
 
Bill BachPresidentCommented:
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.
0
 
lizoftAuthor Commented:
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!
0
 
lizoftAuthor Commented:
Hmm, the characters in my text is translated in the page. But the ascii codes is correct.
sshot-505.jpg
0
 
lizoftAuthor Commented:
And here the result as it looks in VB with OEM/ANSI Conversion.
sshot-506.jpg
0
 
lizoftAuthor Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.