Zoes7.2 upgrade

EgonHugeist · 2013-11-29 22:36:07

Hi Arnaud,

i made some small modifications for your current ZDBC api. I added some comments. You need to bind Zeos.inc to avoid incompatibility issues. In this patch it's commented.

I used the SynDBZeos.pas of the Nighly-Snap-Shots. Hope it was the lates one?

Changes:
-Added (hopefully) better getters & setters for your framework.
-Added some Driver-speziffic options to pimp Zeos a bit
-Added the IdentifierConverter for the Table-Names

I must admit i still had no time to study mORMot so i simply did some logical changes i can see in THIS unit. Should i review more?

/// ZEOS 7.x direct access classes for SynDB units (not DB.pas based)
// - this unit is a part of the freeware Synopse framework,
// licensed under a MPL/GPL/LGPL tri-license; version 1.18
unit SynDBZEOS;

{
  This file is part of Synopse framework.

  Synopse framework. Copyright (C) 2013 Arnaud Bouchez
  Synopse Informatique - http://synopse.info

  *** BEGIN LICENSE BLOCK *****
  Version: MPL 1.1/GPL 2.0/LGPL 2.1

  The contents of this file are subject to the Mozilla Public License Version
  1.1 (the "License"); you may not use this file except in compliance with
  the License. You may obtain a copy of the License at
  http://www.mozilla.org/MPL

  Software distributed under the License is distributed on an "AS IS" basis,
  WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
  for the specific language governing rights and limitations under the License.

  The Original Code is Synopse mORMot framework.

  The Initial Developer of the Original Code is Arnaud Bouchez.

  Portions created by the Initial Developer are Copyright (C) 2013
  the Initial Developer. All Rights Reserved.

  Contributor(s):


  Alternatively, the contents of this file may be used under the terms of
  either the GNU General Public License Version 2 or later (the "GPL"), or
  the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
  in which case the provisions of the GPL or the LGPL are applicable instead
  of those above. If you wish to allow use of your version of this file only
  under the terms of either the GPL or the LGPL, and not to allow others to
  use your version of this file under the terms of the MPL, indicate your
  decision by deleting the provisions above and replace them with the notice
  and other provisions required by the GPL or the LGPL. If you do not delete
  the provisions above, a recipient may use your version of this file under
  the terms of any one of the MPL, the GPL or the LGPL.

  ***** END LICENSE BLOCK *****

  Version 1.18
  - first public release, corresponding to mORMot framework 1.18

  Note:
  - if you want to work as expected with SQlite3 backend (but how would need to
    do it, since it will be MUCH slower compared to SynDBSQlite3), you need
    to change procedure TZSQLiteResultSet.FreeHandle in ZDbcSqLiteResultSet.pas
     from  ErrorCode := FPlainDriver.reset(FStmtHandle)
     into  ErrorCode := SQLITE_OK
    (i.e. disable statement reset)

  Todo:
  - handle MySQL + PostgreSQL
  
}

{$I Synopse.inc} // define HASINLINE USETYPEINFO CPU32 CPU64 OWNNORMTOUPPER
{.$I Zoes.inc}
interface

uses
  Windows, Types, SysUtils,
  {$IFNDEF DELPHI5OROLDER}
  Variants,
  {$ENDIF}
  Classes, Contnrs,
  SynCommons,
  SynDB,
  ZURL, ZDbcIntfs, ZDbcResultSet;




{ -------------- ZEOS database components direct process }

type
  /// Exception type associated to the ZEOS database components
  ESQLDBZEOS = class(ESQLDBException);


  /// implement properties shared by ZEOS connections
  TSQLDBZEOSConnectionProperties = class(TSQLDBConnectionPropertiesThreadSafe)
  protected
    fURL: TZURL;
    fDBMSName: RawUTF8;
    /// initialize fForeignKeys content with all foreign keys of this DB
    // - do nothing by now (ZEOS metadata may be used in the future)
    procedure GetForeignKeys; override;
    /// convert ZDBC field type into mORMot fieldtype
    function TZSQLTypeToTSQLDBFieldType(aNativeType: TZSQLType): TSQLDBFieldType; virtual;
  public
    /// initialize the properties to connect to the ZEOS engine
    // - aServerName shall contain the ZEOS URI, e.g:
    // $ zdbc:firebird-2.0://127.0.0.1:3050/model?username=sysdba;password=masterkey
    // $ sqlite
    // i.e. '[zdbc:]PROTOCOL://HOST:PORT[/DATABASE][?paramname=value]'
    // - you can define the TZConnection.LibraryLocation property by setting a
    // '?LibLocation=...' parameter within the aServerName URL value
    // - or simple use TSQLDBZEOSConnectionProperties.URI() class method
    // - aDatabaseName, aUserID, aPassword are used if not already set as URI
    // in aServerName value
    // - you can use Protocols property to retrieve all available protocol names
    constructor Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8); override;
    /// finalize properties internal structures
    destructor Destroy; override;
    /// create a new connection
    // - caller is responsible of freeing this instance
    // - this overriden method will create an TSQLDBZEOSConnection instance
    function NewConnection: TSQLDBConnection; override;

    /// retrieve the column/field layout of a specified table
    // - this overriden method will use ZDBC metadata to retrieve the information
    procedure GetFields(const aTableName: RawUTF8; var Fields: TSQLDBColumnDefineDynArray); override;
    /// get all table names
    // - this overriden method will use ZDBC metadata to retrieve the information
    // - PostgreSQL note: it was reported that some table names expects to be
    // quoted for this DB engine - and ZDBC won't do it for yourself - please
    // ensure you specify the correct quoted table name e.g. when you register
    // the external PostgreSQL table via function VirtualTableExternalRegister() 
    procedure GetTableNames(var Tables: TRawUTF8DynArray); override;
    /// access to the database metadata, as retrieved by ZEOS
    // - returns TRUE if metadata interface has been retrieved
    function GetDatabaseMetadata(out meta: IZDatabaseMetadata): boolean;
    /// compute the ZEOS URI from a given database engine
    // - you can set an optional full path to the client library name,
    // to be completed on the left side with the executable path
    // - possible use may be:
    // ! PropsOracle := TSQLDBZEOSConnectionProperties.Create(
    // !   TSQLDBZEOSConnectionProperties.URI(dOracle,'oci64\oci.dll'),
    // !   'tnsname','user',pass');
    // ! PropsFirebird := TSQLDBZEOSConnectionProperties.Create(
    // !   TSQLDBZEOSConnectionProperties.URI(dFirebird,'Firebird\fbembed.dll'),
    // !   'databasefilename','',');
    class function URI(aServer: TSQLDBDefinition;
      const aLibraryLocation: TFileName='';
      aLibraryLocationAppendExePath: boolean=true): RawUTF8;
  published
    /// the remote DBMS name, as retrieved from ServerName, i.e. ZEOS URL
    property DBMSName: RawUTF8 read fDBMSName;
  end;


  /// implements a connection via the ZEOS access layer
  TSQLDBZEOSConnection = class(TSQLDBConnectionThreadSafe)
  protected
    fDatabase: IZConnection;
  public
    /// prepare a connection to a specified ZEOS database server
    constructor Create(aProperties: TSQLDBConnectionProperties); override;
    /// connect to the specified ZEOS server
    // - should raise an ESQLDBZEOS on error
    procedure Connect; override;
    /// stop connection to the specified ZEOS database server
    // - should raise an ESQLDBZEOS on error
    procedure Disconnect; override;
    /// return TRUE if Connect has been already successfully called
    function IsConnected: boolean; override;
    /// create a new statement instance
    function NewStatement: TSQLDBStatement; override;
    /// begin a Transaction for this connection
    procedure StartTransaction; override;
    /// commit changes of a Transaction for this connection
    // - StartTransaction method must have been called before
    procedure Commit; override;
    /// discard changes of a Transaction for this connection
    // - StartTransaction method must have been called before
    procedure Rollback; override;
    /// access to the associated ZEOS connection instance
    property Database: IZConnection read fDatabase;
  end;

  /// implements a statement via a ZEOS database connection
  TSQLDBZEOSStatement = class(TSQLDBStatementWithParamsAndColumns)
  protected
    fStatement: IZPreparedStatement;
    fResultSet: IZResultSet;
    fResultInfo: IZResultSetMetaData;
  public
    {{ Prepare an UTF-8 encoded SQL statement
      - parameters marked as ? will be bound later, before ExecutePrepared call
      - if ExpectResults is TRUE, then Step() and Column*() methods are available
      to retrieve the data rows
      - raise an ESQLDBZeos on any error }
    procedure Prepare(const aSQL: RawUTF8; ExpectResults: boolean = false); overload; override;
    {{ Execute a prepared SQL statement
      - parameters marked as ? should have been already bound with Bind*() functions
      - this implementation will also loop through all internal bound array
      of values (if any), to implement BATCH mode
      - this overriden method will log the SQL statement if sllSQL has been
        enabled in SynDBLog.Family.Level
      - raise an ESQLDBZeos on any error }
    procedure ExecutePrepared; override;
    {/ Reset the previous prepared statement
     - this overriden implementation will reset all bindings and the cursor state
     - raise an ESQLDBZeos on any error }
    procedure Reset; override;

    {{ Access the next or first row of data from the SQL Statement result
      - return true on success, with data ready to be retrieved by Column*() methods
      - return false if no more row is available (e.g. if the SQL statement
      is not a SELECT but an UPDATE or INSERT command)
      - if SeekFirst is TRUE, will put the cursor on the first row of results
      - raise an ESQLDBZeos on any error }
    function Step(SeekFirst: boolean = false): boolean; override;
    {{ return a Column integer value of the current Row, first Col is 0 }
    function ColumnInt(Col: Integer): Int64; override;
    {{ returns TRUE if the column contains NULL }
    function ColumnNull(Col: Integer): boolean; override;
    {{ return a Column floating point value of the current Row, first Col is 0 }
    function ColumnDouble(Col: Integer): double; override;
    {{ return a Column date and time value of the current Row, first Col is 0 }
    function ColumnDateTime(Col: Integer): TDateTime; override;
    {{ return a Column currency value of the current Row, first Col is 0 }
    function ColumnCurrency(Col: Integer): currency; override;
    {{ return a Column UTF-8 encoded text value of the current Row, first Col is 0 }
    function ColumnUTF8(Col: Integer): RawUTF8; override;
    {{ return a Column as a blob value of the current Row, first Col is 0 }
    function ColumnBlob(Col: Integer): RawByteString; override;
  end;

var
  /// list of all available ZEOS protocols
  // - you have to call SetZEOSProtocols before using it, to update this
  // global list with all initialized ZPlain*Driver units
  // - to be used e.g. within ZEOS URI, as TSQLDBZEOSConnectionProperties.ServerName
  ZEOSProtocols: TRawUTF8DynArray;

/// to be called in order to populate the global ZEOSProtocols list
procedure SetZEOSProtocols;


implementation



{ TSQLDBZEOSConnectionProperties }

constructor TSQLDBZEOSConnectionProperties.Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8);
const
  PCHARS: array[0..7] of PUTF8Char = (
    'ORACLE','FREETDS_MSSQL','MSSQL','INTERBASE','FIREBIRD','MYSQL','SQLITE','POSTGRESQL');
  TYPES: array[-1..high(PCHARS)] of TSQLDBDefinition = (
    dDefault,dOracle,dMSSQL,dMSSQL,dFirebird,dFirebird,dMySQL,dSQLite,dPostgreSQL);
  // expecting Postgresql + Sybase + ASA support in TSQLDBDefinition
begin
  fServerName :=  aServerName;
  if (fServerName<>'') and (PosEx(':',fServerName)=0) then
    fServerName := fServerName+':';
  if not IdemPChar(Pointer(aServerName),'ZDBC:') then
    fServerName := 'zdbc:'+fServerName;
  fURL := TZURL.Create(UTF8ToString(fServerName));
  if fURL.Database='' then
    fURL.Database := UTF8ToString(aDatabaseName);
  if fURL.UserName='' then
    fURL.UserName := UTF8ToString(aUserID);
  if fURL.Password='' then
    fURL.Password := UTF8ToString(aPassWord);
  fDBMSName := StringToUTF8(fURL.Protocol);
  fDBMS := TYPES[IdemPCharArray(pointer(fDBMSName),PCHARS)];
  inherited Create(aServerName,aDatabaseName,aUserID,aPassWord);
  fURL.Properties.Add('controls_cp=CP_UTF8');
  fUseCache := false; // caching is to be disabled - not found stable enough
  case fDBMS of
  dSQLite: begin // ZEOS support of SQLite3 is just buggy
    //EH: Yes that's right. Zees !trys! to provide a common behavior as much as
    //possible. So actually we won't change this part
    fSQLCreateField[ftInt64] := ' BIGINT'; // SQLite3 INTEGER = 32bit for ZDBC!
    {$IFDEF ZEOS72UP}
    fUseCache := true; //AB, im not sure about this option. But if you set ForceNativeResultSet=True to stmt-info's you have to cache the values
    {$ENDIF}
  end;
  dFirebird: begin
    if not FileExists(fURL.Database) then
      fURL.Properties.Add('createNewDatabase='+UTF8ToString(
        SQLCreateDatabase(StringToUTF8(fURL.Database))));
    fURL.Properties.Add('codepage=UTF8');
    fUseCache := true; // caching rocks with Firebird ZDBC provider :)
  end;
  dOracle, dPostgreSQL: begin
    fURL.Properties.Add('codepage=UTF8');
    fUseCache := true;
  end;
  end;
end;

procedure TSQLDBZEOSConnectionProperties.GetForeignKeys;
begin
  { TODO : get FOREIGN KEYS from ZEOS metadata ? }
end;

function TSQLDBZEOSConnectionProperties.NewConnection: TSQLDBConnection;
begin
  result := TSQLDBZEOSConnection.Create(self);
end;

destructor TSQLDBZEOSConnectionProperties.Destroy;
begin
  fURL.Free;
  inherited;
end;

procedure SetZEOSProtocols;
var List: TStringList;
    i,j: integer;
    Protocols: Types.TStringDynArray;
begin
  List := TStringList.Create;
  try
    with DriverManager.GetDrivers do
      for i := 0 to Count-1 do begin
        Protocols := (Items[i] as IZDriver).GetSupportedProtocols;
        for j := 0 to high(Protocols) do
          List.Add(Protocols[j]);
      end;
    List.Sort;
    SetLength(ZEOSProtocols,List.Count);
    for i := 0 to high(ZEOSProtocols) do
      ZEOSProtocols[i] := StringToUTF8(List[i]);
  finally
    List.Free;
  end;
end;

function TSQLDBZEOSConnectionProperties.GetDatabaseMetadata(out meta: IZDatabaseMetadata): boolean;
var conn: IZConnection;
begin
  conn := (MainConnection as TSQLDBZEOSConnection).fDatabase;
  result := conn.UseMetadata;
  if result then begin
    meta := conn.GetMetadata;
    meta.ClearCache; // we need to retrieve the actual metadata
  end;
end;

procedure TSQLDBZEOSConnectionProperties.GetTableNames(var Tables: TRawUTF8DynArray);
var meta: IZDatabaseMetadata;
    res: IZResultSet;
    TableTypes: Types.TStringDynArray;
    n: integer;
begin
  if GetDatabaseMetadata(meta) then begin
    SetLength(TableTypes,1);
    TableTypes[0] := 'TABLE';
    res := meta.GetTables('','','',TableTypes);
    n := 0;
    while res.Next do
      AddSortedRawUTF8(Tables,n,SynUnicodeToUtf8(res.GetUnicodeString(3)));
    SetLength(Tables,n);
  end else
    inherited;
end;

procedure TSQLDBZEOSConnectionProperties.GetFields(
  const aTableName: RawUTF8; var Fields: TSQLDBColumnDefineDynArray);
var meta: IZDatabaseMetadata;
    res: IZResultSet;
    n, i: integer;
    TableName: string;
    F: TSQLDBColumnDefine;
    FA: TDynArray;
begin
  if GetDatabaseMetadata(meta) then begin
    TableName := meta.GetIdentifierConvertor.Quote(UTF8ToString(UpperCase(aTableName)));
    res := meta.GetColumns('','',TableName,'');
    FA.Init(TypeInfo(TSQLDBColumnDefineDynArray),Fields,@n);
    FA.Compare := SortDynArrayAnsiStringI; // FA.Find() case insensitive
    FillChar(F,sizeof(F),0);
    while res.Next do begin
      F.ColumnName := SynUnicodeToUtf8(res.GetUnicodeString(4));
      F.ColumnTypeNative := SynUnicodeToUtf8(res.GetUnicodeString(6));
      F.ColumnType := TZSQLTypeToTSQLDBFieldType(TZSQLType(res.GetInt(5)));
      F.ColumnLength := res.GetInt(7);
      F.ColumnPrecision := res.GetInt(9);
      FA.Add(F);
    end;
    if n>0 then begin
      res := meta.GetIndexInfo('','',TableName,false,true);
      while res.Next do begin
        F.ColumnName := SynUnicodeToUtf8(res.GetUnicodeString(9));
        i := FA.Find(F);
        if i>=0 then
          Fields[i].ColumnIndexed := true;
      end;
    end;
    SetLength(Fields,n);
    exit;
  end;
  inherited;
end;

function TSQLDBZEOSConnectionProperties.TZSQLTypeToTSQLDBFieldType(aNativeType: TZSQLType): TSQLDBFieldType;
begin
  case aNativeType of
    stBoolean, stByte, stShort, stInteger, stLong
    {$IFDEF ZEOS72UP}, stSmall, stWord, stLongWord, stULong{$ENDIF}:
      result := ftInt64;
    stFloat, stDouble:
      result := ftDouble;
    stBigDecimal{$IFDEF ZEOS72UP}, stCurrency{$ENDIF}:
      result := ftCurrency;
    stDate, stTime, stTimestamp:
      result := ftDate;
    stString, stUnicodeString, stAsciiStream, stUnicodeStream:
      result := ftUTF8;
    stBytes, stBinaryStream:
      result := ftBlob;
    else raise ESQLDBZEOS.CreateFmt('Unexpected TZSQLType "%s"',
      [{$ifdef PUREPASCAl}IntToStr(ord(aNativeType)){$else}
       GetEnumName(Typeinfo(TZSQLType),ord(aNativeType))^{$endif}]);
  end;
end;

class function TSQLDBZEOSConnectionProperties.URI(aServer: TSQLDBDefinition;
  const aLibraryLocation: TFileName; aLibraryLocationAppendExePath: boolean): RawUTF8;
const
  /// ZDBC provider names corresponding to SynDB recognized SQL engines
  ZEOS_PROVIDER: array[TSQLDBDefinition] of RawUTF8 = (
    '','','oracle','mssql','','mysql','sqlite','firebird-2.5','','postgresql-9');
  //EH: Hint. I wouldn't use protocols with version prefix. Zeos always use latest protocol. But keep track for embeddeds
begin
  result := ZEOS_PROVIDER[aServer];
  if (result='') or (aLibraryLocation='') then
    exit;
  result := result+':?LibLocation=';
  if aLibraryLocationAppendExePath then
    result := result+StringToUTF8(ExtractFilePath(ParamStr(0)));
  result := result+StringToUTF8(aLibraryLocation);
end;


{ TSQLDBZEOSConnection }

constructor TSQLDBZEOSConnection.Create(aProperties: TSQLDBConnectionProperties);
begin
  inherited Create(aProperties);
  fDatabase := DriverManager.GetConnectionWithParams(
    (fProperties as TSQLDBZEOSConnectionProperties).fURL.URL,nil);
  fDatabase.SetAutoCommit(true);
  fDatabase.SetTransactionIsolation(tiNone);
end;

procedure TSQLDBZEOSConnection.Connect;
var Log: ISynLog;
begin
  if fDatabase=nil then
    raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSConnection.Connect() on %s failed: Database=nil',
      [fProperties.ServerName]);
  with (fProperties as TSQLDBZEOSConnectionProperties).fURL do
    Log := SynDBLog.Enter(Self,pointer(FormatUTF8('Connect to % % for % at %:%',
      [Protocol,Database,HostName,Port])),true);
  try
    fDatabase.Open;
    Log.Log(sllDB,'Connected to % using % %',
      [fProperties.ServerName,fProperties.DatabaseName,fDatabase.GetClientVersion]);
    inherited Connect; // notify any re-connection
  except
    on E: Exception do begin
      Log.Log(sllError,E);
      Disconnect; // clean up on fail
      raise;
    end;
  end;
end;

procedure TSQLDBZEOSConnection.Disconnect;
begin
  try
    inherited Disconnect; // flush any cached statement
  finally
    if (fDatabase<>nil) and not fDatabase.IsClosed then
      fDatabase.Close;
  end;
end;

function TSQLDBZEOSConnection.IsConnected: boolean;
begin
  result := Assigned(fDatabase) and not fDatabase.IsClosed;
end;

function TSQLDBZEOSConnection.NewStatement: TSQLDBStatement;
begin
  result := TSQLDBZEOSStatement.Create(self);
end;

procedure TSQLDBZEOSConnection.StartTransaction;
begin
  inherited StartTransaction;
  fDatabase.SetAutoCommit(false);
  fDatabase.SetTransactionIsolation(tiReadCommitted);
end;

procedure TSQLDBZEOSConnection.Commit;
begin
  inherited Commit;
  fDatabase.Commit;
  fDatabase.SetAutoCommit(true);
  fDatabase.SetTransactionIsolation(tiNone);
end;

procedure TSQLDBZEOSConnection.Rollback;
begin
  inherited Rollback;
  fDatabase.Rollback;
  fDatabase.SetAutoCommit(true);
  fDatabase.SetTransactionIsolation(tiNone);
end;



{ TSQLDBZEOSStatement }

procedure TSQLDBZEOSStatement.Prepare(const aSQL: RawUTF8;
  ExpectResults: boolean);
var Log: ISynLog;
  Info: TStrings;
begin
  Log := SynDBLog.Enter(Self);
  if (fStatement<>nil) or (fResultSet<>nil) then
    raise ESQLDBZEOS.CreateFmt('%s.Prepare() shall be called once',[ClassName]);
  inherited Prepare(aSQL,ExpectResults); // connect if necessary
  Info := TStringList.Create;
  { oracle option }
  Info.Add('prefetch_count=100000'); //sets OCI_ATTR_PREFETCH_ROWS on prepare a fetch
                                     //default = 100 on 7.1down and 1000 on 7.2up
  { sqlite option }
  Info.Add('ForceNativeResultSet=True'); //get access to the native resultset. This only works sequention/forwardonly (step) and YOU have to localize the values
                                         //new since 7.2up
  { sqlite option }
  //Info.Add('BindDoubleDateTimeValues=True'); //Bind double values instead of ISO formated DataTime-strings. SQLDB is going this route
                                              //introduced since 7.2up
  { mysql }
  //Info.Add('preferprepared=True'); //use mysql real-prepared api instead of string based once
                                     // actually it's not realy faster.. just a hint:
                                     // http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-problems.html

  {oracle, postgresql, mysql-realprepared}
  Info.Add('chunk_size=1048576'); //let's set 1024KB / chunk for synopse  or more?
                                  //retrieving/submitting lob's in chunks. Default is 4096Bytes / Chunk
                                  //it's depending to your local network speed e.g. bad WLAN or so
                                  //for firebird we always using the blob-segment size
  {oracle, mysql, postgresql, firebird}
  Info.Add('cachedlob=false'); //Always load the lobs? Or just on accessing them?
                               //default = False
                               //if you allways copy the data by fetching the row than it doesn't make sence.
                               //introduced since 7.2up

  fStatement := (fConnection as TSQLDBZEOSConnection).fDatabase.
    PrepareStatementWithParams(UTF8ToString(fSQL), Info);
  Info.Free;
end;

procedure TSQLDBZEOSStatement.ExecutePrepared;
var i: integer;
    Props: TSQLDBZEOSConnectionProperties;
    Log: ISynLog;
    blob: IZBlob;
    name: string;
begin
  Log := SynDBLog.Enter(Self);
  with Log.Instance do
    if sllSQL in Family.Level then
      LogLines(sllSQL,pointer(SQLWithInlinedParams),self,'--');
  if fStatement=nil then
    raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared() invalid call',[ClassName]);
  if fResultSet<>nil then
    raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared() miss a Reset',[ClassName]);
  // 1. bind parameters in fParams[] to fQuery.Params
  for i := 1 to fParamCount do
    with fParams[i-1] do
    case VType of
    ftNull:     fStatement.SetNull(i,stUnknown);
    ftInt64:    fStatement.SetLong(i,VInt64);
    ftDouble:   fStatement.SetDouble(i,PDouble(@VInt64)^);
    ftCurrency: fStatement.SetDouble(i,PCurrency(@VInt64)^);
    ftDate:     fStatement.SetTimestamp(i,PDateTime(@VInt64)^);
    ftUTF8:     {$IFDEF ZEOS72UP}
                fStatement.SetUTF8String(i,VData);
                //EH: may i assume you RawUTF8 prevents conversions? So the UTF8-Setter is save for all codepages. Zeos is doing conversions if necessary.
                //fStatement.SetRawByteString(i,VData); //RawbyteString always represents the client encoded raw-string
                //you could also use SetCharRec(i, ZCompatibility.TZCharRec); and point to the values. What is VData type of?
                {$ELSE}
                  {$ifdef UNICODE}  // ZWideString = SynUnicode in fact
                  fStatement.SetString(i,UTF8ToSynUnicode(VData));
                  {$else}
                  fStatement.SetString(i,VData); // thanks to controls_cp=CP_UTF8
                  {$endif}
                {$ENDIF}
    {EH:
    Arnaud if you would make diffences with PAnsiChars than zeos could perform much faster by using the
    TZAnsiRec's (fetching), TZCharRec(update).
    I did decide to deal with Length's everywhere to avoid strlen call's where ever possible
    same for CLobs where you have new setters and getters with the AbstractClob}
    ftBlob: begin
      blob := TZAbstractBlob.CreateWithData(Pointer(VData),length(VData),
        fStatement.GetConnection);
      fStatement.SetBlob(i,stBinaryStream,blob);
    end;
    else
      raise ESQLDBZEOS.CreateFmt('Invalid type on bound parameter #%d',[i]);
    end;
  // 2. Execute query
  if fExpectResults then begin
    fCurrentRow := -1;
    fResultSet := fStatement.ExecuteQueryPrepared;
    fResultInfo := fResultSet.GetMetadata;
    Props := fConnection.Properties as TSQLDBZEOSConnectionProperties;
    fColumnCount := 0;
    fColumn.ReHash;
    for i := 1 to fResultInfo.GetColumnCount do begin
      name := fResultInfo.GetColumnLabel(i);
      if name='' then
        name := fResultInfo.GetColumnName(i);
      PSQLDBColumnProperty(fColumn.AddAndMakeUniqueName(
        // Delphi<2009: already UTF-8 encoded due to controls_cp=CP_UTF8
        {$ifdef UNICODE}StringToUTF8{$endif}(name)))^.ColumnType :=
          Props.TZSQLTypeToTSQLDBFieldType(fResultInfo.GetColumnType(i));
    end;
  end else
    fStatement.ExecutePrepared;
  // 3. handle out parameters -> TODO (fStatement is IZCallableStatement)
end;

procedure TSQLDBZEOSStatement.Reset;
begin
  if fResultSet<>nil then begin
    fResultInfo := nil;
    fResultSet := nil;
  end;
  if fStatement<>nil then
    fStatement.ClearParameters;
  inherited Reset;
end;

function TSQLDBZEOSStatement.Step(SeekFirst: boolean): boolean;
begin
  if fResultSet=nil then
    raise ESQLDBZEOS.Create('TSQLDBZEOSStatement.Step() invalid call');
  if fColumnCount=0 then // no row returned
    result := false else
  if SeekFirst then begin
    result := fResultSet.First;
    if result then
      fCurrentRow := 1 else
      fCurrentRow := 0;
  end else begin
    result := fResultSet.Next;
    if result then
      inc(fCurrentRow);
  end;
end;

function TSQLDBZEOSStatement.ColumnBlob(Col: Integer): RawByteString;
var blob: IZBlob;
begin
  if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
    raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnBlob(%d)',[Col]);
  blob := fResultSet.GetBlob(Col+1);
  if (blob=nil) or blob.IsEmpty then
    result := '' else
    Result := blob.GetString;
    //SetString(result,blob.PAnsiChar(blob.GetBuffer),blob.Length);
end;

function TSQLDBZEOSStatement.ColumnCurrency(Col: Integer): currency;
begin
  if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
    raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnCurrency(%d)',[Col]);
  result := fResultSet.GetBigDecimal(Col+1);
end;

function TSQLDBZEOSStatement.ColumnDateTime(Col: Integer): TDateTime;
begin
  if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
    raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnDateTime(%d)',[Col]);
  result := fResultSet.GetTimestamp(Col+1);
end;

function TSQLDBZEOSStatement.ColumnDouble(Col: Integer): double;
begin
  if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
    raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnDouble(%d)',[Col]);
  result := fResultSet.GetDouble(Col+1);
end;

function TSQLDBZEOSStatement.ColumnInt(Col: Integer): Int64;
begin
  if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
    raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnInt(%d)',[Col]);
  result := fResultSet.GetLong(Col+1);
end;

function TSQLDBZEOSStatement.ColumnNull(Col: Integer): boolean;
begin
  if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
    raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnNull(%d)',[Col]);
  result := fResultSet.IsNull(Col+1);
end;

function TSQLDBZEOSStatement.ColumnUTF8(Col: Integer): RawUTF8;
begin
  if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
    raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnUTF8(%d)',[Col]);
  {$IFDEF ZEOS72UP}
    result := fResultSet.GetUTF8String(Col+1); // GetRawByteString returns also UTF8 if connection charset is UTF8
  {$ELSE}
    {$ifdef UNICODE}
    StringToUTF8(fResultSet.GetString(Col+1),result);
    {$else}
    result := fResultSet.GetString(Col+1); // thanks to controls_cp=CP_UTF8
    {$endif}
end;

end.

Michael

ab · 2013-11-30 13:36:46

Thanks a lot for sharing!

I've introduced most of your proposals.
See http://synopse.info/fossil/info/6e58ad6282

About UTF-8 encoding, I'm not sure what is the best option.
fParams[].VData is an UTF-8 encoded RawByteString (i.e. our RawUTF8 type).

You are writing that "I did decide to deal with Length's everywhere to avoid strlen call's where ever possible".
Are you not making a confusion between StrLen() and Length()? StrLen is a O(n) algorithm, whereas Length() is O(1), since it just return the length of the string, as stored before the data.
But I included your suggestions anyway.

In order to let FireBird embedded work with the libraries in a sub-folder, I had to change the following code:

function TZNativeLibraryLoader.ZLoadLibrary(Location: String): Boolean;
{$IFNDEF UNIX}
var OldDir: TFileName;
{$ENDIF}
begin
   if FLoaded then
      Self.FreeNativeLibrary;
   FLoaded := False;
   Result := False;

{$IFDEF UNIX}
  {$IFDEF FPC}
        FHandle := LoadLibrary(PAnsiChar(Location));
  {$ELSE}
        FHandle := HMODULE(dlopen(PAnsiChar(Location), RTLD_GLOBAL));
  {$ENDIF}
{$ELSE}
        OldDir := GetCurrentDir;
        try
           SetCurrentDir(ExtractFilePath(Location));
           FHandle := LoadLibrary(PChar(Location));
        finally
           SetCurrentDir(OldDir);
        end;
{$ENDIF}

   if (FHandle <> INVALID_HANDLE_VALUE) and (FHandle <> 0) then
   begin
      FLoaded := True;
      FCurrentLocation := Location;
      Result := True;
   end;
end;

Perhaps worth integrating it in the Zeos official code.

EgonHugeist · 2013-11-30 20:13:54

You are writing that "I did decide to deal with Length's everywhere to avoid strlen call's where ever possible".
Are you not making a confusion between StrLen() and Length()? StrLen is a O(n) algorithm, whereas Length() is O(1), since it just return the length of the string, as stored before the data.
But I included your suggestions anyway.

What i mean is: I'm trying to localize the String-Length everywhere and use Pointers to the Strings just to prevent a Length() or StrLen() execution. And i try to prevent localized strings until a user/framework(like yours) needs a "string" which kind ever.

So it might be possible the GetAnsiRec(Col: Integer): TZAnsiRec; getter could fit best to your framework IF the IDE is doing again conversion or moves the data. In this case use something like this:

var: AnsiRec: TZAnsiRec;
begin
  AnsiRec := ResultSet.GetAnsiRec(ColumnIndex);
  SetString(YourRawUTF8, AnsiRec.P, AnsiRec.Len);
end;

But his is working only if you truely have utf8-encoding which isn't the case for mssql. But the GetUTF8String(ColumnIndex: Integer): System.UTF8String; returns always a UTF8-encoded string. Nasty for MSSQL since there is no native way to determine a VARCHAR vs. NVARCHAR column (i did test freeTDS only). The plain is returning always the same type for both. Result is encoding checking until either UTF8 or Ansi was detected. Maybe someone else know a better way.

Back to yours.
Arnaud i'm not sure about the "fUseCache := true;" option. Can you tell me more about purpose? Just to prevent it was wrong what i did.

Accordingly the TZNativeLibraryLoader patch.
Current Zeos code:

function TZNativeLibraryLoader.ZLoadLibrary(Location: String): Boolean;
var newpath, temp: String; // AB modif
begin
  if FLoaded then
    Self.FreeNativeLibrary;

  temp := ''; //init for FPC
  FLoaded := False;
  Result := False;
  newpath := ExtractFilePath(Location);
  // AB modif BEGIN
  try
   if newpath <> '' then begin
     temp := GetCurrentDir;
     SetCurrentDir(newpath);
   end;
  // AB modif END

{$IFDEF UNIX}
  {$IFDEF FPC}
    FHandle := LoadLibrary(PAnsiChar(Location));
  {$ELSE}
    FHandle := HMODULE(dlopen(PAnsiChar(Location), RTLD_GLOBAL));
  {$ENDIF}
{$ELSE}
  FHandle := LoadLibrary(PChar(Location));
{$ENDIF}

  // AB modif BEGIN
  finally
   if temp<>'' then
     SetCurrentDir(temp);
  end;
  // AB modif END
  if (FHandle <> INVALID_HANDLE_VALUE) and (FHandle <> 0) then
  begin
    FLoaded := True;
    FCurrentLocation := Location;
    Result := True;
  end;
end;

I allready applied your previous patch and actuall i don't see real differences except your newly proposal doesn't work for FPC. Am i right or do i overlook something?

Accordingly http://synopse.info/forum/viewtopic.php?id=1467

In my patch above i did overlook something:
User delphinium did propose to spilt TableName and Schema which is right. But uppercase the tablename is WRONG.

Please remove the uppercasing line again. The IdentifierConverter is doing the job. Welcome to the postgres world.

Zeos 'em selves mostly uses Catalog.Schema.TableName for PostgreSQL. The IZDatabaseMetadata-interface can give you informations about: Are Schemas, Catalogs etc. supported.

And we use the IdentifierConverter for all 3 identifiers. PostgreSQL supports Schema=XyZ or xYZ or XYz or...... Same for Catalog and Tablename.
I don't know where your Tablename is comming from but (i'm starting from the premisse you're using the IZDatabasMetadata interface) keep track you force the users to work case sensitive as much as possible. The IdentifierConverter also checks if the Identifier allready is quoted then nothing happens.

Select * from XyZ.xyz.XYz could lead to pain if all schemas+catalogs do have a table called "XYz" and "xYZ" and XYZ. Sure you know what i mean... Just a little info @all.

Edit: I made the quick patches for you:

procedure TSQLDBZEOSConnectionProperties.GetTableNames(var Tables: TRawUTF8DynArray);
var meta: IZDatabaseMetadata;
    res: IZResultSet;
    TableTypes: Types.TStringDynArray;
    n: integer;
begin
  if GetDatabaseMetadata(meta) then begin
    SetLength(TableTypes,1);
    TableTypes[0] := 'TABLE';
    res := meta.GetTables('','','',TableTypes);
    n := 0;
    while res.Next do
      {$IFDEF ZEOS72UP}
      AddSortedRawUTF8(Tables,n,res.GetUTF8String(3));
      {$ELSE !ZEOS72UP}
      AddSortedRawUTF8(Tables,n,SynUnicodeToUtf8(res.GetUnicodeString(3)));
      {$ENDIF ZEOS72UP}
    SetLength(Tables,n);
  end else
    inherited;
end;

procedure TSQLDBZEOSConnectionProperties.GetFields(
  const aTableName: RawUTF8; var Fields: TSQLDBColumnDefineDynArray);
var meta: IZDatabaseMetadata;
    res: IZResultSet;
    n, i: integer;
    TableName: string;
    F: TSQLDBColumnDefine;
    FA: TDynArray;
begin
  if GetDatabaseMetadata(meta) then begin
    TableName := meta.GetIdentifierConvertor.Quote(UTF8ToString(aTableName));
    res := meta.GetColumns('','',TableName,'');
    FA.Init(TypeInfo(TSQLDBColumnDefineDynArray),Fields,@n);
    FA.Compare := SortDynArrayAnsiStringI; // FA.Find() case insensitive
    FillChar(F,sizeof(F),0);
    while res.Next do begin
      {$IFDEF ZEOS72UP}
      F.ColumnName := res.GetUTF8String(4);
      F.ColumnTypeNative := res.GetUTF8String(6);
      {$ELSE !ZEOS72UP}
      F.ColumnName := SynUnicodeToUtf8(res.GetUnicodeString(4));
      F.ColumnTypeNative := SynUnicodeToUtf8(res.GetUnicodeString(6));
      {$ENDIF ZEOS72UP}
      F.ColumnType := TZSQLTypeToTSQLDBFieldType(TZSQLType(res.GetInt(5)));
      F.ColumnLength := res.GetInt(7);
      F.ColumnPrecision := res.GetInt(9);
      FA.Add(F);
    end;
    if n>0 then begin
      res := meta.GetIndexInfo('','',TableName,false,true);
      while res.Next do begin
        {$IFDEF ZEOS72UP}
        F.ColumnName := res.GetUTF8String(9);
        {$ELSE !ZEOS72UP}
        F.ColumnName := SynUnicodeToUtf8(res.GetUnicodeString(9));
        {$ENDIF !ZEOS72UP}
        i := FA.Find(F);
        if i>=0 then
          Fields[i].ColumnIndexed := true;
      end;
    end;
    SetLength(Fields,n);
    exit;
  end;
  inherited;
end;

Last edited by EgonHugeist (2013-11-30 20:30:44)

ab · 2013-11-30 21:00:11

Nice!

For GetTableNames() / GetFields() I suspect it won't make any difference between using GetUTF8String() or passing via an UnicodeString temporary variable, right?
AFAIK we removed any forced uppercase() in our code for identifiers.

But inside mORMot, we use identifiers just as in pascal (since we rely on an ORM) - i.e. XYz=xyZ=XYZ - so we will expect never have to worry about case sensitivity.

fUseCache := true will use a cache for statements.
It will make it MUCH faster.
But, some month ago, when I put fUseCache := true for Sqlite3 with Zeos, it just broke - whereas there was no problem with re-using prepared statements with other providers.
Perhaps it is fixed in newer versions.

EgonHugeist · 2013-11-30 21:32:20

For GetTableNames() / GetFields() I suspect it won't make any difference between using GetUTF8String() or passing via an UnicodeString temporary variable, right?

Except performance, it wouldn't. GetUTF8String returns a true utf8-encoded, #0 terminated string in all cases. It doesn't matter which client encoding, MetaInformation-(Virtual)ResultSets, CachedResultSets or NativeResultSets.

You pointed me to "Zeos is doing to much String-Conversions" in the past. This case is prevented where ever it's possible. Like i wrote in http://synopse.info/forum/viewtopic.php?id=1484 we're not ready. Some small jobs like suppressing the log's etc. And a bit more powerfull GetUTF8String just for you can be done. Btw. you wrote your access is much faster. Do you have a Posgre-Wrapper?

It will make it MUCH faster.
But, some month ago, when I put fUseCache := true for Sqlite3 with Zeos, it just broke - whereas there was no problem with re-using prepared statements with other providers.
Perhaps it is fixed in newer versions.

Lorbs, i think i know the reason for the little bugga. Your fist tests have been made with 7.0 where no Real-Prepared stmt was available for SQLite. The deprecated emulated did a Reset if the fetch or update was complete. I quickly wrote the real-prepared one(3hours?), which doesn't reset the handles execpt on stmt.Close/Destroy/Uprepare and binds the values instead of building a huge string. But if it helps some tests on your side will show it.

Now you've a define for 7.2 to keep track about behavior changes.

Btw. actually i'm fixing floting and integer types for the most RDBM's acordingly stByte/stCurrency and so on. Just have a look to the numeric and decimal declaration: http://www.postgresql.org/docs/8.3/stat … meric.html
It might be possible your Double/Currency types came out of range than... Postgre-Word -> NO LIMITS?

Mark is still busy with the logging things. I'll need some time for finalizing the types and spezially for you the fastest Zeos GetUTF8Gtring. Than i would be interested to see the performance changes with your tests. I only compare Zeos 7.1down agains 7.2up and for DBC and TDataSet i was able to do a lot. Hope it fits for your needs...

Edit:

I read about the DDL BatchUpdates. Have no clue how to realize SLT yet. Could you give me hint what you do expect?

Last edited by EgonHugeist (2013-11-30 21:34:34)

ab · 2013-12-01 12:47:45

I just uploaded latest Zeos 7.2 branch.

In order to work as expected I had to patch it:

function TZSQLiteDatabaseMetadata.UncachedGetColumns(const Catalog: string;
  const SchemaPattern: string; const TableNamePattern: string;
  const ColumnNamePattern: string): IZResultSet;
var
  Temp: string;
  Precision, Decimals, UndefinedVarcharAsStringLength: Integer;
  Temp_scheme: string;
  ResSet: IZResultSet;
begin
  Result:=inherited UncachedGetColumns(Catalog, SchemaPattern, TableNamePattern, ColumnNamePattern);

  if SchemaPattern = '' then
    Temp_scheme := '' // OR  'main.'
  else
    Temp_scheme := SchemaPattern +'.';

  UndefinedVarcharAsStringLength := (GetConnection as IZSQLiteConnection).GetUndefinedVarcharAsStringLength;

  ResSet := GetConnection.CreateStatementWithParams(Finfo).ExecuteQuery(
    Format('PRAGMA %s table_info(''%s'')', [Temp_scheme, TableNamePattern]));
  if ResSet<>nil then
  begin
    while ResSet.Next do
    begin
      Result.MoveToInsertRow;
      if SchemaPattern <> '' then
        Result.UpdateString(1, SchemaPattern)
      else Result.UpdateNull(1);
      Result.UpdateNull(2);
      Result.UpdateString(3, TableNamePattern);
      Result.UpdateRawByteString(4, ResSet.GetRawByteString(2));
      Result.UpdateInt(5, Ord(ConvertSQLiteTypeToSQLType(ResSet.GetRawByteString(3),
        UndefinedVarcharAsStringLength, Precision, Decimals, ConSettings.CPType)));

      { Defines a table name. }
      Temp := UpperCase(ResSet.GetString(3));
      if Pos('(', Temp) > 0 then
        Temp := Copy(Temp, 1, Pos('(', Temp) - 1);
      Result.UpdateString(6, Temp);

      Result.UpdateInt(7, Precision);  //Precision will be converted higher up
      Result.UpdateNull(8);
      Result.UpdateInt(9, Decimals);
      Result.UpdateInt(10, 0);

      if ResSet.GetInt(4) <> 0 then
      begin
        Result.UpdateInt(11, Ord(ntNoNulls));
        Result.UpdateString(18, 'NO');
      end
      else
      begin
        Result.UpdateInt(11, Ord(ntNullable));
        Result.UpdateString(18, 'YES');
      end;

      Result.UpdateNull(12);
      if Trim(ResSet.GetString(5)) <> '' then
        Result.UpdateString(13, ResSet.GetString(5))
//          Result.UpdateString(13, '''' + GetString(5) + '''')
      else Result.UpdateNull(13);
      Result.UpdateNull(14);
      Result.UpdateNull(15);
      Result.UpdateNull(16);
      Result.UpdateInt(17, ResSet.GetInt(1) + 1);

      Result.UpdateBooleanByName('AUTO_INCREMENT',
        (ResSet.GetInt(6) = 1) and (Temp = 'INTEGER'));
      Result.UpdateBooleanByName('CASE_SENSITIVE', False);
      Result.UpdateBooleanByName('SEARCHABLE', True);
      Result.UpdateBooleanByName('WRITABLE', True);
      Result.UpdateBooleanByName('DEFINITELYWRITABLE', True);
      Result.UpdateBooleanByName('READONLY', False);

      Result.InsertRow;
    end;
    ResSet.Close;
  end;
end;

function TZSQLiteDatabaseMetadata.UncachedGetIndexInfo(const Catalog: string;
  const Schema: string; const Table: string; Unique: Boolean;
  Approximate: Boolean): IZResultSet;
var
  MainResultSet, ResultSet: IZResultSet;
  Temp_scheme: string;
begin
    Result:=inherited UncachedGetIndexInfo(Catalog, Schema, Table, Unique, Approximate);

    if Schema = '' then
      Temp_scheme := '' // OR  'main.'
    else
      Temp_scheme := Schema +'.';

    MainResultSet := GetConnection.CreateStatementWithParams(Finfo).ExecuteQuery(
      Format('PRAGMA %s index_list(''%s'')', [Temp_scheme, Table]));
    if MainResultSet<>nil then
    begin
      while MainResultSet.Next do
      begin
        if (Pos(' autoindex ', String(MainResultSet.GetString(2))) = 0)
          and ((Unique = False) or (MainResultSet.GetInt(3) = 0)) then
        begin
          ResultSet := GetConnection.CreateStatementWithParams(Finfo).ExecuteQuery(
            Format('PRAGMA %s index_info(''%s'')', [Temp_scheme,MainResultSet.GetString(2)]));
          while ResultSet.Next do
          begin
            Result.MoveToInsertRow;

            if Schema <> '' then
              Result.UpdateString(1, Schema)
            else Result.UpdateNull(1);
            Result.UpdateNull(2);
            Result.UpdateString(3, Table);
            Result.UpdateBoolean(4, MainResultSet.GetInt(3) = 0);
            Result.UpdateNull(5);
            Result.UpdateString(6, MainResultSet.GetString(2));
            Result.UpdateNull(7);
            Result.UpdateInt(8, ResultSet.GetInt(1) + 1);
            Result.UpdateString(9, ResultSet.GetString(3));
            Result.UpdateString(10, 'A');
            Result.UpdateInt(11, 0);
            Result.UpdateInt(12, 0);
            Result.UpdateNull(13);

            Result.InsertRow;
          end;
          ResultSet.Close;
        end;
      end;
      MainResultSet.Close;
    end;
end;

Here, the IResultSet can be nil (if the table does not exist), so you have an access violation with the current implementation.

Perhaps other methods of this unit need to be fixed, and check explicitly from the IResultSet returned value.

Then, there is still one issue with the Zeos implementation.
It is stated in our SynDBZeos.pas unit:

- if you want to work as expected with SQlite3 backend (but how would need to
do it, since it will be MUCH slower compared to SynDBSQlite3), you need
to change procedure TZSQLiteResultSet.FreeHandle in ZDbcSqLiteResultSet.pas
from ErrorCode := FPlainDriver.reset(FStmtHandle)
into ErrorCode := SQLITE_OK
(i.e. disable statement reset)

That is:

procedure TZSQLiteResultSet.FreeHandle;
var
  ErrorCode: Integer;
begin
  if FFreeHandle then
  begin
    if Assigned(FStmtHandle) then
      ErrorCode := FPlainDriver.Finalize(FStmtHandle)
    else
      ErrorCode := SQLITE_OK;
    FStmtHandle := nil;
    CheckSQLiteError(FPlainDriver, FStmtHandle, ErrorCode, nil,
      lcOther, 'FINALIZE SQLite VM', ConSettings);
  end
  else
  begin
    ErrorCode := SQLITE_OK; //FPlainDriver.reset(FStmtHandle);
    CheckSQLiteError(FPlainDriver, FStmtHandle, ErrorCode, nil, lcBindPrepStmt, 'Reset Prepared Stmt', ConSettings);
    FErrorCode := SQLITE_DONE;
  end;
end;

Now, if I unable statement cache (fUseCache := true), I encounter the same error as before, i.e. "SQL error: library routine called out of sequence":

CheckSQLiteError(Pointer($DDDD44) as IZSQLitePlainDriver,$3FCBDB0,21,???,lcBindPrepStmt,'select ID,FirstName,LastName,Amount,BirthDate,LastChange,CreatedAt from SampleRecord where ID=?',$D7E990)
TZSQLiteCAPIPreparedStatement.BindInParameters
TZSQLiteCAPIPreparedStatement.ExecuteQueryPrepared
TSQLDBZEOSStatement.ExecutePrepared
TSQLDBStatement.ExecutePreparedAndFetchAllAsJSON(True,'')
TSQLRestServerStaticExternal.EngineRetrieve(???,???)
TSQLRestServerURIContext.ExecuteORMGet
TSQLRestServer.URI(('root/Sample/1', 'GET', 'RemoteIP: 127.0.0.1'#$D#$A'ConnectionID: 004AA8FC', '', '', '', 400, 7, $753234))
TSQLRestClientDB.InternalURI(('root/Sample/1', 'GET', 'RemoteIP: 127.0.0.1'#$D#$A'ConnectionID: 004AA8FC', '', '', '', 400, 7, $753234))
TSQLRestClientURI.URI('root/Sample/1','GET',$18F758 '',nil '',nil '')
TSQLRestClientURI.URIGet(TSQLRecordSample,1,'',False)
TSQLRestClientURI.EngineRetrieve(???,1,False,0,'')
TSQLRestClient.Retrieve(1,$DFBF90,False)
TMainForm.Test(TSQLDBZEOSConnectionProperties,'sqlite','','','',' SQlite3',True,smFull,lmNormal)
TMainForm.BtnRunTestsClick(???)

It sounds like if SQlite3 does not allow re-use of prepared statements, whereas other provider do (e.g. Oracle, Firebird....).
Setting ForceNativeResultSet=True does not fix the issue.
Is there any needed additional parameter?

Of course, with my patches above, if I left "fUseCache := false", then the process run as expected.
With some noticeable speed increase:

Plain old 7.0 branch:
{
"ClassName":"TStat",
"Engine": "ZEOS SQlite3",
"CreateTableTime": "15.28ms",
"NumberOfElements": 5000,
"InsertTime": "1.72s",
"InsertRate": 289,
"InsertBatchTime": "1.64s",
"InsertBatchRate": 303,
"InsertTransactionTime": "282.37ms",
"InsertTransactionRate": 17706,
"InsertBatchTransactionTime": "254.03ms",
"InsertBatchTransactionRate": 19682,
"ReadOneByOneTime": "1.32s",
"ReadOneByOneRate": 3781,
"ReadAllVirtualTime": "53.40ms",
"ReadAllVirtualRate": 93632,
"ReadAllDirectTime": "44.13ms",
"ReadAllDirectRate": 113296,
"ClientCloseTime": "40us"
},
{
"ClassName":"TStat",
"Engine": "ZEOS Firebird",
"CreateTableTime": "102.49ms",
"NumberOfElements": 5000,
"InsertTime": "4.67s",
"InsertRate": 1069,
"InsertBatchTime": "4.59s",
"InsertBatchRate": 1088,
"InsertTransactionTime": "350.49ms",
"InsertTransactionRate": 14265,
"InsertBatchTransactionTime": "334.83ms",
"InsertBatchTransactionRate": 14932,
"ReadOneByOneTime": "327.77ms",
"ReadOneByOneRate": 15254,
"ReadAllVirtualTime": "82.82ms",
"ReadAllVirtualRate": 60371,
"ReadAllDirectTime": "66.17ms",
"ReadAllDirectRate": 75554,
"ClientCloseTime": "73us"
},
Latest 7.2 testing branch:
{
"Engine": "ZEOS SQlite3",
"CreateTableTime": "5.71ms",
"NumberOfElements": 5000,
"InsertTime": "1.71s",
"InsertRate": 292,
"InsertBatchTime": "1.68s",
"InsertBatchRate": 296,
"InsertTransactionTime": "304.09ms",
"InsertTransactionRate": 16442,
"InsertBatchTransactionTime": "271.28ms",
"InsertBatchTransactionRate": 18431,
"ReadOneByOneTime": "558.02ms",
"ReadOneByOneRate": 8960,
"ReadAllVirtualTime": "30.36ms",
"ReadAllVirtualRate": 164668,
"ReadAllDirectTime": "20.20ms",
"ReadAllDirectRate": 247524,
"ClientCloseTime": "68us"
}
{
"Engine": "ZEOS Firebird",
"CreateTableTime": "79.90ms",
"NumberOfElements": 5000,
"InsertTime": "507.64ms",
"InsertRate": 9849,
"InsertBatchTime": "491.29ms",
"InsertBatchRate": 10177,
"InsertTransactionTime": "253.37ms",
"InsertTransactionRate": 19733,
"InsertBatchTransactionTime": "227.87ms",
"InsertBatchTransactionRate": 21941,
"ReadOneByOneTime": "330.16ms",
"ReadOneByOneRate": 15143,
"ReadAllVirtualTime": "79.00ms",
"ReadAllVirtualRate": 63288,
"ReadAllDirectTime": "59.72ms",
"ReadAllDirectRate": 83711,
"ClientCloseTime": "85us"
}

Nice!

EgonHugeist · 2013-12-01 14:06:52

It is stated in our SynDBZeos.pas unit:
...
Now, if I unable statement cache (fUseCache := true), I encounter the same error as before, i.e. "SQL error: library routine called out of sequence":

Grumbl. Nope i don't think this patch right or i am wrong.
As far as i understand SQLite:
Prepare stmt.
Bind Parameters.
Execute prepared.

loop
Reuse requires a reset (not finalze like the old code was going). Am i wrong?
So reset the stmt.
Execute Prepared
until Unprepare.

May i ask: Do you make a full fetch until EOF or do you break earlier?
So in preparetion of this case: Seond Patch commited. R2974

Did apply you MetaData-Patch.

It sounds like if SQlite3 does not allow re-use of prepared statements, whereas other provider do (e.g. Oracle, Firebird....).
Setting ForceNativeResultSet=True does not fix the issue.
Is there any needed additional parameter?

Nope not AFAIK.

Maybe my fix makes it possible?

Nice!

Yep but i'm not happy until you get you fUseCache running. And i did commit a GetUTF8String upgrade a half hour ago. Hope it will speed a little bit better. (I just do prevent a locale string before assigning the UTF8String result so the result would be minimal).

If my fix doesn't help, could you attach a little code sequence to reproduce the bugga?

Michael

ab · 2013-12-02 13:38:35

I just tested Zeos 7.2 (latest version) with Oracle.

Current 7.2 implementation fails to connect, whereas the 7.x trunk has no problem.
In TZOracleConnection.Open(), ConSettings^.Database='' and ConSettings^.User='' whereas it was filled as expected in 7.1 branch.
It is a show-stopper to use the 7.2 branch.

I also tested the current trunk version, but it is not consistent with the 7.1 branch: ZEOS72UP is defined, whereas some method signatures are inconsistent.
So I felt back into the latest 7.1.2 stable release, which compiles and run fine with Oracle.

About SQlite3, the RESET is to be run before binding new parameters:
1- Prepare Statement
2- Bind parameters
3- Execute statement - and fetch any returned result (mostly all data, perhaps break before the end since there is an option to retrieve only the Nth rows of data)
4- Reset statement
5- Go to step 2 (i.e. bind new parameters)

But it fails in step 4 (Reset) when the statement returns some data (i.e. INSERTs are OK, but SELECT fails to re-open).
I guess there is something wrong with the re-prepare order, linked to result columns.

EgonHugeist · 2013-12-03 14:34:03

ab wrote:

Current 7.2 implementation fails to connect, whereas the 7.x trunk has no problem.
In TZOracleConnection.Open(), ConSettings^.Database='' and ConSettings^.User='' whereas it was filled as expected in 7.1 branch.
It is a show-stopper to use the 7.2 branch.

?? In 7.1 Branch these fields do not extist in the ConSettings-record. And i run daily tests with oracle too. So actually i can't confirm that. We do not have more fails or exceptions in our tests than in 7.2. Everything is fine on my side and the project manager didn't recognize any behavior changes too.

IF these fields are empty than there must be something wrong in:

ZDbcConnection.pas
constructor TZAbstractConnection.Create(const ZUrl: TZURL);
........
  ConSettings^.Database := ConSettings^.ConvFuncs.ZStringToRaw(FURL.Database, ConSettings^.CTRL_CP, ConSettings^.ClientCodePage^.CP);
  ConSettings^.User := ConSettings^.ConvFuncs.ZStringToRaw(FURL.UserName, ConSettings^.CTRL_CP, ConSettings^.ClientCodePage^.CP);
..

As long as i can't reproduce it i can't fix it. Which compiler did you use for?

ab wrote:

I also tested the current trunk version, but it is not consistent with the 7.1 branch: ZEOS72UP is defined, whereas some method signatures are inconsistent.
So I felt back into the latest 7.1.2 stable release, which compiles and run fine with Oracle.

That's right. trunk is a tested mirror of \testing-7.2 and will be merged weekly if 7.2 patches are older than min. a week.

ab wrote:

But it fails in step 4 (Reset) when the statement returns some data (i.e. INSERTs are OK, but SELECT fails to re-open).
I guess there is something wrong with the re-prepare order, linked to result columns.

As i wrote my previous post i allready commited a patch for that. You're right. The reset did only happen if the fetch was done until EOF. Than the resultet did execute the reset.

i just patched the resultset and:

function TZSQLiteCAPIPreparedStatement.ExecuteQueryPrepared: IZResultSet;
begin
  if Not Prepared then
     Prepare;
  { after reading the last row we reset the statment. So we don't need this here }
  try
    if LastResultSet <> nil then
      LastResultSet.Close; // reset stmt
    LastResultSet := nil;
    BindInParameters;
    FErrorCode := FPlainDriver.Step(FStmtHandle);
    CheckSQLiteError(FPlainDriver, FStmtHandle, FErrorCode, nil, lcOther,
      ConSettings^.ConvFuncs.ZStringToRaw(SCanNotRetrieveResultsetData, ConSettings^.CTRL_CP, ConSettings^.ClientCodePage^.CP),
      ConSettings);
    if ( FErrorCode = SQLITE_ROW ) or ( FErrorCode = SQLITE_DONE) then
      LastResultSet := CreateResultSet(FStmtHandle, FErrorCode);
    Result := LastResultSet;
    inherited ExecuteQueryPrepared;
  except
    raise;
  end;
end;

So i call resultset.close which execs the reset. Hope this helps for option fUseCache. Just update from SVN
Btw. i also cache the stmts for the Cached-Resultsets and do reuse them..

Oracle makes me wondering.. SQLite need a review for your reported issues (no resultset) too.

ab · 2013-12-03 16:09:25

I tried to modify ExecuteQueryPrepared as proposed...
But no change...

This may be due to my own patch (see above), which is necessary otherwise everything breaks and I get access violation (an invalid PStmt instance is provided to Reset - so SQLite_API.sqlite_reset(pStmt) raise a GPF):

  Note:
  - if you want to work as expected with SQlite3 backend (but how would need to
    do it, since it will be MUCH slower compared to SynDBSQlite3), you need
    to change procedure TZSQLiteResultSet.FreeHandle in ZDbcSqLiteResultSet.pas
     from  ErrorCode := FPlainDriver.reset(FStmtHandle)
     into  ErrorCode := SQLITE_OK
    (i.e. disable statement reset)

EgonHugeist · 2013-12-03 17:46:50

Oh i was starting from the premisse you're updating from SVN.

Well in addition to the TZSQLiteCAPIPreparedStatement.ExecuteQueryPrepared: IZResultSet; patch you'll need to patch too.
See:

procedure TZSQLiteResultSet.FreeHandle;
var
  ErrorCode: Integer;
begin
  if FFreeHandle then
  begin
    if Assigned(FStmtHandle) then
      ErrorCode := FPlainDriver.Finalize(FStmtHandle)
    else
      ErrorCode := SQLITE_OK;
    FStmtHandle := nil;
    CheckSQLiteError(FPlainDriver, FStmtHandle, ErrorCode, nil,
      lcOther, 'FINALIZE SQLite VM', ConSettings);
  end
  else
  begin
    if FStmtHandle <> nil then
    begin
      ErrorCode := FPlainDriver.reset(FStmtHandle);
      CheckSQLiteError(FPlainDriver, FStmtHandle, ErrorCode, nil, lcBindPrepStmt, 'Reset Prepared Stmt', ConSettings);
      FStmtHandle := nil;
    end;
    FErrorCode := SQLITE_DONE;
  end;
end;

Sorry.

Added two testcases for your report:

procedure TZTestDbcSQLiteCase.TestReuseResultsetNative;
var
  PreparedStatement: IZPreparedStatement;
  ResultSet: IZResultSet;
  Info: TStrings;
begin
  Info := TStringList.Create;
  Info.Add('ForceNativeResultSet=True');
  PreparedStatement := Connection.PrepareStatementWithParams(
    'SELECT * FROM PEOPLE WHERE p_id > ?', Info);
  try
    PreparedStatement.SetInt(1, 0); //expecting 5 rows
    ResultSet := PreparedStatement.ExecuteQueryPrepared;
    CheckEquals(True, ResultSet.Next); //fetch first row.
    CheckEquals(1, ResultSet.GetInt(1));
    CheckEquals(True, ResultSet.Next); //fetch second row.
    CheckEquals(True, ResultSet.Next); //fetch third row.
    CheckEquals(True, ResultSet.Next); //fetch fourth row.
    {ignore last row}
    PreparedStatement.SetInt(1, 1); //expecting 4 rows
    ResultSet := PreparedStatement.ExecuteQueryPrepared;
    CheckEquals(True, ResultSet.Next); //fetch first row.
    CheckEquals(2, ResultSet.GetInt(1));
    CheckEquals(True, ResultSet.Next); //fetch second row.
    CheckEquals(True, ResultSet.Next); //fetch third row.
    {ignore last row}

    PreparedStatement.SetInt(1, 2); //expecting 3 rows
    ResultSet := PreparedStatement.ExecuteQueryPrepared;
    CheckEquals(True, ResultSet.Next); //fetch first row.
    CheckEquals(3, ResultSet.GetInt(1));
    while ResultSet.Next do; //full fetch automatically resets handle

    PreparedStatement.SetInt(1, 3); //expecting 2 rows
    ResultSet := PreparedStatement.ExecuteQueryPrepared;
    CheckEquals(True, ResultSet.Next); //fetch first row.
    CheckEquals(4, ResultSet.GetInt(1));
    while ResultSet.Next do; //full fetch automatically resets handle

    PreparedStatement.SetInt(1, 10); //expecting !0! rows  e.g AB(synopse) -> no metadata???
    ResultSet := PreparedStatement.ExecuteQueryPrepared;
    CheckNotNull(ResultSet);
    CheckEquals(False, ResultSet.Next); //fetch first row.
  finally
    Info.Free;
    if Assigned(ResultSet) then
      ResultSet.Close;
    PreparedStatement.Close;
  end;
end;

procedure TZTestDbcSQLiteCase.TestReuseResultsetCached;
var
  PreparedStatement: IZPreparedStatement;
  ResultSet: IZResultSet;
  Info: TStrings;
begin
  Info := TStringList.Create;
  PreparedStatement := Connection.PrepareStatement(
    'SELECT * FROM PEOPLE WHERE p_id > ?');
  try
    PreparedStatement.SetInt(1, 0); //expecting 5 rows
    ResultSet := PreparedStatement.ExecuteQueryPrepared;
    CheckEquals(True, ResultSet.Next); //fetch first row.
    CheckEquals(1, ResultSet.GetInt(1));
    CheckEquals(True, ResultSet.Next); //fetch second row.
    CheckEquals(True, ResultSet.Next); //fetch third row.
    CheckEquals(True, ResultSet.Next); //fetch fourth row.
    {ignore last row}
    PreparedStatement.SetInt(1, 1); //expecting 4 rows
    ResultSet := PreparedStatement.ExecuteQueryPrepared;
    CheckEquals(True, ResultSet.Next); //fetch first row.
    CheckEquals(2, ResultSet.GetInt(1));
    CheckEquals(True, ResultSet.Next); //fetch second row.
    CheckEquals(True, ResultSet.Next); //fetch third row.
    {ignore last row}

    PreparedStatement.SetInt(1, 2); //expecting 3 rows
    ResultSet := PreparedStatement.ExecuteQueryPrepared;
    CheckEquals(True, ResultSet.Next); //fetch first row.
    CheckEquals(3, ResultSet.GetInt(1));
    while ResultSet.Next do; //full fetch automatically resets handle

    PreparedStatement.SetInt(1, 3); //expecting 2 rows
    ResultSet := PreparedStatement.ExecuteQueryPrepared;
    CheckEquals(True, ResultSet.Next); //fetch first row.
    CheckEquals(4, ResultSet.GetInt(1));
    while ResultSet.Next do; //full fetch automatically resets handle
  finally
    Info.Free;
    if Assigned(ResultSet) then
      ResultSet.Close;
    PreparedStatement.Close;
  end;
end;

On my side the tests do work well too. If i'm doing something different to your implementation: please change the sequence of my tests to get a reproducable testcase on my side too.

ab · 2013-12-03 18:34:34

Yes!!!

With the two patches, now SQLite3 statements can be re-used with SQLite3!
I've enabled it for ZEOS72UP version.

fUseCache := false wrote:

{
"Engine": "ZEOS SQlite3",
"CreateTableTime": "5.16ms",
"NumberOfElements": 5000,
"InsertTime": "1.06s",
"InsertRate": 471,
"InsertBatchTime": "1.04s",
"InsertBatchRate": 476,
"InsertTransactionTime": "214.57ms",
"InsertTransactionRate": 23302,
"InsertBatchTransactionTime": "183.96ms",
"InsertBatchTransactionRate": 27179,
"ReadOneByOneTime": "2.76s",
"ReadOneByOneRate": 1807,
"ReadAllVirtualTime": "53.98ms",
"ReadAllVirtualRate": 92613,
"ReadAllDirectTime": "45.59ms",
"ReadAllDirectRate": 109651,
"ClientCloseTime": "65us"
}

fUseCache := true wrote:

{
"Engine": "ZEOS SQlite3",
"CreateTableTime": "5.73ms",
"NumberOfElements": 5000,
"InsertTime": "1.03s",
"InsertRate": 482,
"InsertBatchTime": "1.05s",
"InsertBatchRate": 475,
"InsertTransactionTime": "129.58ms",
"InsertTransactionRate": 38583,
"InsertBatchTransactionTime": "94.10ms",
"InsertBatchTransactionRate": 53134,
"ReadOneByOneTime": "2.85s",
"ReadOneByOneRate": 1749,
"ReadAllVirtualTime": "54.62ms",
"ReadAllVirtualRate": 91528,
"ReadAllDirectTime": "46.36ms",
"ReadAllDirectRate": 107846,
"ClientCloseTime": "73us"
}

There is a huge speed benefit for insertions within transactions, but a slightly amelioration only at reading.
Very nice!

For your information, here is what we achieve with our own SynSQLite3 classes:

SynSQLite3 wrote:

{
"Engine": "SQLite3 (ext full)",
"CreateTableTime": "3.66ms",
"NumberOfElements": 5000,
"InsertTime": "1.01s",
"InsertRate": 492,
"InsertBatchTime": "1.02s",
"InsertBatchRate": 489,
"InsertTransactionTime": "52.05ms",
"InsertTransactionRate": 96059,
"InsertBatchTransactionTime": "41.68ms",
"InsertBatchTransactionRate": 119955,
"ReadOneByOneTime": "39.92ms",
"ReadOneByOneRate": 125222,
"ReadAllVirtualTime": "22.05ms",
"ReadAllVirtualRate": 226664,
"ReadAllDirectTime": "11.51ms",
"ReadAllDirectRate": 434140,
"ClientCloseTime": "53us"
}

(it runs here SQLite3 as EXTERNAL database engine, so we get even better results when it is run from the mORMot Sqlite3 core)

EgonHugeist · 2013-12-03 20:37:10

ab wrote:

Yes!!!

Nice!

Accordingly your performance. Man is it possible? Hum have to check what i finally can do. Some remaining ideas i have:
Instead of using column_bytes i'll check how our from FastCode-project ported StrLen() is working.
And finally instead of current PlainDriver-Call i could call the binded SQLite3 function directly. Otherwise i've to pass for fetching speed(except see below).

According Insertiation speed:
As i wrote Mark currently is suppressing value logging which slows down the current performance. And after my suggestion with SetUTF8String i did the follwing patch:

ZVarinat.pas

function TZClientVariantManager.GetAsCharRec(var Value: TZVariant; const CodePage: Word): TZCharRec;
begin
  Result.CP := CodePage;
  case Value.VType of
    vtNull:
      begin
        Result.P := nil;
        Result.Len := 0;
      end;
    vtCharRec:
      if ZCompatibleCodePages(CodePage, Value.VCharRec.CP) then
        Result := Value.VCharRec
      else
        if ZCompatibleCodePages(CodePage, zCP_UTF16) then
        begin
          Value.VUnicodeString := Convert(Value, vtUnicodeString).VUnicodeString;
          Result.P := PWideChar(Value.VUnicodeString);
          Result.Len := Length(Value.VUnicodeString);
        end
        else
        begin
          Value.VRawByteString := GetAsRawByteString(Value, CodePage);
          Result.P := PAnsiChar(Value.VRawByteString);
          Result.Len := Length(Value.VRawByteString);
        end;
    vtUTF8String:
      if CodePage = zCP_UTF8 then
      begin
        Result.P := PAnsiChar(Value.VUTF8String);
        Result.Len := Length(Value.VUTF8String);
      end
      else
      begin
        Value.VRawByteString := GetAsRawByteString(Value, CodePage);
        Result.P := PAnsiChar(Value.VRawByteString);
        Result.Len := Length(Value.VRawByteString);
      end;
    else
      begin
        Value.VRawByteString := GetAsRawByteString(Value, CodePage);
        Result.P := PAnsiChar(Value.VRawByteString);
        Result.Len := Length(Value.VRawByteString);
      end;
  end;
end;

AB is it necceassary to localize your RawUTF8 values while binding them? OR can we point to them?

Then you could use this:

procedure TSQLDBZEOSStatement.ExecutePrepared;
var i: integer;
    Props: TSQLDBZEOSConnectionProperties;
    Log: ISynLog;
    blob: IZBlob;
    name: string;
    {$IFDEF ZEOS72UP}
    CharRec: TZCharRec;
    {$ENDIF}
begin
  Log := SynDBLog.Enter(Self);
  with Log.Instance do
    if sllSQL in Family.Level then
      LogLines(sllSQL,pointer(SQLWithInlinedParams),self,'--');
  if fStatement=nil then
    raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared() invalid call',[ClassName]);
  if fResultSet<>nil then
    raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared() miss a Reset',[ClassName]);
  // 1. bind parameters in fParams[] to fQuery.Params
  for i := 1 to fParamCount do
    with fParams[i-1] do
    case VType of
    ftNull:     fStatement.SetNull(i,stUnknown);
    ftInt64:    fStatement.SetLong(i,VInt64);
    ftDouble:   fStatement.SetDouble(i,PDouble(@VInt64)^);
    ftCurrency: fStatement.SetDouble(i,PCurrency(@VInt64)^);
    ftDate:     fStatement.SetTimestamp(i,PDateTime(@VInt64)^);
    ftUTF8:     {$IFDEF ZEOS72UP}
                begin
                  CharRec.Len := PLongInt(NativeInt(VData) - 4)^; //length of string
                  CharRec.CP := 65001; //utf8
                  CharRec.P := PAnsiChar(VData);
                  fStatement.SetCharRec(i,CharRec);
                end;
                //fStatement.SetUTF8String(i,VData);
                {$ELSE}
                  {$ifdef UNICODE}  // ZWideString = SynUnicode in fact
                  fStatement.SetString(i,UTF8ToSynUnicode(VData));
                  {$else}
                  fStatement.SetString(i,VData); // thanks to controls_cp=CP_UTF8
                  {$endif}
                {$ENDIF}
......
end;

function TSQLDBZEOSStatement.ColumnUTF8(Col: Integer): RawUTF8;
{$IFDEF ZEOS72UP}
var AnsiRec: TZAnsiRec;
{$ENDIF}
begin
  if (fResultSet=nil) or (cardinal(Col)>=cardinal(fColumnCount)) then
    raise ESQLDBZEOS.CreateFmt('TSQLDBZEOSStatement.ColumnUTF8(%d)',[Col]);
  {$IFDEF ZEOS72UP}
  begin
     AnsiRec := fResultSet.GetAnsiRec(Col+1);
     SetString(Result, AnsiRec.P, AnsiRec.Len);
  end;
    //result := fResultSet.GetUTF8String(Col+1); // GetRawByteString returns also UTF8 if connection charset is UTF8
  {$ELSE}
    {$ifdef UNICODE}
    StringToUTF8(fResultSet.GetString(Col+1),result);
    {$else}
    result := fResultSet.GetString(Col+1); // thanks to controls_cp=CP_UTF8
    {$endif}
end;

Which than avoids possible string conversions of the IDE's.
Anyway i don't think i'm able to get such a top-speed running. How did you manage that? Curious. Our users are pretty happy inbetween. I'm sure we won't never reach your speed since it fits perfectly for your needs. But such a difference i didn't expect.....

ab · 2013-12-03 21:39:09

Our internal classes are able to directly output the content from the DB buffer (e.g. SQLite3 UTF-8 memory or Oracle row buffers) directly into JSON, with no temporary memory allocation nor data copy.
This is what occurs in 'ReadAllDirectRate' mode above, which sounds to be 4x faster than Zeos.
The data is marshalled as followed:
- One time with SQLite3
- Then written as JSON from the SQlite3 buffer
- Parsed from JSON into a Delphi TSQLRecord class properties (with RawUTF8 allocation).

But even with the full SynDB layer and our ColumnUTF8() retrieval (with temporary memory allocation), it is still 2 times faster than Zeos ('ReadAllVirtualRate' mode).
And in this case, ALL data is passed via another SQLite3 virtual table within the mORMot ORM core.
That is, data is marshalled:
- One time with SQLite3
- One time with ColumnUTF8()
- One time within the SQlite3 virtual table
- Then written as JSON
- Parsed from JSON into a Delphi TSQLRecord class properties (with RawUTF8 allocation).

In fact, JSON helps to reduce memory allocation, since each row data blocks are mostly re-used by FastMM4.
And JSON features result caching at once.

All mORMot process does involve only a few number of classes and a very small layer above the driver.
For instance, your proposal of using a TZCharRec will ALWAYS allocate a string memory in SetString(Result, AnsiRec.P, AnsiRec.Len), even if your internal structure has already the data as UTF-8 RawByteString, so may be affected to a RawUTF8 with no memory allocation...

With ZEOS, you have about data process:
- One time with SQLite3
- One time within Zeos structures
- One time with ColumnUTF8()
- One time within the SQlite3 virtual table (for ReadAllVirtualRate only)
- Then written as JSON
- Parsed from JSON into a Delphi TSQLRecord class properties (with RawUTF8 allocation).
Perhaps implementing a TSQLDBZEOSStatement.FetchAllToJSON() method may help, if we manage to directly write from the ZDBC buffers into JSON.

EgonHugeist · 2013-12-03 21:40:21

Edit:

did commit my remaining ideas. R2979 \testing-7.2
Hmpf hope it helps? I've got some more fetching speed with the Ansi-IDE's and a bit more with the Unicode-IDE's.

Propose you patch SynDBZEOS as suggesest and refresh ZDBC. Otherwise ... I've to pass, Arnaud. I did never expect to top your performance but such differences.. ? Nope i didn't expect it...
Hands up!!!!!

ab · 2013-12-03 21:43:15

(I edited my answer above)

Writing a TSQLDBZEOSStatement.FetchAllToJSON() method may help...

EgonHugeist · 2013-12-03 22:06:56

Oki.
First of all: is there a csv or svn repo to be up to date?
Did you study the 7.2 implementations i made?
Just to avoid loosing my time if you know better ways to implement the FetchAllToJSON override.
And i need a little advice:
Which files and functions/procedures need to be reviewed? Hint: All patches i propose aren't testet, just a theoretical suggestion (actually) until i'll find the time to learn a bit more about the morrmots (: . As you know Mark(my second pair of eyes, coder, project-manager) and me are the "main"-maintainers of Zeos. And Zeos is ... work enough, trust me.

Don't think i wanna bet here. But i'm sure there are other users too which eventually do try something equal. So having a huge bandwidth of support wouldn't be a problem for me.

Let's go if you're ready (:

Last edited by EgonHugeist (2013-12-03 23:12:38)

ab · 2013-12-04 11:07:54

About Oracle I do not understand how 7.2 branch may work on your side.

There was a problem here:

constructor TZAbstractConnection.Create(const ZUrl: TZURL);
....
  // should be set BEFORE InternalCreate
  ConSettings^.Protocol := NotEmptyStringToASCII7(FIZPlainDriver.GetProtocol);
  ConSettings^.Database := ConSettings^.ConvFuncs.ZStringToRaw(FURL.Database, ConSettings^.CTRL_CP, ConSettings^.ClientCodePage^.CP);
  ConSettings^.User := ConSettings^.ConvFuncs.ZStringToRaw(FURL.UserName, ConSettings^.CTRL_CP, ConSettings^.ClientCodePage^.CP);
  // now InternalCreate will work, since it will try to Open the connection
  InternalCreate;
  SetDateTimeFormatProperties;
...

Now Oracle connection is established.

But then, I've got an error with a stCurrency field.
We need to patch:

procedure InitializeOracleVar(PlainDriver: IZOraclePlainDriver;
  Connection: IZConnection; var Variable: PZSQLVar;
  DataType: TZSQLType; OracleType: ub2; DataSize: Integer);
var
  Length: Integer;
  OracleConnection: IZOracleConnection;
begin
  OracleConnection := Connection as IZOracleConnection;
  Variable.ColType := DataType;
  Variable.TypeCode := OracleType;
  Variable.DataSize := DataSize;
  Length := 0;
  case Variable.ColType of
    stByte, stShort, stWord, stSmall, stInteger:
      begin
        Variable.TypeCode := SQLT_INT;
        Length := SizeOf(LongInt);
      end;
    stFloat, stDouble, stLongWord, stUlong, stLong, stCurrency: // add stCurrency HERE!
      begin
        Variable.TypeCode := SQLT_FLT;
        Length := SizeOf(Double);
      end;
    stDate, stTime, stTimestamp:
   ....

I suspect you may need to extend your test case scenario for Oracle, to support all supported kind of data.
With SynDB, we restricted to a very small set of data type (the same as SQLite3 + Currency + DataTime), so code is much easier to test/maintain. For instance, we have just one Int64 kind of value.*

Here are the SQLite3 results for 7.2 branch:

Zeos 7.2 wrote:

{
"Engine": "ZEOS SQlite3",
"CreateTableTime": "4.44ms",
"NumberOfElements": 5000,
"InsertTime": "1.07s",
"InsertRate": 466,
"InsertBatchTime": "1.48s",
"InsertBatchRate": 335,
"InsertTransactionTime": "119.03ms",
"InsertTransactionRate": 42004,
"InsertBatchTransactionTime": "95.30ms",
"InsertBatchTransactionRate": 52460,
"ReadOneByOneTime": "134.81ms",
"ReadOneByOneRate": 37088,
"ReadAllVirtualTime": "26.22ms",
"ReadAllVirtualRate": 190679,
"ReadAllDirectTime": "19.01ms",
"ReadAllDirectRate": 262936,
"ClientCloseTime": "81us"
}

Sounds like a very nice speed improvement in regard to 7.1 branch!
I suspect it may be difficult to make it much better.
(compiled and run with Delphi 7)

For the repository, see http://synopse.info/fossil/wiki?name=Get+the+source
And also a daily snapshot at http://synopse.info/files/mORMotNightlyBuild.zip

EgonHugeist · 2013-12-04 19:53:23

Hossa!!

That looks pretty good now.

I did commit your additional fixes. Yes of coures our current tests do NOT catch all possible issues. But they mostly grow equal to the tickets we get. I added a loads of tests inbetween.

I also fixed the SQLite 'INTEGER' problem after reading this: http://www.sqlite.org/autoinc.html

So you can disable the BIGINT improvement with the 72UP define.

Regarding the newly FieldTypes. Yes Zeos pre 7.2 did also only use a rare set of types. But i got some more Bugreports for the newer IDE's + ADO/Sybase/MySQL. Main reason they all support Unsigned integer types up to UInt64. And one of the TDataSet-performance killer is TField and TParam. So i'm working out a better implementation which fits better to Zeos72 up.

I'm not sure if we can do more... As i said we are not ready with the logging patches. And some plains like oracle need a review. For DBlib+FreeTDS i plan a read-prepared stmt which currently is the remaining driver which doesn't support such one.
I did check SynSQLITE3.pas

procedure TSQLRequest.FieldsToJSON(WR: TJSONWriter; DoNotFletchBlobs: boolean);
var i: integer;
begin
  if Request=0 then
    raise ESQLite3Exception.Create(RequestDB,SQLITE_MISUSE);
  if WR.Expand then
    WR.Add('{');
  for i := 0 to FieldCount-1 do begin
    if WR.Expand then
      WR.AddString(WR.ColNames[i]); // '"'+ColNames[]+'":'
    case sqlite3.column_type(Request,i) of // fast evaluation: type may vary
      SQLITE_BLOB:
        if DoNotFletchBlobs then
          WR.AddShort('null') else
          WR.WrBase64(sqlite3.column_blob(Request,i),
            sqlite3.column_bytes(Request,i),true); // withMagic=true
      SQLITE_NULL:
        WR.AddNoJSONEscape(PAnsiChar('null'),4); // returned also for ""
      SQLITE_INTEGER:
        WR.Add(sqlite3.column_int64(Request,i));
      SQLITE_FLOAT:
        WR.Add(sqlite3.column_double(Request,i));
      SQLITE_TEXT: begin
        WR.Add('"');
        WR.AddJSONEscape(sqlite3.column_text(Request,i),0);
        WR.Add('"');
       end;
    end; // case ColTypes[]
    WR.Add(',');
  end;
  WR.CancelLastComma; // cancel last ','
  if WR.Expand then
    WR.Add('}');
end;

Huge sureprice: You do NOT make differences with ftDateTime? Which format are you using for?
Since 7.2 i added Connection options for reading/writing datetime-formats different from ISO-format. Reason was PostgresSQL and MSSQL.
See: TZAbstractConnection.SetDateTimeFormatProperties(DetermineFromInfo: Boolean = True);

Hum you said having a own FieldsToJSON function could perform better. Is there a generic way for an override? I don't see this declaration here.. OR where exactly should it be placed than?
Again: I don't expect to top your speed but having more Zeos-Users which hopefully help me a bit would be nice...

ab · 2013-12-04 21:50:57

At SQLite3 level, date times are ISO-8601 text.
There is no dedicated Date/Time format.

SQLite3 documentation wrote:

1.2 Date and Time Datatype
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
See http://www.sqlite.org/datatype3.html

We also used this TEXT format at JSON level (there is not date/time format for JSON either).

For the same reason, there is no "currency" type at SQLite3 level either.
It works only with "double" values.

But when we work at SynDB level, we did add TDateTime and Currency types, as I wrote above.
And some special UTF-8 markers to identify date/time and blob parameters (which are mostly supplied as text).

It is clear to me that the fact that your ZDBC layer is not based on DB.pas and its TDateSet/TField/TParam types is a HUGE BONUS.
Even the more optimized libraries (like AnyDac/FireDAC) suffers from this TDataSet when reading one record.
With its 7.2 branch, ZDBC is a first class citizen candidate for mORMot.
Only missing feature is array DML, i.e. the possibility to bind an array of values on each parameter: this makes INSERT much faster, since all set of data parameters is set at once to the server. See e.g. our native Oracle classes, or FireDAC with Oracle or MSSQL.

I will update the mORMot benchmarks with latest Zeos 7.2 branch.
And with Delphi XE4 certainly (those tests above were with Delphi 7).
When do you expect to publish a stable release?

Thanks anyway!
It is a pleasure to let Open Source projects contribute and play together!
Delphi rocks!

EgonHugeist · 2013-12-06 07:45:09

ab wrote:

It is clear to me that the fact that your ZDBC layer is not based on DB.pas and its TDateSet/TField/TParam types is a HUGE BONUS.

Sure. But this will take some more time until i'm ready.

ab wrote:

And with Delphi XE4 certainly (those tests above were with Delphi 7).

The UnicodeIDE's do also perform much better with my tests. Where i'm not sure: Is the GetUTF8String/SetUTF8String best option for these IDE's? I hope no mem-moves do happen between your RawUTF8 and the UTF8String. Otherwise we should try my suggestion with the records.

ab wrote:

When do you expect to publish a stable release?

No idea. The current code is AS stable as the official releases are. I wouldn't have any objections BUT:
Going beta means only bugfixes are allowed. A feature freeze mill happen than. So all basic implementations, i wanna do need a stable state than.
Which means i need finilize my TZFields, start the TZParam, write a real-prepared stmt for DBLIB/FreeTDS and i need to check plains like Oracle if we could do some more accordingly performance (My impression for ZDBC: topspeed reached for MySQL, PostgresSQL, SQLite, FB, ADO as plains. The current TDataSet implementation uses also a loads of more tricks with the lobs and the performance did grow x3(cachedlobs=true)/x20(cachedlobs=false) with !OUR! Test-table "high_load").

ab wrote:

It is a pleasure to let Open Source projects contribute and play together!

Yes it is! Thank you too, Arnaud. Your patches are always welcome. Hope some more users will join Zeos. And i really hope to get some more help (most people do only report if something is wrong ).
Accordingly ArrayBindings: I think i can do that. I'll check your OCI implementation about best practice..
(:

Last edited by EgonHugeist (2013-12-06 09:01:48)

ab · 2013-12-06 09:35:42

EgonHugeist wrote:

The UnicodeIDE's do also perform much better with my tests. Where i'm not sure: Is the GetUTF8String/SetUTF8String best option for these IDE's? I hope no mem-moves do happen between your RawUTF8 and the UTF8String. Otherwise we should try my suggestion with the records.

Yes, there is no conversion between RawUTF8 and UTF8String.

BTW, there are some optimizations in your code which are not worth it.
For instance, newer compilers have inlining feature so there is no need to write "PLongInt(NativeInt(Result) - 4)^" instead of "Length(Result)", since Length() function will be inlined by the compiler.
A good habit is to check the generated asm code with Alt-F2 in debugging mode. You will find out all calls and hidden temporary variables (and hidden try..finally), which could have performance costs. Even if you do not know asm, you can easily find out the execution process, just by looking at the calls.
For instance, you sometimes write "PAnsiChar(aAnsiString)" whereas you should better write "Pointer(aAnsiString)" if you just want to read the string buffer, to avoid a RTL call.

But your code sounds optimized enough by now.
I'm less convinced by the inclusion of FastCode to the project. If you rely on it, you will only have a few % of speed increase, in some border cases (like huge memory blocks), whereas profiling the code may give you a much better speed increase (from 200 to 1000% sometimes!).
Profiling is everything. We spend time sometimes for premature optimization, which is the root of all evil (Knuth).
See http://www.delphitools.info/samplingprofiler/

Now results are indeed higher then with Delphi 7, when compiled with Delphi XE4:

Zeos 7.2 Delphi XE4 wrote:

{
"ClassName":"TStat",
"Engine": "ZEOS SQlite3",
"CreateTableTime": "534.57ms",
"NumberOfElements": 5000,
"InsertTime": "1.02s",
"InsertRate": 485,
"InsertBatchTime": "1.03s",
"InsertBatchRate": 483,
"InsertTransactionTime": "130.90ms",
"InsertTransactionRate": 38197,
"InsertBatchTransactionTime": "99.03ms",
"InsertBatchTransactionRate": 50487,
"ReadOneByOneTime": "139.54ms",
"ReadOneByOneRate": 35830,
"ReadAllVirtualTime": "23.73ms",
"ReadAllVirtualRate": 210703,
"ReadAllDirectTime": "15.54ms",
"ReadAllDirectRate": 321564,
"ClientCloseTime": "69us"
},

EgonHugeist wrote:

Accordingly ArrayBindings: I think i can do that. I'll check your OCI implementation about best practice..

Nice!

A weird issue with SQLite3 current implementation.
You do not check returned error code for TZSQLiteConnection.Close, and it returns in fact SQLITE_LOCKED (5).
So the DB file is not closed - and I cannot delete the file when running my tests several times.
You have some pending statements around, I'm afraid.

EgonHugeist · 2013-12-07 23:02:25

ab wrote:

BTW, there are some optimizations in your code which are not worth it.
For instance, newer compilers have inlining feature so there is no need to write "PLongInt(NativeInt(Result) - 4)^" instead of "Length(Result)", since Length() function will be inlined by the compiler.
A good habit is to check the generated asm code with Alt-F2 in debugging mode. You will find out all calls and hidden temporary variables (and hidden try..finally), which could have performance costs. Even if you do not know asm, you can easily find out the execution process, just by looking at the calls.
For instance, you sometimes write "PAnsiChar(aAnsiString)" whereas you should better write "Pointer(aAnsiString)" if you just want to read the string buffer, to avoid a RTL call.

Thank you for such hints! You're welcome! Sade, my ASM time is 10 years ago(just on learning my job). Never used again..
AB i could grant you SVN access. I don't expect you'll develop Zeos but for small fixes your help would be welcome.. I just need your SF.Net user-name.

ab wrote:

I'm less convinced by the inclusion of FastCode to the project.

That might be true. But loads of pards did make faster conversions (mainly for the Unicode-IDE's) with number conversion in any kind. I also wrote a set of high performance functions which you can find in ZSysUtils.pas.
Also do i use some FastCode-based code for Conversions to UInt64 f.e. while delphi just use Format('%u') ... as replacement. Anyway parts of this code really performce better. Most parts like Move/FillChar is used with defines if the IDE's already use this code.

ab wrote:

A weird issue with SQLite3 current implementation.
You do not check returned error code for TZSQLiteConnection.Close, and it returns in fact SQLITE_LOCKED (5).
So the DB file is not closed - and I cannot delete the file when running my tests several times.
You have some pending statements around, I'm afraid.

I'm affraid too. sqlite3_close was wrong decalred. I fixed this. Than did add the check on closing the connection.
Et voila: You're right.

Found and fixed a SQLite3 longstanding bug in the TZSQLiteStatment. An issue in Zeos alltime code.

But than it took a while to have a common solution for ForceNativeResultSet (as you reported before) and without Statement.Close. I simply use a weak pointer ref to LastResultSet and a way back to inform about ResultSet-destruction.

Patch done: R2995 /testing-7.2

Confirmed?

Last edited by EgonHugeist (2013-12-08 00:54:16)

EgonHugeist · 2014-08-09 22:02:29

Arnaud, may i pik up that old thread again?

Did some modifications...

ab wrote:

BTW, there are some optimizations in your code which are not worth it.
For instance, newer compilers have inlining feature so there is no need to write "PLongInt(NativeInt(Result) - 4)^" instead of "Length(Result)", since Length() function will be inlined by the compiler.

I noticed you use this code too inbetween. Friendly hint: FPC uses SizeInt for RefCount and Length. So the type is 4/8Byte in regard of compile target.
To fix it i introduced a LengthInt, LengthIntOffSet, RefCountInt and RefcountOffSet.

ab wrote:

A weird issue with SQLite3 current implementation.
You do not check returned error code for TZSQLiteConnection.Close, and it returns in fact SQLITE_LOCKED (5).
So the DB file is not closed - and I cannot delete the file when running my tests several times.
You have some pending statements around, I'm afraid.

This issue is resolved, i hope. And one of the results: i broke with the "ForceNativeResultSet" option. Please omit this code.

EgonHugeist wrote:

Accordingly ArrayBindings: I think i can do that. I'll check your OCI implementation about best practice..

I've added ArrayBindings in all kinds but for OCI only(actually).
Just a test case i'm using as an example:

{$WARNINGS OFF} //implizit string conversion of...
procedure TZTestDbcOracleCase.TestArrayBindings;
const
  hl_id_Index           = {$IFDEF GENERIC_INDEX}0{$ELSE}1{$ENDIF};
  stBooleanArray_Index  = {$IFDEF GENERIC_INDEX}1{$ELSE}2{$ENDIF};
  stByte_Index          = {$IFDEF GENERIC_INDEX}2{$ELSE}3{$ENDIF};
  stShort_Index         = {$IFDEF GENERIC_INDEX}3{$ELSE}4{$ENDIF};
  stInteger_Index       = {$IFDEF GENERIC_INDEX}4{$ELSE}5{$ENDIF};
  stLong_Index          = {$IFDEF GENERIC_INDEX}5{$ELSE}6{$ENDIF};
  stFloat_Index         = {$IFDEF GENERIC_INDEX}6{$ELSE}7{$ENDIF};
  stDouble_Index        = {$IFDEF GENERIC_INDEX}7{$ELSE}8{$ENDIF};
  stBigDecimal_Index    = {$IFDEF GENERIC_INDEX}8{$ELSE}9{$ENDIF};
  stString_Index        = {$IFDEF GENERIC_INDEX}9{$ELSE}10{$ENDIF};
  stUnicode_Index       = {$IFDEF GENERIC_INDEX}10{$ELSE}11{$ENDIF};
  stBytes_Index         = {$IFDEF GENERIC_INDEX}11{$ELSE}12{$ENDIF};
  stDate_Index          = {$IFDEF GENERIC_INDEX}12{$ELSE}13{$ENDIF};
  stTime_Index          = {$IFDEF GENERIC_INDEX}13{$ELSE}14{$ENDIF};
  stTimeStamp_Index     = {$IFDEF GENERIC_INDEX}14{$ELSE}15{$ENDIF};
  stGUID_Index          = {$IFDEF GENERIC_INDEX}15{$ELSE}16{$ENDIF};
  stAsciiStream_Index   = {$IFDEF GENERIC_INDEX}16{$ELSE}17{$ENDIF};
  stUnicodeStream_Index = {$IFDEF GENERIC_INDEX}17{$ELSE}18{$ENDIF};
  stBinaryStream_Index  = {$IFDEF GENERIC_INDEX}18{$ELSE}19{$ENDIF};
var
  PStatement: IZPreparedStatement;
  hl_idArray: TIntegerDynArray;
  stBooleanArray: TBooleanDynArray;
  stByteArray: TByteDynArray;
  stShortArray: TShortIntDynArray;
  stLongArray: TInt64DynArray;
  stIntegerArray: TIntegerDynArray;
  stFloatArray: TSingleDynArray;
  stDoubleArray: TDoubleDynArray;
  stBigDecimalArray: TExtendedDynArray;
  stStringArray: TRawByteStringDynArray;
  stUnicodeStringArray: TUnicodeStringDynArray;
  stBytesArray: TBytesDynArray;
  stDateArray: TDateTimeDynArray;
  stTimeArray: TDateTimeDynArray;
  stTimeStampArray: TDateTimeDynArray;
  stGUIDArray: TGUIDDynArray;
  stAsciiStreamArray: TZCharRecDynArray;
  stUnicodeStreamArray: TUTF8StringDynArray;
  stBinaryStreamArray: TInterfaceDynArray;
  stBooleanNullArray: array of TBooleanDynArray;
  stByteNullArray: array of TByteDynArray;
  stShortNullArray: array of TShortIntDynArray;
  stWordNullArray: array of TWordDynArray;
  stSmallNullArray: array of TSmallIntDynArray;
  stLongWordNullArray: array of TLongWordDynArray;
  stIntegerNullArray: array of TIntegerDynArray;
  stULongNullArray: array of TUInt64DynArray;
  stLongNullArray: array of TInt64DynArray;
  stFloatNullArray: array of TSingleDynArray;
  stDoubleNullArray: array of TDoubleDynArray;
  stCurrencyNullArray: array of TCurrencyDynArray;
  stBigDecimalNullArray: array of TExtendedDynArray;
  stStringNullArray: array of TRawByteStringDynArray;
  stUnicodeStringNullArray: array of TUnicodeStringDynArray;
  I, J: Integer;

  procedure PrepareSomeData;
  var I: Integer;
  begin
    SetLength(hl_idArray, 50);
    SetLength(stBooleanArray, 50);
    SetLength(stByteArray, 50);
    SetLength(stShortArray, 50);
    SetLength(stLongArray, 50);
    SetLength(stIntegerArray, 50);
    SetLength(stFloatArray, 50);
    SetLength(stDoubleArray, 50);
    SetLength(stBigDecimalArray, 50);
    SetLength(stStringArray, 50);
    SetLength(stUnicodeStringArray, 50);
    SetLength(stBytesArray, 50);
    SetLength(stDateArray, 50);
    SetLength(stTimeArray, 50);
    SetLength(stTimeStampArray, 50);
    SetLength(stGUIDArray, 50);
    SetLength(stAsciiStreamArray, 50);
    SetLength(stUnicodeStreamArray, 50);
    SetLength(stBinaryStreamArray, 50);
    for i := 0 to 49 do
    begin
      hl_idArray[i] := I;
      stBooleanArray[i] := Boolean(Random(1));
      stByteArray[i] := Random(255);
      stShortArray[i] := I;
      stLongArray[i] := I;
      stIntegerArray[i] := I;
      stFloatArray[i] := RandomFloat(-5000, 5000);
      stDoubleArray[i] := RandomFloat(-5000, 5000);
      stBigDecimalArray[i] := RandomFloat(-5000, 5000);
      stStringArray[i] := RandomStr(Random(99)+1);
      stUnicodeStringArray[i] := RandomStr(Random(254+1));
      stBytesArray[i] := RandomBts(50);
      stDateArray[i] := Trunc(Now);
      stTimeArray[i] := Frac(Now);
      stTimeStampArray[i] := Now;
      stGUIDArray[i] := RandomGUID;
      stAsciiStreamArray[i].Len := Length(stStringArray[i]);
      stAsciiStreamArray[i].P := Pointer(stStringArray[i]);
      stAsciiStreamArray[i].CP := Connection.GetConSettings^.ClientCodePage^.CP; {safe we're passing ASCII7 only to the raws}
      stUnicodeStreamArray[i] := RandomStr(MaxPerformanceLobSize);
      stBinaryStreamArray[i] := TZAbstractBlob.Create;
      (stBinaryStreamArray[i] as IZBlob).SetBytes(RandomBts(MaxPerformanceLobSize));
    end;
  end;
begin
  Connection.PrepareStatement('delete from high_load').ExecutePrepared;
  PStatement := Connection.PrepareStatement(
  'insert into high_load(hl_id, stBoolean, stByte, stShort, stInteger, stLong, '+
    'stFloat, stDouble, stBigDecimal, stString, stUnicodeString, stBytes,'+
    'stDate, stTime, stTimestamp, stGUID, stAsciiStream, stUnicodeStream, '+
    'stBinaryStream) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
  CheckNotNull(PStatement);
  PrepareSomeData;
  PStatement.SetDataArray(hl_id_Index, hl_idArray, stInteger);
  PStatement.SetDataArray(stBooleanArray_Index, stBooleanArray, stBoolean);
  PStatement.SetDataArray(stByte_Index, stByteArray, stByte);
  PStatement.SetDataArray(stShort_Index, stShortArray, stShort);
  PStatement.SetDataArray(stInteger_Index, stIntegerArray, stInteger);
  PStatement.SetDataArray(stLong_Index, stLongArray, stLong);
  PStatement.SetDataArray(stFloat_Index, stFloatArray, stFloat);
  PStatement.SetDataArray(stDouble_Index, stDoubleArray, stDouble);
  PStatement.SetDataArray(stBigDecimal_Index, stBigDecimalArray, stBigDecimal);
  PStatement.SetDataArray(stString_Index, stStringArray, stString, vtRawByteString);
  PStatement.SetDataArray(stUnicode_Index, stUnicodeStringArray, stUnicodeString, vtUnicodeString);
  PStatement.SetDataArray(stBytes_Index, stBytesArray, stBytes);
  PStatement.SetDataArray(stDate_Index, stDateArray, stDate);
  PStatement.SetDataArray(stTime_Index, stTimeArray, stTime);
  PStatement.SetDataArray(stTimeStamp_Index, stTimeStampArray, stTimeStamp);
  PStatement.SetDataArray(stGUID_Index, stGUIDArray, stGUID);
  PStatement.SetDataArray(stAsciiStream_Index, stAsciiStreamArray, stString, vtCharRec);
  PStatement.SetDataArray(stUnicodeStream_Index, stUnicodeStreamArray, stString, vtUTF8String);
  PStatement.SetDataArray(stBinaryStream_Index, stBinaryStreamArray, stBinaryStream);

  for i := FirstDbcIndex to 19{$IFDEF GENERIC_INDEX}-1{$ENDIF} do
    case TZSQLType(Random(14)+1) of
      stBoolean:
        begin
          SetLength(stBooleanNullArray, Length(stBooleanNullArray) +1);
          SetLength(stBooleanNullArray[High(stBooleanNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stBooleanNullArray[High(stBooleanNullArray)][J] := False
            else
              stBooleanNullArray[High(stBooleanNullArray)][J] := Boolean(Random(1));
          PStatement.SetNullArray(I, stBoolean, stBooleanNullArray[High(stBooleanNullArray)]);
        end;
      stByte:
        begin
          SetLength(stByteNullArray, Length(stByteNullArray)+1);
          SetLength(stByteNullArray[High(stByteNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stByteNullArray[High(stByteNullArray)][J] := Ord(False)
            else
              stByteNullArray[High(stByteNullArray)][J] := Random(2);
          PStatement.SetNullArray(I, stByte, stByteNullArray[High(stByteNullArray)]);
        end;
      stShort:
        begin
          SetLength(stShortNullArray, Length(stShortNullArray)+1);
          SetLength(stShortNullArray[High(stShortNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stShortNullArray[High(stShortNullArray)][J] := 0
            else
              stShortNullArray[High(stShortNullArray)][J] := Random(2);
          PStatement.SetNullArray(I, stShort, stShortNullArray[High(stShortNullArray)]);
        end;
      stWord:
        begin
          SetLength(stWordNullArray, Length(stWordNullArray)+1);
          SetLength(stWordNullArray[High(stWordNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stWordNullArray[High(stWordNullArray)][j] := 0
            else
              stWordNullArray[High(stWordNullArray)][J] := Random(2);
          PStatement.SetNullArray(I, stWord, stWordNullArray[High(stWordNullArray)]);
        end;
      stSmall:
        begin
          SetLength(stSmallNullArray, Length(stSmallNullArray)+1);
          SetLength(stSmallNullArray[High(stSmallNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stSmallNullArray[High(stSmallNullArray)][J] := 0
            else
              stSmallNullArray[High(stSmallNullArray)][J] := -Random(2);
          PStatement.SetNullArray(I, stSmall, stSmallNullArray[High(stSmallNullArray)]);
        end;
      stLongWord:
        begin
          SetLength(stLongWordNullArray, Length(stLongWordNullArray)+1);
          SetLength(stLongWordNullArray[High(stLongWordNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stLongWordNullArray[High(stLongWordNullArray)][J] := 0
            else
              stLongWordNullArray[High(stLongWordNullArray)][J] := Random(2);
          PStatement.SetNullArray(I, stLongWord, stLongWordNullArray[High(stLongWordNullArray)]);
        end;
      stInteger:
        begin
          SetLength(stIntegerNullArray, Length(stIntegerNullArray)+1);
          SetLength(stIntegerNullArray[High(stIntegerNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stIntegerNullArray[High(stIntegerNullArray)][J] := 0
            else
              stIntegerNullArray[High(stIntegerNullArray)][J] := Random(2);
          PStatement.SetNullArray(I, stInteger, stIntegerNullArray[High(stIntegerNullArray)]);
        end;
      stULong:
        begin
          SetLength(stULongNullArray, Length(stULongNullArray)+1);
          SetLength(stULongNullArray[High(stULongNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stULongNullArray[High(stULongNullArray)][J] := 0
            else
              stULongNullArray[High(stULongNullArray)][J] := Random(2);
          PStatement.SetNullArray(I, stULong, stULongNullArray[High(stULongNullArray)]);
        end;
      stLong:
        begin
          SetLength(stLongNullArray, Length(stLongNullArray) +1);
          SetLength(stLongNullArray[High(stLongNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stLongNullArray[High(stLongNullArray)][J] := 0
            else
              stLongNullArray[High(stLongNullArray)][J] := Random(2)-1;
          PStatement.SetNullArray(I, stLong, stLongNullArray[High(stLongNullArray)]);
        end;
      stFloat:
        begin
          SetLength(stFloatNullArray, Length(stFloatNullArray)+1);
          SetLength(stFloatNullArray[High(stFloatNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stFloatNullArray[High(stFloatNullArray)][J] := 0
            else
              stFloatNullArray[High(stFloatNullArray)][J] := Random(2)-1;
          PStatement.SetNullArray(I, stFloat, stFloatNullArray[High(stFloatNullArray)]);
        end;
      stDouble:
        begin
          SetLength(stDoubleNullArray, Length(stDoubleNullArray)+1);
          SetLength(stDoubleNullArray[high(stDoubleNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stDoubleNullArray[high(stDoubleNullArray)][J] := 0
            else
              stDoubleNullArray[high(stDoubleNullArray)][J] := Random(2)-1;
          PStatement.SetNullArray(I, stDouble, stDoubleNullArray[high(stDoubleNullArray)]);
        end;
      stCurrency:
        begin
          SetLength(stCurrencyNullArray, Length(stCurrencyNullArray)+1);
          SetLength(stCurrencyNullArray[High(stCurrencyNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stCurrencyNullArray[High(stCurrencyNullArray)][J] := 0
            else
              stCurrencyNullArray[High(stCurrencyNullArray)][J] := Random(2)-1;
          PStatement.SetNullArray(I, stCurrency, stCurrencyNullArray[High(stCurrencyNullArray)]);
        end;
      stBigDecimal:
        begin
          SetLength(stBigDecimalNullArray, Length(stBigDecimalNullArray)+1);
          SetLength(stBigDecimalNullArray[High(stBigDecimalNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stBigDecimalNullArray[High(stBigDecimalNullArray)][J] := 0
            else
              stBigDecimalNullArray[High(stBigDecimalNullArray)][J] := Random(2)-1;
          PStatement.SetNullArray(I, stBigDecimal, stBigDecimalNullArray[High(stBigDecimalNullArray)]);
        end;
      {stString:
        begin
          SetLength(stStringNullArray, Length(stStringNullArray)+1);
          SetLength(stStringNullArray[High(stStringNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stStringNullArray[High(stStringNullArray)][J] := 'FALSE'
            else
              if Random(2) = 0 then
                stStringNullArray[High(stStringNullArray)][J] := 'FALSE'
              else
                stStringNullArray[High(stStringNullArray)][J] := 'TRUE';
          PStatement.SetNullArray(I, stString, stStringNullArray[High(stStringNullArray)], vtRawByteString);
        end;}
      stUnicodeString:
        begin
          SetLength(stUnicodeStringNullArray, Length(stUnicodeStringNullArray)+1);
          SetLength(stUnicodeStringNullArray[High(stUnicodeStringNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stUnicodeStringNullArray[High(stUnicodeStringNullArray)][J] := 'FALSE'
            else
              if Random(2) = 0 then
                stUnicodeStringNullArray[High(stUnicodeStringNullArray)][J] := 'FALSE'
              else
                stUnicodeStringNullArray[High(stUnicodeStringNullArray)][J] := 'TRUE';
          PStatement.SetNullArray(I, stUnicodeString, stUnicodeStringNullArray[High(stUnicodeStringNullArray)], vtUnicodeString);
        end;
      else
        begin
          SetLength(stStringNullArray, Length(stStringNullArray)+1);
          SetLength(stStringNullArray[High(stStringNullArray)], 50);
          for J := 0 to 49 do
            if I = FirstDbcIndex then
              stStringNullArray[High(stStringNullArray)][J] := 'FALSE'
            else
              if Random(2) = 0 then
                stStringNullArray[High(stStringNullArray)][J] := 'FALSE'
              else
                stStringNullArray[High(stStringNullArray)][J] := 'TRUE';
          PStatement.SetNullArray(I, stString, stStringNullArray[High(stStringNullArray)], vtRawByteString);
        end;
      {stBytes:
      stGUID:
      stDate:
      stTime:
      stTimestamp:
      stArray:
      stDataSet:
      stAsciiStream:
      stUnicodeStream:
      stBinaryStream:}
    end;
  PStatement.ExecuteUpdatePrepared;
  //SetLength(stShortNullArray, 0);
end;
{$WARNINGS ON} //implizit string conversion of...

As you can see you have to set the DataArray first. You also have the possibility to add a NullIndicatorArray. If no NullIndicatorArray is given, i start from the premisse no NULL values are available.

Arnaud if you have time, please implement this part for Oracle only. I'll replay if i have a generic implementation running.
Btw:
1. On my side the Oracle performance tests do work nice.
2. Btw. Debugging an issue with mORMot and you Performance test is a bit .... i give up, why the hell i do not get a notification if something went wrong?
3. Do you notice the {GENERIC_INDEX} define? It means using index 0..High instead of current 1..Length index behavior..

Cheers, Michael

ab · 2014-08-10 07:23:00

Nice work!
It's pretty good to see that Delphi Open Source projects can be so active!

Which svn branch should I use?
The trunk or the 7.2 branch?
I'll try the trunk first.

I currently do not have an Oracle server available any more for testing...
But I'll try to install one Express edition in a VM.

The 15 sample is based on performance, not debugging.
But it does check that the written data is correct AFAIR.

I still can't get the benefit of {GENERIC_INDEX}, but for code obfuscation.

Trunk does compile and run fine with Delphi 7,Delphi 2007 and Delphi XE6.
Performance is very high, especially for reading (since we are directly linked to ZDBC, we bypass the TDataSet bottleneck).

Running tests using Synopse mORMot framework 1.18, compiled with Delphi 7, against SQLite 3.8.5, at 2014-08-10 09:58:49.

Insertion speed

 	                Direct 	Batch	Trans	Batch Trans
SQLite3 (file full)	440	427	77471	98943
SQLite3 (file off)	2092	2140	81310	104536
SQLite3 (file off exc)	26346	29130	81368	107332
SQLite3 (mem)	        69131	88555	85875	108825
TObjectList (static)	289653	392711	295229	404825
TObjectList (virtual)	280221	415420	289855	415006
SQLite3 (ext full)	457	13094	85851	150665
SQLite3 (ext off)	2179	39430	89183	153846
SQLite3 (ext off exc)	34435	147410	88977	158072
SQLite3 (ext mem)	76112	181785	90072	185825
MongoDB (ack)	        9457	88657	9222	87859
MongoDB (no ack)	32762	274982	34965	276931
ODBC SQLite3	        532	12622	33069	71996
ZEOS SQlite3	        485	14062	52267	80900
FireDAC SQlite3	        20615	37548	40281	119169
ODBC Firebird	        1044	15707	11283	15034
ZEOS Firebird	        10595	10523	22629	25195
FireDAC Firebird        19864	49319	19783	49746
MSSQL2012 local	        4055	37488	12181	46676
ODBC MSSQL2012	        3671	7315	6444	6603
FireDAC MSSQL2012	3222	5705	9651	39852
ZEOS PostgreSQL	        3012	32333	7210	41668
ODBC PostgreSQL	        2863	28100	3396	30461
FireDAC PostgreSQL	2851	27283	6793	28604

Read speed

   	               By one	All Virtual	All Direct
SQLite3 (file full)	21731	377871	391880
SQLite3 (file off)	22073	242471	398851
SQLite3 (file off exc)	97059	390411	399520
SQLite3 (mem)	        97287	402220	401445
TObjectList (static)	257042	760687	652571
TObjectList (virtual)	263213	303840	779909
SQLite3 (ext full)	106596	184781	396290
SQLite3 (ext off)	104845	185219	396196
SQLite3 (ext off exc)	107075	185763	384408
SQLite3 (ext mem)	102999	146361	368378
MongoDB (ack)	        7710	278862	283061
MongoDB (no ack)	7755	270109	272153
ODBC SQLite3	        16893	107709	159240
ZEOS SQlite3	        37840	150357	240789
FireDAC SQlite3	        8138	75743	102074
ODBC Firebird	        1999	55310	80732
ZEOS Firebird	        21410	73815	109318
FireDAC Firebird        2222	52675	67108
MSSQL2012 local	        8892	165667	343241
ODBC MSSQL2012	        10130	100373	164869
FireDAC MSSQL2012	4015	84741	137189
ZEOS PostgreSQL	        6958	118452	180186
ODBC PostgreSQL	        6920	63200	90540
FireDAC PostgreSQL	1745	58096	77221

Congrats!

ab · 2014-08-10 08:26:20

BTW, how do you identify if a provider does support array binding?
We have to know this in BATCH mode, to perform individual statements binding, or use SetDataArray().

EgonHugeist · 2014-08-10 10:02:04

Thanks AB, for testing.

AB wrote:

Which svn branch should I use?

Note \trunk is a "testet" mirror of \testing-7.2 (crrently). \trunk always adapts to the latest development branch.

ab wrote:

I currently do not have an Oracle server available any more for testing...
But I'll try to install one Express edition in a VM.

Sade, i was able to increase the reading performance in a very high rate too! Our single insertiation in non transactional mode seems to be a bit faster than yours. Weird i can't find the issue why our transactional insertiation is so much slower than yours.
Some new StatmentParameters for OCI:
'row_prefetch_size' is set to 128KB by default like yours.
'internal_buffer_size' is set to 128KB by default also, just like yours.
And finally i broke with the 'prefetch_count' parameter for OCI it was a nasty perfromance killer...

Some more little hints for your implementation:

SQLite:
It seems like FireDac is using synchronous=0 and locking_mode=EXCLUSIVE by default. Zeos doesn't use it by default but you can activate these modes also by adding
'synchronous=0' and 'locking_mode=EXCLUSIVE' to the ZURL properties. As you know this bumps the insertation performance in a very high rate! (;

I noticed you bench also against PostgresSQL. Did you ever test MySQL too?

ab wrote:

I still can't get the benefit of {GENERIC_INDEX}, but for code obfuscation.

Inital idea: avoid up and down processing of the Index for columns and parameters. But performance changes are not realy noticeable since CPU's are very fast novadays.
Anyway it did make more sence for the TZCachedResutlset which you don't need for your framework, AFAIR.

As i wrote IF you have time to play with zdbc, please add the ArrayBinding stuff for Zeos+Oracle.

I'll notify you if you can use it for all plains we support.

Michael

EgonHugeist · 2014-08-10 10:03:44

ab wrote:

BTW, how do you identify if a provider does support array binding?
We have to know this in BATCH mode, to perform individual statements binding, or use SetDataArray().

acrually i have no way to do this. Thought you can resolve this? ))):

ab · 2014-08-10 11:17:15

Our BATCH mode for Oracle uses array binding, so is much faster.
This is why we have to use SetDataArray() if it is available.
I suppose that we may do it in SynDBZeos, but it should be much better to have a standard way within the ZDBC layer to guess the provider abilities.
I suppose IZDatabaseInfo is the right place to retrieve the SetDataArray() support.

We can then do the same for SynDBZeos as we do with SynDBFireDac, i.e. set fDatasetSupportBatchBinding := true.
AFAIR FireDAC always support BATCH process, emulating it if the provider does not support true array binding.
We could use the new property in IZDatabaseInfo to safely set fDatasetSupportBatchBinding := true.

Note that in our BATCH mode, we also have an explicit TSQLDBConnectionProperties.OnBatchInsert property.
It is able, by default, to create multi INSERT statements, depending on the provider, at ORM level. This make BATCH mode also faster in most cases, even if the provider does not support array binding.
See TSQLDBConnectionProperties.Create().
Note that this trick at ORM level works also with ZDBC, since it is performed before calling ZDBC.
I know than some providers (like MS SQL or FireBird) do have a "BULK" insert API. It could be used at in SynDBZeos / ZDBC level directly via a dedicated OnBatchInsert method.

Perhaps you may try some tuning at TSQLDBZEOSConnectionProperties.Create() level, e.g. re-introduce OnBatchInsert for FireBird, or set another parameters.
For instance, should the prefetch_count=10000 be removed for dOracle in this method?

Yes, FireDAC is unfair about SQlite3 access.
It is the same as "SQLite3 (file off exc)" and "SQLite3 (ext off exc)" in our benchmarks.

We did test MySQL some months ago - included in our "official" benchmark blog articles and SAD pdf doc.
See http://blog.synopse.info/post/2014/03/0 … PostgreSQL
MySQL is sometimes a little faster than PostgreSQL, sometimes not.

EgonHugeist · 2014-08-10 12:02:09

ab wrote:

I suppose IZDatabaseInfo is the right place to retrieve the SetDataArray() support.

Cool idea -> done R3249 \testing-7.2 (SVN)
Added
function SupportsArrayBindings: Boolean;
To the IZDataBaseInfo. Thank you.

ab wrote:

I know than some providers (like MS SQL or FireBird) do have a "BULK" insert API. It could be used at in SynDBZeos / ZDBC level directly via a dedicated OnBatchInsert method.

Yes i know about. AFAIK should we use the bcp_xx functions for MSSQL and the "EXECUTE BLOCK" syntax for FireBird. But i didnt had time yet.
Major issue i have Zeos originally seems not to be made for max performance. So refactoring the whole code in a more optimal way needs time, as you know propably! And Zeos is just a hobby code i maintain. Have also family and a job...

ab wrote:

Perhaps you may try some tuning at TSQLDBZEOSConnectionProperties.Create() level, e.g. re-introduce OnBatchInsert for FireBird, or set another parameters.
For instance, should the prefetch_count=10000 be removed for dOracle in this method?

Yes if we go Beta i'll invest some more time to "optimize" your framework for ZDBC, Arnaud. But there is always a bug-tracker and users who are doing things nobody did think about before.

ab wrote:

For instance, should the prefetch_count=10000 be removed for dOracle in this method?

It won't be ahandled any more. So removing is not required..

Last edited by EgonHugeist (2014-08-10 12:10:13)

EgonHugeist · 2014-08-18 12:56:23

Arnaud,

constructor TSQLDBZEOSConnectionProperties.Create should look like:

constructor TSQLDBZEOSConnectionProperties.Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8);
const
  PCHARS: array[0..7] of PAnsiChar = (
    'ORACLE','FREETDS_MSSQL','MSSQL','INTERBASE','FIREBIRD','MYSQL','SQLITE','POSTGRESQL');
  TYPES: array[-1..high(PCHARS)] of TSQLDBDefinition = (
    dDefault,dOracle,dMSSQL,dMSSQL,dFirebird,dFirebird,dMySQL,dSQLite,dPostgreSQL);
  // expecting Postgresql + Sybase + ASA support in TSQLDBDefinition
begin
  fServerName :=  aServerName;
  if (fServerName<>'') and (PosEx(':',fServerName)=0) then
    fServerName := fServerName+':';
  if not IdemPChar(Pointer(aServerName),'ZDBC:') then
    fServerName := 'zdbc:'+fServerName;
  fURL := TZURL.Create(UTF8ToString(fServerName));
  if fURL.Database='' then
    fURL.Database := UTF8ToString(aDatabaseName);
  if fURL.UserName='' then
    fURL.UserName := UTF8ToString(aUserID);
  if fURL.Password='' then
    fURL.Password := UTF8ToString(aPassWord);
  StringToUTF8(fURL.Protocol,fDBMSName);
  fDBMS := TYPES[IdemPCharArray(pointer(fDBMSName),PCHARS)];
  inherited Create(aServerName,aDatabaseName,aUserID,aPassWord);
  fURL.Properties.Add('controls_cp=CP_UTF8');
  fUseCache := false; // caching is to be disabled - not found stable enough
  case fDBMS of
  dSQLite: begin
    {$ifdef ZEOS72UP}
    fUseCache := true; // statement cache has been fixed in 7.2 branch
    {$ELSE}
    fSQLCreateField[ftInt64] := ' BIGINT'; // SQLite3 INTEGER = 32bit for ZDBC!
    {$endif}
  end;
  dFirebird: begin
    if (fURL.HostName='') and // Firebird embedded: create db file if needed
       (fURL.Database<>'') and not FileExists(fURL.Database) then
      fURL.Properties.Add('createNewDatabase='+UTF8ToString(
        SQLCreateDatabase(StringToUTF8(fURL.Database))));
    fURL.Properties.Add('codepage=UTF8');
    fUseCache := true; // caching rocks with Firebird ZDBC provider :)
    if Assigned(OnBatchInsert) then begin
      // ZDBC: MultipleValuesInsertFirebird is buggy, MultipleValuesInsert slower
      fBatchSendingAbilities := [];
      OnBatchInsert := nil;
    end;
  end;
  dOracle, dPostgreSQL, dMySQL: begin
    fURL.Properties.Add('codepage=UTF8');
    fUseCache := true;
  end;
  end;
  fStatementParams := TStringList.Create;
  case fDBMS of
  dOracle:
    {$IFDEF ZEOS72UP}
    fOnBatchInsert := nil;
    {$ENDIF}
  dSQLite: begin
    {$ifdef ZEOS72UP} // new since 7.2up
    // Bind double values instead of ISO formated DateTime-strings
    //fStatementParams.Add('BindDoubleDateTimeValues=True');
    {$endif}
  end;
  dMySQL: begin
    // use mysql real-prepared api instead of string based once
    // actually it's not realy faster.. just a hint:
    // http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-problems.html
    //fStatementParams.Add('preferprepared=True');
  end;
  end;
  if fDBMS in [dOracle,dPostgreSQL,dMySQL] then begin
    // let's set 1024KB / chunk for synopse  or more?
    // retrieving/submitting lob's in chunks. Default is 4096Bytes / Chunk
    // it's depending to your local network speed e.g. bad WLAN or so
    // for Firebird we always using the blob-segment size
    fStatementParams.Add('chunk_size=1048576');
  end;
  if fDBMS in [dOracle,dPostgreSQL,dFireBird] then begin
    {$ifdef ZEOS72UP} // new since 7.2up
    // Always load the lobs? Or just on accessing them?
    // if you allways copy the data by fetching the row than it doesn't make sence.
    fStatementParams.Add('cachedlob=false'); //default = False
    {$endif}
  end;

end;

As you can see i set fOnBatchInsert := nil; for Oracle.

But than your tests do fail because the arrays aren't bound. May i ask for best practice? Any suggestions?

EgonHugeist · 2014-08-18 19:38:33

Triple post ):

Arnaud,

did play a while unit SynDBZEOS.pas

constructor TSQLDBZEOSConnectionProperties.Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8);
const
  PCHARS: array[0..7] of PAnsiChar = (
    'ORACLE','FREETDS_MSSQL','MSSQL','INTERBASE','FIREBIRD','MYSQL','SQLITE','POSTGRESQL');
  TYPES: array[-1..high(PCHARS)] of TSQLDBDefinition = (
    dDefault,dOracle,dMSSQL,dMSSQL,dFirebird,dFirebird,dMySQL,dSQLite,dPostgreSQL);
  // expecting Postgresql + Sybase + ASA support in TSQLDBDefinition
begin
  fServerName :=  aServerName;
  if (fServerName<>'') and (PosEx(':',fServerName)=0) then
    fServerName := fServerName+':';
  if not IdemPChar(Pointer(aServerName),'ZDBC:') then
    fServerName := 'zdbc:'+fServerName;
  fURL := TZURL.Create(UTF8ToString(fServerName));
  if fURL.Database='' then
    fURL.Database := UTF8ToString(aDatabaseName);
  if fURL.UserName='' then
    fURL.UserName := UTF8ToString(aUserID);
  if fURL.Password='' then
    fURL.Password := UTF8ToString(aPassWord);
  StringToUTF8(fURL.Protocol,fDBMSName);
  fDBMS := TYPES[IdemPCharArray(pointer(fDBMSName),PCHARS)];
  inherited Create(aServerName,aDatabaseName,aUserID,aPassWord);
  fURL.Properties.Add('controls_cp=CP_UTF8');
  fUseCache := false; // caching is to be disabled - not found stable enough
  case fDBMS of
  dSQLite: begin
    {$ifdef ZEOS72UP}
    fUseCache := true; // statement cache has been fixed in 7.2 branch
    {$ELSE}
    fSQLCreateField[ftInt64] := ' BIGINT'; // SQLite3 INTEGER = 32bit for ZDBC!
    {$endif}
  end;
  dFirebird: begin
    if (fURL.HostName='') and // Firebird embedded: create db file if needed
       (fURL.Database<>'') and not FileExists(fURL.Database) then
      fURL.Properties.Add('createNewDatabase='+UTF8ToString(
        SQLCreateDatabase(StringToUTF8(fURL.Database))));
    fURL.Properties.Add('codepage=UTF8');
    fUseCache := true; // caching rocks with Firebird ZDBC provider :)
    {if Assigned(OnBatchInsert) then begin
      // ZDBC: MultipleValuesInsertFirebird is buggy, MultipleValuesInsert slower
      fBatchSendingAbilities := [];
      OnBatchInsert := nil;
    end;}
  end;
  dOracle, dPostgreSQL, dMySQL: begin
    fURL.Properties.Add('codepage=UTF8');
    fUseCache := true;
  end;
  end;
  fStatementParams := TStringList.Create;
  case fDBMS of
  dOracle:
    {$IFDEF ZEOS72UP}
    begin
      fBatchSendingAbilities := [cCreate, cUpdate, cDelete];
      OnBatchInsert := nil;
    end;
    {$ENDIF}
  dSQLite: begin
    {$ifdef ZEOS72UP} // new since 7.2up
    // Bind double values instead of ISO formated DateTime-strings
    //fStatementParams.Add('BindDoubleDateTimeValues=True');
    {$endif}
  end;
  dMySQL: begin
    // use mysql real-prepared api instead of string based once
    // actually it's not realy faster.. just a hint:
    // http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-problems.html
    //fStatementParams.Add('preferprepared=True');
  end;
  end;
  if fDBMS in [dOracle,dPostgreSQL,dMySQL] then begin
    // let's set 1024KB / chunk for synopse  or more?
    // retrieving/submitting lob's in chunks. Default is 4096Bytes / Chunk
    // it's depending to your local network speed e.g. bad WLAN or so
    // for Firebird we always using the blob-segment size
    fStatementParams.Add('chunk_size=1048576');
  end;
  if fDBMS in [dOracle,dPostgreSQL,dFireBird] then begin
    {$ifdef ZEOS72UP} // new since 7.2up
    // Always load the lobs? Or just on accessing them?
    // if you allways copy the data by fetching the row than it doesn't make sence.
    fStatementParams.Add('cachedlob=false'); //default = False
    {$endif}
  end;

end;

procedure TSQLDBZEOSStatement.ExecutePrepared;
var p{$ifdef ZEOS72UP}, j{$ENDIF}: integer;
    Props: TSQLDBZEOSConnectionProperties;
    Log: ISynLog;
    blob: IZBlob;
    name: string;
{$ifdef ZEOS72UP}
  NullArray: array of TBooleanDynArray;
  Int64Array: array of TInt64DynArray;
  DoubleArray: array of TDoubleDynArray;
  CurDynArray: array of TCurrencyDynArray;
  DateDynArray: array of TDateTimeDynArray;
  UTF8DynArray: array of TRawUTF8DynArray;
  BlobDynArray: array of TInterfaceDynArray;
{$ENDIF}
begin
  Log := SynDBLog.Enter(Self);
  with Log.Instance do
    if sllSQL in Family.Level then
      LogLines(sllSQL,pointer(SQLWithInlinedParams),self,'--');
  if fStatement=nil then
    raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared() invalid call',[fStatementClassName]);
  if fResultSet<>nil then
    raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared() miss a Reset',[fStatementClassName]);
  // 1. bind parameters in fParams[] to fQuery.Params
{$ifdef ZEOS72UP}
  if (fParamsArrayCount > 0) and fStatement.GetConnection.GetMetadata.GetDatabaseInfo.SupportsArrayBindings then
  begin
    SetLength(NullArray, fParamCount);
    SetLength(Int64Array, fParamCount);
    SetLength(DoubleArray, fParamCount);
    SetLength(CurDynArray, fParamCount);
    SetLength(Int64Array, fParamCount);
    SetLength(DateDynArray, fParamCount);
    SetLength(UTF8DynArray, fParamCount);
    SetLength(BlobDynArray, fParamCount);
    for p := 0 to fParamCount-1 do
    begin
      if fParams[p].VInt64<>fParamsArrayCount then
        raise Exception.CreateFmt('%s.ExecutePrepared: %d parameter expected array count %d, got %d',
          [fStatementClassName,p,fParamsArrayCount,fParams[p].VInt64]);
      SetLength(NullArray[p], fParamsArrayCount);
      with fParams[p] do
      case VType of
      ftNull:
        begin
          for J := 0 to fParamsArrayCount -1 do
          begin
            NullArray[p][j] := True;
            UTF8DynArray[p][j] := '';
          end;
          fStatement.SetDataArray(p+1,UTF8DynArray[p],stString,vtUTF8String);
        end;
      ftInt64:
        begin
          SetLength(Int64Array[p], fParamsArrayCount);
          for J := 0 to fParamsArrayCount -1 do
          begin
            NullArray[p][j] := Varray[j] = 'null';
            if not NullArray[p][j] then
              SetInt64(POinter(Varray[j]), Int64Array[p][j]);
          end;
          fStatement.SetDataArray(p+1,Int64Array[p], stLong);
        end;
      ftDouble:
        begin
          SetLength(DoubleArray[p], fParamsArrayCount);
          for J := 0 to fParamsArrayCount -1 do
          begin
            NullArray[p][j] := Varray[j] = 'null';
            if not NullArray[p][j] then
              DoubleArray[p][j] := ZFastCode.RawToFloatDef(POinter(Varray[j]), '.', 0);
          end;
          fStatement.SetDataArray(p+1,DoubleArray[p],stDouble);
        end;
      ftCurrency:
        begin
          SetLength(CurDynArray[p], fParamsArrayCount);
          for J := 0 to fParamsArrayCount -1 do
          begin
            NullArray[p][j] := Varray[j] = 'null';
            if not NullArray[p][j] then
              CurDynArray[p][j] := StrToCurrency(POinter(Varray[j]));
          end;
          fStatement.SetDataArray(p+1,CurDynArray[p],stCurrency);
        end;
      ftDate:
        begin
          SetLength(DateDynArray[p], fParamsArrayCount);
          for J := 0 to fParamsArrayCount -1 do
          begin
            NullArray[p][j] := Varray[j] = 'null';
            if not NullArray[p][j] then
              DateDynArray[p][j] := Iso8601ToDateTime(Varray[j]);
          end;
          fStatement.SetDataArray(p+1,DateDynArray[p],stTimeStamp);
        end;
      ftUTF8:
        begin
          SetLength(UTF8DynArray[p], fParamsArrayCount);
          for J := 0 to fParamsArrayCount -1 do
          begin
            NullArray[p][j] := Varray[j] = 'null';
            if NullArray[p][j] then
              UTF8DynArray[p][j] := ''
            else
              UnQuoteSQLString(pointer(VArray[j]), UTF8DynArray[p][j]);
          end;
          fStatement.SetDataArray(p+1,UTF8DynArray[p],stString,vtUTF8String);
        end;
      ftBlob:
        begin
          SetLength(BlobDynArray[p], fParamsArrayCount);
          for J := 0 to fParamsArrayCount -1 do
          begin
            NullArray[p][j] := Varray[j] = 'null';
            if NullArray[p][j] then
              BlobDynArray[p][j] := nil
            else
              BlobDynArray[p][j] := TZAbstractBlob.CreateWithData(Pointer(VData),length(VData)
                {$ifndef ZEOS72UP},fStatement.GetConnection{$endif});
          end;
          fStatement.SetDataArray(p+1,BlobDynArray[p], stBinaryStream);
        end;
      else
        raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared: Invalid type on bound parameter #%d',
          [fStatementClassName,p]);
      end;
      fStatement.SetNullArray(p+1,stBoolean, NullArray[p]);
    end;
  end
  else
{$ENDIF}

  for p := 1 to fParamCount do
    with fParams[p-1] do
    case VType of
    ftNull:     fStatement.SetNull(p,stUnknown);
    ftInt64:    fStatement.SetLong(p,VInt64);
    ftDouble:   fStatement.SetDouble(p,PDouble(@VInt64)^);
    ftCurrency: {$ifdef ZEOS72UP}
                fStatement.SetCurrency(p,PCurrency(@VInt64)^);
                {$else}
                fStatement.SetBigDecimal(p,PCurrency(@VInt64)^);
                {$endif}
    ftDate:     fStatement.SetTimestamp(p,PDateTime(@VInt64)^);
    ftUTF8:     {$ifdef ZEOS72UP}
                fStatement.SetUTF8String(p,VData);
                {$else}
                  {$ifdef UNICODE}  // ZWideString = SynUnicode in fact
                  fStatement.SetString(p,UTF8ToSynUnicode(VData));
                  {$else}
                  fStatement.SetString(p,VData); // see controls_cp=CP_UTF8
                  {$endif}
                {$endif}
    ftBlob: begin
      blob := TZAbstractBlob.CreateWithData(Pointer(VData),length(VData)
        {$ifndef ZEOS72UP},fStatement.GetConnection{$endif});
      fStatement.SetBlob(p,stBinaryStream,blob);
    end;
    else
      raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared: Invalid type on bound parameter #%d',
        [fStatementClassName,p]);
    end;
  // 2. Execute query
  if fExpectResults then begin
    fCurrentRow := -1;
    fResultSet := fStatement.ExecuteQueryPrepared;
    if fResultSet=nil then
      raise ESQLDBZEOS.CreateFmt('%s.ExecutePrepared(%s) returned nil',
        [fStatementClassName,SQLWithInlinedParams]);
    fResultInfo := fResultSet.GetMetadata;
    Props := fConnection.Properties as TSQLDBZEOSConnectionProperties;
    fColumnCount := 0;
    fColumn.ReHash;
    for p := 1 to fResultInfo.GetColumnCount do begin
      name := fResultInfo.GetColumnLabel(p);
      if name='' then
        name := fResultInfo.GetColumnName(p);
      PSQLDBColumnProperty(fColumn.AddAndMakeUniqueName(
        // Delphi<2009: already UTF-8 encoded due to controls_cp=CP_UTF8
        {$ifdef UNICODE}StringToUTF8{$endif}(name)))^.ColumnType :=
          Props.TZSQLTypeToTSQLDBFieldType(fResultInfo.GetColumnType(p));
    end;
  end else
    fStatement.ExecutePrepared;
  // 3. handle out parameters -> TODO (fStatement is IZCallableStatement)
end;

It might be a good idea if you lay my suggestion over your current WC.

I don't know if my changes are best way because i think having a own OnBatchInsert proc would be best way to maintain.
A thing i don't like in my patch was the unit binding to ZFastCode.pas -> DoubleArray[p][j] := ZFastCode.RawToFloatDef(POinter(Varray[j]), '.', 0); -> maybe you've got faster conversions running but in short i didn't find them ):

Results:

{
	"Engine": "Oracle",
	"CreateTableTime": "620.55ms",
	"NumberOfElements": 5000,
	"InsertTime": "1.57s",
	"InsertRate": 3180,
	"InsertBatchTime": "49.16ms",
	"InsertBatchRate": 101698,
	"InsertTransactionTime": "991.56ms",
	"InsertTransactionRate": 5042,
	"InsertBatchTransactionTime": "49.63ms",
	"InsertBatchTransactionRate": 100741,
	"ReadOneByOneTime": "686.56ms",
	"ReadOneByOneRate": 7282,
	"ReadAllVirtualTime": "35.50ms",
	"ReadAllVirtualRate": 140845,
	"ReadAllDirectTime": "23.34ms",
	"ReadAllDirectRate": 214215,
	"ClientCloseTime": "26.67ms"
}
{
	"Engine": "ZEOS Oracle",
	"CreateTableTime": "54.61ms",
	"NumberOfElements": 5000,
	"InsertTime": "1.46s",
	"InsertRate": 3408,
	"InsertBatchTime": "77.26ms",
	"InsertBatchRate": 64713,
	"InsertTransactionTime": "878.46ms",
	"InsertTransactionRate": 5691,
	"InsertBatchTransactionTime": "75.43ms",
	"InsertBatchTransactionRate": 66282,
	"ReadOneByOneTime": "677.51ms",
	"ReadOneByOneRate": 7379,
	"ReadAllVirtualTime": "37.43ms",
	"ReadAllVirtualRate": 133557,
	"ReadAllDirectTime": "27.32ms",
	"ReadAllDirectRate": 182982,
	"ClientCloseTime": "30.23ms"
}

still not as good as yours, since i have to create new arrays, an process data twice
But looks much better inbetween. Have no other component at hand to compare them

ab · 2014-08-18 20:53:55

Wow!
SO nice!

You put so much effort to enhance our code, that I would include direct support of array binding in SynDBZeos.
Thanks for sharing!

Could I use your latest version of TSQLDBZEOSConnectionProperties.Create() as reference for our main version?
About array binding, I suspect you are making too much array allocations, but I think I would be able to refactor all this into something more "mORMotish".

Thanks again!

EgonHugeist · 2014-08-18 21:28:32

ab wrote:

Wow!
Could I use your latest version of TSQLDBZEOSConnectionProperties.Create() as reference for our main version?

Of course. Did compile with 7.1.3a:

{
	"Engine": "Oracle",
	"CreateTableTime": "52.82ms",
	"NumberOfElements": 5000,
	"InsertTime": "1.57s",
	"InsertRate": 3166,
	"InsertBatchTime": "45.52ms",
	"InsertBatchRate": 109839,
	"InsertTransactionTime": "944.02ms",
	"InsertTransactionRate": 5296,
	"InsertBatchTransactionTime": "46.67ms",
	"InsertBatchTransactionRate": 107114,
	"ReadOneByOneTime": "712.43ms",
	"ReadOneByOneRate": 7018,
	"ReadAllVirtualTime": "35.87ms",
	"ReadAllVirtualRate": 139380,
	"ReadAllDirectTime": "22.26ms",
	"ReadAllDirectRate": 224618,
	"ClientCloseTime": "27.29ms"
}
{
	"Engine": "ZEOS Oracle",
	"CreateTableTime": "49.39ms",
	"NumberOfElements": 5000,
	"InsertTime": "1.74s",
	"InsertRate": 2867,
	"InsertBatchTime": "436.94ms",
	"InsertBatchRate": 11443,
	"InsertTransactionTime": "858.89ms",
	"InsertTransactionRate": 5821,
	"InsertBatchTransactionTime": "413.38ms",
	"InsertBatchTransactionRate": 12095,
	"ReadOneByOneTime": "705.80ms",
	"ReadOneByOneRate": 7084,
	"ReadAllVirtualTime": "58.20ms",
	"ReadAllVirtualRate": 85897,
	"ReadAllDirectTime": "43.27ms",
	"ReadAllDirectRate": 115540,
	"ClientCloseTime": "24.65ms"
}

And with current 7.2-alphatest from SVN /testing-7.2
Where im not sure about are these commented lines now:

    {if Assigned(OnBatchInsert) then begin
      // ZDBC: MultipleValuesInsertFirebird is buggy, MultipleValuesInsert slower
      fBatchSendingAbilities := [];
      OnBatchInsert := nil;
    end;}

didn't compare the FB results yet. It seems it works too, while comment says "buggy"..

Accordingly my array binding:
Yes it could be more optimal. It's just a minimal solution to get it running and wait for a feedback. Take care you've localized the arrays somewhere. Zeos only references by pointers so no IntlockIncrement is called AFAIK.

Agree with "mORMotish" refactoring, thanks for your feedback.

Arnaud TSQLDBStatementWithParams supports some BindArray() overloads, are they really used somewhere? Don't you think we should make a full override for the Bind___() functions? AFAICS do you convert all arrays back to RawUTF8-Strings...

Last edited by EgonHugeist (2014-08-18 21:45:34)

ab · 2014-08-19 14:34:53

About MultipleValuesInsertFirebird: the EXECUTE BLOCK statement fails to execute on ZDBC...

About our BindArray() overloads, they are not used by the ORM, which use VArray[]: RawUTF8 storage, directly converted from the incoming JSON buffer.
Then conversions to expected binary format are done only once, in SynDBZeos.
IMHO this is not the biggest bottleneck.

I've integrated your proposal, a bit rewritten and refactored.
See http://synopse.info/fossil/info/0b0f595b12
But I was not able to test it on Oracle, since I do not have any Oracle server available here yet.
Your feedback is welcome!

We need the 7.2 unstable branch. The trunk does not have yet IZDatabaseInfo.SupportsArrayBindings property, AFAIK.

EgonHugeist · 2014-08-20 09:31:59

ab wrote:

About MultipleValuesInsertFirebird: the EXECUTE BLOCK statement fails to execute on ZDBC...

nasty ): I'll check it later too. Did start now a own EXECUTE BLOCK assembling idea. Here the strings your allways using do bring some advantages. Also could it be usefull for PostgresSQL which is always string based, for MySQL in emulation mode too. So the "bottleneck" could be usefull too if we use it right.

Just an unimplemented idea:

procedure TZAbstractPreparedStatement.SetDataArray(ParameterIndex: Integer;
  const Value; const SQLType: TZSQLType; const VariantType: TZVariantType = vtNull);

We could set the binary SQLType like stInteger as SQLType, the VariantType could differ here. So if we set the Variant type to vtUTF8String i would know on Zeos side i don't need quotes for the strings and add your already processed strings to the EXECUTE BLOCK stmt or point to the strings with postgressql. Just an idea. All we need would be an aditional indicator to know if binary arrays are prefered or not...

ab wrote:

I've integrated your proposal, a bit rewritten and refactored.
See http://synopse.info/fossil/info/0b0f595b12
But I was not able to test it on Oracle, since I do not have any Oracle server available here yet.
Your feedback is welcome!

Compiles out of the box and performce pretty nice:

{
	"Engine": "Oracle",
	"CreateTableTime": "48.12ms",
	"NumberOfElements": 5000,
	"InsertTime": "1.49s",
	"InsertRate": 3342,
	"InsertBatchTime": "45.02ms",
	"InsertBatchRate": 111044,
	"InsertTransactionTime": "910.61ms",
	"InsertTransactionRate": 5490,
	"InsertBatchTransactionTime": "45.33ms",
	"InsertBatchTransactionRate": 110297,
	"ReadOneByOneTime": "679.41ms",
	"ReadOneByOneRate": 7359,
	"ReadAllVirtualTime": "33.88ms",
	"ReadAllVirtualRate": 147544,
	"ReadAllDirectTime": "21.65ms",
	"ReadAllDirectRate": 230936,
	"ClientCloseTime": "28.37ms"
}
{
	"Engine": "ZEOS Oracle",
	"CreateTableTime": "17.40ms",
	"NumberOfElements": 5000,
	"InsertTime": "1.40s",
	"InsertRate": 3557,
	"InsertBatchTime": "55.62ms",
	"InsertBatchRate": 89895,
	"InsertTransactionTime": "827.88ms",
	"InsertTransactionRate": 6039,
	"InsertBatchTransactionTime": "59.28ms",
	"InsertBatchTransactionRate": 84335,
	"ReadOneByOneTime": "687.13ms",
	"ReadOneByOneRate": 7276,
	"ReadAllVirtualTime": "35.29ms",
	"ReadAllVirtualRate": 141679,
	"ReadAllDirectTime": "26.08ms",
	"ReadAllDirectRate": 191688,
	"ClientCloseTime": "24.34ms"
}

ab wrote:

We need the 7.2 unstable branch. The trunk does not have yet IZDatabaseInfo.SupportsArrayBindings property, AFAIK.

That's what our users want too. The current team did agree going beta(even if i'm really not ready). The latest paches which are required for this array bindings will be merged to trunk in a periode of a week or two.

I'll notify you if i have some more plains ready for the ArrayBindings.

Thank you again, it's a pleasure to work with you.

Michael

ab · 2014-08-20 09:47:34

Nice!
I wonder why my implementation is more than 20% faster than your initial version.
Sounds just like a small refactoring from your initial patch.

It is sure in all cases that ZDBC Oracle support is much better, in terms of performance and stability, than the 6.x or 7.0 versions!
Congrats!

I'm not sure that a library like ZDBC should have to create on the fly SQL, like EXECUTE BLOCK or multi-INSERT...
IMHO it should be best handled at caller level.

If you had such features, perhaps a dedicated set of interfaces/classes would make sense.
I think it should not be part of the main Database/Connection/Statement workflow, but something like a new Database/CodeGeneration workflow.

In SynDB, we implemented it as part of ORM-oriented methods, implementing TOnBatchInsert signature: TSQLDBConnectionProperties.MultipleValuesInsert() and TSQLDBConnectionProperties.MultipleValuesInsertFirebird().
It was not too much difficult on our side, since we support only a much smaller value types set than ZDBC, and the BindArray() data is already supplied as text.
In all cases, if you implemented SQL generation within ZDBC, performance should be lower than our TOnBatchInsert pattern, which avoid most data conversion, since all is already supplied as text, directly from the incoming JSON content.

All this is very exciting!
Open Source rocks, and Zeos/mORMot seems to be a perfect fit now - outperforming other closed source Delphi libraries I know.
Array DML was the latest missing feature of Zeos. Hope more providers will be implemented.

danielkuettner · 2014-08-20 14:04:44

I've tested Zeos 7.2 beta with nightly mORMot with following problems connecting to Firebird-RemoteServer:

Props:= TSQLDBZEOSConnectionProperties.Create(
TSQLDBZEOSConnectionProperties.URI(
dFirebird,
''), '192.168.1.249:3050:G:\firebird\prcash_test.om', 'SYSDBA', 'masterkey');

Here servername will not extract and set right in
SynDBZeos.pas

This would be working:

constructor TSQLDBZEOSConnectionProperties.Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8);
const
...
var
  s1, s2: RawUTF8;
begin
  fServerName :=  aServerName;
  ...
  fURL := TZURL.Create(UTF8ToString(fServerName));

  Split(aDatabaseName, ':', s1, s2);
  if Length(s1) > 1 then
    fURL.HostName:= s1;

  Split(s2, ':', s1, s2);
  if Length(s1) > 1 then begin
    fURL.Port:= StrToInt(s1);
    fURL.Database:= s2;
  end;

  if fURL.Database='' then
    fURL.Database := UTF8ToString(aDatabaseName);
  ...

danielkuettner · 2014-08-20 14:07:10

Second problem are sql-select with DateTime values e.g.:

http://localhost:8180/root/?sql=select+RowID,*+from+kontoauszug+where+BTAG%3E%2701-01-2014%27

Here comes a conversion error: EZIBConvertError with 'Convertion error'.

The same with ODBC works without problems.

ab · 2014-08-20 14:25:45

danielkuettner wrote:

Here servername will not extract and set right

AFAIR you should set the server as parameter to URI(), not as you did.

Typically (from documentation):

PropsFirebirdEmbedded := TSQLDBZEOSConnectionProperties.Create(
TSQLDBZEOSConnectionProperties.URI(dFirebird,'','Firebird\fbembed.dll')
'databasefilename','',');
PropsFirebirdRemote := TSQLDBZEOSConnectionProperties.Create(
TSQLDBZEOSConnectionProperties.URI(dFirebird,'192.168.1.10:3055',
'c:\Firebird_2_5\bin\fbclient.dll',false),
'3camadas', 'sysdba', 'masterkey');

danielkuettner wrote:

Second problem are sql-select with DateTime values.

How is your ORM request written?
Are you using a ? parameter and DateTimeToSQL(), as explained in the "5.3.2. Query parameters" paragraph of the SAD pdf?

Typically (from documentation):

Standard simple kind of parameters (RawUTF8, integer, double..) can be bound directly - as in the sample code above for Name or Sex properties. The first parameter will be bound as 'A%' RawUTF8 TEXT, and the second as the 1 INTEGER value.
Any TDateTime bound parameter shall better be specified using DateToSQL(), DateTimeToSQL() or TimeLogToSQL() functions, as such:
aRec.CreateAndFillPrepare(Client,'Datum=?',[DateToSQL(EncodeDate(2012,5,4))]);
aRec.CreateAndFillPrepare(Client,'Datum>=?',[DateToSQL(2012,5,4)]);
aRec.CreateAndFillPrepare(Client,'Datum<=?',[DateTimeToSQL(Now)]);
aRec.CreateAndFillPrepare(Client,'Datum<=?',[TimeLogToSQL(Client.ServerTimeStamp)]);

danielkuettner · 2014-08-20 15:39:05

AB wrote:

AFAIR you should set the server as parameter to URI(), not as you did.

This was my first attempt, but setting Servername will ignored completely.

AB wrote:

How is your ORM request written?
Are you using a ? parameter and DateTimeToSQL(), as explained in the "5.3.2. Query parameters" paragraph of the SAD pdf?

I'm doing http-request only with ajax. Can I use this function in URI?

Last edited by danielkuettner (2014-08-20 15:39:17)

ab · 2014-08-20 16:10:15

For DateTimeToSQL(), just add the \uFFF1 character before the ISO-8601 date/time - it should be UTF-8 encoded as %EF%BF%B1 at the URI level.

danielkuettner · 2014-08-20 17:04:17

I try with \uFFF1 but no chance, sorry.

If I do it with aRec.CreateAndFillPrepare(Client,'Datum=?',[DateToSQL(EncodeDate(2012,5,4))])
then it works and in log file you see:
TSQLDBZEOSStatement(00000000025AAC60) select KONTOAUSZUG_ID as ID,KONTOAUSZUG_BTAG as BTAG,KONTOAUSZUG_INFO as Info from Kontoauszug where KONTOAUSZUG_BTAG>='2014-05-04'

If I do it with URI then it don't work and in log file you see:
TSQLDBZEOSStatement(00000000025AAEC0) select KONTOAUSZUG_BTAG,KONTOAUSZUG_INFO,KONTOAUSZUG_ID from Kontoauszug where KONTOAUSZUG_BTAG>'2014-05-04'
EZIBConvertError ("Convertion error") at 0000000000879FCE

ab · 2014-08-20 18:14:01

Could you try with Win32 platform instead of Win64?

What is the stack trace of EZIBConvertError?
Please enable source code line numbers in the log file by setting "Map File / Detailed" in the project options.

Could you use the debugger on server side, and find out why it complains about the date format?
Is the parameter bound as date/time?

danielkuettner · 2014-08-20 18:44:42

Now with Win32:

TSQLDBZEOSStatement(0000000003353250).000000000055A5FC SynDBZEOS.TSQLDBZEOSStatement.ExecutePrepared (826)
TSQLDBZEOSStatement(0000000003353250) select KONTOAUSZUG_BTAG,KONTOAUSZUG_INFO,KONTOAUSZUG_ID from Kontoauszug where KONTOAUSZUG_BTAG>='2014-05-04'
EZIBConvertError ("Convertion error") at 00000000004AE52B ZDbcInterbase6Utils.TZParamsSQLDA.UpdatePAnsiChar (2296)

How do I get a stack trace?

Yes of course I can debug. I thought there is just a mistake in my URI.

ab · 2014-08-20 18:51:01

The URI sounds just fine.

An address like 0000000003353250 means that you are still in Win64.

In particular, step in TSQLDBConnectionProperties.PrepareInlined() and check that ExtractInlineParameters() identifies the parameter as sptDateTime and call Query.BindDateTime() as expected.

EgonHugeist · 2014-08-20 20:58:38

Sorry guys, wasn't @computer yet.

Just commited a patch R3269 \testing-7.2 (SVN). Indeed there was a conversion from string to Date/Time/DateTime-Fields missing -> i broke with "on the fly" memory reallocation for the XSQLDA record of FB/IB.

Daniel don't you think a new thread would be right in your case? Thought i do inform synopse about Zeos realted !upgrades!
Could you update Zeos and try if your issue is resolved?

ab wrote:

I'm not sure that a library like ZDBC should have to create on the fly SQL, like EXECUTE BLOCK or multi-INSERT...
IMHO it should be best handled at caller level.

My first intention was to agree. BUT there is always the spezial CharacterSet 'NONE' case, Arnaud.
What i plan to do is:
call prepare and isc_decribe_bind for the "normal" insert stmt. This returns the SQLDA informations about the column with the expected codepages/column for the string fields. If i do not go this route i just see new tickets in a short time..

Out of time now, the lady is waiting Cheers

ab · 2014-08-20 21:30:26

Thanks for the feedback!

And never make your lady wait...
:-)

danielkuettner · 2014-08-21 07:03:27

So, the night is over. After 14 hours sleep - mORMOt has stressed me so...

Now with Zeos update it go's better, but only a little bit.

With my 32bit (sorry) version the log says now:

TSQLDBZEOSStatement(0090D3C0) select KONTOAUSZUG_BTAG,KONTOAUSZUG_INFO,KONTOAUSZUG_ID from Kontoauszug where KONTOAUSZUG_BTAG>='2014-05-04'

TSQLDatabase(025F9D08) [{"rowid":578363,"BTAG":"2007-01-02","Info":"VOLKSBANK REGENSBURG EG/GEBUEHR FUER BAREINZAHLUNGEPAUSCHAL/1090062906/75090000/DAUERAUFTRAG"},{"rowid":578424,"BTAG":"2007-01-02","Info":"HEINRICH ZELLMEIER/MIETE/100811246/74369088/LASTSCHRIFT"},{"rowid":578425,"BTAG":"2007-01-02","Info":"HEINRICH ZELLMEIER/MIETZINS PFAFFENBERG/100811246/74369088/LASTSCHRIFT"},{"rowid":578426,"BTAG":"2007-01-02","Info":"////EINZAHLUNG"},{"rowid":578427,"BTAG":"2007-01-02","Info":"////EINZAHLUNG"},{"rowid":578428,"BTAG":"2007-01

TSQLPrcashRestServerDB(0274BDB0) GET -> 200

As you can see, my Date condition will be ignored. He gives me all rows.

danielkuettner · 2014-08-21 07:42:33

EugenHougeist wrote:

Daniel don't you think a new thread would be right in your case? Thought i do inform synopse about Zeos realted !upgrades!

Ok, I would do it, but how should I name it? "URI-related mMORmot clients"?
The problem exists not with ODBC, but with Zeos.

I've an other request. I want do

http://localhost:port/root/?sql=select+count(*)+from+table

Actually that isn't possible.

mORMot Open Source

#1 2013-11-29 22:36:07

Zoes7.2 upgrade

#2 2013-11-30 13:36:46

Re: Zoes7.2 upgrade

#3 2013-11-30 20:13:54

Re: Zoes7.2 upgrade

#4 2013-11-30 21:00:11

Re: Zoes7.2 upgrade

#5 2013-11-30 21:32:20

Re: Zoes7.2 upgrade

#6 2013-12-01 12:47:45

Re: Zoes7.2 upgrade

#7 2013-12-01 14:06:52

Re: Zoes7.2 upgrade

#8 2013-12-02 13:38:35

Re: Zoes7.2 upgrade

#9 2013-12-03 14:34:03

Re: Zoes7.2 upgrade

#10 2013-12-03 16:09:25

Re: Zoes7.2 upgrade

#11 2013-12-03 17:46:50

Re: Zoes7.2 upgrade

#12 2013-12-03 18:34:34

Re: Zoes7.2 upgrade

#13 2013-12-03 20:37:10

Re: Zoes7.2 upgrade

#14 2013-12-03 21:39:09

Re: Zoes7.2 upgrade

#15 2013-12-03 21:40:21

Re: Zoes7.2 upgrade

#16 2013-12-03 21:43:15

Re: Zoes7.2 upgrade

#17 2013-12-03 22:06:56

Re: Zoes7.2 upgrade

#18 2013-12-04 11:07:54

Re: Zoes7.2 upgrade

#19 2013-12-04 19:53:23

Re: Zoes7.2 upgrade

#20 2013-12-04 21:50:57

Re: Zoes7.2 upgrade

#21 2013-12-06 07:45:09

Re: Zoes7.2 upgrade

#22 2013-12-06 09:35:42

Re: Zoes7.2 upgrade

#23 2013-12-07 23:02:25

Re: Zoes7.2 upgrade

#24 2014-08-09 22:02:29

Re: Zoes7.2 upgrade

#25 2014-08-10 07:23:00

Re: Zoes7.2 upgrade

#26 2014-08-10 08:26:20

Re: Zoes7.2 upgrade

#27 2014-08-10 10:02:04

Re: Zoes7.2 upgrade

#28 2014-08-10 10:03:44

Re: Zoes7.2 upgrade

#29 2014-08-10 11:17:15

Re: Zoes7.2 upgrade

#30 2014-08-10 12:02:09

Re: Zoes7.2 upgrade

#31 2014-08-18 12:56:23

Re: Zoes7.2 upgrade

#32 2014-08-18 19:38:33

Re: Zoes7.2 upgrade

#33 2014-08-18 20:53:55

Re: Zoes7.2 upgrade

#34 2014-08-18 21:28:32

Re: Zoes7.2 upgrade

#35 2014-08-19 14:34:53

Re: Zoes7.2 upgrade

#36 2014-08-20 09:31:59

Re: Zoes7.2 upgrade

#37 2014-08-20 09:47:34

Re: Zoes7.2 upgrade

#38 2014-08-20 14:04:44

Re: Zoes7.2 upgrade

#39 2014-08-20 14:07:10

Re: Zoes7.2 upgrade

#40 2014-08-20 14:25:45