Home » Open Source » Programming Interfaces » PHP insert or update nvarchar2 (PHP 5.2.16, Oracle 9i, OCI8 from Instant Client 10.2.x)
PHP insert or update nvarchar2 [message #542604] Wed, 08 February 2012 03:30 Go to next message
stephan.wild
Messages: 4
Registered: February 2012
Location: Regensburg - Bavaria - Ge...
Junior Member
Hello together,
I have a little problem with inserting / updateing data from PHP to Oracle. My Oracle charset setup is:
NLS_CHARACTERSET=WE8MSWIN1252 and NLS_NCHAR_CHARACTERSET=AL16UTF16

Now I want to put in some cyrillic signs. My SQL-statement looks like:
update table set 
field_1 = utl_raw.cast_to_nvarchar2(utl_raw.convert('пуищ','AMERICAN_AMERICA.AL16UTF16','AMERICAN_AMERICA.AL32UTF8')), 
field_2 = utl_raw.cast_to_nvarchar2(utl_raw.convert('','AMERICAN_AMERICA.AL16UTF16','AMERICAN_AMERICA.AL32UTF8')) 
where rtrim(field_3,' ') = '1014006981' and langcode = 'RU';


My error message is:

SQL-Fehler: ORA-01465: invalid hex number
01465. 00000 -  "invalid hex number"
*Cause:    
*Action:


I hope someone of has the answer.

Thank you very much


Stephan
Re: PHP insert or update nvarchar2 [message #542622 is a reply to message #542604] Wed, 08 February 2012 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
Quote:
field_2 = utl_raw.cast_to_nvarchar2(utl_raw.convert('','AMERICAN_AMERICA.AL16UTF16','AMERICAN_AMERICA.AL32UTF8'))

NULL (empty string) is the same NULL in any character set, so just replace it by "field_2 = NULL".

2/
utl_raw.convert takes a RAW data as first parameter not a string.
You have to first apply utl_raw.cast_to_raw but I'm not sure it will.
Why don't you just use "field_1 = N'пуищ'" (if field1 is of NVARCHAR2 datatype, if not please post the datatype of each column)?

Regards
Michel



[Updated on: Wed, 08 February 2012 04:07]

Report message to a moderator

Re: PHP insert or update nvarchar2 [message #542632 is a reply to message #542622] Wed, 08 February 2012 04:45 Go to previous messageGo to next message
stephan.wild
Messages: 4
Registered: February 2012
Location: Regensburg - Bavaria - Ge...
Junior Member
Hi
Thank you for you're hints.
Sometimes there is an empty string in my PHP form. I can't set the field with "NULL".

Yes the fields are NVARCHAR2.

I tried the N'cyrillic' signs, but now I have these signs in my database:
¿¿¿¿

Very strange.

Re: PHP insert or update nvarchar2 [message #542642 is a reply to message #542632] Wed, 08 February 2012 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does your NLS_LANG setting of your application server support cyrillic?

1/
Otherwise, Oracle replaces (on insert/update) these character by what it names a replacement character (which depends on the character set but is often ¿).

2/
This replacement character can only be seen in the opposite way: the characters are correct in the database but when you query it Oracle replaces them by ¿ (due to a wrong value of NLS_LANG).

The way to see in which case you are is to dump the value inside the database (using DUMP function).

In the end, you should set your application server NLS_LANG to <YOUR-LANGUAGE>_<YOUR-COUNTRY>.AL32UTF8 and restart.

Regards
Michel
Re: PHP insert or update nvarchar2 [message #542677 is a reply to message #542642] Wed, 08 February 2012 09:28 Go to previous messageGo to next message
stephan.wild
Messages: 4
Registered: February 2012
Location: Regensburg - Bavaria - Ge...
Junior Member
Hi,

my environment for NLS_LANG is set to "AMERICAN_AMERICA.AL32UTF8".
When I dump this special field I get:
Typ=1 Len=8: 0,191,0,191,0,191,0,191

Another field with cyrillic signs I get:
Typ=1 Len=60: 4,58,4,62,4,59,4,76,4,70,4,62,0,32,4,68,4,62,4,64,4,60,4,75,0,32,0,86,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32


I don't know what this data means.
Re: PHP insert or update nvarchar2 [message #542678 is a reply to message #542677] Wed, 08 February 2012 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
character values less than 128 are ASCII characters
[Solved] Re: PHP insert or update nvarchar2 [message #543198 is a reply to message #542678] Mon, 13 February 2012 06:47 Go to previous messageGo to next message
stephan.wild
Messages: 4
Registered: February 2012
Location: Regensburg - Bavaria - Ge...
Junior Member
Sorry for my late reply.
Found a solution:
I do a conversation from UTF8-string to HEX with a function:
function strToHex($string)
{
    $hex='';
    for ($i=0; $i < strlen($string); $i++)
    {
        $hex .= dechex(ord($string[$i]));
    }
    return $hex;
}


then I use this SQL:

$field_1 = strToHex($field_1);
$field_2 = strToHex($field_2);
update table set 
field_1 = utl_raw.cast_to_nvarchar2(utl_raw.convert('$field_1','AMERICAN_AMERICA.AL16UTF16','AMERICAN_AMERICA.AL32UTF8')), 
field_2 = utl_raw.cast_to_nvarchar2(utl_raw.convert('$field_2','AMERICAN_AMERICA.AL16UTF16','AMERICAN_AMERICA.AL32UTF8')) 
where rtrim(field_3,' ') = '1014006981' and langcode = 'RU';


Thank you very very much.
Re: [Solved] Re: PHP insert or update nvarchar2 [message #543202 is a reply to message #543198] Mon, 13 February 2012 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Re: [Solved] Re: PHP insert or update nvarchar2 [message #666671 is a reply to message #543202] Mon, 20 November 2017 03:43 Go to previous message
anjalik097
Messages: 2
Registered: November 2017
Location: Pune
Junior Member

Thanks for this doubt I was looking for it. Sometime the hint also important than whole solution
Previous Topic: Software Engineer
Next Topic: IST sysdate default is not working with Entity Framwork
Goto Forum:
  


Current Time: Thu Mar 28 05:30:05 CDT 2024