Delphi XE5 RestClient and PHP generated JSON with boolean data types

Question

I'm doing my first Android app with delphi XE5.

I need to access data in a PostgreSQL database running on the intranet.

Given that access to the database directly from mobile devices is not recommended, I have installed Apache Web server and PHP application to provide data.

Everything works fine, but I have a problem with Boolean fields:

This is a sample table:

create table resources(
  resource_id integer not null primary key,
  resource_name varchar(50),
  is_working boolean not null default false
);

INSERT INTO resources (resource_id, resource_name, is_working) VALUES (1,'Lathe 01', false);
INSERT INTO resources (resource_id, resource_name, is_working) VALUES (2,'Lathe 02', true);
INSERT INTO resources (resource_id, resource_name, is_working) VALUES (3,'Press 01', true);

The PHP script execute the query on the database:

select * from resources;

and uses json_econder function to return data in json format:

... 
  $r = $dbh->query($sql);
  if ($r) {      
    $f = $r->fetchAll();        
    echo json_encode($f);  
  }
...

the returned data is as follows:

[{"resource_id": 1, "0": 1, "resource_name": "Lathe 01", "1": "Lathe 01", "is_working": false, "2": false}, 
 {"resource_id": 2, "0": 2, "resource_name": "Lathe 02", "1": "Lathe 02", "is_working": true, "2": true} 
 {"resource_id": 3, "0": 3, "resource_name": "Press 01", "1": "Press 01", "is_working": true, "2": true}]

The Delphi program contains the following components:

RESTClient1: TRestClient
RESTResponse1: RESTResponse
RestRequest1: TRestRequest
RESTResponseDataSetAdapter1: TRESTResponseDataSetAdapter
ClientDataSet1: TClientDataset

In component RESTResponseDataSetAdapter1 I have created the necessary fields:

resource_id (ftInteger)
resource_name (ftString)
is_working (ftBoolean)

and i have updated the ClientDataSet1 fields according to RESTResponseDataSetAdapter1.

If I run the App, in the command

RESTRequest1.Execute;

Is raised the exception:

Invalid value for field 'is_working'

If I run the program in debug mode is raised two exceptions:

EVariantTypeCastError with message 'Cold not convert variant of type (UnicodeString) into type (Boolean)'

and then:

EDatabse Error with message 'Invalid value for field 'is_working''

What goes wrong?

@TLama: thanks for correcting my question, I explored the changes and I figured out how to highlight the various languages! Now I try to build a rest server in Delphi and check the result... — AndreaBoc

reckface reckface · Accepted Answer · 2015-04-07T09:12:31

The problem is in the unit REST.Response.Adapter. the callbacks for parsing your data into a dataset are not quite finished.

procedure TCustomJSONDataSetAdapter.CB_CollectFieldDefs(const AJSONObject: TJSONObject);
var
  LJSONPair: TJSONPair;
begin
  for LJSONPair in AJSONObject do
  begin
    DoAddDataSetFieldDef(LJSONPair.JsonString.Value, ftString);
  end;
end;
// and
procedure TCustomJSONDataSetAdapter.CB_CollectFieldDefs(const AJSONObject: TJSONObject);
var
  LJSONPair: TJSONPair;
begin
  for LJSONPair in AJSONObject do
  begin
    DoAddDataSetFieldDef(LJSONPair.JsonString.Value, ftString);
  end;
end;

In your case the CB_CollectFieldDefs is not called as you have manually created the dataset field definitions. This should make parsing easier, but sadly that isn't the case. This is because the TJsonPair in the TJsonObject does not correctly parse boolean json values. By the time CB_CollectFieldData is called all the boolean values are empty strings. So we get the exception 'Could not convert variant of type (UnicodeString) into type (Boolean)'.

Delphi XE5 RestClient and PHP generated JSON with boolean data types

1 Answers