How do I use 'N' in my prepared SELECT statement
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
2
down vote
favorite
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
New contributor
add a comment |
up vote
2
down vote
favorite
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
New contributor
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
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
New contributor
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
New contributor
New contributor
edited 18 hours ago
New contributor
asked yesterday
Los Porcos
112
112
New contributor
New contributor
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
up vote
2
down vote
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
21 hours ago
Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant.
– Los Porcos
20 hours ago
add a comment |
up vote
1
down vote
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 |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
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
21 hours ago
Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant.
– Los Porcos
20 hours ago
add a comment |
up vote
2
down vote
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
21 hours ago
Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant.
– Los Porcos
20 hours ago
add a comment |
up vote
2
down vote
up vote
2
down vote
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 yesterday
Peter B
209110
209110
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
21 hours ago
Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant.
– Los Porcos
20 hours ago
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
21 hours ago
Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant.
– Los Porcos
20 hours ago
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
21 hours ago
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
21 hours ago
Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant.
– Los Porcos
20 hours ago
Using PARAM_STR_NATL doesn't give me an error but I get no results with this contant.
– Los Porcos
20 hours ago
add a comment |
up vote
1
down vote
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 |
up vote
1
down vote
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 |
up vote
1
down vote
up vote
1
down vote
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 yesterday
David Browne - Microsoft
9,676725
9,676725
add a comment |
add a comment |
Los Porcos is a new contributor. Be nice, and check out our Code of Conduct.
Los Porcos is a new contributor. Be nice, and check out our Code of Conduct.
Los Porcos is a new contributor. Be nice, and check out our Code of Conduct.
Los Porcos is a new contributor. Be nice, and check out our Code of Conduct.
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
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
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
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
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