Characterset, VFP9, and Oracle 10g

[torturedmind]

Hi all. I know most of you have experienced using Oracle as the back-end and VFP as the front-end in one way or another. I've posted this question in the Oracle 10g forum last week and it seems to me the guys there are too busy to answer my question. See my original post here. So I posted this here hoping to find even if a bit of an answer. Anyway, here is my scenario:

We have an Oracle 10g on an RH9 server with the following NLS settings:

PARAMETER                   VALUE                       
--------------------------- ----------------------------
NLS_LANGUAGE                AMERICAN                    
NLS_TERRITORY               AMERICA                     
NLS_CURRENCY                $                           
NLS_ISO_CURRENCY            AMERICA                     
NLS_NUMERIC_CHARACTERS      .,                          
NLS_CHARACTERSET            UTF8                        
NLS_CALENDAR                GREGORIAN                   
NLS_DATE_FORMAT             DD-MON-RR                   
NLS_DATE_LANGUAGE           AMERICAN                    
NLS_SORT                    BINARY                      
NLS_TIME_FORMAT             HH.MI.SSXFF AM              
NLS_TIMESTAMP_FORMAT        DD-MON-RR HH.MI.SSXFF AM    
NLS_TIME_TZ_FORMAT          HH.MI.SSXFF AM TZR          
NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY           $                           
NLS_COMP                    BINARY                      
NLS_LENGTH_SEMANTICS        BYTE                        
NLS_NCHAR_CONV_EXCP         FALSE                       
NLS_NCHAR_CHARACTERSET      AL16UTF16                   
NLS_RDBMS_VERSION           10.2.0.3.0

In my dev pc (WinXP SP3), I set the registry value of NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 which actually coincides with my OS's code page value:

\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\Codepage\ACP = 1252

My problem is, whenever I enter, say an "Ñ" or any multi-byte character, to a table with a VARCHAR2(5 BYTE) column, the error message:

ORA-12899: Value too large for column "TESTDB"."TESTTABLE"."TESTCOL" (actual: 6, maximum: 5)

always appear. I've already asked Mr. Google and also searched the Oracle forum. Most answers I've found for the same problem is that, the issue is in the client and not the database on the server. I've tried doing their instructions step-by-step to no avail. So now am hoping maybe you guys know the answer. Even how irrelevant your ideas may seem, I'm ready and willing to try them. Heck am ready to try anything at this point. Please, anyone?

TIA

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Dan Freeman]

You may need to wrap values being sent to the database in StrConv() to provide something the back end will accept.

 

[torturedmind]

Sorry for my late reply. And thanks Dan for replying.

Ok so I ran a test on a dummy table with only 1 field (AAA type VARCHAR2 with length 30) by issuing:

anychar = "Ñ"     [COLOR=green]&& which is equivalent to CHR(0209)[/color]
singl2dbl2utf = STRCONV(STRCONV(anychar, 1), 9)     [COLOR=green]&& converts sinlge to double to UTF-8[/color]
INSERT INTO testtable (aaa VALUES singl2dbl2utf)

When I issue TABLEUPDATE() the following error message appears:

Connectivity error: [Oracle][ODBC][Ora]ORA-12899: value too large for column "TESTDB"."TESTTABLE"."AAA" (actual: 33, maximum: 30)

If my understanding is correct, I can't directly convert my string to UTF-8 because it's a single byte so I need to convert it first to double-byte then to UTF-8. But I guess that's wrong because it wouldn't throw me an error if it isn't.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."