mORMot Open Source

If they are not meant to be defined as external, I'm not really sure how you see the use of UserAuthentication with these tables when using an external database.

But when creating the TSQLRestServer i have set HandleUserauthentication to false ...

Ok, fog is clearing up a bit now
What i also would expect is a CreateFillAndPrepare that will only substitute '?' with bound parameters

This test is done against a oracle database, the empty datetime returned as '0000-01-01T00:00:00' is from the Oracle driver ....

It turns out the field was empty for another reason (on creating the record we used BatchAdd(Rec, false, true) in stead of BatchAdd(Rec, True, True))
This created empty records in the oracle table, and when they were read back, the TDatetime field was empty too of course

Ok, thanks for the adjustments.

Is it possible to add my own native database implementation, without having to make major alterations to your sources?
With the current setup I have to add dNexusDB to the TSQLDBDefinition. But when i do that, i also have to alter all the const arrays containing database specific implementations ..
So i was wondering if that was the way to go ..

In my current implementation of NexusDB i use the original TQuery.Params[] created when assigning the SQL statement to the TQuery.SQL.Text property
The binding is done with BindTextU and this assigns the Iso8601 string to the param value .AsString
As far as i know the TParam object does not know what kind of datatype is expected ... so i cannot make a special case when a date is given

Another difference: date literals in parameters, for NexusDB they need to be formatted like DATE '2012-01-29'

I'm still not convinced ;-)

What will be the result of the following code:

var Customer: Variant;
begin
  with Props.Execute( 'select * from Sales.Customer where AccountNumber like ?', ['AW000001%'],@Customer) do
      assert(Copy(Customer.AccountNumber,1,8)='AW000001');
end;

Or in a question: Isn't the first record the active record after running Props.Execute() ?

Thanks for your suggestions, they are really usefull!

The reason we are looking for this extra implementation is that the current version of our product make heavy use of NexusDB. We are in the start-up of developing a major overhaul of our application, and of course cannot ignore our installed base, even it is only necessary to convert to a better solution ;-)

We'll will keep you posted on our progress, and of course will supply you with the resulting unit

Can i set this attribute by hand somehow? maybe with SQLPLUS?

Hahaha you have fixed the problem faster than i can think of a workaround,
thanks

Again, thanks for the advise!

One of the things I don't like so much about your approach to the ORM is the mis-use of existing Delphi constructs like "index n" attribute for the maximum length of a string-property. Other ORMs solve this i.e. with official Class-attributes like TMS Aurelius:

type
  [Entity]
  [Table('ARTISTS')]
  [Sequence('SEQ_ARTISTS')]
  [Id('FId', TIdGenerator.IdentityOrSequence)]
  TArtist = class
  private
    [Column('ID', [TColumnProp.Unique, TColumnProp.Required, TColumnProp.NoUpdate])]
    FId: Integer;
    FArtistName: string;
    FGenre: Nullable<string>;
    function GetArtistName: string;
    procedure SetArtistName(const Value: string);
  public
    property Id: integer read FId;

    [Column('ARTIST_NAME', [TColumnProp.Required], 100)]
    property ArtistName: string read GetArtistName write SetArtistName;

    [Column('GENRE', [], 100)]
    property Genre: Nullable<string> read FGenre write FGenre;
  end;

IMHO this approach has a few advantages:

• Every class can be persisted this way, without any specific demands on class-hierarchy

• Cleaner code, much better readable because there are no standard language construct that mean something else in this ORM context

What are your thoughts on this?

We are not sure yet. At the moment we are testing several ORMs learning what can be realistic performances.
At the moment we have a single client windows application, who's design principles date more than 15 years back. It uses an embedded NexusDB engine which is (of course) lightning fast. There are a lot of very data-intensive calculations needed in all kinds of analyses. The dataset can be from just a few Mb to several dozens of GB. Becuase of its design all the analyses were done on a record-by-record basis in the client (Dataset.First - .Next - Until .EOF)

For the newly designed application client-server with multiple clients (windows, HTML, Scripting, ...), SOAP or REST, multiple databases (Oracle and MSSQL required, others are optional) are all MustHave requirements. This all demands a very different approach to the new design, and our way of thinking (guess we need a bit of re-education ;-)).

The application also has to have the possibility to convert external data into its own model, and this could also be a few MB or several GB that has to be converted on a regular basis...

Another important function for us developer ( not so much from a users point of view) is the possibility to update the Model and its underlying database when the model changes with new version of the application (new or dropped classes, new or changed published properties, new indeces etc). This is something we have not found in mORMot yet (while it is present i.e. in InstantObjects. Any plans on such a thing?

The latency as stated by TNSPING is 10-20 msec

Executed some more tests, directly executing 4 possible scenarios in one go, using the following code:

procedure TForm1.FormCreate(Sender: TObject);
begin
  Props := TSQLDBOracleConnectionProperties.Create('OPFTEST','OPF_MORMOT','secret', 871);
  Model := CreateSampleModel; // from SampleData unit
  VirtualTableExternalRegister(Model,TSQLSampleRecord,Props,'SampleRecord');
  Client := TSQLRestClientDB.Create(Model, nil, 'test.db3', TSQLRestServerDB);
  Client.Server.CreateMissingTables;
end;

function TForm1.Addmany(aUseBatch, aUseTransactions: boolean): double;
var
  i: Integer;
  Starttime, Endtime: cardinal;
  Rec: TSQLSampleRecord;
  Ida: TIntegerDynArray;

begin

  Rec := TSQLSampleRecord.Create;

  Rec.Question := QuestionMemo.Text;
  Rec.Address_ := 'Dit is het adres laan, 23';
  Rec.postalCode := '1234AA';
  Rec.City := 'Nijmegen';

  if aUseBatch then
    Client.BatchStart(TSQLSampleRecord);
  if aUseTransactions then
    Client.TransactionBegin(TSQLSampleRecord);

  StartTime := GetTickCount;
  try
    for i := 0 to cRecordCount do
    begin
      Rec.Name := NameEdit.Text + IntToString(i);
      if aUseBatch then
        begin
          Client.BatchAdd(Rec,false,true);
          if (i mod 1000) = 0 then
          begin
            Client.BatchSend(Ida);
            Client.BatchStart(TSQLSampleRecord);
          end;
        end
      else
        Client.Add(Rec,true);

    end;
  finally
    Rec.Free;
    if aUseBatch then
      Client.BatchSend(Ida);
    if aUseTransactions then
      Client.Commit;
  end;

  EndTime := GetTickCount;
  Result := cRecordCount /((endtime - starttime)/1000);
end;

• No Batch, No Transactions: 600 obj/sec

• Batch, No Transactions: 706 obj/sec

• Batch, Transactions: 681 obj/sec

• No Batch, Transactions: 624 obj/sec

As to your other questions and suggestions: I will be very happy to provide you with the answers and results, but as we are only investigating the possibilities of mORMot (we haven't reached a conclusion yet), I cannot invest to much time in finding out how these can be realized ;-). If you provide me with the code or hints on how to do those things, I'll execute them and post the result in here.

Looking at the registry, i see the Oracle settings in HKLM\SOFTWARE\Wow6432Node\Oracle
so i'm pretty sure its the 32bits client i have installed

The Status returned is -1
I can connect to the database with the Enterprise Manager COnsole
I have only one client installed on my PC.

I added ORACLE_HOME environment var. cause that wasnt defined, but doesnt make a difference
I checked all loaded oracle dlls, but loaded libs and dll are from the correct clientfolder ...