query a nullable VARCHAR column fails with PDO

Question

In this :

public static function getHCIwith(string $issuer, string $number, ?string $version=null) : ? HealthCareIdentifier{
        $q = null;
        $e = null;
        $ret = null;

        $jurisdiction = HealthCareJurisdiction::cast(HealthCareJurisdiction::getJurisdictionForIssuer($issuer));
        if (!$jurisdiction) {
            self::logQueryFail("Could not find a  Health Care Jurisdiction issuer [$issuer]");
            return $ret;
        }

        $sql = "SELECT * FROM HealthCareIdentifier 
                 WHERE issuer_id=:ii
                   AND  number=:nn
                   AND version=:ver 
        ";
        try {
            if ($q = self::getClassPDO()->prepare($sql)) {
                $q->bindValue('ii' , $jurisdiction->getId() , \PDO::PARAM_INT);
                $q->bindValue('nn' , $number , \PDO::PARAM_STR);
                $q->bindValue('ver' , $version , \PDO::PARAM_STR);
                if ($q->execute()) {
                    $rows = $q->fetchAll();
                    if($rows && count($rows)==1){
                        $ret = HealthCareIdentifier::fromAssociativeArray($rows[0]);
                    }
                } else {
                    self::logQueryFail('Failed query to get a Health Care Identifier' , $q , $e);
                }
            } else {
                self::logQueryFail('Error preparing the sql statement to query a Health Care Identifier' , $q , $e);
            }
        } catch (\Exception $e) {
            self::logQueryFail('Exception when querying a Health Care Identifier' , $q , $e);
        }
        return $ret;
    }

when invoked with "RAMQ", "REDACTED", null , the query succeeds, but returns no rows, even if there is a record with the same values.

If on the other hand i change the select to AND version IS NULL, the query returns the record.

I am trying to avoid having a variable geometry select and binds depending on the nullness of a field, obviously not succeeding. What am I missing?

table :

CREATE TABLE `HealthCareIdentifier` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`patient_id` bigint(20) NOT NULL,
`issuer_id` bigint(20) NOT NULL,
`number` varchar(50) NOT NULL,
`version` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_identifier` (`issuer_id`,`number`,`version`),
KEY `idx_patient` (`patient_id`),
KEY `idx_issuer` (`issuer_id`),
CONSTRAINT `fk_patienthcn_jurisdiction` FOREIGN KEY (`issuer_id`) REFERENCES `HealthCareJurisdiction` (`id`),
CONSTRAINT `fk_patienthcn_patient` FOREIGN KEY (`patient_id`) REFERENCES `Patient` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=44547 DEFAULT CHARSET=utf8mb4

php 7.2.8, mysql 5.7.12

There's no magic way binding null to make it `IS NULL` in the query. You will need to alter the query manually to append `IS NULL` if the parameter is null and do not bind it afterwards — Royal Bg, Apr 15 '20 at 08:42
@RoyalBg albeit this would work for a nullable BIGINT ... arghhhh — YvesLeBorg, Apr 15 '20 at 08:44
Interesting, have a look at https://stackoverflow.com/questions/48643967/php-pdo-prepared-statement-bind-null-value and see if that solves the problem (i.e. use `<=>` instead) — Nigel Ren, Apr 15 '20 at 08:44
The only thing that pops in mind is to use `PDO::PARAM_NULL` instead of string if the value is null — Royal Bg, Apr 15 '20 at 08:45
@Nigel The NULL-Safe operator seems nice. Honestly, I've never tried it — Royal Bg, Apr 15 '20 at 08:47
why you're calling logQueryFail on the every line? why not to write a single function that would log all errors? — Your Common Sense, Apr 15 '20 at 09:01
@YourCommonSense :) that is the next refactoring branch on this codebase. logQueryFail hase some side-effects, poor call. — YvesLeBorg, Apr 15 '20 at 09:04

query a nullable VARCHAR column fails with PDO

0 Answers0