Fetching a bit field from MariaDB always returns 0
lazarus / 16-Databases

----------------------------------------------------------------------
kinlion · 2020-05-27 10:45

Environment:
  Server: Ubuntu 18.04
  Database: MariaDB 10.4.13
  Client: Win10 x64

Lazarus 2.0.8 + FPC 3.0.4
Connection: TMySQL56Connection

Test:
Firstly, create a table for the test:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE test_bit(flag BIT(1));INSERT INTO test_bit(flag)
VALUES(1);SELECT * FROM test_bit; // Surely this returns value 1 
But, when I test it using lazarus:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   with
AppDB.newQuery do // create a new TSQLQuery and set it's Database,
etc.  try    SQL.Text := 'select * from test_bit';    Open;    if
Fields[0].AsInteger <> 1 then    begin      raise
Exception.Create('bit field value error!');    end;    Close; 
finally    Free;  end; 
In the above code, Fields[0].AsInteger is ALWAYS 0!

Why?
Something I did wrong or it's a bug ?

Furthermore, I checked the type of Fields[0], its ftLargeInt, and the
FieldKind of the field is fkData

The libmysql.dll I used to connect to DB server is from HeidiSQL. It's
too large to upload

----------------------------------------------------------------------
LacaK · 2020-06-03 05:21

Quote from: kinlion on May 27, 2020, 12:45:24 pmFurthermore, I checked
the type of Fields[0], its ftLargeInt, and the FieldKind of the field
is fkData

It is as expected, because BIT data type can be defined as BIT(1) to
BIT(64), so precision 64 corresponds to ftlargeInt = Int64
So MySQL56Connection recognized correctly data type and creates right
field type.

Why you get always zero values is question. Can you test with server
MySQL 5.6 or above (not MariaDB?)

----------------------------------------------------------------------
kinlion · 2020-06-18 13:05

Quote from: LacaK on June 03, 2020, 07:21:52 amWhy you get always zero
values is question. Can you test with server MySQL 5.6 or above (not
MariaDB?)

OK, I just installed MySql8.0.20 on Ubuntu 20.04 and made a test, it
still returns 0.

----------------------------------------------------------------------
Okoba · 2020-06-18 14:11

I attached a working version.

----------------------------------------------------------------------
kinlion · 2020-06-22 00:20

Quote from: OkobaPatino on June 18, 2020, 04:11:13 pmI attached a
working version.

Sorry  but I can't open the project

----------------------------------------------------------------------
Okoba · 2020-06-22 10:31

My Lazarus version is newer than yours.
Make a clean project and put TSQLConnector, TSQLQuery, SQLTransaction1
on the form, copy this code and run.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.FormCreate(Sender: TObject);begin  with SQLConnector1 do 
begin    ConnectorType := 'MySQL 5.7';    HostName := '???';   
UserName := '???';    Password := '???';    DatabaseName := '???';   
Transaction := SQLTransaction1;    Connected := True;  end;  with
SQLQuery1 do // create a new TSQLQuery and set it's Database, etc.   
try      Transaction := SQLTransaction1;      SQL.Text := 'select *
from test_bit';      Open;      if Fields[0].AsInteger <> 1 then     
begin        raise Exception.Create('bit field value error!');     
end;      Close;    finally      Free;    end;end;