OraFAQ Forum: Programming Interfaces » PHP insert or update nvarchar2

Home » Open Source » Programming Interfaces » PHP insert or update nvarchar2 (PHP 5.2.16, Oracle 9i, OCI8 from Instant Client 10.2.x)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PHP insert or update nvarchar2 [message #542604]

Wed, 08 February 2012 03:30

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:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: PHP insert or update nvarchar2 [message #542622 is a reply to message #542604]

Wed, 08 February 2012 04:06

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

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.

Report message to a moderator

Re: PHP insert or update nvarchar2 [message #542642 is a reply to message #542632]

Wed, 08 February 2012 05:15

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: PHP insert or update nvarchar2 [message #542677 is a reply to message #542642]

Wed, 08 February 2012 09:28

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:

Code: [Select all] [Show/ hide]

Typ=1 Len=8: 0,191,0,191,0,191,0,191

Another field with cyrillic signs I get:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: PHP insert or update nvarchar2 [message #542678 is a reply to message #542677]

Wed, 08 February 2012 09:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

character values less than 128 are ASCII characters

Report message to a moderator

[Solved] Re: PHP insert or update nvarchar2 [message #543198 is a reply to message #542678]

Mon, 13 February 2012 06:47

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:

Code: [Select all] [Show/ hide]

function strToHex($string)
{
    $hex='';
    for ($i=0; $i < strlen($string); $i++)
    {
        $hex .= dechex(ord($string[$i]));
    }
    return $hex;
}

then I use this SQL:

Code: [Select all] [Show/ hide]

$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.

Report message to a moderator