mORMot Open Source

Hi AB today i update mORMot to the actual Version and testet it
We use SynDBUniDAC.
But some of the changes from matkov throw unexpected SQL Errors with MySQL and MSSQL
I had to revert SynDBUniDac to the "old" Version.

MySQL Error:

with Query:

delete from tabellenbeziehungen where (beziehungsart = :("MZ_MDELizenzen"):) and (referenztabelle = :("monteure"):) and (referenzfeld = :("monteurid"):) and (bezug = :("DFCloud.Lizenzen"):) and (bezugsfeld = :("Lizenzcode"):) and (referenzwert = :(53):)

MS-SQL Error:

with Query:

SELECT stammdatenid f0, parentID f1, kategorie f2, auswahl f3, anzeigen f4, feld01 f5, hostwert f6, unplausibel_meldungstext f7, extrem_unplausibel_meldungstext f8 FROM stammdaten where (kategorie = :("Vorgangsgrund"):) order by stammdatenid
(and other select queries)

Removing the Prepare in line 768:

function TSQLDBUniDACStatement.DatasetPrepare(const aSQL: string): boolean;
begin
  (fQuery as TUniQuery).SQL.Text := aSQL;
//  TUniQuery(fQuery).Prepare;
  fQueryParams := TUniQuery(fQuery).Params;
  result := fQueryParams<>nil;
end;

solves the Problem also, but as i don't know what matkov used to do with the patch, i reverted complete to the former SynDBUniDAC.pas - i think i don't need the patch

Hi AB i found a litte BUG in NewStatementPrepared:

see the line with // itSDS

function TSQLDBConnection.NewStatementPrepared(const aSQL: RawUTF8;
  ExpectResults, RaiseExceptionOnError, AllowReconnect: Boolean): ISQLDBStatement;
var Stmt: TSQLDBStatement;
    ToCache: boolean;
    ndx,altern: integer;
    cachedSQL: RawUTF8;

  procedure TryPrepare(doraise: boolean);
  var Stmt: TSQLDBStatement;
  begin
    Stmt := nil;
    try
      InternalProcess(speActive);
      try
        Stmt := NewStatement;
        Stmt.Prepare(aSQL,ExpectResults);
        if ToCache then begin
          if fCache=nil then
            fCache := TRawUTF8List.Create([fObjectsOwned,fNoDuplicate,fCaseSensitive]);
          if fCache.AddObject(aSQL,Stmt)>=0 then			// itSDS
            Stmt._AddRef else // will be owned by fCache.Objects[]
            SynDBLog.Add.Log(sllWarning,'NewStatementPrepared: unexpected '+
              'cache duplicate for %',[Stmt.SQLWithInlinedParams],self);
        end;
        result := Stmt;
      finally
        InternalProcess(speNonActive);
      end;
    except
      on E: Exception do begin
        with SynDBLog.Add do
          if [sllSQL,sllDB,sllException,sllError]*Family.Level<>[] then
            LogLines(sllSQL,pointer(Stmt.SQLWithInlinedParams),self,'--');
        Stmt.Free;
        result := nil;
        StringToUTF8(E.Message,fErrorMessage);
        fErrorException := PPointer(E)^;
        if doraise then
          raise;
      end;
    end;
  end;

the former used cachedSQL - Value is sometimes empty

Hi i use UniDAC 8.1.2 for SQL Server 11 with mORMot's SynDBUniDAC.
Here i made some Speedtests and compared the divers SQL Server.Provider Options.
The Queries itself are nearly with the same Speed. But in the first Connect Part of any ServerRequest the prDirect Option uses 120ms on my System to Connect. the prAuto only 10ms that is 12times slower.

We use mORMot as Rest/Soa Service, for every Rest Request the Connection is newly opened.
Some Heavily transfers use 2seconds with prAuto and 10s with prDirect.
The Reason ist the 120ms Connection Time.

Does anybody have a idea why direct connect is that slower ?

Hi Arnaud,

i try to select some SQLRecord from External MS-SQL Database.
we wan't to select all record where the field (Messzeitpunkt : TDateTime) = date

i tried a lot of valid ms-sql statements, but every time the mORMot Parser makes errors:

Version1:
whereStatement = convert(date, GETDATE()) = convert(date, Messzeitpunkt)

20190801 22095955  2 warn    mORMotDB.TSQLRestStorageExternal(02c45990) TSQLRestStorageExternal.AdaptSQLForEngineList: statement too complex -> would use SQLite3 virtual engine [SELECT ID,CreationDate,LastEditDate,AuftragStartEventID,Gruppierung,Ursprung,IPAdresse,Port,Messzeitpunkt,Barcode,Drehmoment,DrehmomentErgebnis,Drehwinkel,DrehwinkelErgebnis,GesamtErgebnis,Daten,SpindelTag,ControllerName,TighteningID,TorqueUnits,BatchCount,BatchSize,BatchStatus,CountOfScrewSteps,Screwprogram,Torquehighlimit,Torquelowlimit,Anglehighlimit,Anglelowlimit,ResultUniqueID,EORTimeStamp FROM EOR_QSYSOpenProtocolRecord WHERE convert(date, GETDATE()) = convert(date, Messzeitpunkt)]

Verstion 2:
whereStatement = CONVERT(nvarchar(max), messzeitpunkt, 23)=?, ['2019-08-01']
Error:
mORMotDB.TSQLRestStorageExternal(02ab5990) TSQLRestStorageExternal.AdaptSQLForEngineList: unsupported function CONVERT() for [SELECT ID,CreationDate,LastEditDate,AuftragStartEventID,Gruppierung,Ursprung,IPAdresse,Port,Messzeitpunkt,Barcode,Drehmoment,DrehmomentErgebnis,Drehwinkel,DrehwinkelErgebnis,GesamtErgebnis,Daten,SpindelTag,ControllerName,TighteningID,TorqueUnits,BatchCount,BatchSize,BatchStatus,CountOfScrewSteps,Screwprogram,Torquehighlimit,Torquelowlimit,Anglehighlimit,Anglelowlimit,ResultUniqueID,EORTimeStamp FROM EOR_QSYSOpenProtocolRecord WHERE CONVERT(nvarchar(max), messzeitpunkt, 23)=:('2019-08-01'):]

do you have an idea how to get the desired records ?

Is it a bug or do i use it wrong ?

Some of my installations use Microsoft SQL Server as Backend. But cause of some mysterious Exception with TOleDBMSSQL2012ConnectionProperties i tried the Other Implementations for MS-SQL
First i have to say i get only 2 to work first the TOleDBMSSQL2012ConnectionProperties an second the TSQLDBUniDACConnectionProperties
In the UniDAC i don't get the Exception i get with the other (see here: https://synopse.info/forum/viewtopic.php?id=4971)

as described i tried to use The ZEOS Implementation but don'get it to work. The main reason is: i can't find working examples.
I wan't to replace

TOleDBMSSQL2012ConnectionProperties.Create(ADBSettings.Host, ADBSettings.Database, ADBSettings.Username, ADBSettings.Password);

with something like:

TSQLDBZEOSConnectionProperties.Create(TSQLDBZEOSConnectionProperties.URI(dMSSQL, ADBSettings.Host + ':' + ADBSettings.Port), ADBSettings.Database, ADBSettings.Username, ADBSettings.Password);

But it raises an Exception ntwdblib.dll not found. In the Documentation a 4 Interfaces which i can use OleDB, ADO, ODBC i tried different String instead of dMSSQL OleDB[mssql], ADO[mssql] but the error is driver not found.

can anybody pls make some working examples for the ZEOS uri to connect as stated above ?
What are the prerequisites
And last but not least what will be the best (fastest) way to connect to MS-SQL

Thank you in advance

Hi AB i got an Exception in SynOLEDB - TOleDBStatement.ExecutePrepared
mr was nil after Query

Here is my Patch (Line 1997 ff)

        if not OleDBConnection.OleDBProperties.fSupportsOnlyIRowset then begin
          // use IMultipleResults for 'insert into table1 values (...); select ... from table2 where ...'
          res := fCommand.Execute(nil,IID_IMultipleResults,fDBParams,@fUpdateCount,@mr);
          if assigned(mr) and (res=E_NOINTERFACE) then		// <-- PATCH itSDS
            OleDBConnection.OleDBProperties.fSupportsOnlyIRowset := true else begin
            repeat
              res := mr.GetResult(nil,0,IID_IRowset,@fUpdateCount,@RowSet);
            until Assigned(RowSet) or (res <> S_OK);
          end;
        end;

Hi ab,

everything runs fine, but since Yesterday we get a database error i can not identify / don't know what to do with

The Errormessage is this:

SynDBZeos.TSQLDBZEOSStatement(061AE280) update BinaerdatenRecord set CreationDate=0,LastEditDate=135469259944,GUID='{90944806-B92D-C7C9-1CFE-99CF08C3C3AA}',Binaerdaten=*BLOB*,Datenformat='image/jpeg',GroesseInByte=90112 where ID=80358
EZSQLException ("SQL Error: Can't send long data for non-string/non-binary data types (parameter: 3)")

SynDBZeos.TSQLDBZEOSStatement(062D0AB0) update BinaerdatenRecord set LastEditDate=135469259945,Binaerdaten=*BLOB*,GroesseInByte=122880 where ID=80359
EZSQLException ("SQL Error: Can't send long data for non-string/non-binary data types (parameter: 1)")

Our Environment ist libZEOS - 7.3 V5275
mORMot latest Version
Our Database is a MySQL 5.7.19

We have a simple TSQLRecord with 1 TSQLRawBlob called "Binaerdaten"

Does you or anybody have a clue for us ?

Hi AB there is the following compiler error generated from bcc of Tokyo (and other i suppose)

./amalgamation/sqlite3.c:50463:21: error: Wert in Ausdruck erwartet
#if SQLITE_HAS_CODEC     <----

in sqlite.c you define SQLITE_HAS_CODEC
with

#define SQLITE_HAS_CODEC

to make the code compile you have to provide a value to SQLITE_HAS_CODEC

correct code:

#define SQLITE_HAS_CODEC 0
or
#define SQLITE_HAS_CODEC 1

I non't know if it should be a 1 or 0

can you pls fix this

Hi Arnaud, we got a 0xc000005 in ExistsIniName, cause P was nil (Empty String assigned to PChar)

SynCommons - Line 29306ff

function ExistsIniName(P: PUTF8Char; UpperName: PAnsiChar): boolean;
var table: PNormTable;
begin
  result := false;
  table := @NormToUpperAnsi7;
  if (P <> nil) and (P^<>'[') then    // <<-- Add this pls
    repeat
      if P^=' ' then begin
        repeat inc(P) until P^<>' '; // trim left ' '
        if P^=#0 then
          break;

can you pls add this to repository - Many Thx

Hi AB in your Documentation is a "d" missing at the end of TSQLRecordTableDelete the Tables name is TSQLRecordTableDeleted

Hi AB
using my MVC Server i entered for testing reason the URL of a TMVCAction in my Browser and got Exception 0x0000000

Please change this function

function TInterfaceFactory.FindMethodIndex(const aMethodName: RawUTF8): integer;
begin
  if (self=nil) or (aMethodName='') then
    result := -1 else
  if fMethodsCount<10 then begin
    for result := 0 to fMethodsCount-1 do
      if IdemPropNameU(fMethods[result].URI,aMethodName) then
        exit;
    result := -1;
  end else
    result := fMethod.FindHashed(aMethodName);
  if (result<0) and (Length(aMethodName) > 0) and (aMethodName[1]<>'_') then /// <<<<---- added Length Control. aMethodName was = '' in my case and [1] caused the Exception !
    result := FindMethodIndex('_'+aMethodName);
end;

Thanks