0

I am testing the JSON data type in a MariaDB database. Here is the table create statement

CREATE TABLE `vs` (
`AutoID` INT(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`ShowID` VARCHAR(25) NULL DEFAULT '0' COLLATE 'utf8mb4_unicode_ci',
`ClientID` VARCHAR(25) NULL DEFAULT '0' COLLATE 'utf8mb4_unicode_ci',
`Categories` LONGTEXT NULL DEFAULT '0' COLLATE 'utf8mb4_bin',
`Attr` LONGTEXT NULL DEFAULT '0' COLLATE 'utf8mb4_bin',
`UUID` VARCHAR(50) NULL DEFAULT '0' COLLATE 'utf8mb4_unicode_ci',
PRIMARY KEY (`AutoID`),
UNIQUE INDEX `VSUUID` (`UUID`),
INDEX `VS-Show` (`AutoID`, `ShowID`, `ClientID`),
INDEX `VS-clientID` (`ClientID`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

Here is the sample row data

INSERT INTO `vs` (`AutoID`, `ShowID`, `ClientID`, `Categories`, `Attr`, `UUID`) VALUES (0000000001, 'Demo', 'NEA', '{"buttonfmt": "red", "categories": ["Parts", "Paint / PBE / Refinishing", "OEM / Auto Manufacturer", "Tools & Equipment", "Education / Trade Organization / Consulting", "Mechanical Repair", "Other"]}', '0', 'c41ac5cb-d900-4991-9c3e-0b710fded735');

When I execute the following perl code I do not see any data for the result column

use strict;
use DBI;
use Data::Dumper;

our $SQL;
our $sth='';
my $UUID;
my $rv;

my $dbh = DBI->connect("DBI:MariaDB:IBKREG:localhost",
                       'UserID', 'Password',
                       { RaiseError => 1, PrintError => 0 });

$SQL="select Showid, JSON_EXTRACT(categories,'$.buttonfmt') as result from ibkreg.vs where ClientID='NEA';";  
$sth  = $dbh->prepare($SQL);
$sth->execute();
while (my $hr = $sth->fetchrow_hashref) {
    print Dumper($hr);
}
exit;

The following is the output from Data::Dumper

$VAR1 = {
'Showid' => 'Demo',
'result' => undef
};

When I run the SQL statement in Heidi SQL I get "RED" in the Result field as I should. I have updated DBI and the DBD::mysql and DBD::MariaDB modules. Am I missing something?

Richard Noble
  • 1
  • 1
  • This has nothing to do with the $ in the SQL statement as I'm getting partial data back. – Richard Noble Jul 16 '20 at 17:52
  • 1
    If you had `use warnings` enabled you'd see the warnings coming from `$.` showing you that, yes, you have a string escaping issue. – AKHolland Jul 16 '20 at 18:56
  • @RichardNoble: Oh, it really is. The `$.` is being replaced with the empty string (assuming you're not reading from a file) so your query becomes - `select Showid, JSON_EXTRACT(categories,'buttonfmt') as result ...`. So you'll get `Showid` but not `result`. Which is exactly what you're seeing. Please just try escaping the `$` and report back on what you find. – Dave Cross Jul 16 '20 at 19:11
  • 1
    I was wrong, the solution was as Quentin suggested. – Richard Noble Jul 16 '20 at 19:14

0 Answers0