develooper Front page | perl.dbi.users | Postings from July 2020

DBI NULLs Test

From:
Carl Reynolds
Date:
July 11, 2020 19:03
Subject:
DBI NULLs Test
Message ID:
2413a78d-3954-4b0f-b0e7-7b1f06248f48@hyperbole-software.com
Using DBI:mysql:database=mysql;hostname=localhost, db version:
5.5.5-10.1.41-MariaDB-0ubuntu0.18.04.1
=> Drop table 'dbi__null_test_tmp', if it already exists...
=> Create table 'dbi__null_test_tmp'...
=> Insert 4 rows into the table...
   Inserting values (1, 'Homer')
   Inserting values (2, NULL)
   Inserting values (3, 'Marge')
   Inserting values (4, NULL)

=> Testing clause style 0: WHERE mycol = ?...
   Selecting row with Marge
   Selecting rows with NULL
=> WHERE clause style 0 returned incorrect results.
   Non-NULL test rows returned these row ids: 3
   The NULL test rows returned these row ids:

=> Testing clause style 1: WHERE NVL(mycol, '-') = NVL(?, '-')...
   Selecting row with Marge
DBD::mysql::st execute failed: FUNCTION mysql.NVL does not exist at
perl_dbi_nulls_test.pl line 123.

=> Testing clause style 2: WHERE ISNULL(mycol, '-') = ISNULL(?, '-')...
   Selecting row with Marge
DBD::mysql::st execute failed: Incorrect parameter count in the call to
native function 'ISNULL' at perl_dbi_nulls_test.pl line 123.

=> Testing clause style 3: WHERE DECODE(mycol, ?, 1, 0) = 1...
   Selecting row with Marge
DBD::mysql::st execute failed: Incorrect parameter count in the call to
native function 'DECODE' at perl_dbi_nulls_test.pl line 123.

=> Testing clause style 4: WHERE mycol = ? OR (mycol IS NULL AND ? IS
NULL)...
   Selecting row with Marge
   Selecting rows with NULL
=> WHERE clause style 4 is supported.

=> Testing clause style 5: WHERE mycol = ? OR (mycol IS NULL AND
SP_ISNULL(?) = 1)...
   Selecting row with Marge
DBD::mysql::st execute failed: FUNCTION mysql.SP_ISNULL does not exist
at perl_dbi_nulls_test.pl line 123.

=> Testing clause style 6: WHERE mycol = ? OR (mycol IS NULL AND ? = 1)...
   Selecting row with Marge
   Selecting rows with NULL
=> WHERE clause style 6 is supported.

------------------------------------------------------------------------
2 styles are supported:
    Style 4: WHERE mycol = ? OR (mycol IS NULL AND ? IS NULL)
    Style 6: WHERE mycol = ? OR (mycol IS NULL AND ? = 1)
------------------------------------------------------------------------



nntp.perl.org: Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at ask@perl.org | Group listing | About