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

[Richard Quadling <rquadling@xxxxxxxxxxxxxx>]

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