PHP PDO Sqlsrv always return every values as string

Question

How do I get value from DB with correct field type?

I'm using PDO Sqlsrv to fetch data from DB and I always receive data as string like: customer_id : "1" My customer_id field type is integer it should be customer_id : 1 But phone_number field type is varchar it return to correct type as string like: phone_number : "12345678"

EDIT:

I'm using PHP 5.5. I've tried this.

$this->dbh->setAttribute(PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE, true);

And I got this error Fatal error: Undefined class constant 'SQLSRV_ATTR_FETCHES_NUMERIC_TYPE'

My PDO code :

$this->dbh = new PDO('sqlsrv:Server=' . $_ENV['DB_HOST'] . ';Database=' . $_ENV['DB_NAME'], $_ENV['DB_USER'], $_ENV['DB_PASS']);
$this->dbh->exec('set names ' . $_ENV['DB_CHAR_SET']);
$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->dbh->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
$this->dbh->setAttribute(PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE, true); // Got Error 
$this->dbh->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_UTF8);

$this->dbh->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false); It is right? I got this error when i'm using PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE Fatal error: Undefined class constant 'SQLSRV_ATTR_FETCHES_NUMERIC_TYPE' — Vintage Beef
– Vintage Beef, Commented Nov 28, 2018 at 8:20
Can you please clarify if the problem is the setting not taking effect (as stated in the question) or the setting no existing at all (as stated in comments)? You should also mention the version of the SQLSRV library. Please edit relevant information into the question itself. — Álvaro González
– Álvaro González, Commented Nov 28, 2018 at 8:32
Have a look at a similar problem, it seems like you are not using the appropriate driver, or you have not installed it. drupal.org/project/sqlsrv/issues/1037558 — user2560539
– user2560539, Commented Nov 28, 2018 at 8:57

Zhorov · Accepted Answer · 2018-11-28 13:16:58Z

2

Solution:

I will answer exactly to your question and what you may try is to use PDOStatement::bindColumn. You use PHP 5.5 and based on support matrix, your PHP Driver version should be 3.1 or 3.2. In this version, based on change log, SQLSRV_ATTR_FETCHES_NUMERIC_TYPE is not supported.

...
$stmt->bindColumn('customer_id', $customer_id, PDO::PARAM_INT);
$stmt->bindColumn('phone_number', $phone_number, PDO::PARAM_STR);
while ($row = $stmt->fetch(PDO::FETCH_BOUND)) {
    var_dump($customer_id);
    var_dump($phone_number);
    echo '<br>';
}
...

edited Nov 28, 2018 at 13:16

answered Nov 28, 2018 at 10:58

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Vintage Beef Over a year ago

That's work great. How do I check my PHP Driver version on sql server?

Zhorov Over a year ago

@VintageBeef With 1. <?php phpinfo();?> and check for sqlsrv sections or 2. var_dump($this->dbh->getAttribute(PDO::ATTR_DRIVER_NAME)); and value of ["ExtensionVer"] or 3. Properties for extensions's dll file (php_pdo_sqlsrv_*.dll for example). In my case result is: 4.3.0+9904.

Vintage Beef Over a year ago

I got this 3.0.2.2R (Unofficial)

Zhorov Over a year ago

@VintageBeef Yes, but based on docs, it's unsupported version. If you use PHP 5.5, your option is driver version 3.1 or 3.2 (again based on support matrix).

Zhorov Over a year ago

@VintageBeef Yes, SQL Server 2008 R2 is supported by these versions (again based on support matrix documentation). But I can not test this.

|

Álvaro González · Accepted Answer · 2018-11-28 10:13:08Z

0

You don't say what version of SQLSRV you are using but chances are that it's older than 4.0.8, when the directive was implemented:

[Added]
- SQLSRV_ATTR_FETCHES_NUMERIC_TYPE attribute support.

I don't think there's a simple generic way to determine what your version is (phpinfo()'s output does not display it) but you can just upgrade to latest version.

Update: try sqlsrv_client_info().

edited Nov 28, 2018 at 10:13

answered Nov 28, 2018 at 10:02

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

2 Comments

Vintage Beef Over a year ago

I got this SQL SERVER NATIVE CLIENT 11.0

Álvaro González Over a year ago

That's not the SQLSRV extension.

Collectives™ on Stack Overflow

PHP PDO Sqlsrv always return every values as string

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related