mORMot Open Source

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

Concerning to topic https://synopse.info/forum/viewtopic.php?id=4061
i made some modifications to my code and made the ConnectionProperties a Member of my ViewModel Class. so far so good (german so weit so gut )

Now i made a few tests with TSQLDBZEOSConnectionProperties and have encountered the following problems. (Our Main Database is a MySQL Server !)

Scenario 1

Stop MySQL
Start Service
TSQLDBZEOSConnectionProperties.Create raises Exception - Could not find server. As WorkAround i programmed a function like this

    property ConnectionProperties : TSQLDBConnectionProperties read GetConnectionProperties;

function TApplication.GetConnectionProperties: TSQLDBConnectionProperties;

  function CreateConnectionProperties: TSQLDBConnectionProperties;
  begin
    try
        exit(TSQLDBZEOSConnectionProperties.Create(TSQLDBZEOSConnectionProperties.URI(dMySQL, FHost + ':' + FPort), FDatabase, FUsername, FPassword));
    except
    end;
    exit(nil);
  end;

begin
  if not Assigned(FConnectionProperties) then
    FConnectionProperties := CreateConnectionProperties;

  exit(FConnectionProperties);
end;

Now the Properties are created if Server is startet - else nil is returned.

Scenario 2

Start MySQL
Start Service
Use Service's ConnectionProperties

After a while stop MySQL
Using the *ConnectionProperties.ExecuteInlined* returns "Lost Connection to MySQL Server during query" error as expected.
Now start MySQL again.

Now using *ConnectionProperties.ExecuteInlined* returns "MySQL Server has gone away"

Is there a Auto Reconnect Option ?

Hi AB Posting a TSQLRecord containing a BLOB (RawUtf8 without Index) we get the Error ORA 24816
we use TSQLDBZEOSConnectionProperties with dOracle

"ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column"

Using the Rest Interface.

The Query mORMot generates is a standard SQL Query

insert into xxx (blobfield) values ('<500 Char String>')

If i defined

TSQLRecord
..
blobfield : RawUtf8;
..

if i modify with

blobfield : RawUtf8 index 500;

Everything works fine.
ATM this "workaround" is ok for me my field is smaller than 500 char.

Is there a better solution ?

Hi AB the MVC Views can render .static data and views.

I have the Problem that we have to display thousands of dynamic Pictures in our View.
The Images are stored in the database.
Now i like to dynamically display images on my Client Site.

My Idea is to enhance the MVC View Renderer (InternalRunOnRestServer) with a kind of dynamic contents rendering.
For Example if URL start with .dynamic (like your .static) followed by some Text .dynamic/Pic1 then this Pic1 should be passed to a MVC function which can Return my Image Data.

The Other way would be to store the requested Images in .static/tmp subfolder but than i would have to care about garbage collection.

As other Option i have to check if the Client has the right to view the picture before sending it.

What do you think ?

Hi i get error Message calling mvc view with latest version.

The error seem to be in mormot.pas:

function TSQLRestServerURIContext.GetInputAsTDocVariant: variant;

i replaced the function with former one and the mvc works as expected.

the Error is that

fInput in this call has the Value 'null' and not ''
mormotmvc - line 1484

              if not exec.ExecuteJson([fApplication.fFactoryEntry],pointer(fInput),WR,true) then

the null is calculated with actual version of GetInputasTDocVariant.

here the older working funktion:

function TSQLRestServerURIContext.GetInputAsTDocVariant: variant;
var ndx: integer;
    v: variant;
    MultiPart: TMultiPartDynArray;
begin
  VarClear(result);
  FillInput;
  if fInput<>nil then begin
    with TDocVariantData(result) do begin
      InitFast;
      for ndx := 0 to (length(fInput) shr 1)-1 do begin
        GetVariantFromJSON(pointer(fInput[ndx*2+1]),false,v,@JSON_OPTIONS[true]);
        AddValue(fInput[ndx*2],v);
      end;
    end;
  end else
  if InputAsMultiPart(MultiPart) then
    with TDocVariantData(result) do begin
      InitFast;
      for ndx := 0 to high(MultiPart) do
        with MultiPart[ndx] do
          if ContentType=TEXT_CONTENT_TYPE then begin
            // append as regular "Name":"TextValue" field
            RawUTF8ToVariant(Content,v);
            AddValue(Name,v);
          end else
            // append binary file as an object, with Base64-encoded data
            AddValue(Name,_ObjFast(['data',BinToBase64(Content),
              'filename',FileName,'contenttype',ContentType]));
    end;
end;