How do I use 'N' in my prepared SELECT statement
I'm connecting to an SQL Server 2008 database using pdo and the ODBC Driver 13 for SQL Server.
I'm trying to run the following query which searches the database for Chinese characters:
SELECT TOP (10) ... WHERE (sItemName LIKE N:term1)
$text = "%简况%";
$query = $this->DBH->prepare($sql);
$query->bindParam(':term1', $text, PDO::PARAM_STR );
$query->execute();
This query returns results with random characters so it looks like the Character encoding is somehow incorrect.
I was able to return the same results by running this query directly on the DB using Heidi:
SELECT TOP (10) ... WHERE (sItemName LIKE '%简况%')
Changing the query to the following by adding an 'N' before the string returned the correct results:
SELECT TOP (10) ... WHERE (sItemName LIKE N'%简况%')
But when I try to do the same with my prepared statement as shown below I get a syntax error:
SELECT TOP (10) ... WHERE (CI.sItemName LIKE N:term1)
$text = "%简况%";
$query = $this->DBH->prepare($sql);
$query->bindParam(':term1', $text, PDO::PARAM_STR );
$query->execute();
Here's the error:
Syntax error or access violation: 102 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'N:'.
So my question is how do I use 'N' in my prepared statement without getting an error. I'm not a DBO by trade so apologies if I've left anything out!
This returns the expected result:
$seach_term = (string)'简况';
$text = '%'.$seach_term.'%';
$query = $this->DBH->prepare("SELECT * FROM Articles WHERE sHeadline LIKE :term");
$query->bindParam(':term', $text, PDO::PARAM_STR);
But using the PARAM_STR_NATL constant doesn't return anything:
$query->bindParam(':term', $text, PDO::PARAM_STR_NATL);
sql-server sql-server-2008 php odbc character-set
add a comment |
I'm connecting to an SQL Server 2008 database using pdo and the ODBC Driver 13 for SQL Server.
I'm trying to run the following query which searches the database for Chinese characters:
SELECT TOP (10) ... WHERE (sItemName LIKE N:term1)
$text = "%简况%";
$query = $this->DBH->prepare($sql);
$query->bindParam(':term1', $text, PDO::PARAM_STR );
$query->execute();
This query returns results with random characters so it looks like the Character encoding is somehow incorrect.
I was able to return the same results by running this query directly on the DB using Heidi:
SELECT TOP (10) ... WHERE (sItemName LIKE '%简况%')
Changing the query to the following by adding an 'N' before the string returned the correct results:
SELECT TOP (10) ... WHERE (sItemName LIKE N'%简况%')
But when I try to do the same with my prepared statement as shown below I get a syntax error:
SELECT TOP (10) ... WHERE (CI.sItemName LIKE N:term1)
$text = "%简况%";
$query = $this->DBH->prepare($sql);
$query->bindParam(':term1', $text, PDO::PARAM_STR );
$query->execute();
Here's the error:
Syntax error or access violation: 102 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'N:'.
So my question is how do I use 'N' in my prepared statement without getting an error. I'm not a DBO by trade so apologies if I've left anything out!
This returns the expected result:
$seach_term = (string)'简况';
$text = '%'.$seach_term.'%';
$query = $this->DBH->prepare("SELECT * FROM Articles WHERE sHeadline LIKE :term");
$query->bindParam(':term', $text, PDO::PARAM_STR);
But using the PARAM_STR_NATL constant doesn't return anything:
$query->bindParam(':term', $text, PDO::PARAM_STR_NATL);
sql-server sql-server-2008 php odbc character-set
add a comment |
I'm connecting to an SQL Server 2008 database using pdo and the ODBC Driver 13 for SQL Server.
I'm trying to run the following query which searches the database for Chinese characters:
SELECT TOP (10) ... WHERE (sItemName LIKE N:term1)
$text = "%简况%";
$query = $this->DBH->prepare($sql);
$query->bindParam(':term1', $text, PDO::PARAM_STR );
$query->execute();
This query returns results with random characters so it looks like the Character encoding is somehow incorrect.
I was able to return the same results by running this query directly on the DB using Heidi:
SELECT TOP (10) ... WHERE (sItemName LIKE '%简况%')
Changing the query to the following by adding an 'N' before the string returned the correct results:
SELECT TOP (10) ... WHERE (sItemName LIKE N'%简况%')
But when I try to do the same with my prepared statement as shown below I get a syntax error:
SELECT TOP (10) ... WHERE (CI.sItemName LIKE N:term1)
$text = "%简况%";
$query = $this->DBH->prepare($sql);
$query->bindParam(':term1', $text, PDO::PARAM_STR );
$query->execute();
Here's the error:
Syntax error or access violation: 102 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'N:'.
So my question is how do I use 'N' in my prepared statement without getting an error. I'm not a DBO by trade so apologies if I've left anything out!
This returns the expected result:
$seach_term = (string)'简况';
$text = '%'.$seach_term.'%';
$query = $this->DBH->prepare("SELECT * FROM Articles WHERE sHeadline LIKE :term");
$query->bindParam(':term', $text, PDO::PARAM_STR);
But using the PARAM_STR_NATL constant doesn't return anything:
$query->bindParam(':term', $text, PDO::PARAM_STR_NATL);
sql-server sql-server-2008 php odbc character-set
I'm connecting to an SQL Server 2008 database using pdo and the ODBC Driver 13 for SQL Server.
I'm trying to run the following query which searches the database for Chinese characters:
SELECT TOP (10) ... WHERE (sItemName LIKE N:term1)
$text = "%简况%";
$query = $this->DBH->prepare($sql);
$query->bindParam(':term1', $text, PDO::PARAM_STR );
$query->execute();
This query returns results with random characters so it looks like the Character encoding is somehow incorrect.
I was able to return the same results by running this query directly on the DB using Heidi:
SELECT TOP (10) ... WHERE (sItemName LIKE '%简况%')
Changing the query to the following by adding an 'N' before the string returned the correct results:
SELECT TOP (10) ... WHERE (sItemName LIKE N'%简况%')
But when I try to do the same with my prepared statement as shown below I get a syntax error:
SELECT TOP (10) ... WHERE (CI.sItemName LIKE N:term1)
$text = "%简况%";
$query = $this->DBH->prepare($sql);
$query->bindParam(':term1', $text, PDO::PARAM_STR );
$query->execute();
Here's the error:
Syntax error or access violation: 102 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'N:'.
So my question is how do I use 'N' in my prepared statement without getting an error. I'm not a DBO by trade so apologies if I've left anything out!
This returns the expected result:
$seach_term = (string)'简况';
$text = '%'.$seach_term.'%';
$query = $this->DBH->prepare("SELECT * FROM Articles WHERE sHeadline LIKE :term");
$query->bindParam(':term', $text, PDO::PARAM_STR);
But using the PARAM_STR_NATL constant doesn't return anything:
$query->bindParam(':term', $text, PDO::PARAM_STR_NATL);
sql-server sql-server-2008 php odbc character-set
sql-server sql-server-2008 php odbc character-set
edited Nov 14 '18 at 10:47
Los Porcos
asked Nov 13 '18 at 15:33
Los PorcosLos Porcos
112
112
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You should bind them using the (relatively new) PDO_PARAM_STR_NATL
constant. This constant is available in PHP 7.2 and up. It will cause the desired N
prefix to be added.
$query->bindParam(':term1', $text, PDO::PDO_PARAM_STR_NATL);
In case you're interested, some history can be found here:
https://bugs.php.net/bug.php?id=60818
The Github commit that is linked there shows how N
is added (lines 178-184):
https://github.com/php/php-src/commit/4afce8ec8c6660ebd9f9eb174d2614361d1c6129
Strange, I get the following error message when using this constant. PHP Fatal error: Uncaught Error: Undefined class constant 'PDO_PARAM_STR_NATL' I'm on PHP72. Will do some more digging. Thanks
– Los Porcos
Nov 14 '18 at 8:27
Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant.
– Los Porcos
Nov 14 '18 at 9:04
Looks like it's an issue with the subquery. For some reason the param placeholder isn't converted to NVARCHAR when being used in an IN operator.
– Los Porcos
Nov 15 '18 at 8:01
add a comment |
The N is only needed for literals. In a prepared statement you bind typed parameters to parameter placeholders. The parameters will be typed as NVarchar and set to Unicode values.
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222441%2fhow-do-i-use-n-in-my-prepared-select-statement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You should bind them using the (relatively new) PDO_PARAM_STR_NATL
constant. This constant is available in PHP 7.2 and up. It will cause the desired N
prefix to be added.
$query->bindParam(':term1', $text, PDO::PDO_PARAM_STR_NATL);
In case you're interested, some history can be found here:
https://bugs.php.net/bug.php?id=60818
The Github commit that is linked there shows how N
is added (lines 178-184):
https://github.com/php/php-src/commit/4afce8ec8c6660ebd9f9eb174d2614361d1c6129
Strange, I get the following error message when using this constant. PHP Fatal error: Uncaught Error: Undefined class constant 'PDO_PARAM_STR_NATL' I'm on PHP72. Will do some more digging. Thanks
– Los Porcos
Nov 14 '18 at 8:27
Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant.
– Los Porcos
Nov 14 '18 at 9:04
Looks like it's an issue with the subquery. For some reason the param placeholder isn't converted to NVARCHAR when being used in an IN operator.
– Los Porcos
Nov 15 '18 at 8:01
add a comment |
You should bind them using the (relatively new) PDO_PARAM_STR_NATL
constant. This constant is available in PHP 7.2 and up. It will cause the desired N
prefix to be added.
$query->bindParam(':term1', $text, PDO::PDO_PARAM_STR_NATL);
In case you're interested, some history can be found here:
https://bugs.php.net/bug.php?id=60818
The Github commit that is linked there shows how N
is added (lines 178-184):
https://github.com/php/php-src/commit/4afce8ec8c6660ebd9f9eb174d2614361d1c6129
Strange, I get the following error message when using this constant. PHP Fatal error: Uncaught Error: Undefined class constant 'PDO_PARAM_STR_NATL' I'm on PHP72. Will do some more digging. Thanks
– Los Porcos
Nov 14 '18 at 8:27
Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant.
– Los Porcos
Nov 14 '18 at 9:04
Looks like it's an issue with the subquery. For some reason the param placeholder isn't converted to NVARCHAR when being used in an IN operator.
– Los Porcos
Nov 15 '18 at 8:01
add a comment |
You should bind them using the (relatively new) PDO_PARAM_STR_NATL
constant. This constant is available in PHP 7.2 and up. It will cause the desired N
prefix to be added.
$query->bindParam(':term1', $text, PDO::PDO_PARAM_STR_NATL);
In case you're interested, some history can be found here:
https://bugs.php.net/bug.php?id=60818
The Github commit that is linked there shows how N
is added (lines 178-184):
https://github.com/php/php-src/commit/4afce8ec8c6660ebd9f9eb174d2614361d1c6129
You should bind them using the (relatively new) PDO_PARAM_STR_NATL
constant. This constant is available in PHP 7.2 and up. It will cause the desired N
prefix to be added.
$query->bindParam(':term1', $text, PDO::PDO_PARAM_STR_NATL);
In case you're interested, some history can be found here:
https://bugs.php.net/bug.php?id=60818
The Github commit that is linked there shows how N
is added (lines 178-184):
https://github.com/php/php-src/commit/4afce8ec8c6660ebd9f9eb174d2614361d1c6129
answered Nov 13 '18 at 16:14
Peter BPeter B
207110
207110
Strange, I get the following error message when using this constant. PHP Fatal error: Uncaught Error: Undefined class constant 'PDO_PARAM_STR_NATL' I'm on PHP72. Will do some more digging. Thanks
– Los Porcos
Nov 14 '18 at 8:27
Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant.
– Los Porcos
Nov 14 '18 at 9:04
Looks like it's an issue with the subquery. For some reason the param placeholder isn't converted to NVARCHAR when being used in an IN operator.
– Los Porcos
Nov 15 '18 at 8:01
add a comment |
Strange, I get the following error message when using this constant. PHP Fatal error: Uncaught Error: Undefined class constant 'PDO_PARAM_STR_NATL' I'm on PHP72. Will do some more digging. Thanks
– Los Porcos
Nov 14 '18 at 8:27
Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant.
– Los Porcos
Nov 14 '18 at 9:04
Looks like it's an issue with the subquery. For some reason the param placeholder isn't converted to NVARCHAR when being used in an IN operator.
– Los Porcos
Nov 15 '18 at 8:01
Strange, I get the following error message when using this constant. PHP Fatal error: Uncaught Error: Undefined class constant 'PDO_PARAM_STR_NATL' I'm on PHP72. Will do some more digging. Thanks
– Los Porcos
Nov 14 '18 at 8:27
Strange, I get the following error message when using this constant. PHP Fatal error: Uncaught Error: Undefined class constant 'PDO_PARAM_STR_NATL' I'm on PHP72. Will do some more digging. Thanks
– Los Porcos
Nov 14 '18 at 8:27
Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant.
– Los Porcos
Nov 14 '18 at 9:04
Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant.
– Los Porcos
Nov 14 '18 at 9:04
Looks like it's an issue with the subquery. For some reason the param placeholder isn't converted to NVARCHAR when being used in an IN operator.
– Los Porcos
Nov 15 '18 at 8:01
Looks like it's an issue with the subquery. For some reason the param placeholder isn't converted to NVARCHAR when being used in an IN operator.
– Los Porcos
Nov 15 '18 at 8:01
add a comment |
The N is only needed for literals. In a prepared statement you bind typed parameters to parameter placeholders. The parameters will be typed as NVarchar and set to Unicode values.
add a comment |
The N is only needed for literals. In a prepared statement you bind typed parameters to parameter placeholders. The parameters will be typed as NVarchar and set to Unicode values.
add a comment |
The N is only needed for literals. In a prepared statement you bind typed parameters to parameter placeholders. The parameters will be typed as NVarchar and set to Unicode values.
The N is only needed for literals. In a prepared statement you bind typed parameters to parameter placeholders. The parameters will be typed as NVarchar and set to Unicode values.
answered Nov 13 '18 at 16:05
David Browne - MicrosoftDavid Browne - Microsoft
10.8k727
10.8k727
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222441%2fhow-do-i-use-n-in-my-prepared-select-statement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown