Out of range value for column TINYINT UNSIGNED

[Dmitry Onoshko 0]

I’m using FireDAC with MariaDB. Since I have quite a lot of stuff done with stored procedures, I wrote a simple function like this:

function TMyDataModule.OpenProc(const AName: String; AArgs: array of Variant): TFDStoredProc;
begin
  Result := TFDStoredProc.Create(nil);
  try
    Result.Connection := MyConnectionComponent;
    Result.Open(AName, AArgs);
  except
    Result.Free;
    raise;
  end;
end;

In my database I have a table:

CREATE TABLE `Data` (
	`ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`When` TIMESTAMP NOT NULL DEFAULT current_timestamp(),
	`MyID` BIGINT(20) UNSIGNED NOT NULL,
	`Bool1` TINYINT(1) NOT NULL,
	`FOURCC` CHAR(4) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`Comment` TINYTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`Arg1` TINYINT(3) UNSIGNED NOT NULL,
	`Arg2` TINYINT(3) UNSIGNED NOT NULL,
	`Arg3` TINYINT(3) UNSIGNED NOT NULL,
	`Arg4` TINYINT(3) UNSIGNED NOT NULL,
	PRIMARY KEY (`ID`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

… and a stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `MyProc`(
	IN `aMyID` BIGINT,
	IN `aBool1` TINYINT(1),
	IN `aFOURCC` CHAR(4),
	IN `aComment` TINYTEXT,
	IN `aArg1` TINYINT UNSIGNED,
	IN `aArg2` TINYINT UNSIGNED,
	IN `aArg3` TINYINT UNSIGNED,
	IN `aArg4` TINYINT UNSIGNED
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	INSERT INTO `Data`(`MyID`, `Bool1`, `FOURCC`, `Comment`, `Arg1`, `Arg2`, `Arg3`, `Arg4`)
		VALUES(aMyID, aBool1, aFOURCC, aComment, aArg1, aArg2, aArg3, aArg4);
END

When I try to invoke MyProc, I get this error: “Out of range value for column 'aArg2' at row 0”.

 

In Delphi debugger I see AArgs containing perfectly valid values: a number, a boolean, a 4-character string, an empty string for aComment and four integers perfectly in the range of 0..255. But at OpenProc call it fails.

 

I used Wireshark to see what data actually goes “onto the wire”: the byte values are those they should be. The same procedure with the same argument values runs just fine with HeidiSQL. But HeidiSQL seems to use simple text queries while FireDAC sends arguments in their binary representation.

 

What could be the reason and fix to the problem? Thanks.

Edited August 24 by Dmitry Onoshko

[Dmitry Onoshko 0]

P.S. Turns out the values of AArgs matter: those were (124, 152, 10, 2). I tried (127, 0, 0, 0) and it worked. Then I tried (128, 0, 0, 0) and it failed with error saying about aArg1 this time.

 

When I inspected AArgs in the debugger adding TVarData(AArgs[...]) to watches, I saw the aArgX array items were of type Byte inside the Variant. But somehow it seems FireDAC manages to ignore both the UNSIGNED part on the SQL side and the unsignedness of the variants on the client side.