Case Sensitivity in MS SqlSrvr drivers

RayPooley

Here is an interesting observation.

MS driver php_pdo_sqlsrv_82_nts_x64.dll

Connection: $conn = new PDO("sqlsrv:server=$serverName;Database=$database", $uid, $pwd);

Ther is case sensitivity when accessing data using the column name.

See here that the query returns a single column of data "RefName".

Referencing $row['RefName'] (matching case) returns the cell value.
Referencing $row['refname'] (or any non matching case) returns blank.

Here is the code;

$query = "select top 5 RefName from AvailableReferences;";
         

$stmt = $conn->query($query);
        

while ( $row = $stmt->fetch() ){
     echo "Cell value ".$row['RefName']."<br />\n";
}  

echo "<br />";
$stmt = $conn->query($query);

while ( $row = $stmt->fetch() ){
     echo "Cell value ".$row['refname']."<br />\n";
}

This code produces the following:

Matching case:
Cell value STAFFID
Cell value PRODUCTCATEGORYID
Cell value PRODUCTID
Cell value SALEID
Cell value SALETYPEID

Non matching case
Cell value
Cell value
Cell value
Cell value
Cell value

Anyone familiar with this? Is there a way to ignore case?

Thanks

NogDog

RayPooley Is there a way to ignore case?

Only use snake_case names for your table/column names. 😉

Seriously, though, even if technically in certain situations you can get away with different cases, I highly recommend being consistent everywhere in your application code and database schema; obviating the need to try to remember where you can and cannot get away with using the "wrong" case (if ever).

Weedpacket

Apparently, case sensitivity of identifiers in SQL Server is affected by the collation rules in use:
https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-database-collation?view=sql-server-ver16

Changing the database collation affects the collation of things like table and column names. Why that was considered a good idea ... 🤷

RayPooley

Weedpacket
I have been developing n-tier applications for 25 years.
I have 14 databases on my MS SQL Server instance all using the same protocol.
12 .Net web and desktop applications and 2 old Classic ASP.
All stored procedure calls no inline programming.
Never had any problem with case sensitivity.
Until now.
I suspect it's the driver that's the problem.

pbismad

Case sensitivity in the identifier would mean that you must use the same letter case in the sql query statement that the identifier is defined with. If you can use any letter case in the sql query statement, such as refname, without producing an sql error, then the identifier is not case sensitive.

What you are seeing in the fetched data is the same letter case you wrote in the sql query statement is what the associative array index name is. This is true for mysql/mariadb database types as well. If you can write refname in the sql query statement and it doesn't produce an sql error, you would fetch the data using $row['refname'].

For the current query, using RefName, and php code you would be getting undefined index php errors at the reference to $row['refname'], regardless of database type.

RayPooley

pbismad
Been writing camelcase for a long time. Makes the code more readable.
Not about to change that because a MS PHP driver can't handle case.
I guess I will just have to bear it in mind,
It works if you are aware of the case sensitivity.
Just that I haven't had to deal with that kind of thing since early Oracle versions.
Long time ago.

RayPooley

NogDog
Been writing camelcase for a long time. Makes the code more readable.
Not about to change that because a MS PHP driver can't handle case.
I guess I will just have to bear it in mind,
It works if you are aware of the case sensitivity.
Just that I haven't had to deal with that kind of thing since early Oracle versions.
Long time ago.
Thought I had seen the last of it.

Weedpacket

RayPooley
As @pbismad pointed out, it has nothing to do with the driver.

But if you name a result column RefName then it's going to be called RefName in the array, not refname. Those are different strings with different values and would be different keys in an array. It has nothing to do with the database.

$array['refname' => 17, 'RefName' => 42];
echo $array['refname'],"\n";
echo $array['RefName'],"\n";
echo $array['REFNAME'], "\n";

You can see this in your own code if you look at the entire row that is retrieved:

<?php
$query = "select top 5 RefName from AvailableReferences;";
         
$stmt = $conn->query($query);

while ( $row = $stmt->fetch() ){
     var_dump($row);
}  

echo "<br />";
$stmt = $conn->query($query);

while ( $row = $stmt->fetch() ){
     var_dump($row);
}
?>

(Note that you really only need one of those because now both queries and both loops are identical.)

RayPooley

Weedpacket
What?????
Do you think that's actually live code? It's DEMO code. To demonstrate the issue.
That's why the two loops are there.
And why do you feel the need to explain what case sensitivity is?
You think people don't know?
And it has everything to do with the driver.
The driver interfaces with the database. It's code.
A statement like
Code that doesn't ignore case.

Weedpacket

RayPooley What?????
What?????
Do you think that's actually live code?

Is there anything in what I wrote that would vaguely suggest that?

It's DEMO code. To demonstrate the issue.
That's why the two loops are there.

And my code demonstrated why you were getting the results you were getting. $row['refname'] didn't have any results because the array doesn't have any element with a key of 'refname'.

So what case do you think should be used for the array key? The driver has to pick something for the sequence of bytes it is going to use for the strings that are going to be used to index the array. Is there any sane reason why it shouldn't be what was actually used in the query, on the assumption that the programmer knows what they are doing?

If you really want to use a different case for keys that is different from what is used in the query, you can tell the driver to force them to uppercase or lowercase. But it's going to have to pick something or the alternative would be to populate the array with $row['refname'], $row['refnamE'], $row['refnaMe'], $row['refnaME'], $row['refnAme'] .... $row['REFNAme'], $row['REFNAmE'], $row['REFNAMe'], and $row['REFNAME'] just in case someone gets it into their head to use any one of them.

RayPooley

Weedpacket
"Is there anything in what I wrote that would vaguely suggest that?"
It's implicit in your comment.

Case handling is easy.
You just lower case the text before making a comparison.

if (string1 == string2){ case sensitive }
if (tolower(string1) == tolower(string2){ ignore case }

Weedpacket

RayPooley It's implicit in your comment.

Where? I have been known to write demonstration code for illustrative purposes once or twice in my life; I've seen it done a few times as well, and have been known to recognise it.

RayPooley Case handling is easy.
You just lower case the text before making a comparison.
if (string1 == string2){ case sensitive }
if (tolower(string1) == tolower(string2){ ignore case }

So what's your problem?

It sounds like you want strings used as array keys to be handled in a case-insensitive manner. $a['polish'] should be the same element as $a['Polish'] (or $a['элемент'] and $a['Элемент']). I guess you could file a Feature Request on the PHP bugtracker, but it's not gonna happen.