Posts by firstfriday

firstfriday · mORMot 2

Accessing a SQLite Database with multiple threads without ORM. Direct Data Access using mORmot2.

program SqliteMormotThreadTest_ThreadSafe;

{$APPTYPE CONSOLE}


uses
  System.SysUtils,
  System.Classes,
  System.SyncObjs,
  System.Generics.Collections,
  mormot.db.sql,
  mormot.db.raw.sqlite3,
  mormot.db.sql.sqlite3,
  mormot.db.raw.sqlite3.static,
  mormot.core.base,
  mormot.core.os,
  mormot.core.text;

type
  TWorker = class(TThread)
  private
    FProps:TSQLDBSQLite3ConnectionProperties;
    FIterations: Integer;
    FOkCount: Integer;
    FErrCount: Integer;
    FLastError: string;
    function PtrToStr(P: Pointer): string;
    procedure Log(const Msg: string);
  protected
    procedure Execute; override;
  public
    constructor Create(AProps: TSQLDBSQLite3ConnectionProperties; AIterations: Integer);
  end;

var
  LogCS: TCriticalSection;

{ TWorker }

constructor TWorker.Create(AProps: TSqlDBSQLite3ConnectionProperties; AIterations: Integer);
begin
  inherited Create(True);
  FreeOnTerminate := False;
  FProps    := AProps;
  FIterations := AIterations;
end;

function TWorker.PtrToStr(P: Pointer): string;
begin
  Result := IntToHex(NativeUInt(P), SizeOf(Pointer) * 2);
end;

procedure TWorker.Log(const Msg: string);
begin
  LogCS.Acquire;
  try
    Writeln(FormatDateTime('hh:nn:ss.zzz', Now) + ' ' + Msg);
  finally
    LogCS.Release;
  end;
end;

procedure TWorker.Execute;
var
  Conn: TSqlDBSQLite3Connection;
  Stmt: ISqlDBStatement;
  I, V: Integer;
begin

  Conn := FProps.NewConnection as TSqlDBSQLite3Connection;
  try
    Conn.Connect;
    Log(Format('START tid=%d conn=%s', [GetCurrentThreadId, PtrToStr(Pointer(Conn))]));

    Conn.DB.BusyTimeout := 5000;
    try
      for I := 1 to FIterations do
      begin
        Stmt := nil; // explizit nil setzen vor jedem Aufruf
        try
          Stmt := Conn.NewStatementPrepared('SELECT 1', True);
          Stmt.ExecutePrepared;
          if not Stmt.Step then
            raise Exception.Create('No row');
          V := Stmt.ColumnInt(0);
          if V <> 1 then
            raise Exception.CreateFmt('Bad result: %d', [V]);
          Stmt := nil; // Lock freigeben vor nächster Iteration
          Inc(FOkCount);
        except
          on E: Exception do
          begin
            Stmt := nil;
            Inc(FErrCount);
            FLastError := E.ClassName + ': ' + E.Message;
            Log(Format('ERR tid=%d iter=%d %s', [GetCurrentThreadId, I, FLastError]));
          end;
        end;
      end;
    finally
      Conn.Disconnect;
    end;
  finally
    Conn.Free;
  end;
  Log(Format('SUMMARY tid=%d ok=%d err=%d last="%s"', [GetCurrentThreadId, FOkCount, FErrCount, FLastError]));
end;

//
// *** Main ***
//
//TSQLDBSQLite3ConnectionProperties   ← ONE in the application, stores only configurations parameter for the database, no access to database.
//        │
//        ├── ThreadSafeConnection    ← handles connection pooling  via threadvars
//        │
//        └── NewConnection           ← returns a fresh TSqlDBConnection instance
var
  Props: TSQLDBSQLite3ConnectionProperties;
  Conn: TSqlDBConnection;
  Workers: array of TWorker;
  I: Integer;
  ThreadCount: Integer;
  Iterations: Integer;

begin
  ReportMemoryLeaksOnShutdown := True;

  LogCS := TCriticalSection.Create; // only for log
  try
    ThreadCount := 10;
    Iterations := 1150000;

    Props := TSqlDBSQLite3ConnectionProperties.Create('test.db', '', '', '');

    var SetupConn: TSqlDBSQLite3Connection;
    SetupConn := Props.NewConnection as TSqlDBSQLite3Connection;
    try
      SetupConn.Connect;
      SetupConn.DB.WALMode       := True;       // journal_mode=WAL
      SetupConn.DB.BusyTimeout   := 5000;       // busy_timeout=5000ms
      SetupConn.DB.Synchronous   := smNormal;   // PRAGMA synchronous=NORMAL
      SetupConn.Disconnect;
    finally
      SetupConn.Free;
    end;

    try
      SetLength(Workers, ThreadCount);

      for I := 0 to ThreadCount - 1 do
        Workers[I] := TWorker.Create(Props, Iterations);

      for I := 0 to ThreadCount - 1 do
        Workers[I].Start;

      for I := 0 to ThreadCount - 1 do
        Workers[I].WaitFor;

      for I := 0 to ThreadCount - 1 do
        Workers[I].Free;
    finally
      Props.Free;
    end;
  finally
    LogCS.Free;
  end;

  Writeln('DONE');
  Readln;
end.

firstfriday · mORMot 2

Btw: Yes I did use GPT to create this example. But it was based on my real code.

And Claude Sonnet 4.6 explained me what I did wrong.

Btw: I really appreciate that you and Arnaud responded fast.
But "Our SQLite3 wrapper has already its own lock." and to "use TSqlDataBase" didn't help me to understand my problem.

I should have done:
1) use WALMode
2) create ONE TSqlDBSQLite3ConnectionProperties per application
3) use ONE instance of conn:=Props.NewConnection for every thread

I will post my running example for others having to solve this problem.

firstfriday · mORMot 2

Could you please be more specific. E.g. what functions suite my requirements. Or where to find the info.

firstfriday · mORMot 2

I was experiencing intermittent crashes and database errors when using latest mORMot2 + SQLite (3.51.2) in a multi-threaded scenario.

Scenario:

Multiple worker threads (e.g. 4 threads)
All threads access the same SQLite database file.

I prepared a smal example with runs with no other dependencies. Just create a console application with name SqliteMormotThreadTest and past all in in.
Place a breakpoint at bm12345. I would be very thankful if somebody can help me.

program SqliteMormotThreadTest;

{$APPTYPE CONSOLE}

uses
  System.SysUtils,
  System.Classes,
  System.SyncObjs,
  System.Generics.Collections,
  mormot.db.sql,
  mormot.db.raw.sqlite3,
  mormot.db.sql.sqlite3,
  mormot.db.raw.sqlite3.static,
  mormot.core.base,
  mormot.core.os,
  mormot.core.text;

type
  TTestMode = (tmSelectOnly, tmInsert);

  TRunStats = record
    ThreadId: Cardinal;
    Iterations: Integer;
    SuccessCount: Integer;
    ErrorCount: Integer;
    LastError: string;
  end;

  TConsoleLog = class
  private
    class var FLock: TObject;
  public
    class constructor Create;
    class destructor Destroy;
    class procedure Line(const S: string); static;
  end;

  TWorker = class(TThread)
  private
    FDbFile: string;
    FMode: TTestMode;
    FIterations: Integer;
    FStats: TRunStats;
    procedure Log(const Msg: string);
    procedure ExecuteSelectOnly;
    procedure ExecuteInsert;
    procedure VerifyHeader;
  protected
    procedure Execute; override;
  public
    constructor Create(const ADbFile: string; AMode: TTestMode; AIterations: Integer);
    property Stats: TRunStats read FStats;
  end;

class constructor TConsoleLog.Create;
begin
  FLock := TObject.Create;
end;

class destructor TConsoleLog.Destroy;
begin
  FLock.Free;
end;

class procedure TConsoleLog.Line(const S: string);
begin
  TMonitor.Enter(FLock);
  try
    Writeln(FormatDateTime('hh:nn:ss.zzz', Now), ' ', S);
  finally
    TMonitor.Exit(FLock);
  end;
end;

constructor TWorker.Create(const ADbFile: string; AMode: TTestMode; AIterations: Integer);
begin
  inherited Create(True);
  FreeOnTerminate := False;
  FDbFile := ADbFile;
  FMode := AMode;
  FIterations := AIterations;
  FillChar(FStats, SizeOf(FStats), 0);
end;

procedure TWorker.Log(const Msg: string);
begin
  TConsoleLog.Line(Format('[TID=%d] %s', [GetCurrentThreadId, Msg]));
end;

procedure TWorker.VerifyHeader;
var
  FS: TFileStream;
  Buf: TBytes;
  Header: AnsiString;
begin
  if not FileExists(FDbFile) then
    raise Exception.Create('DB file not found: ' + FDbFile);

  FS := TFileStream.Create(FDbFile, fmOpenRead or fmShareDenyNone);
  try
    if FS.Size < 16 then
      raise Exception.Create('DB file too small: ' + FDbFile);
    SetLength(Buf, 16);
    FS.ReadBuffer(Buf[0], 16);
    SetString(Header, PAnsiChar(@Buf[0]), 15);
    if Header <> 'SQLite format 3' then
      raise Exception.Create('Invalid SQLite header: ' + string(Header));
  finally
    FS.Free;
  end;
end;

procedure TWorker.ExecuteSelectOnly;
var
  I: Integer;
  Props: TSQLDBSQLite3ConnectionProperties;
  Conn: TSQLDBConnection;
  Stmt: ISQLDBStatement;
  V: Int64;
begin
  for I := 1 to FIterations do
  begin
    if Terminated then
      Exit;

    VerifyHeader;
    Props := nil;
    Conn := nil;
    try
      Log(Format('ITER=%d pre-props DB="%s"', [I, FDbFile]));
      Props := TSQLDBSQLite3ConnectionProperties.Create(FDbFile, '', '', '');

      Log(Format('ITER=%d pre-connect Props=%p', [I, Pointer(Props)]));
      Conn := Props.NewConnection;
      if Conn = nil then
        raise Exception.Create('NewConnection returned nil');

      Log(Format('ITER=%d pre-Connect Conn=%p', [I, Pointer(Conn)]));
      Conn.Connect;
      Log(Format('ITER=%d connected Conn=%p', [I, Pointer(Conn)]));

      Stmt := Conn.NewStatementPrepared('select 1', True);
      Stmt.ExecutePrepared;
      if not Stmt.Step then
        raise Exception.Create('select 1 returned no row');
      V := Stmt.ColumnInt(0);
      if V <> 1 then
        raise Exception.CreateFmt('select 1 returned %d', [V]);

      Inc(FStats.SuccessCount);
    except
      on E: Exception do
      begin
        Inc(FStats.ErrorCount); // bm12345
        FStats.LastError := E.ClassName + ': ' + E.Message;
        Log(Format('ITER=%d ERROR=%s', [I, FStats.LastError]));
      end;
    end;

    try
      if Conn <> nil then
        Conn.Disconnect;
    except
      on E: Exception do
        Log('Disconnect ERROR=' + E.Message);
    end;

    Conn := nil;
    Props.Free;
    Props := nil;
    Inc(FStats.Iterations);
  end;
end;

procedure TWorker.ExecuteInsert;
var
  I: Integer;
  Props: TSQLDBSQLite3ConnectionProperties;
  Conn: TSQLDBConnection;
  Stmt: ISQLDBStatement;
begin
  for I := 1 to FIterations do
  begin
    if Terminated then
      Exit;

    VerifyHeader;
    Props := nil;
    Conn := nil;
    try
      Props := TSQLDBSQLite3ConnectionProperties.Create(FDbFile, '', '', '');

      Conn := Props.NewConnection;
      if Conn = nil then
        raise Exception.Create('NewConnection returned nil');
      Conn.Connect;

      Stmt := Conn.NewStatementPrepared(
        'insert into thread_test(thread_id, iteration_no, created_utc) values(?,?,strftime(''%Y-%m-%d %H:%M:%f'',''now''))',
        False);
      Stmt.Bind(1, GetCurrentThreadId);
      Stmt.Bind(2, I);
      Stmt.ExecutePrepared;
      Inc(FStats.SuccessCount);
    except
      on E: Exception do
      begin
        Inc(FStats.ErrorCount);
        FStats.LastError := E.ClassName + ': ' + E.Message;
        Log(Format('ITER=%d ERROR=%s', [I, FStats.LastError]));
      end;
    end;

    try
      if Conn <> nil then
        Conn.Disconnect;
    except
      on E: Exception do
        Log('Disconnect ERROR=' + E.Message);
    end;

    Conn := nil;
    Props.Free;
    Props := nil;
    Inc(FStats.Iterations);
  end;
end;

procedure TWorker.Execute;
begin
  FStats.ThreadId := GetCurrentThreadId;
  Log('started');
  case FMode of
    tmSelectOnly:
      ExecuteSelectOnly;
    tmInsert:
      ExecuteInsert;
  end;
  Log(Format('finished Iter=%d Ok=%d Err=%d Last="%s"',
    [FStats.Iterations, FStats.SuccessCount, FStats.ErrorCount, FStats.LastError]));
end;

procedure EnsureDatabase(const DbFile: string);
var
  Props: TSQLDBSQLite3ConnectionProperties;
  Conn: TSQLDBConnection;
  Stmt: ISQLDBStatement;
begin
  ForceDirectories(ExtractFilePath(DbFile));

  Props := TSQLDBSQLite3ConnectionProperties.Create(DbFile, '', '', '');
  try
    Conn := Props.NewConnection;
    try
      Conn.Connect;

      Stmt := Conn.NewStatementPrepared(
        'create table if not exists thread_test (' +
        ' id integer primary key autoincrement,' +
        ' thread_id integer not null,' +
        ' iteration_no integer not null,' +
        ' created_utc text not null' +
        ')', False);
      Stmt.ExecutePrepared;

      Stmt := Conn.NewStatementPrepared('delete from thread_test', False);
      Stmt.ExecutePrepared;
      while Stmt.Step() do
      ;
      Stmt:=nil;
    finally
      Conn.Disconnect;
      Conn := nil;
    end;
  finally
    Props.Free;
  end;
end;

procedure RunTest(const DbFile: string; ThreadCount, Iterations: Integer; Mode: TTestMode);
var
  Workers: TObjectList<TWorker>;
  I: Integer;
  TotalOk: Integer;
  TotalErr: Integer;
  ModeName: string;
begin
  case Mode of
    tmSelectOnly: ModeName := 'SELECT';
    tmInsert:     ModeName := 'INSERT';
  end;

  TConsoleLog.Line(Format('RUN mode=%s threads=%d iterations=%d db="%s"',
    [ModeName, ThreadCount, Iterations, DbFile]));

  Workers := TObjectList<TWorker>.Create(True);
  try
    for I := 1 to ThreadCount do
      Workers.Add(TWorker.Create(DbFile, Mode, Iterations));

    for I := 0 to Workers.Count - 1 do
      Workers[I].Start;

    for I := 0 to Workers.Count - 1 do
      Workers[I].WaitFor;

    TotalOk := 0;
    TotalErr := 0;
    for I := 0 to Workers.Count - 1 do
    begin
      Inc(TotalOk, Workers[I].Stats.SuccessCount);
      Inc(TotalErr, Workers[I].Stats.ErrorCount);
      TConsoleLog.Line(Format(
        'SUMMARY tid=%d iter=%d ok=%d err=%d last="%s"',
        [Workers[I].Stats.ThreadId,
         Workers[I].Stats.Iterations,
         Workers[I].Stats.SuccessCount,
         Workers[I].Stats.ErrorCount,
         Workers[I].Stats.LastError]));
    end;

    TConsoleLog.Line(Format('DONE mode=%s totalOk=%d totalErr=%d',
      [ModeName, TotalOk, TotalErr]));
  finally
    Workers.Free;
  end;
end;

const
  dbpath='..\..\test_thread.db';
var
  DbFile: string;
begin
  try
    ReportMemoryLeaksOnShutdown := True;
    // start fresh
    DeleteFile(dbpath);

    if ParamCount > 0 then
      DbFile := ExpandFileName(ParamStr(1))
    else
      DbFile := ExpandFileName(dbpath);

    TConsoleLog.Line('EXE=' + ParamStr(0));
    TConsoleLog.Line('DB =' + DbFile);
    // create empty database;
    EnsureDatabase(DbFile);

    // fill it with data in non thread manner
    TConsoleLog.Line('INSERT DATA NON THREADED.');

    var w1:TWorker;
    w1:=TWorker.Create(DbFile,TTestMode.tmInsert,500);
    w1.ExecuteInsert;
    w1.ExecuteSelectOnly;
    w1.free;

    // test threading
    TConsoleLog.Line('INSERT DATA THREADED.');
    RunTest(DbFile, 1, 200, tmSelectOnly);  // works with on thread
    RunTest(DbFile, 4, 500, tmSelectOnly);  // error with >1 thead, see bm12345
    //RunTest(DbFile, 4, 500, tmInsert); // fails also

    TConsoleLog.Line('ALL TESTS FINISHED.');
  except
    on E: Exception do
    begin
      TConsoleLog.Line('FATAL ' + E.ClassName + ': ' + E.Message);
    end;
  end;
  ReadLn;
end.

firstfriday · mORMot 2

I found finally out that
1) I need to start the server with 0.0.0.0:8080 to be reached form outside my network.
With http I could start it with localhost, with https not.

2) With parameter useHttpApiRegisteringURI the TLS(secTLSSelfSigned) is delegating the TLS functionality to the windows system. In theory you will have to configure it with the management console: prompt:Certmgr.msc and import the certificate generated with opelSSL. But I could not get it running.

The only combination worked for me was

 FServer := TRestHttpServer.create(''0.0.0.0:8080', [FSQLRestServer], '+',
                  useHttpAsync,32,secTLSSelfSigned);

The whole issue about certificates is quite confusing and I found it difficulte to find a working manual for windows to manage it.

firstfriday · mORMot 2

I am pritty sure that your code works fine. It is a general Certificate or Windows problem.
I cannot debug because it does not even reach your Framework.
Server (started as an admin with no error):

      certFile := 'cert.pem';
      keyFile := 'key.pem';  
      // these files created with
      // openssl req -x509 -newkey rsa:2048 -keyout key.pem -out cert.pem -days 365 -nodes -subj "/CN=mydyndns"
      FServer := TRestHttpServer.create('localhost:8080', [FSQLRestServer], '+',
                  useHttpApiRegisteringURI,32,secTLS,certFile, keyFile); // secTLSSelfSigned has same effect

then I try to access it with a browser and it returns:
ERR_CONNECTION_RESET
or
with curl: curl: (35) Recv failure: Connection was reset

firstfriday · mORMot 2

hi, I have a problem with secTLSSelfSigned.

The client returns always an error code 666.

FHTTPClient.TLS.Enabled:=true;
FHTTPClient.TLS.IgnoreCertificateErrors:=true;
StatusCode := FHTTPClient.Request(FBaseURL, 'POST',10 ,'Content-Type: application/json'#13#10 +  'Accept: application/json',RequestBody);
// StatusCode  always 666

Server ist created like this:

HttpServer := TRestHttpServer.Create('8080', [RestServer], '+', useBidirSocket, 32, secTLSSelfSigned );

what am I doing wrong?

firstfriday · mORMot 1

I am using interfaced rest server WITHOUT Object-Relational Mapping. Only Rest interface.
Like documented the default session is closed after 30 minutes. But since I am not using any of the table operations the session is closed EVEN I use the interface all the time.
How can I set a kind of activity flag?
Thanks

firstfriday · mORMot 1

1) HTTP Host: header is indeed modified by Chrome but also Firefox does modify it.

2) Editing the hosts file
127.0.0.1 images.localhost
does work.

3) Only one thing of understanding is bugging me:
I agree totally with the DNS resolution process. But this should work then:
http://images.<hostname>:8083/test.jpg
hostname=computername. But it also fails.

firstfriday · mORMot 1

1) curl -v http://images.localhost:8083/test.jpg
* Could not resolve host: images.localhost
* Closing connection 0
curl: (6) Could not resolve host: images.localhost
2) maybe thats an option but after 2h of reading the manual I gave up.
3) tried already.
4) http://images.hostname:8083/test.jpg does also not work on this computer other yes

I suppose some problems never get solved or are not worth finding :-(

firstfriday · mORMot 1

These were interesting answers. I (believe) to be closer to the problem.

Very interesting is that
- Chrome Browser CAN access http://images.localhost:8083/test.jpg
- Fire Fox, Edge, ie CAN NOT and
- THTTPClient responds Error sending data: (12007) The server name or address could not be resolved

In my mind this must be a DNS problem while google chrome somehow resolved that on it's own.

With
- "logman.exe start httptrace -p Microsoft-Windows-HttpService 0xFFFF -o httptrace.etl –ets"
you can trace any http requests but no traffic.
- hosts file is empty
- I checked also TCPIP settings for alternative DNS->No

More ideas?

firstfriday · mORMot 1

Thank you for your reply.

Both latest Windows 10.

Unfortunatly no errors and the logs of both computer are identical.

TSynLog 1.18.5499 2020-02-01T04:40:52
image.server.model.TSQLHttpServerroot(056a38f0) http.sys registration of http://+:8083/root
SetThreadName 1130=TSQLHttpServerroot 8083/root THttpApiServer
image.server.model.TSQLHttpServerroot(056a38f0) {"THttpApiServer(05d8d7f0)":{"RegisteredUrl":"http://+:8083/root/","MaxBandwidth":-1,"MaxConnections":-1,"APIVersion":"HTTP API 2.0","ServerName":"mORMot (Windows)","ProcessName":"root "}} initialized for root
image.server.model.TSQLHttpServerroot(056a38f0) Redirect http://localhost:8083 to http://localhost:8083/root
image.server.model.TSQLHttpServerroot(056a38f0) http.sys registration of http://+:8083//

DomainHostRedirect('images.localhost','root/images');

http://localhost:8083/root/images/test.jpg // works on both computers
http://images.localhost:8083/test.jpg // does not work on one computer, Page not found

I don't think it is related to mormot or my code more because if I set A1) breakpoint

unit SynCrtSock;
...
Err := Http.ReceiveHttpRequest(fReqQueue,ReqID,0,Req^,length(ReqBuf),bytesRead);
if Terminated then // A1) breakpoint here

and call http://images.localhost:8083/test.jpg
the breakpoint is not reached.
I checked also the windows eventlog but there is nothing interesting to find.
How exactly is the way from a browser-call images.localhost to this breakpoint?

BTW:
In Debug-Mode this has no effect:
TSynLog.Family.DestinationPath:='log\';
In Release-mode it does.

firstfriday · mORMot 1

Hi, I have a Mormot Server written with server.DomainHostRedirect(..) which works on one computer but not on another one.

Same programm on both computers and startet as Admin to register the server.
no error occurs.

Even THttpApiServer.Execute is not executed which leads me to the idea that the https server is not passing the request.
but why? Any ideas where to look?

firstfriday · mORMot 1

Delphi Tokio 10.2.3 Update 3 solves the problem

firstfriday · mORMot 1

I installed
- RAD Studio 10.2 Hotfix for Toolchain and
- RAD Studio 10.2.2 Tokyo February 2018 Patch
but no change. IDE hangs. Another idea?

firstfriday · mORMot 1

Hi,
I can compile the examples (SQLite3\Samples) on 32-bit but not on 64-bit.

The compiler hangs during compilation of mormot.pas (started from IDE).
Has anyone the same problem and a solution?

Edgar

firstfriday · mORMot 1

The full code is this:

procedure TMainForm.Button1Click(Sender: TObject);
var
  Props :TODBCConnectionProperties;
  Q: TQuery;
begin
  Props :=TODBCConnectionProperties.Create('','DRIVER=MySQL ODBC 5.3 Unicode Driver;SERVER=localhost;DATABASE=db1;USER=root;PASSWORD=;Port=3306','','');
  Q := TQuery.Create(Props.NewConnection);
  try
    Q.SQL.Clear; // optional
    Q.SQL.Add('select * from table1);
    Q.Open;
    while not Q.Eof do
    begin
      Q.Next;
    end;
    Q.Close;    // optional
  finally
    Q.Free;
  end;
end;

Maybe TQuery is just not meant to use with mySQL. Of cource I could use the Step Command but for legacy purposes this is not an easy job. Or I can override this origin TQuery.

firstfriday · mORMot 1

Thank you for your fast response. I have the latest version and here you see "first" and the end. this causes the error in mySQL.

unit SynDB;

procedure TQuery.Open;
var i, h: integer;
    added: boolean;
    ColumnName: string;
begin
  if fResultCount>0 then
    Close;
  Execute(true);
  for i := 0 to fPrepared.ColumnCount-1 do begin
    ColumnName := UTF8ToString(fPrepared.ColumnName(i));
    h := fResult.FindHashedForAdding(ColumnName,added);
    if not added then
      raise ESQLQueryException.CreateUTF8('Duplicated column name "%"',[ColumnName]);
    with fResults[h] do begin
      fQuery := self;
      fRowIndex := 0;
      fColumnIndex := i;
      fName := ColumnName;
    end;
  end;
  assert(fResultCount=fPrepared.ColumnCount);
  // always read the first row
  First;
end;

I wonder if I can change somehow the cursor type.

firstfriday · mORMot 1

Yes...but my question is:
I use the wrapper TQuery of mormot and unfortunatly inside this function it uses

while i.Step(true) do

I use this TQuery with mssql and it works here but not with mySQL.

firstfriday · mORMot 1

I would like to use the TQuery wrapper with mySQL but I failed because of this error:

TODBCStatement - TODBCLib error: [HY106] [MySQL][ODBC 5.3(w) Driver][mysqld-5.5.5-10.1.21-MariaDB]Wrong fetchtype with FORWARD ONLY cursor (537)

Reproduction can be easily done by this code
TQuery uses this: while I.Step(true)

  Props :=TODBCConnectionProperties.Create('','DRIVER=MySQL ODBC 5.3 Unicode Driver;SERVER=localhost;DATABASE=testdb;USER=root;PASSWORD=;Port=3306','','');
  I:=Props.Execute('select * from table',[]);
  while I.Step(true) do // < ERROR but while I.Step(false) works!
  begin

  end;

firstfriday · mORMot 1

I just got the book. I did not read it fully but it tells you less than the documentation at Synopse. It's a book for beginners. No ORM/SOA/DDD or any deeper concepts. And the layout is not good.
Big font, specially of the code listings (approx. 50% of the book, 100 pages code print of the demos of this website) which have ugly line breaks which makes them difficult to read. Sometimes just one single word on a page. I' am pretty sure nobody looked at it before giving it to the press.
That reminds me of bad copies of Lonely Planet you can buy in Asia on a market.
Somebody "above" wrote the book has a good quality. I really cannot agree with this. At least not my version: Printed in Poland by Amazon Fulfillment.
For almost 60€ I've expected a better quality - far to expensive.

firstfriday · mORMot 1

Props.UseCache := False;

Removes this error

firstfriday · mORMot 1

Props.UseCache := False;
was the a goog idea! Works now. (yes, it's only a test case)
Thanks

firstfriday · mORMot 1

When doing mass insertions with MSSQL (only here, not SQLite) there is a quite big memory loss when using parameters(see Version2).
Without params everything works fine (see Version1).

Code to reproduce:

procedure TForm2.cmdErrorClick(Sender: TObject);
var
  i:integer;
  SQL,s:string;
  Props:TOleDBMSSQL2012ConnectionProperties;
  SQLDBConnection:TSQLDBConnection;
  Query : TQuery;
begin
  (*
    create a mssql database execute this
    create table [dbo].[test] (
      [id] [int] not null ,
      [id_u] [int] null,
      [fkey] [nvarchar] (255) collate sql_latin1_general_cp1_ci_as null ,
      [svalue] [nvarchar] (255) collate sql_latin1_general_cp1_ci_as null ,
      [ivalue] [int] null ,
      [fvalue] [float] null
    ) on [primary]
    go
  *)
  Props:=TOleDBMSSQL2012ConnectionProperties.create('localhost','test_db','sa','');
  SQLDBConnection:=props.NewConnection;

  SQL := 'TRUNCATE TABLE test';
  query:=Tquery.Create(SQLDBConnection);
  query.SQL.clear;
  query.SQL.Add(SQL);
  query.ExecSQL;
  query.free;

  s:='String';
  query:=Tquery.Create(SQLDBConnection);
  for i:=1 to 100000 do
  begin
    //SQL := format('insert into test (id,ivalue,fkey,svalue) values (%d,%d,''%s'',''%s'')',[i,i,s,s]); // Version 1=> works
    SQL := 'insert into test (id,ivalue,fkey,svalue) values (:id,:ivalue,:fkey,:svalue)' ;// Version 2 =>memory leak when executed
    query.SQL.Clear;
    query.SQL.Add(SQL);
    // Version 2
    query.ParamByName('id').AsInteger     :=i;
    query.ParamByName('ivalue').AsInteger :=i;
    query.ParamByName('fkey').AsString   :='String '+inttostr(i);
    query.ParamByName('svalue').AsString :='String '+inttostr(i);

    query.ExecSQL;
  end;
  query.free;
end;

Arnauld, can you fix this?

firstfriday · mORMot 1

ok, I testet it: With SQLite there is no problem.

After taken the latest version of mormot it is possible to re-use Tquery it with MSSQL.

firstfriday · mORMot 1

Great, just ordered it, I am looking forward to it.

For the french guy:
https://www.amazon.de/dp/1517516005/ref … +Databases

firstfriday · mORMot 1

Thank you for your answer but the query is closed anyway, look:

procedure TQuery.ExecSQL;
begin
Execute(false);
Close;
end;

may be it is a problem with mssql?
I will create a sample with sqlite and compare....

firstfriday · mORMot 1

Why is it not possibe to use Tquery more than 1 time?
Code to reproduce:

procedure TForm2.cmdErrorClick(Sender: TObject);
var
  i:integer;
  SQL,s:string;
  Props:TOleDBMSSQL2012ConnectionProperties;
  SQLDBConnection:TSQLDBConnection;
  Query : TQuery;
begin
  (*
    create a mssql database execute this
    create table [dbo].[test] (
      [id] [int] not null ,
      [id_u] [int] null,
      [fkey] [nvarchar] (255) collate sql_latin1_general_cp1_ci_as null ,
      [svalue] [nvarchar] (255) collate sql_latin1_general_cp1_ci_as null ,
      [ivalue] [int] null ,
      [fvalue] [float] null
    ) on [primary]
    go
  *)
  Props:=TOleDBMSSQL2012ConnectionProperties.create('localhost','test_db','sa','');
  SQLDBConnection:=props.NewConnection;

  SQL := 'TRUNCATE TABLE test';
  query:=Tquery.Create(SQLDBConnection);
  query.SQL.clear;
  query.SQL.Add(SQL);
  query.ExecSQL;
  query.free;

  s:='String';
  query:=Tquery.Create(SQLDBConnection);
  for i:=1 to 100000 do
  begin
    //SQL := format('insert into test (id,ivalue,fkey,svalue) values (%d,%d,''%s'',''%s'')',[i,i,s,s]); // Version 1=> works
    SQL := 'insert into test (id,ivalue,fkey,svalue) values (:id,:ivalue,:fkey,:svalue)' ;// Version 2 =>memory leak when executed
    query.SQL.Clear;
    query.SQL.Add(SQL);
    // Version 2
    query.ParamByName('id').AsInteger     :=i;
    query.ParamByName('ivalue').AsInteger :=i;
    query.ParamByName('fkey').AsString   :='String '+inttostr(i);
    query.ParamByName('svalue').AsString :='String '+inttostr(i);

    query.ExecSQL;
  end;
  query.free;
end;

firstfriday · mORMot 1

Has anybody encountered this message after inserting records for approx. 1 h:

TOleDBConnection: Not enough storage is available to complete this operation

firstfriday · mORMot 1

I ' am using your TQuery wrapper.

How can I find out the datatype of the fields of a query? Similar to the ado function:
Query.Fields[0].DataType

Specially to find out type boolean and memo, text.

SQLDBConnection:=props.NewConnection;
Query := TQuery.Create(SQLDBConnection);
Query.sql.add('select * from mytable');

Query.Fields[0].DataType ???

Thank you

firstfriday · mORMot 1

uuuuhh, a copy past error, thanks

firstfriday · mORMot 1

Hallo!
Can anybody help me in this matter?
How can I send content-Type HTML instead of PLAIN TEXT?
Thank you

firstfriday · mORMot 1

This is probably easy but this code keeps returning

Content-Type=text/plain; charset=UTF-8

instead of

Content-Type=text/html; charset=UTF-8

procedure TSQLRestServerFullMemory.Help(Ctxt: TSQLRestServerURIContext);
begin
Ctxt.Returns('<html><body>Help</body></html>',HTML_SUCCESS,HTML_CONTENT_TYPE);
end;

Please help
Edgar

mORMot Open Source

#1 Re: mORMot 2 » Access a Database with Threads » 2026-03-21 16:56:47

#2 Re: mORMot 2 » Access a Database with Threads » 2026-03-21 16:53:32

#3 Re: mORMot 2 » Access a Database with Threads » 2026-03-21 01:42:59

#4 mORMot 2 » Access a Database with Threads » 2026-03-20 18:52:23

#5 Re: mORMot 2 » Connect to a https Server » 2025-09-06 11:31:04

#6 Re: mORMot 2 » Connect to a https Server » 2025-09-05 23:18:41

#7 mORMot 2 » Connect to a https Server » 2025-08-12 00:30:18

#8 mORMot 1 » Sessiontimeout » 2023-04-07 23:04:55

#9 Re: mORMot 1 » DomainHostRedirect not working » 2020-02-03 23:51:02

#10 Re: mORMot 1 » DomainHostRedirect not working » 2020-02-03 06:24:30

#11 Re: mORMot 1 » DomainHostRedirect not working » 2020-02-02 05:34:41

#12 Re: mORMot 1 » DomainHostRedirect not working » 2020-02-01 05:57:42

#13 mORMot 1 » DomainHostRedirect not working » 2020-01-31 04:15:04

#14 Re: mORMot 1 » mORmot examples 64-bit on Delphi10.2 fails compiling » 2018-06-03 08:32:12

#15 Re: mORMot 1 » mORmot examples 64-bit on Delphi10.2 fails compiling » 2018-02-22 07:10:46

#16 mORMot 1 » mORmot examples 64-bit on Delphi10.2 fails compiling » 2018-02-21 18:02:07

#17 Re: mORMot 1 » MySQL Wrong fetchtype with FORWARD ONLY cursor » 2017-05-06 08:39:59

#18 Re: mORMot 1 » MySQL Wrong fetchtype with FORWARD ONLY cursor » 2017-05-05 15:31:42

#19 Re: mORMot 1 » MySQL Wrong fetchtype with FORWARD ONLY cursor » 2017-05-05 11:06:30

#20 mORMot 1 » MySQL Wrong fetchtype with FORWARD ONLY cursor » 2017-05-04 18:38:31

#21 Re: mORMot 1 » new book about mORMot » 2016-12-22 22:27:15

#22 Re: mORMot 1 » TOleDBConnection: Not enough storage is available ... » 2016-12-20 18:17:15

#23 Re: mORMot 1 » Memory Leak when using Parameter in Query with MSSQL » 2016-12-19 09:45:44

#24 mORMot 1 » Memory Leak when using Parameter in Query with MSSQL » 2016-12-18 13:11:08

#25 Re: mORMot 1 » Re-use of TQuery does not work » 2016-12-18 12:10:36

#26 Re: mORMot 1 » new book about mORMot » 2016-12-18 11:33:59

#27 Re: mORMot 1 » Re-use of TQuery does not work » 2016-12-18 08:22:18

#28 mORMot 1 » Re-use of TQuery does not work » 2016-12-17 16:47:19

#29 mORMot 1 » TOleDBConnection: Not enough storage is available ... » 2016-12-17 16:25:25

#30 mORMot 1 » Determine the Field/Datatype of a Query » 2016-12-16 06:52:30

#31 Re: mORMot 1 » Returning HTML » 2015-07-26 10:30:46

#32 Re: mORMot 1 » Returning HTML » 2015-07-24 10:16:58

#33 mORMot 1 » Returning HTML » 2015-07-22 08:50:44

Board footer