Some questions about database access in method-based services

Luzcka · 2013-09-27 15:24:12

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.

mpv · 2013-09-27 15:58:00

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

Luzcka · 2013-09-27 16:08:34

Hi Mpv!

Thanks for the quick reply, I will test this solution

Luzcka · 2013-09-27 16:31:15

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...

ab · 2013-09-27 16:44:57

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.

Luzcka · 2013-09-27 17:22:04

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!

mpv · 2013-09-27 18:12:20

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)

Luzcka · 2013-09-27 18:49:24

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).

Luzcka · 2013-09-27 19:44:04

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)

mORMot Open Source

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

Some questions about database access in method-based services

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Board footer