mORMot Open Source

dka1

Member

From: Greece

Registered: 2017-07-25

Posts: 28

SynDB/SynOleDB new features

Hello Arnaud,
I need

1. A datatype ftAnsiString (as varchar) as it faster (no conversion RawUnicodeToUtf8) and has smaller size

2. The oleDB's multi result feature means get all results (one by one) of a multi select sql statement eg: "select a;select b;select c;"

I implement both a) in SynDB and b) in SynOleDB and works.
Do you want to send the code and add it to your codebase?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: SynDB/SynOleDB new features

I'm afraid a) is premature optimization, since most drivers features UTF-8, and our conversion functions are very optimized for speed (faster than OS API or Delphi's RTL).
The probability of having some charset problems will increase, since on Windows the code page will depend on the OS, whereas our RawUTF8 is truly Unicode-ready.

About b) I'm curious to see what you meant by this "multi-result" feature.
But if it is an OleDB-only feature, it won't have its place on the main SynDB feature set..

dka1

Member

From: Greece

Registered: 2017-07-25

Posts: 28

Re: SynDB/SynOleDB new features

Hello Arnaud,
I implemented varchar only for OLEDB (synOleDB) driver.
I change synDB   

procedure TSQLDBStatement.ColumnsToBinary
...
  ftAnsiString:
    W.Write(ColumnString(F));
...

and synCommons to add it on type TSQLDBFieldType.
I did because I have many Varchar fields and I want to gain as more space and speed (I use TStringField and TClientDataSet).

About multi result as I wrote I implemented b) in SynOleDB and works.
The requirement is to send one sql batch that include more than one result set.
I use this ms sql feature on SDAC and I need it on synOLEDB (as I intend to abandon SDAC).
An example:

declare @tbl table(id int);
insert into @tbl(id) select id from Customer where name like 'Alpha%' and code like '1__%';
select code,name from customer cus inner join @tbl t on t.id= cus.id;
select trndate, trnAmount from customerTrans cust inner join @tbl t on t.id= cust.CusID;

My implementation is

  ISQLDBStatementMultiResult = interface(ISQLDBStatement)
  ['{A4792685-787C-4DFD-848B-90503AA23EE3}']
    procedure ExecutePreparedMultiResult(out mr: IMultipleResults);
    function GetNextResult(const mr: IMultipleResults): Boolean;    
  end;

  TOleDBStatement = class(TSQLDBStatement, ISQLDBStatementMultiResult)
  ...
    // ISQLDBStatementMultiResult
    procedure ExecutePreparedMultiResult(out mr: IMultipleResults);
    function GetNextResult(const mr: IMultipleResults): Boolean;

  implementation

function TOleDBStatement.GetNextResult(const mr: IMultipleResults): Boolean;
var
  res: HResult;
  RowSet: IRowSet;
begin
  result:= false;
  Reset;
  if mr = nil then
    Exit;
  repeat
    res := mr.GetResult(nil,0,IID_IRowset, @fUpdateCount, @RowSet);
  until Assigned(RowSet) or (res <> S_OK);
  result:= Assigned(RowSet);
  if result then
    FromRowSet(RowSet);
end;

procedure TOleDBStatement.ExecutePreparedMultiResult(out mr: IMultipleResults);
   code here is the same as original procedure ExecutePrepared
   the only difference is that declaration of mr: IMultipleResults is not part of local var but an out param.

I change also procedure ExecutePrepared as

procedure TOleDBStatement.ExecutePrepared;
var
  mr: IMultipleResults;
begin
  ExecutePreparedMultiResult(mr);
end;

Here is how I use it

var
  mr: IMultipleResults;
  stm:  ISQLDBStatementMultiResult;
begin
  stm:= db.NewThreadSafeStatementPrepared(''
           +'declare @tbl table(id int);'
           +'insert into @tbl(id) select id from Customer where name like ''Alpha%'' and code like ''1__%''; '
           +'select code,name from customer cus inner join @tbl t on t.id= cus.id;'
           +'select trndate, trnAmount from customerTrans cust inner join @tbl t on t.id= cust.CusID;'  , True);
  stm:= db.NewThreadSafeStatementPrepared(s, True);
  stm.ExecutePreparedMultiResult(mr);
  while stm.GetNextResult(mr) do
    while stm.Step do
      ShowMessage(stm.ColumnName(0));

Your feedback is welcome.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: SynDB/SynOleDB new features

a) is clearly premature optimization, will in fact be slower on the server side, and will break SynDB simple data model.
Using UTF-8, even with a greek charset, won't hurt the performance, in respect to plain ISO 8859-7 encoding: the buffer may be slighter bigger, but conversion to AnsiString or UnicodeString will be very fast, and size slightly smaller than plain UnicodeString.
It is therefore unneeded.

b) is OleDB-specific, so may be added to SynOleDB.pas, but without changing SynDB.pas shared behavior.

dka1

Member

From: Greece

Registered: 2017-07-25

Posts: 28

Re: SynDB/SynOleDB new features

you are welcome if you want something more.