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

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 =3D 'nnnnnnnnn',
@s_Recipient =3D N'Vergölst GmbH (Business)',
@b_Rechargeable =3D 1,
@b_AllowOverride =3D 0


I've tried using ...

$s_Recipient =3D 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.1=
50

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 =3D OFF, STATISTICS_NORECOMPUTE =3D OFF, IGNORE_DUP_KEY
=3D OFF, ALLOW_ROW_LOCKS =3D ON, ALLOW_PAGE_LOCKS =3D ON) ON [PRIMARY]
) ON [PRIMARY]


and

ALTER PROCEDURE [dbo].[usp_HandleDirectory]
@s_PhoneNumber varchar(20) =3D Null,
@s_Recipient nvarchar(255) =3D Null,
@b_Rechargeable bit =3D 1, -- Rechargeable by default.
@b_AllowOverride bit =3D 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 =3D 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 =3D 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 =3D Phones_Directory.UniqueID
END

--=20
-----
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=3DZEND002498&r=3D213474=
731
ZOPA : http://uk.zopa.com/member/RQuadling

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