Serialize to JSON when postgresql has field as boolean data type

claudiomarconi · 2015-12-27 15:39:31

My project is based on an existing database (Postgresql). The tables that have the boolean fields, when I try to serialize to JSON, the result is "isactive": t when in fact it should be "isactive": true. How can I fix this?

unit ServiceRemoteSQL;

interface
uses
  mORMot,
  SynCommons,
  SynDB,
  RESTModel;

type
  TServiceRemoteSQL = class(TInterfacedObject, IRemoteSQL)
  private
      procedure Connect;
  protected
    fProps: TSQLDBConnectionProperties;
  public
    destructor Destroy; override;
  public
    function Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON;
  end;

implementation
uses
  SysUtils;

{ TServiceRemoteSQL }

procedure TServiceRemoteSQL.Connect;
begin
  fProps := aProps;
end;

destructor TServiceRemoteSQL.Destroy;
begin
  FreeAndNil(fProps);
  inherited;
end;

function TServiceRemoteSQL.Execute(const aSQL: RawUTF8; aExpectResults,
  aExpanded: Boolean): RawJSON;
var
  Res: ISQLDBRows;
begin
  Connect;
  if fProps = nil then
    raise Exception.Create('Call Connect method before Execute');

    Res := fProps.ExecuteInlined(aSQL, aExpectResults);

  if Res = nil  then
    Result := ''
  else
    Result := Res.FetchAllAsJSON(aExpanded); //error occurs here. When field is a boolean
end;

end.

aProps: TSQLDBConnectionProperties; //comes from RESTModel.pas

when I call:

     
  sCommand := 'select * from users where user_name = ''marconi'' ';
  sJSON := FServiceRemoteSQL.Execute(StringToUTF8(sCommand), True, True);

Error occours. Message from Log Error:

20151227 12251527 ! EXC EInterfaceFactoryException {"Message":"TInterfacedObjectFakeClient.FakeCall(IRemoteSQL.Execute) failed: 'Invalid returned JSON content: expects {\"result\":...}, got {\"result"} at 011D6ECF mORMot.RaiseError (46603) stack trace API

my table in database (Postgresql) and my Model :

CREATE TABLE users
(
  id serial NOT NULL,
  user_name character varying(50) NOT NULL,
  login character(40) NOT NULL,
  password character varying(80) NOT NULL,
  isactive boolean NOT NULL DEFAULT false,
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  
  CONSTRAINT users_pkey PRIMARY KEY (id )
)  
  WITH (
  OIDS=FALSE
);



TBaseObject = class(TInterfacedPersistent)
published
  property Id: Integer read FId write SetId;
  property Created_At: TDateTime ...;
  property Updated_At: TDateTime ...;
end;

TUsers = class(TBaseObject)
published
  property UserName: string read FUserName write SetUserName;
  property Login: string ...;
  property Password: string ...;
  property IsActive: Boolean ...;
end;

Any help will be appreciated.

ab · 2015-12-27 16:02:35

Which data provider library to you use?
SynDBODBC? SynDBZeos? any other?

It is hard to understand what is your code like, with a lot of missing info.

In SynDB, there is no "boolean" native type.
We usually use an integer field, e.g. at ORM level.

claudiomarconi · 2015-12-27 16:14:46

I'm using Zeos (7.2.0). In Server app:

  aProps := TSQLDBZEOSConnectionProperties.Create(
     TSQLDBZEOSConnectionProperties.URI(dPostgreSQL, ServerName, '', False),
     DBName,  'postgres', '#nx'
  );

claudiomarconi · 2015-12-27 16:21:36

My Sever (.dpr:)

program OpecServer;

{$APPTYPE CONSOLE}

uses
  SynCommons,
  SynLog,
  mORMot,
  mORMotSQLite3,
  SynSQLite3Static,
  mORMotDB,
  mORMotHttpServer,
  SynDB,
  SynDBZEOS,
  RESTModel,
  Classes,
  SysUtils,
  ServiceRemoteSQL in 'Services\ServiceRemoteSQL.pas';

// data model unit, shared between server and client

var
  i: Integer;
  aModel: TSQLModel;
  //aProps: TSQLDBConnectionProperties;
  ListParams: TStrings;
  ServerName: string;
  DBName: string;
  s: string;
{ TServiceConfiguration }


label
  msg_usage;
begin
  // set logging abilities
  SQLite3Log.Family.Level := LOG_VERBOSE;
  SQLite3Log.Family.EchoToConsole := LOG_VERBOSE;
  SQLite3Log.Family.PerThreadLog := ptIdentifiedInOnFile;
  // ODBC driver e.g. from http://ftp.postgresql.org/pub/odbc/versions/msi

  if ParamCount = 0 then
    goto msg_usage;

  if ParamCount > 0 then
    ListParams := TStringList.Create;

  for i := 0 to ParamCount do
  begin
    s := ParamStr(i);
    ListParams.Add(s);

    if ListParams.IndexOf('--help') > 0 then
    begin
      if Assigned(ListParams) then
        ListParams.Free;
      goto msg_usage;
    end;
  end;

  ServerName :=  ListParams.Values['--server'];
  DBName :=  ListParams.Values['--db_name'];

  if (Trim(ServerName) = '') or (Trim(DBName)='') then
    goto msg_usage;

  aProps := TSQLDBZEOSConnectionProperties.Create(
     TSQLDBZEOSConnectionProperties.URI(dPostgreSQL, ServerName, '', False),
    DBName,  'postgres', '#nx');
  try

    aModel := DataModel;

    // use PostgreSQL database for all tables
    VirtualTableExternalRegisterAll(aModel,aProps);
    try
      // create the main mORMot server
      aRestServer := TSQLRestServerDB.Create(aModel,':memory:',false); // authentication=false
      try

        // optionally execute all PostgreSQL requests in a single thread
        aRestServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
        aRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;
        // create tables or fields if missing
        aRestServer.CreateMissingTables;
        // serve aRestServer data over HTTP
        aHttpServer := TSQLHttpServer.Create(SERVER_PORT,[aRestServer],'+',useHttpApiRegisteringURI);

        {: Included by me }
        // register our IRemoteSQL service on the server side
        aRestServer.ServiceRegister(TServiceRemoteSQL, [TypeInfo(IRemoteSQL)], sicClientDriven).
        // fProps should better be executed/released in the one main thread
           SetOptions([], [optExecInMainThread, optFreeInMainThread]);

        try
          aHttpServer.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
          Sleep(200); // allow all HTTP threads to be launched and logged

          writeln('Background server is running.'#10);
          write('Press [Enter] to close the server.');
          ConsoleWaitForEnterKey;
        finally
          aHttpServer.Free;
        end;
      finally
        aRestServer.Free;
      end;
    finally
      aModel.Free;
    end;
  finally
    aProps.Free;
  end;

msg_usage:
      WriteLn('Usar:  --server={nome do servidor} --db_name={nome do banco de dados}');


end.

claudiomarconi · 2015-12-27 16:30:16

my ServiceRemoteSQL.pas:

unit ServiceRemoteSQL;

interface
uses
  mORMot,
  SynCommons,
  SynDB,
  RESTModel;

type
  TServiceRemoteSQL = class(TInterfacedObject, IRemoteSQL)
  private
      procedure Connect;
  protected
    fProps: TSQLDBConnectionProperties;
  public
    destructor Destroy; override;
  public // implements IRemoteSQL methods
    //procedure Connect(aEngine: TRemoteSQLEngine; const aServerName, aDatabaseName,
    //  aUserID, aPassWord: RawUTF8);
    function GetTableNames: TRawUTF8DynArray;
    function Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON;
  end;

implementation
uses
  SysUtils;

{ TServiceRemoteSQL }

procedure TServiceRemoteSQL.Connect;
begin
  fProps := aProps;
end;

destructor TServiceRemoteSQL.Destroy;
begin
  FreeAndNil(fProps);
  inherited;
end;

function TServiceRemoteSQL.Execute(const aSQL: RawUTF8; aExpectResults,
  aExpanded: Boolean): RawJSON;
var
  Res: ISQLDBRows;
begin
  Connect;
  if fProps = nil then
    raise Exception.Create('Connect is requered before call Execute');

  try
    Res := fProps.ExecuteInlined(aSQL, aExpectResults);
  except on E: Exception do
    // study way to send error message to client
  end;

  if Res = nil  then
    Result := ''
  else
    Result := Res.FetchAllAsJSON(aExpanded);
end;

function TServiceRemoteSQL.GetTableNames: TRawUTF8DynArray;
begin
  Connect;
  if fProps=nil then
    raise Exception.Create('Connection is required before GetTableNames');
  fProps.GetTableNames(result);
end;

end.

and my RESTMode.pas

unit RESTModel;

interface

uses
  SynCommons,
  mORMot,
  mORMotHttpServer,
  mORMotDB,
  mORMotSQLite3,
  mORMotDDD,
  SynSQLite3Static,
  SynDB;
type


  TPicturePackage = record
    FileName: RawUTF8;
    Binary: TByteDynArray;
  end;

  IPictureService = Interface(IInvokable)
    ['{524E3861-0F6B-49BC-A41C-4563B653CC21}']
    function GetPicture(Index: Integer): TPicturePackage;
    function GetPictureCQRS(Index: Integer; out APicture: TPicturePackage): TCQRSResult;
  end;

  IRemoteSQL = interface(IInvokable)
    ['{9A60C8ED-CEB2-4E09-87D4-4A16F496E5FE}']
    //procedure Connect(aEngine: TRemoteSQLEngine; const aServerName, aDatabaseName,
    //  aUserID, aPassWord: RawUTF8);
    function GetTableNames: TRawUTF8DynArray;
    function Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON;
  end;


function DataModel: TSQLModel;

const
  SERVER_ROOT = 'root';
  SERVER_PORT = '888';
var
  aRestServer: TSQLRestServerDB;
  aProps: TSQLDBConnectionProperties;
  aHttpServer: TSQLHttpServer;


implementation

{: my }
uses
  Person;

function DataModel: TSQLModel;
begin

  result := TSQLModel.Create([TPerson],SERVER_ROOT);
//  TPerson.AddFilterOrValidate('Name',TSynValidateText.Create); // ensure exists
end;

{ TServiceConfiguration }


end.

How can I fix this ? There are easy way ?

ab · 2015-12-27 17:21:55

You do not show how you created your TSQLDBConnectionProperties instance...
Which one are you using?

claudiomarconi · 2015-12-27 17:42:26

in RESTModel.pas:

var
  aProps: TSQLDBConnectionProperties;

In OpecServer.dpr:

  aProps := TSQLDBZEOSConnectionProperties.Create(
     TSQLDBZEOSConnectionProperties.URI(dPostgreSQL, ServerName, '', False),
    DBName,  'postgres', '#nx');

mORMot Open Source

#1 2015-12-27 15:39:31

Serialize to JSON when postgresql has field as boolean data type

#2 2015-12-27 16:02:35

Re: Serialize to JSON when postgresql has field as boolean data type

#3 2015-12-27 16:14:46

Re: Serialize to JSON when postgresql has field as boolean data type

#4 2015-12-27 16:21:36

Re: Serialize to JSON when postgresql has field as boolean data type

#5 2015-12-27 16:30:16

Re: Serialize to JSON when postgresql has field as boolean data type

#6 2015-12-27 17:21:55

Re: Serialize to JSON when postgresql has field as boolean data type

#7 2015-12-27 17:42:26

Re: Serialize to JSON when postgresql has field as boolean data type

Board footer