How exactly do I get UTF-8 data into SQL Server on Windows via ODBC SQL Native Client 10.0?

[Richard Quadling]

Hi.

I'm trying to get data into an MS SQL Server 2005 database. The data
is coming to me as UTF-8 encoded data. The data is primarily European
languages (so I've got a few accented characters), but I know I've got
Korean, Vietnamese and some Japanese coming later.

I'm using PHP 5.3.2-dev. The SQL Server is 2005 and the ODBC driver
I'm using is SQL Native Client 10.0

I've cut'n'pasted my table and SP below...

I'm calling the SP (all the phone numbers have been edited) ...

EXEC PhoneBilling.dbo.usp_HandleDirectory
        @s_PhoneNumber   = 'nnnnnnnnn',
        @s_Recipient     = N'Vergölst GmbH (Business)',
        @b_Rechargeable  = 1,
        @b_AllowOverride = 0


I've tried using ...

$s_Recipient = mb_convert_encoding($s_Recipient, 'UCS-2', 'UTF-8');

But this produces a string with nulls in which terminates the sql
string inappropriately.

I've also tried UTF-16

If I do nothing, the data in SQL is ...

45639 nnnnnnnnn Vergölst GmbH (Business) 1 0 2009-10-07 08:29:42.137
45641 nnnnnnnnm Vergölst GmbH (Mobile) 1 0 2009-10-07 08:29:42.150

If I execute the above EXEC call in MSSQL Server Management Studio, I
get valid results when I select the inserted rows.

48388 nnnnnnnno Vergölst GmbH (Business) 1 0 2009-10-07 08:44:42.673
48389 nnnnnnnnp Vergölst GmbH (Business) 1 0 2009-10-07 08:46:22.730

which is what I want.


I must be missing a trick as I'm sure it can't be that difficult.

I'm not in a position to try PHP6 yet.


Any suggestions would be gratefully received.


Regards,

Richard Quadling.



CREATE TABLE [dbo].[Phones_Directory](
        [UniqueID] [int] IDENTITY(1,1) NOT NULL,
        [PhoneNumber] [varchar](20) NOT NULL,
        [Recipient] [nvarchar](255) NULL,
        [Rechargeable] [bit] NOT NULL CONSTRAINT
[DF_Phones_Directory_Rechargeable]  DEFAULT ((1)),
        [AllowOverride] [bit] NOT NULL CONSTRAINT
[DF_Phones_Directory_AllowOverride]  DEFAULT ((0)),
        [Added] [datetime] NOT NULL,
 CONSTRAINT [PK_Phones_Directory] PRIMARY KEY CLUSTERED
(
        [UniqueID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


and

ALTER PROCEDURE [dbo].[usp_HandleDirectory]
        @s_PhoneNumber varchar(20) = Null,
        @s_Recipient nvarchar(255) = Null,
        @b_Rechargeable bit = 1, -- Rechargeable by default.
        @b_AllowOverride bit = 0 -- Not overridable by default.
AS
BEGIN
        DECLARE @tbl_Results TABLE
                (
                UniqueID int,
                Result int
                )

        SET NOCOUNT ON;

        IF NOT EXISTS
                (
                SELECT
                        UniqueID
                FROM
                        Phones_Directory
                WHERE
                        @s_PhoneNumber = PhoneNumber
                )
                BEGIN
                INSERT INTO
                        Phones_Directory
                                (
                                PhoneNumber,
                                Recipient,
                                Rechargeable,
                                AllowOverride,
                                Added
                                )
                        VALUES
                                (
                                @s_PhoneNumber,
                                @s_Recipient,
                                @b_Rechargeable,
                                @b_AllowOverride,
                                GetDate()
                                )

                INSERT INTO
                        @tbl_Results
                        VALUES
                                (
                                SCOPE_IDENTITY(),
                                1 -- Indicates a new entry
                                )
                END
        ELSE
                BEGIN
                INSERT INTO
                        @tbl_Results
                SELECT
                        UniqueID,
                        2 -- Indicates a pre-existing entry
                FROM
                        Phones_Directory
                WHERE
                        @s_PhoneNumber = PhoneNumber
                END

        -- Return the data
        SET NOCOUNT OFF
        SELECT
                CAST(Results.Result AS int) AS 'Result',
                Phones_Directory.*
        FROM
                @tbl_Results Results
                LEFT OUTER JOIN
                Phones_Directory
                        ON
                                Results.UniqueID = Phones_Directory.UniqueID
END

--
-----
Richard Quadling
"Standing on the shoulders of some very clever giants!"
EE : http://www.experts-exchange.com/M_248814.html
Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731
ZOPA : http://uk.zopa.com/member/RQuadling

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[Pierre Joye]

hi,

On Wed, Oct 7, 2009 at 10:36 AM, Richard Quadling
<rquadling@...> wrote:
> Hi.
>
> I'm trying to get data into an MS SQL Server 2005 database. The data
> is coming to me as UTF-8 encoded data.

Yes, pdo ODBC converts the results from UCS-2 to UTF-8. UCS-2 is what
sqlserver uses.

> The data is primarily European
> languages (so I've got a few accented characters), but I know I've got
> Korean, Vietnamese and some Japanese coming later.
>
> I'm using PHP 5.3.2-dev. The SQL Server is 2005 and the ODBC driver
> I'm using is SQL Native Client 10.0
> I've tried using ...
>
> $s_Recipient = mb_convert_encoding($s_Recipient, 'UCS-2', 'UTF-8');

Why do you want to go back to UCS-2?

Cheers,
--
Pierre

http://blog.thepimp.net | http://www.libgd.org

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php