mORMot Open Source

I have noticed some problems with TSQLDBServerHttpApi when using a thread pool with TOleDBMSSQL2012ConnectionProperties.  The Ole CoUninit assertion seems to fail at times.

The Server runs in a Windows Service:

FProps := TOleDBMSSQL2012ConnectionProperties.Create('127.0.0.1\SQLEXPRESS','test','test','test');
FHttpServer := TSQLDBServerHttpApi.Create(FProps,'test',80,'test','test',False,64,nil,tmThreadPool);

The Client connection is created in a datamodule constructor and freed in the destructor:

http := TSQLDBWinHTTPConnectionProperties.Create(...)

statement := http.NewThreadSafeStatementPrepared(SomeSQL,True,True);

....parameter binding....

statement.ExecutePrepared;

Seeing this in the log at times:
20220715 06004760  ' ERROR "EAssertionFailed(125fef00)":"You should call TOleDBConnection.Free from the same thread which called its Create: i.e. call MyProps.EndCurrentThread from an THttpServerGeneric.OnHttpThreadTerminate event - see ticket 213544b2f5

Memory allocation seems to keep increasing over time because of this.  .  I tried using a HttpThreadTerminate event calling TSQLDBConnectionPropertiesThreadSafe(FProperties).EndCurrentThread, but the problem persists;  Please help.

I'm having a problem with a stored procedure that returns 300+ columns.  FetchAllToBinary throws a 'Too Many Columns' exception.  Unfortunately I'm unable to modify the procedure to return less columns.  Any suggestions?

  FillChar(Null,sizeof(Null),0);
  result := 0;
  W := TFileBufferWriter.Create(Dest);
  try
    W.WriteVarUInt32(FETCHALLTOBINARY_MAGIC);
    FMax := ColumnCount;
    W.WriteVarUInt32(FMax);
    if FMax>0 then begin
      // write column description
      SetLength(ColTypes,FMax);
      dec(FMax);
      for F := 0 to FMax do begin
        W.Write(ColumnName(F));
        ColTypes[F] := ColumnType(F,@FieldSize);
        W.Write1(ord(ColTypes[F]));
        W.WriteVarUInt32(FieldSize);
      end;
      // initialize null handling
      NullRowSize := (FMax shr 3)+1;
      if NullRowSize>sizeof(Null) then                     <-------------------------
        raise ESQLDBException.CreateUTF8(
          '%.FetchAllToBinary: too many columns',[self]);

I'm attempting to migrate an old legacy project from ADO/Soap to something a little faster.  TSQLDBServerHttpApi seems to be working fine for normal selects, inserts and stored procedures.  My problem is with stored procedures that return very large result sets that are need for certain reports (up to 50k rows and 100+ columns -  Unfortunately I'm unable to change the logic for these stored procedures).  I'm getting WinHTTP.dll timeouts on the client after about 20 seconds.  I have tried increasing the client KeepAliveMS with no success.  Any help would be appreciated.

Server:

    Props := TOleDBMSSQL2012ConnectionProperties.Create('127.0.0.1','ReportsDB','user','password');
    HttpServer := TSQLDBServerHttpApi.Create(Props,'ReportsDB','8787','user','password');
    HttpServer.Properties.ConnectionTimeOutMinutes := 2;

Client:

  http := TSQLDBWinHTTPConnectionProperties.Create('1.2.3.4:8787','ReportsDB','user','pass');

  try
    ds := TDataSet.Create(Self);
    sql:= 'exec GetReportsSP param1, param2, param3';
    ds := ToDataSet(ds,http.Execute(sql,[]));
    PopulateGrid(ds);
  finally
    ds.Free;
  end;