MySQL results of stored procedure not returned correctly

[MerijnB]

Hi all,

I'm currenlty using 7.2a because I'm working with XE5 and I'm running into some strange issues.

In short, I have a single table with an unsigned bigint field (ID), with two records:
2041403756142144128
2041403818419171840

I have one stored procedure which does nothing but: select ID from table
When I run this stored procedure from the MySQL command line client (or HeidiSQL) all is fine.
When I run this stored procedure from my app using ZeosLib, one of the two records comes out wrong, I get:
2041403756142144000
2041403818419171840

I have been able to reproduce this on different machines, against different MySQL servers.
I'm about to dig into Zeos to see if I can find the cause, but I hope someone with more Zeos knowledge can help me out

Please see this SQL dump of my test db:

Code: Select all

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               5.6.15 - MySQL Community Server (GPL)
-- Server OS:                    Win64
-- HeidiSQL Version:             8.3.0.4694
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping database structure for dbTest
CREATE DATABASE IF NOT EXISTS `dbTest` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `dbTest`;

-- Dumping structure for table dbTest.tblTest
CREATE TABLE IF NOT EXISTS `tblTest` (
  `ID` bigint(20) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Dumping data for table dbTest.tblTest: ~2 rows (approximately)
/*!40000 ALTER TABLE `tblTest` DISABLE KEYS */;
REPLACE INTO `tblTest` (`ID`) VALUES
	(2041403756142144128),
	(2041403818419171840);
/*!40000 ALTER TABLE `tblTest` ENABLE KEYS */;


-- Dumping structure for procedure dbTest.spTest
DELIMITER //
CREATE DEFINER=`user`@`%` PROCEDURE `spTest`()
BEGIN
  select tblTest.ID from tblTest;
END//
DELIMITER ;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

And here (part of) my test app which shows the error:

Code: Select all

procedure TForm5.FormShow(Sender: TObject);
var ZConnection: TZConnection;
    ZQuery: TZQuery;
    ZSP: TZStoredProc;
begin
 ZConnection := TZConnection.Create(nil);
 ZQuery := TZQuery.Create(nil);
 ZSP := TZStoredProc.Create(nil);

 try
  ZConnection.HostName := '127.0.0.1';
  ZConnection.Protocol := 'mysql';
  ZConnection.Database := 'dbTest';
  ZConnection.User := 'user';
  ZConnection.Password := 'password';
  ZConnection.Connect();

  ZSP.Connection := ZConnection;
  ZSP.StoredProcName := 'spTest';
  ZSP.Open();

  Memo1.Clear();

  while not ZSP.Eof do
  begin
   Memo1.Lines.Add(IntToStr(ZSP.FieldByName('ID').AsLargeInt));
   ZSP.Next;
  end;

 finally
  ZConnection.Free();
  ZQuery.Free();
  ZSP.Free();
 end;
end;

If added a zip file with sources of the delphi project + the SQL dump of the database:

MySQLTest.zip

[MerijnB]

I have found the reason why this is happening.

When the result set is grabbed from the C connector DLL, at some point procedure TZAbstractRODataset.InternalInitFieldDefs; is called.

in that method, there is this line:

FieldType := ConvertDbcToDatasetType(GetColumnType(I));

When I call the SP, the columntype return by GetColumnType(I) is stBigDecimal, which is converted to ftFloat by ConvertDbcToDatasetType, it seems that somewhere the data is put in a float and there some of the precision is lost.

When I simply use a TZQuery to run 'select * from tblTest', the columntype returned by GetColumnType(I) is stLong, which is converted to ftLargeInt by ConvertDbcToDatasetType. This does work ok.

For now, it seems I can 'fix' this, by changing ConvertDbcToDatasetType:

Code: Select all

function ConvertDbcToDatasetType(Value: TZSQLType): TFieldType;
begin
  case Value of
    stBoolean:
      Result := ftBoolean;
    stByte, stShort:
      Result := ftSmallInt;
    stInteger:
      Result := ftInteger;
    stLong:
      Result := ftLargeInt;
    stFloat, stDouble, stBigDecimal:
      Result := ftFloat;

becomes:

Code: Select all

function ConvertDbcToDatasetType(Value: TZSQLType): TFieldType;
begin
  case Value of
    stBoolean:
      Result := ftBoolean;
    stByte, stShort:
      Result := ftSmallInt;
    stInteger:
      Result := ftInteger;
    stLong, stBigDecimal:
      Result := ftLargeInt;
    stFloat, stDouble:
      Result := ftFloat;

I have no idea what drawback this has though. The question I cannot solve so far is, why does the same table give a fieldtype stBigDecimal when called through a SP, and stLong when called through a query.

I hope someone can give some insights in this behavior.