mORMot Open Source

Hi!!!!!

In the past I asked about transactions inside Method-based services (http://synopse.info/forum/viewtopic.php?id=1427)
and got the following code:

function TServiceRemoteSQL.Execute(const aSQL: RawUTF8; const Params: array of const; aExpanded: Boolean = True): RawJSON;
var
  Res: ISQLDBRows;
  Conn: TSQLDBConnection;
begin
  if fProps=nil then
    raise Exception.Create('Objeto database não associado.');
  Conn := fProps.ThreadSafeConnection;
  Conn.Connect;
  try
    Conn.StartTransaction;
    try
      res := fProps.Execute(aSQL, Params);
      if res=nil then
        result := ''
      else
        result := res.FetchAllAsJSON(aExpanded);
      Conn.Commit;
    except
      Conn.Rollback;
    end;
  finally
    Conn.Disconnect;
  end;
end;

This code works on my computer, but when deploy on a basic amazon server it causes an AV error... this error is intermittent.

{
"ErrorCode":500,
"ErrorText":"Exception EAccessViolation: Access violation at address 0040C45C in module 'ServidorTeste.exe'. Read of address 00200055"
}

I created a test application to try to reproduce the error, and this actually happened due to the Path structure
(containing spaces and other characters). On the server I created a small folder structure with no special characters,
but the problem persists.

It occurs after the final 'end;' but is caused by 'conn.Disconnect;' as if this line is extracted (or all with 'conn')
the error no longer exists.

On my local computer the error happens in the following function (remember, in my experiment is because the path):

function TSQLPropInfoRTTICurrency.CompareValue(Item1,Item2: TObject; CaseInsensitive: boolean): PtrInt;

Any idea is welcome...

Thanks in advance.

Hello!

I have some questions and I appreciate if you can help me, I'll be straight.

I'm create a mORMot server (without using ORM) that accesses a database Firebird 2.5.2, on the other hand there is an application
Asp.Net (Oxygen / Prism) accessing the server with Method-based services.

questions/dudes:

1) I'm not sure if this method to access the database is the best or recommended, show the main structure and would like a review.
  In tests with one access it have worked, I do not know if it would have multiple access problem as we have not performed tests.
 
  Statements for base access, within the class TServiceRemoteSQL was declared the Field FProps TSQLDBConnectionProperties.

   IRemoteSQL = interface(IInvokable)
    ['{1B6CFCCB-9077-4767-B242-A7928643DB07}']
    procedure Connect(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8);
    function  GetTableNames: TRawUTF8DynArray;
    function  Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON;
  end;

  TServiceRemoteSQL = class(TInterfacedObject, IRemoteSQL)
  protected
    fProps: TSQLDBConnectionProperties;
    FConn: TSQLDBZEOSConnection;
  public
    destructor Destroy; override;
  public // implements IRemoteSQL methods
    procedure Connect(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8);
    function  GetTableNames: TRawUTF8DynArray;
    function  Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON;
    function  ExecuteNoResult(const aSQL: RawUTF8; const Params: array of const): Integer;
  end;

and the class TServiceRemoteSQL is used inside the TServiceServer in following way:

type
  TServiceServer = class(TSQLRestServerFullMemory)
  private
    FDatabase: TServiceRemoteSQL;
  public
    constructor Create(aModel: TSQLModel; const aFileName: TFileName='';
      aBinaryFile: boolean=false; aHandleUserAuthentication: boolean=false); reintroduce; virtual;
    destructor Destroy; override;
  published
    ...
  end;
 
  ...
 
constructor TServiceServer.Create(aModel: TSQLModel; const aFileName: TFileName; aBinaryFile, aHandleUserAuthentication: boolean);
var
  vDatabase: RawUTF8;
  vDBUser  : RawUTF8;
  vDBPass  : RawUTF8;
begin
  inherited Create(aModel, aFileName, aBinaryFile, aHandleUserAuthentication);
  FDatabase := TServiceRemoteSQL.Create;
  vDatabase := StringToUTF8(Format('%s\%s', [ExtractFileDir(System.ParamStr(0)), 'Database\UTF8.FDB']));
  vDBUser := 'sysdba';
  vDBPass := 'masterkey';
  FDatabase.Connect(StringToUTF8('LocalHost'), vDatabase, vDBUser, vDBPass);
end;
 
var
  aModel: TSQLModel;
  aServer: TSQLRestServer;
  aHTTPServer: TSQLHttpServer;

begin
  with TSQLLog.Family do begin
    Level := LOG_VERBOSE;
    EchoToConsole := LOG_VERBOSE;
  end;
  AllocConsole;
  TextColor(ccLightGray);
  aModel := TSQLModel.Create([],ROOT_NAME);
  try
    aServer := TServiceServer.Create(aModel,'users.json',false, {$ifdef USE_SECURITY}True{$else}False{$endif});
    try
      {$ifdef USE_JSON}
      aServer.ServicesRouting := rmJSON_RPC;
      {$else}
      aServer.ServicesRouting := rmREST;
      {$endif}
      aHTTPServer := TSQLHttpServer.Create('8080',[aServer],'+',useHttpApiRegisteringURI);
      try
        aHTTPServer.AccessControlAllowOrigin := '*';
        writeln(#10'Background server is running.'#10);
        writeln('Press [Enter] to close the server.'#10);
        ConsoleWaitForEnterKey;
      finally
        aHTTPServer.Free;
      end;
    finally
      aServer.Free;
    end;
  finally
    aModel.Free;
  end;
end.

This would be a valid way?? Any recommendations?

2) Which class would be recommended for use with Firebird since TSQLDBZEOSConnectionProperties only supports database embeded?
   Until I started some changes for use in multi user environment, but have no time now to continue.

3) I have a problem that I believe is caused by transaction, since the information is not saved in the database, where I run a stored procedure:

  TDAL_Seguranca = class
    private
      FDatabase       : TServiceRemoteSQL;
    public
      constructor Create(pDatabase: TServiceRemoteSQL);
      function Login(const EMail: RawUTF8; Senha: RawUTF8; IP : RawUTF8): RawJSON;
  end;

  Execution:
 
function TDAL_Seguranca.Login(const EMail: RawUTF8; Senha: RawUTF8; IP : RawUTF8): RawJSON;
const
  cDML = 'Select IdUsuario, NomeUsuario, NivelAcesso, Situacao, IdEntidade, NomeFantasia, PageToGo' +
         '  From Login(%s, %s, %s)';
var
  vDML: RawUTF8;
begin
  vDML := StringToUTF8(Format(cDML, [QuotedStr(EMail), QuotedStr(Senha), QuotedStr(IP)]));
  result := FDatabase.Execute(vDML, True, True);
end;

Within the stored procedure that checks the login and get other information runs a second stored procedure that saves the
information on table LOG, indicating whether the login was successful or not, IP access, Timestamp, etc. LOG data not being saved ...
checked if the stored procedure is executed and she really is, so I suspect a Transaction problem.
At first I tried to solve using StartTransaction but an error occurs which obviously must be my misuse ...
The error code and follow. What would be the correct way to start a transaction and perform the Commit / Rollback? for this case?

"ErrorText":"Exception ESQLDBException: TSQLDBZEOSConnection on firebird-2.5:?
Liblocation=... database... should be connected"

function TServiceRemoteSQL.Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON;
var res: ISQLDBRows;
begin
  if fProps=nil then
    raise Exception.Create('Objeto database não associado.');
  fProps.MainConnection.StartTransaction;
  try
    res := fProps.ExecuteInlined(aSQL, aExpectResults);
    if res=nil then
      result := ''
    else
      result := res.FetchAllAsJSON(aExpanded);
    fProps.MainConnection.Commit;
  except
    fProps.MainConnection.Rollback;
  end;
end;

mORMot has proved very reliable and elegant, and it is for these reasons that I have used on a personal project.

Thank you very much for the excellent tool and clarification.

Hello!
I've been doing some testing with access to database mORMmot using Zeos and Firebird, for it I create a separate application that only uses TSQLDBZEOSConnectionProperties to run a query. Using as a basis of information the demo "15 - External DB performance" and thus had some doubts:

Using Embedded:
1) Using the embedded Firebird I found that it is necessary to include the unit ZDbcInterbase6, something that had not already thought that I wanted to use only the Firebird ... The absence of the unit does not cause compilation error, but generates an error when trying to connect to the server. Am I correct to include this unit? Would not it be clearer to separate what is from Interbase and what is from Firebird?

Using Server:
1) The method TSQLDBZEOSConnectionProperties.URI returns a string not usable (or I failed to use the method) where I want to inform a host and a port. To perform the tests created by hand the following string:

'zdbc:firebird-2.5://127.0.0.1:3050/eConsultor-UTF8?LibLocation=fbclient.dll;username=sysdba;password=masterkey'

The method URI should not have parameters Host, Port, User and Pass?

2) I noticed that when trying to use a Firebird server using alias (can be defined in one or more alias file aliases.conf and use the name as the string that rode hand) the class is lost and tries to create a database outside and that he lost at one point the Host and Port. This problem occurs when trying to connect. Can I use Alias? Would not it be better to use a connection string (URI return) in the method TSQLDBZEOSConnectionProperties.Create out instead of the current parameters?

3) was unable to perform a query to the server, even changing the alias for the full file name of the database. The error occurs when trying to start a transaction. Follows the code that created and below the method name and line where the error occurs. Any explanation or assistance is welcome in the case of my misuse, or if it is a bug and if they want, I can try to find a solution.

My Code:

//Executed OK
procedure TServiceRemoteSQL.Connect(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8);
var
  Server: RawUTF8;
  vDatabase: RawUTF8;
begin
//  vDatabase := aDatabaseName;
  vDatabase := StringToUTF8(Format('%s\%s', [ExtractFileDir(Application.ExeName), 'Data\ECONSULTOR-UTF8.FDB']));
  {$ifdef CPU64}
    Suffix := '64';
  {$endif}
  if fProps<>nil then
    raise Exception.Create('Connect called more than once');
//  Server := TSQLDBZEOSConnectionProperties.URI(dFirebird, 'fbembed.dll'); //'gds32.dll' 'fbclient.dll'
//  Server := 'zdbc:firebird-2.5://127.0.0.1:3050/eConsultor-UTF8?LibLocation=fbclient.dll;username=sysdba;password=masterkey';
  Server := 'zdbc:firebird-2.5://127.0.0.1:3050/E:\Trabajos\Data\ECONSULTOR-UTF8.FDB?LibLocation=fbclient.dll;username=sysdba;password=masterkey';
  fProps := TSQLDBZEOSConnectionProperties.Create(Server,vDatabase,aUserID,aPassWord);
end;


//Executed with error
function TServiceRemoteSQL.Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON;
var res: ISQLDBRows;
begin
  if fProps=nil then
    raise Exception.Create('Connect call required before Execute');
  res := fProps.ExecuteInlined(aSQL, aExpectResults);   ---------------> Error
  if res=nil then
    result := ''
  else
    result := res.FetchAllAsJSON(aExpanded);
end;


mORMot code error

procedure TZInterbase6Connection.StartTransaction;
var
  Params: TStrings;
  PTEB: PISC_TEB;
begin
  PTEB := nil;
  Params := TStringList.Create;

  { Set transaction parameters by TransactIsolationLevel }
  //Params.Values['isc_dpb_lc_ctype'] := FClientCodePage; //Set CharacterSet allways if option is set
  Params.Add('isc_tpb_version3');
  case TransactIsolationLevel of
    tiReadCommitted:
      begin
        Params.Add('isc_tpb_read_committed');
        Params.Add('isc_tpb_rec_version');
        Params.Add('isc_tpb_nowait');
      end;
    tiRepeatableRead:
      begin
        Params.Add('isc_tpb_concurrency');
        Params.Add('isc_tpb_nowait');
      end;
    tiSerializable:
      begin
        Params.Add('isc_tpb_consistency');
      end;
  else
    begin
      { Add user defined parameters for transaction }
      Params.Clear;
      Params.AddStrings(Info);
    end;
  end;

  try
    { GenerateTPB return PTEB with null pointer tpb_address from default
      transaction }
    PTEB := GenerateTPB(Params, FHandle);
    GetPlainDriver.isc_start_multiple(@FStatusVector, @FTrHandle, 1, PTEB);
    CheckInterbase6Error(GetPlainDriver, FStatusVector, lcTransaction);            ---------------> Eror gerated here
    DriverManager.LogMessage(lcTransaction, PlainDriver.GetProtocol,
      'TRANSACTION STARTED.');
  finally
    FreeAndNil(Params);
    StrDispose(PTEB.tpb_address);
    FreeMem(PTEB);
  end
end;

Hi!
I'm new in this tool and have some questions, but to facilitate research in the forum'll ask the questions in separate threads and obviously after searching, reading and trying to solve it myself.
The problem in question is to convert an object to JSON, where the manual and examples I use ObjectToJSON taking care to use the directive {$ M +} or the class is a descendant of TPersistence, this works perfectly ... but have difficulty in properties that are a TObjectList <> or are Dynamic Arrays of objects ... I understand I could use TJSONSerializer.RegisterCustomSerializer creating a class with callback's for reading and writing. Below is a code example and would like to know the best way to solve (or only one way) the problem.

{$M+}
type
  TDataType = (tlTemperature, tlHumidity);
  TData = class
    private
      FPortId  : Integer;
      FDataType: TDataType;
      FValue   : Double;
      function Get_PortId: Integer;
      function Get_DataType: TDataType;
      function Get_Value: Double;
      procedure Set_PortId(const Value: Integer);
      procedure Set_DataType(const Value: TDataType);
      procedure Set_Value(const Value: Double);
    published
      property PortId  : Integer read Get_PortId write Set_PortId;
      property DataType: TDataType read Get_DataType write Set_DataType;
      property Value   : Double read Get_Value write Set_Value;
  end;

  TDataList = class (TList<TData>)
  end;
  TDataArray = Array of TData;

  TAcquisitionBoard = class
    private
      FIdentify: RawUTF8;
      FModel   : RawUTF8;
      FData : TDataArray;
      function Get_Identify: RawUTF8;
      function Get_Model: RawUTF8;
      function Get_Data: TDataArray;
      procedure Set_Identify(const Value: RawUTF8);
      procedure Set_Model(const Value: RawUTF8);
    public
      constructor Create;
      destructor Destroy; override;

      function AddData: TData;
    published
      property Identify: RawUTF8 read Get_Identify write Set_Identify;
      property Model   : RawUTF8 read Get_Model write Set_Model;
      property Data    : TDataArray read Get_Data;
  end;

Whereas the objects have the following data:

Identify: 12345 Model: A2013
Temperature: 23.79
Temperature: 30.02

When running memoJSON.Lines.Text: = UTF8ToString (ObjectToJSON (FAcquisitionBoard, []));

I get: {"Identify": "12345", "Model": "A2013", "Data": []}
Without informing the content of the property "Data".

How to get complete information? Data as Dynamic Array or TObjectList<> or other structure...

Thanks in advance!