mORMot Open Source

anj

Member

Registered: 2016-10-05

Posts: 2

Newbie, stuck at the 1st hurdle

I'm sure this is just me...

Quick background.  We've got a large (over 15 years old) legacy application and we're looking to move parts of the "core" system into more modern layers so that web support can be added while at the same time the existing "thick client" can continue (as this wouldn't be a small task).

While doing this we'd like to allow for modernisation of a number of areas like using ORM etc. to minimise the maintenance from our side etc.  One of things I'm looking at is mORMot - it looks as though it'd fulfill our needs exactly as we can use it "in process" for the thick client, but also use it separately for the web side of things, with the ultimate goal that the "thick client" is no longer thick

So, after reading a lot of the documentation I've come to try a simple test.  In our case, coming from a legacy application we're obviously database 1st and our "thick client" runs on SQL Server or Microsoft Access (don't ask).

I'm trying to keep it simple, but I'm purposefully using the field/table mapping features as this would be necessary for our use case.  So, I've created a very cut down version of our "_CUSTOMERS" table as a test.

SQL Server:
  CUSTOMER_ID int
  CUSTOMER_NAME nvarchar(30)
  CUSTOMER_DESC nvarchar(100)

MS Access:
  CUSTOMER_ID long integer
  CUSTOMER_NAME short text (30)
  CUSTOMER_DESC short text (100)

My proof of concept code is as follows:

type
  TCustomer = class(TSQLRecord)
  private
    FName: RawUTF8;
    FDescription: RawUTF8;
  published
    property Name: RawUTF8 index 30 read FName write FName;
    property Description: RawUTF8 index 100 read FDescription write FDescription;
  end;

procedure TForm1.Button1Click(Sender: TObject);
var
  Properties: TSQLDBConnectionProperties;
  Model: TSQLModel;
  Server: TSQLRESTServerDB;
  Customer: TCustomer;
begin
  Properties := TODBCConnectionProperties.Create('','Driver={SQL Server Native Client 11.0};Server=.\SQL2014;Database=TEST;UID=FRS','','');
  try
    Model := TSQLModel.Create([TCustomer]);
    try
      VirtualTableExternalMap(Model, TCustomer, Properties, '_CUSTOMERS')
        .MapField('ID', 'CUSTOMER_ID')
        .MapField('Name', 'CUSTOMER_NAME')
        .MapField('Description', 'CUSTOMER_DESC');

      Server := TSQLRESTServerDB.Create(Model);
      try
        Server.CreateMissingTables;

        Customer := TCustomer.Create;
        try
          Customer.Name := 'Test 1';
          Customer.Description := 'This is the 1st test';
          Server.Add(Customer, true);
        finally
          Customer.Free;
        end;
      finally
        Server.Free;
      end;
    finally
      Model.Free;
    end;
  finally
    Properties.Free;
  end;
end;

The above does work the 1st time (when the _CUSTOMERS table is empty).  However, on subsequent attempts it errors with "EODBCException with message 'TODBCStatement - TODBCLib error: [HY000] [Microsoft][SQL Server Native Client 11.0]Connection is busy with results for another command (0)" when mORMot is calling TSQLRestStorageExternal.EngineLockedNextID() to retrieve the next ID.

Any ideas?

I acknowledge that the above is overkill as it's creating the server etc. every time, but it's a simple test.  Also, in our real use case we'd be providing our own "next id" hook, however I want to be sure that the basics work and I'm stuck at the 1st gate

If I change the connection to point to the MS Access database:

  Properties := TODBCConnectionProperties.Create('','DRIVER={Microsoft Access Driver (*.mdb)};DBQ=E:\TEST.MDB','','');

The above doesn't work at all.  It errors with "EODBCException with message 'TODBCStatement - TODBCLib error: [HY104] [Microsoft][ODBC Microsoft Access Driver]Invalid precision value  (98)" when mORMot is calling TODBCStatement.ExecutePrepared() to execute the insert statement.

Any help would be appreciated.  I'm sure it's something wrong on my part.  I've using the version of mORMot (from 1st October) and I based my test on the "28 - Simple RESTful ORM Server" sample.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: Newbie, stuck at the 1st hurdle

I guess this may be related to this known issue:
http://synopse.info/forum/viewtopic.php … 391#p14391

anj

Member

Registered: 2016-10-05

Posts: 2

Re: Newbie, stuck at the 1st hurdle

Maybe.  From that post though I've tried using OLEDB rather than ODBC and this appears to work perfectly (at least with my simple test).  To be honest I don't know why I picked the ODBC route, our existing application uses OLEDB

  Properties := TOLEDBMSSQLConnectionProperties.Create('.\SQL2014','TEST','FRS','');

or

  Properties := TOLEDBJetConnectionProperties.Create('E:\TEST.MDB', '', '', '');

Thanks for your quick reply!

Another newbie question.  Is there a standard pattern for trapping errors as exceptions aren't raised?  Taking my simple test as an example:

        Customer := TCustomer.Create;
        try
          Customer.Name := 'Test 1';
          Customer.Description := 'This is the 1st test';
          Server.Add(Customer, true);
        finally
          Customer.Free;
        end;

If "Customer.Name" should be unique (and has a unique index on it in the DB), running the above twice should fail on the 2nd attempt.  It does fail (as in the record is not added to the DB), but it fails completely silently!

The SQL generated by mORMot is:

exec sp_executesql N'insert into dbo._CUSTOMERS (CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_DESC) values (@P1,@P2,@P3)',N'@P1 bigint,@P2 nvarchar(6),@P3 nvarchar(20)',2,N'Test 1',N'This is the 1st test'

Running the SQL manually in SSMS errors with:
Msg 2601, Level 14, State 1, Line 4
Cannot insert duplicate key row in object 'dbo._CUSTOMERS' with unique index 'IX__CUSTOMERS'. The duplicate key value is (Test 1).
The statement has been terminated.

By silently, I mean completely silently, even within the IDE no exception is raised

oz

Member

Registered: 2015-09-02

Posts: 98

Re: Newbie, stuck at the 1st hurdle

What's the return value of "Server.Add(Customer, true);"? Not "0"?