Recent

Author Topic: Fetching a bit field from MariaDB always returns 0  (Read 1260 times)

kinlion

  • Jr. Member
  • **
  • Posts: 82
  • I Love Lazarus
Fetching a bit field from MariaDB always returns 0
« on: May 27, 2020, 12:45:24 pm »
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][+][-]
  1. CREATE TABLE test_bit(flag BIT(1));
  2. INSERT INTO test_bit(flag) VALUES(1);
  3. SELECT * FROM test_bit; // Surely this returns value 1
  4.  

But, when I test it using lazarus:
Code: Pascal  [Select][+][-]
  1.   with AppDB.newQuery do // create a new TSQLQuery and set it's Database, etc.
  2.   try
  3.     SQL.Text := 'select * from test_bit';
  4.     Open;
  5.     if Fields[0].AsInteger <> 1 then
  6.     begin
  7.       raise Exception.Create('bit field value error!');
  8.     end;
  9.     Close;
  10.   finally
  11.     Free;
  12.   end;
  13.  

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

  • Hero Member
  • *****
  • Posts: 691
Re: Fetching a bit field from MariaDB always returns 0
« Reply #1 on: June 03, 2020, 07:21:52 am »
Furthermore, 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

  • Jr. Member
  • **
  • Posts: 82
  • I Love Lazarus
Re: Fetching a bit field from MariaDB always returns 0
« Reply #2 on: June 18, 2020, 03:05:31 pm »
Why 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

  • Hero Member
  • *****
  • Posts: 528
Re: Fetching a bit field from MariaDB always returns 0
« Reply #3 on: June 18, 2020, 04:11:13 pm »
I attached a working version.

kinlion

  • Jr. Member
  • **
  • Posts: 82
  • I Love Lazarus
Re: Fetching a bit field from MariaDB always returns 0
« Reply #4 on: June 22, 2020, 02:20:35 am »
I attached a working version.

Sorry  but I can't open the project :(

Okoba

  • Hero Member
  • *****
  • Posts: 528
Re: Fetching a bit field from MariaDB always returns 0
« Reply #5 on: June 22, 2020, 12:31:44 pm »
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][+][-]
  1. procedure TForm1.FormCreate(Sender: TObject);
  2. begin
  3.   with SQLConnector1 do
  4.   begin
  5.     ConnectorType := 'MySQL 5.7';
  6.     HostName := '???';
  7.     UserName := '???';
  8.     Password := '???';
  9.     DatabaseName := '???';
  10.     Transaction := SQLTransaction1;
  11.     Connected := True;
  12.   end;
  13.   with SQLQuery1 do // create a new TSQLQuery and set it's Database, etc.
  14.     try
  15.       Transaction := SQLTransaction1;
  16.       SQL.Text := 'select * from test_bit';
  17.       Open;
  18.       if Fields[0].AsInteger <> 1 then
  19.       begin
  20.         raise Exception.Create('bit field value error!');
  21.       end;
  22.       Close;
  23.     finally
  24.       Free;
  25.     end;
  26. end;  

 

TinyPortal © 2005-2018