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

claudiomarconi
Member
Registered: 2015-12-23
Posts: 5

Serialize to JSON when postgresql has field as boolean data type

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,604
Website

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

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.

Offline

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

claudiomarconi
Member
Registered: 2015-12-23
Posts: 5

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

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

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

Offline

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

claudiomarconi
Member
Registered: 2015-12-23
Posts: 5

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

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.

Offline

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

claudiomarconi
Member
Registered: 2015-12-23
Posts: 5

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

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 ?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,604
Website

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

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

Offline

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

claudiomarconi
Member
Registered: 2015-12-23
Posts: 5

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

in RESTModel.pas:

var
  aProps: TSQLDBConnectionProperties;

In OpecServer.dpr:

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

Offline

Board footer

Powered by FluxBB