Search the Community

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.

If I connect a TFDTable to a MariaDB table containing 2000 rows, and immediately call FindLast(), I get back True for success, but it positions on the 100th row of the table, not the 2000th. FDTable1.Open(); B:=FDTable1.FindLast(); Memo1.Lines.Add(Format('Return=%s A=%d', [NameFromBoolean(B), FDTable1A.AsInteger])); FDTable1.Close(); I'm running Delphi XE10.2. I've dug through the FireDAC source code and found it is applying LIMIT 100 to the initial select query to populate the TFDTable, but I'm not understanding why the FindLast runs in that initial query result rather than the whole table. Is there any workaround for this? Thanks.