5

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);
Zhorov
  • 28,486
  • 6
  • 27
  • 52
Vintage Beef
  • 475
  • 6
  • 18
  • 1
    Have you set `ATTR_STRINGIFY_FETCHES`? – Álvaro González Nov 28 '18 at 08:19
  • `$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 Nov 28 '18 at 08:20
  • 2
    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](https://stackoverflow.com/posts/53514815/edit) itself. – Álvaro González Nov 28 '18 at 08:32
  • My question is updated. – Vintage Beef Nov 28 '18 at 08:41
  • Have a look at a similar problem, it seems like you are not using the appropriate driver, or you have not installed it. https://www.drupal.org/project/sqlsrv/issues/1037558 –  Nov 28 '18 at 08:57

2 Answers2

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>';
}
...
Zhorov
  • 28,486
  • 6
  • 27
  • 52
  • That's work great. How do I check my PHP Driver version on sql server? – Vintage Beef Nov 30 '18 at 02:33
  • 1
    @VintageBeef With 1. `` 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. – Zhorov Nov 30 '18 at 06:44
  • I got this 3.0.2.2R (Unofficial) – Vintage Beef Nov 30 '18 at 07:30
  • @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 Nov 30 '18 at 07:52
  • Thank you so much, I have to upgrade my PHP Driver right? Is 3.1 or 3.2 support on MSSQL Server 2008 R2? – Vintage Beef Nov 30 '18 at 08:11
  • 1
    @VintageBeef Yes, SQL Server 2008 R2 is supported by these versions (again based on support matrix documentation). But I can not test this. – Zhorov Nov 30 '18 at 08:16
  • Vintage Microsoft. "Yeah we thought returning everything as a string would come in handy". – Kees de Kooter Sep 02 '22 at 12:21
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().

Álvaro González
  • 142,137
  • 41
  • 261
  • 360