0

I have MS SQL database with collation set to SQL_Latin1_General_CP1_CI_AS. I am connecting to the database through PHP (Nginx server on Freebsd OS). When I am trying to save characters like ജെയിംസ്, جیمز etc it is storing as ????. My default_charset is UTF-8 in PHP.

UPDATE: When I tried it from my local Windows env, it was storing the characters successfully. But from the Freebsd environment it is having problem. Below is the stored procedure call from Windows and Freebsd environment respectively.

Trace from Windows machine: 

declare @p1 int
set @p1=2
declare @p14 int
set @p14=0
exec sp_prepexec @p1 output,N'@P1 nvarchar(15),@P2 nvarchar(16),@P3 nvarchar(10),@P4 nvarchar(max),@P5 nvarchar(max),@P6 char(1),@P7 char(1),@P8 nvarchar(max),@P9 int,@P10 nvarchar(1),@P11 int OUTPUT',N'EXEC ValidateFBLogin_sp @p_UserName = @P1, @p_Email = @P2, @p_FBID = @P3, @p_DeviceToken = @P4, @p_IPAddress = @P5, @p_Latitude = @P6, @p_Longitude = @P7, @p_EndpointArn = @P8, @p_UserAccountTypeID = @P9, @p_DeviceType = @P10, @p_ErrID = @P11 OUTPUT',N'ആഷ്ലി',N'ashley@gmail.com',N'67463sgs7s',N'',N'',NULL,NULL,N'',2,N'1',@p14 output
select @p1, @p14

Trace from Freebsd: 

EXEC ValidateFBLogin_sp 
@p_UserName = 'ആഷ്ലി', @p_Email = 'ashley@gmail.com', @p_FBID = '45534552', @p_DeviceToken = '', 
@p_IPAddress = '', @p_Latitude = NULL, @p_Longitude = NULL, 
    @p_EndpointArn = '', @p_UserAccountTypeID = 2, 
@p_DeviceType = '1', @p_ErrID = 0

From windows it is prep-ending N with the in param p_UserName but in Freebsd it is not.

Update2: Regarding database connection from PHP Laravel5.3 Framework

Connection parameters: config/database.php

return [
'sqlsrvstaging' => [
    'driver' => 'sqlsrv',
    'host' => 'XXXXX',
    'database' => 'XXXXX',
    'username' => 'XXXXX',
    'password' => 'XXXXX',
    'prefix' => '',
    'charset' => 'UTF-8',
    'strict' => false,
    'pooling' => false,
    ]
];

Code is $this->pdo = DB::connection('sqlsrvstaging')->getPdo();

halfer
  • 19,824
  • 17
  • 99
  • 186
Kiren S
  • 3,037
  • 7
  • 41
  • 69

2 Answers2

0

Not sure if this works in PHP, but you can try this:

  • change the type of the column to nvarchar(xx),
  • when doing insert or update, use the N prefix.

Like this:

insert into Table(C1, C2)
values (N'123456789', N'Name')
sventevit
  • 4,766
  • 10
  • 57
  • 89
0

You cannot.

Latin-1 is a single-byte charset designed for Western Europe languages (Spanish, French...). It cannot physically store random Unicode characters like the ones you need.

You need to get back to the design table and change the types of the columns involved to its Unicode-aware counterparts (aka "national"), e.g. from VARCHAR to NVARCHAR.

You also need to ensure your PHP connection is properly configured. You don't say what extension you are using but in e.g. the SQLSRV extension you'd do this:

sqlsrv_connect($dsn, array(
    // ...
    'CharacterSet' => 'UTF-8',
));
Álvaro González
  • 142,137
  • 41
  • 261
  • 360