#1 2013-09-27 15:24:12

Luzcka
Member
From: Brazil / Chile
Registered: 2012-12-07
Posts: 20

Some questions about database access in method-based services

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.

Offline

#2 2013-09-27 15:58:00

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,299
Website

Re: Some questions about database access in method-based services

About last question - you must not operate with MainConnection in your case. Instead use ThreadSafeConnection.

conn := fProps.ThreadSafeConnection;
 try
    res := fProps.ExecuteInlined(aSQL, aExpectResults);
    if res=nil then
      result := ''
    else
      result := res.FetchAllAsJSON(aExpanded);
    conn.Commit;
  except
    conn.Rollback;
  end;

Take a look inside of fProps.ExecuteInlined - it actually retrive ThreadSafeConnection inside

Offline

#3 2013-09-27 16:08:34

Luzcka
Member
From: Brazil / Chile
Registered: 2012-12-07
Posts: 20

Re: Some questions about database access in method-based services

Hi Mpv!

Thanks for the quick reply, I will test this solution

Offline

#4 2013-09-27 16:31:15

Luzcka
Member
From: Brazil / Chile
Registered: 2012-12-07
Posts: 20

Re: Some questions about database access in method-based services

Hi Mpv,

Do not work...

Error: Invalid TSQLDBZeosConnection.Commit call

I'm change de code to:

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

Is posible the fact of the Embedded Database be the problem??? Not make sence to me... but...

Offline

#5 2013-09-27 16:44:57

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 13,182
Website

Re: Some questions about database access in method-based services

AFAIK the transaction should be in the same connection, so the same thread.

Therefore, your last code is better. But for SELECT statements you do not need a transaction.

Offline

#6 2013-09-27 17:22:04

Luzcka
Member
From: Brazil / Chile
Registered: 2012-12-07
Posts: 20

Re: Some questions about database access in method-based services

Hello!

For this particular case I need... inside the stored procedure "Login" is executed another stored procedure that performs an Insert in LOG table ...

Inside a method-service is possible to make a transaction??? Looking my code... any mistake?

Tkz!

Offline

#7 2013-09-27 18:12:20

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,299
Website

Re: Some questions about database access in method-based services

Luzka, I miss one line on code - you must start transaction before commit or rollback
I'm not see sources now, something like

conn := fProps.ThreadSafeConnection;
conn.StartTransaction; //(don't remember method name) <---- this line we miss

Last edited by mpv (2013-09-27 18:12:50)

Offline

#8 2013-09-27 18:49:24

Luzcka
Member
From: Brazil / Chile
Registered: 2012-12-07
Posts: 20

Re: Some questions about database access in method-based services

Mvp, I have erred, I don't see before the lack of StartTransaction. I changed the code by putting the line you mentioned,

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

The impression is a database not connected, but without a transaction contro it work (the select, not the insert).

Offline

#9 2013-09-27 19:44:04

Luzcka
Member
From: Brazil / Chile
Registered: 2012-12-07
Posts: 20

Re: Some questions about database access in method-based services

Well... changing the code it work!
I add a   "Conn.Connect;" before the starttransaction!
and now it save info in LOG table!  :-)
Thanks to Mvp and Ab.

but I still wonder if I use the best structure for using mORMot using method-based service...
and again I ask, what would be the best class to remotely access the Firebird (or multi user access)??

The altered code:


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

Last edited by Luzcka (2013-09-27 19:58:28)

Offline

Board footer

Powered by FluxBB