Test mORMmot + Zeos +Firebird

Luzcka · 2013-05-28 22:01:50

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;

ab · 2013-05-29 09:08:31

AFAIK ZDbcInterbase6 is needed from Zeos point of view.
It defines the Interbase API, which is used by Firebird AFAIK.

Yes, we only tested Firebird via Zeos using embedded Firebird, by now.
This is why such feedback as yours is precious!
Thanks!

Have you something to propose for TSQLDBZEOSConnectionProperties.URI() work as expected in remote mode?
Perhaps an overloaded method including the server?

(in your code, you can use FormatUTF8('%\%', [...]) instead of a temporary conversion to string)

Luzcka · 2013-05-29 14:49:53

>AFAIK ZDbcInterbase6 is needed from Zeos point of view.
>It defines the Interbase API, which is used by Firebird AFAIK.

I understand the need to use ZDbcInterbase6 (since Firebird is a fork of Interbase6), and to understand share some code, but these definitions (and uses) could not be used internally in units Firebird? What I say is because it seems a little strange ... and may end up confusing and complicating the user.

>Yes, we only tested Firebird via Zeos using embedded Firebird, by now.
>This is why such feedback as yours is precious!
>Thanks!

I will conduct further tests and analyze the cause of the error, where I can help the project will be happy to do! :-)

>Have you something to propose for TSQLDBZEOSConnectionProperties.URI() work as expected in remote mode?
>Perhaps an overloaded method including the server?

I liked the idea of the method TSQLDBZEOSConnectionProperties.URI (), but in my opinion I would create a method with parameters "Host", "Port", "Database", "User", "Password" (common in most databases connections), and with this I would check if the "Host" is remote to decide whether I can create the database, and if it a local host (informed or not) try to verify if it is a file or an "Alias" (Alias has no path) and if there is a file that is not there ... I would! Using Alias is recommended for safety and ease since there informs nor require the client application to know the name of the file or database and its physical location.

So we could go a little further and implement the procedure URI as a function that returns a small class "Connection String" (which are actually the properties ...) This function has the parameters mentioned properties and would have to set some parameters that would be restricted to common parameters in DBMS's and finally having a property to set parameters specific to the dbms. I put here a small example that is just an idea and would have to be analyzed to better define the properties.

TStringConnection = class
  private
     FHost: RawUTF8;
     FPort: Cardinal;
     FDabatabse: RawUTF8;
     FUser: RawUTF8;
     FPassword: RawUTF8;  //Be encrypted???
     FCharset:...
     FBlaBlaBla:...

     procedure Set...
     function   Get...
  public
     constructor Create(pHost: RawUTF8; pPort: Cardinal; pDatabase: RawUTF8; pUser: RawUTF8; pPass: RawUTF8);
     function AsString: RawUTF8;
  published
     property Host: RawUTF8..
     property Port: Cardinal..
     property Dabatabse: RawUTF8...
     property User: RawUTF8...
     property Password: RawUTF8...
     property Charset:...
     property Params:...
end;

...

TSQLDBZEOSConnectionProperties.URI (pHost: RawUTF8; pPort: Cardinal; pDatabase: RawUTF8; pUser: RawUTF8; pPass: RawUTF8): TStringConnection;

...

var 
  vStringConnection: TStringConnection;

  vStringConnection := TSQLDBZEOSConnectionProperties.URI ('127.0.0.1' {or empty},  3050 {or 0}, 'C:\...\MyDB.FDB' {or Alias}, 'sysdba', 'masterkey').AsString;
  vStringConnection.Charset := UFT8; {or other property or other value etc...} {if omited use default...}
  vStringConnection.Params.Set('Specific_DBMS_Property', 'Specific_Value');

With this I think I can specify the connection properties of any database using the URI idea, but open the user to optemize the connection.

>(in your code, you can use FormatUTF8('%\%', [...]) instead of a temporary conversion to string)

Thanks for the info! I ended up not bothering me much to use library functions and used what was easiest to test, but I use to be able to start getting used to! ;-)

Luzcka · 2013-06-05 21:22:42

Hi!
Sorry the delay... Making a code trace I get it:

0)function TSQLDBConnectionProperties.ExecuteInlined(const aSQL: RawUTF8; ExpectResults: Boolean): ISQLDBRows;
1)TSQLDBConnectionProperties.NewThreadSafeStatementPrepared(const aSQL: RawUTF8; ExpectResults: Boolean; RaiseExceptionOnError: Boolean=false): ISQLDBStatement;
2a) function TSQLDBConnectionPropertiesThreadSafe.ThreadSafeConnection: TSQLDBConnection;
---- (2b Executed on exit 2a)
2b)function TSQLDBConnection.NewStatementPrepared(const aSQL: RawUTF8;ExpectResults: Boolean; RaiseExceptionOnError: Boolean=false): ISQLDBStatement;
// default implementation with no cache
Stmt := nil;
try
InternalProcess(speActive);
try
Stmt := NewStatement;
Stmt.Prepare(aSQL,ExpectResults); <---- Enter here. aSQL = 'Select * from Material_Texto', ExpectResults = true
3)procedure TSQLDBZEOSStatement.Prepare(const aSQL: RawUTF8;ExpectResults: boolean);
inherited Prepare(aSQL,ExpectResults); // connect if necessary <---- Enter here
4)procedure TSQLDBZEOSConnection.Connect;
5)procedure TZInterbase6Connection.Open;
DPB = #1#$1C#6'sysdba'#$1D#8'masterkey'
DBName = ('1', '2', '7', '.', '0', '.', '0', '.', '1', ':', 'E', ':', '\', 'T', 'r', 'a', 'b', 'a', 'j', 'o', 's', '\', 'D', 'a', 't', 'a', '\', 'E', 'C', 'O', 'N', 'S', 'U', 'L', 'T', 'O', 'R', '-', 'U', 'T', 'F', '8', '.', 'F', 'D', 'B', #0,....)
FStatusVector = (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
FHandle = 0
FDPBLength = 19
call GetPlainDriver.isc_attach_database(@FStatusVector, StrLen(DBName), DBName, @FHandle, FDPBLength, DPB);

6) function TZFirebirdBaseDriver.isc_attach_database(status_vector: PISC_STATUS; db_name_length: Short; db_name: PAnsiChar; db_handle: PISC_DB_HANDLE; parm_buffer_length: Short; parm_buffer: PAnsiChar): ISC_STATUS;
7)function CheckInterbase6Error(PlainDriver: IZInterbasePlainDriver;StatusVector: TARRAY_ISC_STATUS; LoggingCategory: TZLoggingCategory = lcOther;SQL: string = '') : Integer;
StatusVector[0] = 1 and StatusVector[1] = 335544324
------> First Error messge = ' Your user name and password are not defined. Ask your database administrator to set up a Firebird login.' //I confirm the user and pass. work in Zeos

After execute line: ErrorCode := PlainDriver.isc_sqlcode(@StatusVector);
------> msg = ' invalid database handle (no active connection)'
------> ErrorSQLMessage = 'can''t format message 13:96 -- message file C:\Windows\firebird.msg not found'
------> Firebird Error Meassage = 'SQL error: invalid database handle (no active connection. Error Code: -904 Unsuccessful execution caused by an unavaliable resource.'

mORMot Open Source

#1 2013-05-28 22:01:50

Test mORMmot + Zeos +Firebird

#2 2013-05-29 09:08:31

Re: Test mORMmot + Zeos +Firebird

#3 2013-05-29 14:49:53

Re: Test mORMmot + Zeos +Firebird

#4 2013-06-05 21:22:42

Re: Test mORMmot + Zeos +Firebird

Board footer