Skip to content

Commit 7b56c7e

Browse files
yitamyitam
committed
Added pdo extended string types and examples
1 parent d8bd35f commit 7b56c7e

5 files changed

Lines changed: 106 additions & 4 deletions

File tree

‎docs/connect/php/pdo-getattribute.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@ The following table contains the list of supported attributes.
3636
|-------------|----------------|--------------------|---------------|
3737
|PDO::ATTR_CASE|PDO|PDO::CASE_LOWER<br /><br />PDO::CASE_NATURAL<br /><br />PDO::CASE_UPPER|Specifies whether the column names should be in a specific case. PDO::CASE_LOWER forces lower case column names, PDO::CASE_NATURAL leaves the column name as returned by the database, and PDO::CASE_UPPER forces column names to upper case.<br /><br />The default is PDO::CASE_NATURAL.<br /><br />This attribute can also be set using PDO::setAttribute.|
3838
|PDO::ATTR_CLIENT_VERSION|[!INCLUDE[ssDriverPHP](../../includes/ssdriverphp_md.md)]|Array of strings|Describes the versions of the driver and related libraries. Returns an array with the following elements: ODBC version (*MajorVer*.*MinorVer*), [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Native Client DLL name and version, [!INCLUDE[ssDriverPHP](../../includes/ssdriverphp_md.md)] version (*MajorVer*.*MinorVer*.*BuildNumber*.*Revision*)|
39+
|PDO::ATTR_DEFAULT_STR_PARAM|PDO|PDO::PARAM_STR_CHAR<br /><br />PDO::PARAM_STR_NATL|If not set to PDO::PARAM_STR_CHAR, PDO::PARAM_STR_NATL is returned.|
3940
|PDO::ATTR_DRIVER_NAME|PDO|String|Always returns "sqlsrv".|
4041
|PDO::ATTR_DRIVER_VERSION|[!INCLUDE[ssDriverPHP](../../includes/ssdriverphp_md.md)]|String|Indicates the [!INCLUDE[ssDriverPHP](../../includes/ssdriverphp_md.md)] version (*MajorVer*.*MinorVer*.*BuildNumber*.*Revision*)|
4142
|PDO::ATTR_ERRMODE|PDO|PDO::ERRMODE_SILENT<br /><br />PDO::ERRMODE_WARNING<br /><br />PDO::ERRMODE_EXCEPTION|Specifies how failures should be handled by the driver.<br /><br />PDO::ERRMODE_SILENT (the default) sets the error codes and information.<br /><br />PDO::ERRMODE_WARNING raises an E_WARNING.<br /><br />PDO::ERRMODE_EXCEPTION raises an exception.<br /><br />This attribute can also be set using PDO::setAttribute.|

‎docs/connect/php/pdo-prepare.md‎

Lines changed: 74 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
---
22
title: "PDO::prepare | Microsoft Docs"
33
ms.custom: ""
4-
ms.date: "04/25/2019"
4+
ms.date: "01/31/2020"
55
ms.prod: sql
66
ms.prod_service: connectivity
77
ms.reviewer: ""
@@ -133,6 +133,33 @@ print_r($row);
133133
?>
134134
```
135135

136+
## Example
137+
The examples below show two different snippets how to use PDO::prepare with data targeted for CHAR/VARCHAR columns. Because the default encoding for PDO::prepare is UTF-8, the user can use the option `PDO::SQLSRV_ENCODING_SYSTEM` to avoid implicit conversions.
138+
139+
**Option 1**
140+
```
141+
$options = array(PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_SYSTEM);
142+
$statement = $pdo->prepare(
143+
'SELECT *
144+
FROM myTable
145+
WHERE myVarcharColumn = :myVarcharValue',
146+
$options
147+
);
148+
149+
$statement->bindValue(':myVarcharValue', 'my data', PDO::PARAM_STR);
150+
```
151+
152+
**Option 2**
153+
```
154+
$statement = $pdo->prepare(
155+
'SELECT *
156+
FROM myTable
157+
WHERE myVarcharColumn = :myVarcharValue'
158+
);
159+
$p = 'my data';
160+
$statement->bindParam(':myVarcharValue', $p, PDO::PARAM_STR, 0, PDO::SQLSRV_ENCODING_SYSTEM);
161+
```
162+
136163
<a name="emulate-prepare" />
137164

138165
## Example
@@ -211,6 +238,52 @@ The PDO_SQLSRV driver first checks the encoding specified in `PDO::bindParam()`
211238

212239
If not found, the driver checks if any encoding is set in `PDO::prepare()` or `PDOStatement::setAttribute()`. Otherwise, the driver will use the encoding specified in `PDO::__construct()` or `PDO::setAttribute()`.
213240

241+
In addition, beginning with version 5.8.0, when using PDO::prepare with `PDO::ATTR_EMULATE_PREPARES` set to true, the user may use [the extended string types introduced in PHP 7.2](https://wiki.php.net/rfc/extended-string-types-for-pdo) to ensure that the `N` prefix is used. The snippets below display various alternatives.
242+
243+
> [!NOTE]
244+
> By default, emulate prepares is set to false, in which case the extended PDO string constants will be ignored.
245+
246+
**Using driver option PDO::SQLSRV_ENCODING_UTF8 when binding**
247+
248+
```
249+
$p = '가각';
250+
$sql = 'SELECT :value';
251+
$options = array(PDO::ATTR_EMULATE_PREPARES => true);
252+
$stmt = $conn->prepare($sql, $options);
253+
$stmt->bindParam(':value', $p, PDO::PARAM_STR, 0, PDO::SQLSRV_ENCODING_UTF8);
254+
$stmt->execute();
255+
```
256+
257+
**Using the PDO::SQLSRV_ATTR_ENCODING attribute**
258+
259+
```
260+
$p = '가각';
261+
$sql = 'SELECT :value';
262+
$options = array(PDO::ATTR_EMULATE_PREPARES => true, PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_UTF8);
263+
$stmt = $conn->prepare($sql, $options);
264+
$stmt->execute([':value' => $p]);
265+
```
266+
267+
**Using the PDO constant PDO::PARAM_STR_NATL**
268+
```
269+
$p = '가각';
270+
$sql = 'SELECT :value';
271+
$options = array(PDO::ATTR_EMULATE_PREPARES => true);
272+
$stmt = $conn->prepare($sql, $options);
273+
$stmt->bindParam(':value', $p, PDO::PARAM_STR | PDO::PARAM_STR_NATL);
274+
$stmt->execute();
275+
```
276+
277+
**Setting the default string param type PDO::PARAM_STR_NATL**
278+
```
279+
$conn->setAttribute(PDO::ATTR_DEFAULT_STR_PARAM, PDO::PARAM_STR_NATL);
280+
$p = '가각';
281+
$sql = 'SELECT :value';
282+
$options = array(PDO::ATTR_EMULATE_PREPARES => true);
283+
$stmt = $conn->prepare($sql, $options);
284+
$stmt->execute([':value' => $p]);
285+
```
286+
214287
### Limitations
215288

216289
As you can see, binding is done internally by the driver. A valid query is sent to the server for execution without any parameter. Compared to the regular case, some limitations result when the parameterized query feature is not in use.

‎docs/connect/php/pdo-quote.md‎

Lines changed: 28 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
---
22
title: "PDO::quote | Microsoft Docs"
33
ms.custom: ""
4-
ms.date: "01/19/2017"
4+
ms.date: "01/31/2020"
55
ms.prod: sql
66
ms.prod_service: connectivity
77
ms.reviewer: ""
@@ -27,6 +27,14 @@ string PDO::quote( $string[, $parameter_type ] )
2727
$*string*: The string to quote.
2828

2929
$*parameter_type*: An optional (integer) symbol indicating the data type. The default is PDO::PARAM_STR.
30+
31+
New PDO constants were introduced in PHP 7.2 to add support for [binding Unicode and non-Unicode strings](https://wiki.php.net/rfc/extended-string-types-for-pdo). That is, Unicode strings can be surrounded with quotes with an N as a prefix. (i.e. N'string' instead of 'string'.)
32+
33+
1. PDO::PARAM_STR_NATL - a new type for Unicode strings, to be applied as a bitwise-OR to PDO::PARAM_STR
34+
1. PDO::PARAM_STR_CHAR - a new type for non-Unicode strings, to be applied as a bitwise-OR to PDO::PARAM_STR
35+
1. PDO::ATTR_DEFAULT_STR_PARAM - set to either PDO::PARAM_STR_NATL or PDO::PARAM_STR_CHAR to indicate a value to bitwise-OR to PDO::PARAM_STR by default
36+
37+
Beginning with version 5.8.0, you can use these constants with PDO::quote.
3038

3139
## Return Value
3240
A quoted string that can be passed to an SQL statement, or false if failure.
@@ -55,6 +63,25 @@ $stmt->execute(array($param, $param2));
5563
?>
5664
```
5765

66+
## Example
67+
68+
The following script shows a few examples of how extended string types affect PDO::quote() with PHP 7.2+.
69+
70+
```
71+
<?php
72+
$database = "test";
73+
$server = "(local)";
74+
$db = new PDO("sqlsrv:server=$server; Database=$database", "", "");
75+
76+
$db->quote('über', PDO::PARAM_STR | PDO::PARAM_STR_NATL); // N'über'
77+
$db->quote('foo'); // 'foo'
78+
79+
$db->setAttribute(PDO::ATTR_DEFAULT_STR_PARAM, PDO::PARAM_STR_NATL);
80+
$db->quote('über'); // N'über'
81+
$db->quote('foo', PDO::PARAM_STR | PDO::PARAM_STR_CHAR); // 'foo'
82+
?>
83+
```
84+
5885
## See Also
5986
[PDO Class](../../connect/php/pdo-class.md)
6087

‎docs/connect/php/pdo-setattribute.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@ Returns true on success, otherwise false.
3636
|-------------|----------------|--------------------|---------------|
3737
|PDO::ATTR_CASE|PDO|PDO::CASE_LOWER<br /><br />PDO::CASE_NATURAL<br /><br />PDO::CASE_UPPER|Specifies the case of column names.<br /><br />PDO::CASE_LOWER causes lower case column names.<br /><br />PDO::CASE_NATURAL (the default) displays column names as returned by the database.<br /><br />PDO::CASE_UPPER causes column names to upper case.<br /><br />This attribute can be set using PDO::setAttribute.|
3838
|PDO::ATTR_DEFAULT_FETCH_MODE|PDO|See the PDO documentation.|See the PDO documentation.|
39+
|PDO::ATTR_DEFAULT_STR_PARAM|PDO|PDO::PARAM_STR_CHAR<br /><br />PDO::PARAM_STR_NATL|For more information see the examples in [PDO::quote](../../connect/php/pdo-quote.md).|
3940
|PDO::ATTR_ERRMODE|PDO|PDO::ERRMODE_SILENT<br /><br />PDO::ERRMODE_WARNING<br /><br />PDO::ERRMODE_EXCEPTION|Specifies how the driver reports failures.<br /><br />PDO::ERRMODE_SILENT (the default) sets the error codes and information.<br /><br />PDO::ERRMODE_WARNING raises E_WARNING.<br /><br />PDO::ERRMODE_EXCEPTION causes an exception to be thrown.<br /><br />This attribute can be set using PDO::setAttribute.|
4041
|PDO::ATTR_ORACLE_NULLS|PDO|See the PDO documentation.|Specifies how nulls should be returned.<br /><br />PDO::NULL_NATURAL does no conversion.<br /><br />PDO::NULL_EMPTY_STRING converts an empty string to null.<br /><br />PDO::NULL_TO_STRING converts null to an empty string.|
4142
|PDO::ATTR_STATEMENT_CLASS|PDO|See the PDO documentation.|Sets the user-supplied statement class derived from PDOStatement.<br /><br />Requires `array(string classname, array(mixed constructor_args))`.<br /><br />For more information, see the PDO documentation.|

‎docs/connect/php/release-notes-php-sql-driver.md‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -48,9 +48,9 @@ Thanks a lot. 2019-03-28 (DevO= 1467988)
4848
| Support for Language option when connecting to SQL Server. | &nbsp; |
4949
| Support for PHP extended string types introduced in PHP 7.2. | &nbsp; |
5050
| Support for Data Classification sensitivity metadata retrieval. | Requires SQL Server 2019 and ODBC Driver 17.4 or above. |
51-
| Support for Always Encrypted with secure enclaves. | Requires ODBC Driver 17.5 or above. |
51+
| Support for Always Encrypted with secure enclaves. | Requires ODBC Driver 17.4 or above. |
5252
| Support configurable options for locale settings in Linux and macOS. |
53-
| Improved performance by caching metadata on fetches. | &nbsp; |
53+
| Improved performance by caching metadata on fetches and omitting redundant calls. | &nbsp; |
5454
| &nbsp; | &nbsp; |
5555

5656
## What's New in Version 5.6

0 commit comments

Comments
 (0)