Converting Sample 28 REST ORM Server from Postgre to Firebird Server

msgendodoc@gmail.com · 2018-02-03 14:32:08

Hi,

Converting Sample 28 - REST ORM Server from Postgre to use the Firebird 2.1 Server with FBClient.dll 32 bit. I am used to Firebird and want to use it.

Using Delphi 10.1.2 with output exe a Win32 bit file, so bitness should match. The Firebird server is running.

OK, so I've worked out that the Postgre database type and set up parameters are set in the aProps: TSQLDBConnectionProperties;

I have found SynDBFirebrid, put it in the uses clause, and changed aProps to a TSQLDBFirebirdConnectionProperties class instead. Is this the right thing to do?

I have the following code in the RestServer.pas at about line 32

  aProps := TSQLDBFirebirdConnectionProperties.Create(ToUTF8('Firebird'),
  ToUTF8('C:\MG\MGSoftware\MORMOT\Source\mORMot-master\SQLite3\Samples\28 - Simple RESTful ORM Server\Data\MGTestDB\TestFBDB.fdb'),
  ToUTF8('SYSDBA'), ToUTF8('masterkey'));

instead of

// aProps := TODBCConnectionProperties.Create('','Driver=PostgreSQL Unicode'+
// {$ifdef CPU64}'(x64)'+{$endif}';Database=postgres;'+
// 'Server=localhost;Port=5433;UID=postgres;Pwd=postgresPassword','','');

I have converted the string st UTF8 type

I have given the database file path and filename - but of course this file has not yet been created.

The code compiles and runs and the expected error that it can't find the fbclient.dll comes up as per the SynDBFirebird.pas line 1018 code -as this is not the embedded Firebird version. But then it should look for the fbclient.dll as per line 1020. But it does not and raises the further error that invalid gds32.dll and cant find the client library. The fbclient.dll is in the project source directory alongside the server exe, which is also in the

As a work around test I have tried the embedded Firebird (i.e. all the local Firebird embedded files in the project source directory, but with the same errors, it doesnt work either)

Any guidance? What next to try? How to provide the Firebird library file fbclient.dll to the mORMot sever?

Many thanks,

Martin

EMartin · 2018-02-03 16:03:33

We use Firebird with Zeos (SynDBZeos.pas) and tested with heavy load and works very fine.

msgendodoc@gmail.com · 2018-02-04 01:39:10

Good to hear Esteban. I know that mORMot will be the best framework for my software as a service - just a question of the learning curve and getting it to work.

Please tell me more, point me to relevant links. So do you also need to use SynDBFirebird.pas as well as SynDBZeos.pas, or just one or the other? Do you need to download anything from Zeos or is everything you need in the SynDBZeos.pas unit? If using Zeos and if you do need to download Zeos files then do they need to be installed as components into the IDE for Delphi and Lazarus or are the Zeos directories just put in the IDE/project paths and the relevant pas put in the uses clause, as done with mORMot? Zeos has an option of plain or not plan. Which to use?

Many thanks for your help.

Martin

msgendodoc@gmail.com · 2018-02-04 04:48:16

OK everyone,

Some progress

I gave up on SynDBFirebird.pas, and have used SynDBZeos.pas

- I downloaded the Zeos database objects v 7.2 and put its directories and subdirectories in the Delphi 10.1.2 IDE path. I have not installed them in the Delphi IDE (Note that Zeos v 7.2 is 10 x faster than 7.1, says Arnaud in the docs)
- I put SynDBZeos in the users clause
- I changed aProps: TSQLDBFirebirdConnectionProperties to TSQLDBZEOSConnectionProperties
- I declared ADBCon: TSQLDBConnection;
- I replaced my old fbclient.dll for Firebird 2.1 with the fbclient.dll for Firebird 3.01 (I know mORMot is designed and tested with Firebird 2.5 but v 3 still worked)
- I used the following to create and define the database connection

  aProps := TSQLDBZeosConnectionProperties.Create(TSQLDBZEOSConnectionProperties.URI(dFirebird,ToUTF8(''),
  'fbclient.dll'),
  ToUTF8('C:\MG\MGSoftware\MORMOT\Source\mORMot-master\SQLite3\Samples\28 - Simple RESTful ORM Server\Data\TestFBDB.fdb'),
  ToUTF8('SYSDBA'), ToUTF8('masterkey'));

  ADBCon := aProps.NewConnection;

It worked in Sample 28. It found the fbclient.dll library, it was compatible, and it then recognized my database file was not there, and created it, albeit spitting error messages all the way. But it worked.

Its taken me about 12 hours of work to get this far!! Let's hope the mORMot learning curve isn't quite so steep from here on in...

Wonder if I can get it to work in Lazarus too?

Arnaud, is it that SynDBFirebird.pas is not yet functional? can it be used at all?

Cheers,

Martin

ab · 2018-02-04 09:24:40

msgendodoc@gmail.com wrote:

Arnaud, is it that SynDBFirebird.pas is not yet functional? can it be used at all?

No, it is not functional, it is an experimental/unfinished unit, which does not appear in the documentation.
If you search in https://synopse.info/files/html/Synopse … 01.18.html you will find only Zeos, ODBC, FireDAC or UniDAC access.
Just look at TSQLDBFirebirdStatement: a lot of methods are just void!

I've made it explicit in the unit code, to avoid confusion.
See https://synopse.info/fossil/info/2582276b70

Sorry for the problem.

msgendodoc@gmail.com · 2018-02-04 10:00:49

Hi Arnaud,

Thanks for clarifying that the SynDBFirebird.pas unit was non functional.

The SynDBZeos.pas unit is working now.

But my success was short lived. On running the server again I get a Firebird SQL error "unsuccessful metadata update STORE RDB$INDEX_5 Error code: -607. can't format message 13:393 - message file ....SQL: CREATE UNIQUE DESC INDEX NDXPersonID on PersonID"

i.e. its trying to set up again the index for the Person table, and of course there can not be more than one index set up per table per field, the field ID in this case.

How to fix this? I have isolated the problem to the call aRestServer.CreateMissingTables; which as you might imaging will try to do what it says. But if the table is already there then why is it trying to recreate it, or try to make a replacement index for it? In CreateMissingTables in MormotSQLLite3.pas at line 1032 one can set Options: TSQLInitializeTableOptions which are declared in Mormot.pas at line 6807 as...

TSQLInitializeTableOption = (
itoNoAutoCreateGroups, itoNoAutoCreateUsers,
itoNoCreateMissingField,
itoNoIndex4ID, itoNoIndex4UniqueField,
itoNoIndex4NestedRecord, itoNoIndex4RecordReference,
itoNoIndex4TID, itoNoIndex4RecordVersion);

but none of these seem to be appropriate as we do need an index on ID or TID database fields in the first run when first creating the database. btw FlameRobin the Firebird open source database admin tools shows the Person table has been created, there is no data yet, but there are two indices on the ID field, one ascending and one descending. The DDL is

SET STATISTICS INDEX NDXPERSONID
SET STATISTICS INDEX RDB$PRIMARY1

Any ideas how to fix this database index error? Sorry to keep asking for help - I am determined to get mORMot up and running, so bear with me, and my time available is not enough with all these problems.

Cheers,

camu72 · 2018-02-04 12:49:43

HI msgendodoc, i was a similar problem, and if i well remeber, when the table was at lest one record (filled with a external administrator ), the issue stop. This not resolve the problem, but is something that help to trace or resolve the problem.

camu72 · 2018-02-04 12:54:10

Maybe the problem is the Firebird version... you with fb3 and I with fb1.5, and the ZeosLib is for FB2.5.... colud be a sintax problem waiting some intruction only available in FB2.5...

msgendodoc@gmail.com · 2018-02-05 11:51:23

Thx for the info camu72. But I need to have an empty table remain functional as table creation is part of the ORM use case so would like to fix this. How and where is this occurring. Any ideas Arnaud?

EMartin · 2018-02-05 13:14:12

I think that you have existing primary key in the table, mORMot need a primary key and then do you need map existing PK to internal mORMOt PK:

VirtualTableExternalMap(<model>, <TSQLTable>, <TSQLDBConnectionPropertie>, <Table name>).MapFields('ID', <External PK field>).SetOptions([rpmAutoMapKeywordFields, rpmNoCreateMissingTable, rpmNoCreateMissingField]);

msgendodoc@gmail.com · 2018-02-06 06:18:54

Hi Esteban,

The table was created from nothing by mORMot and this was fine on the first run, but then on a second run of the mORMot server in Sample 28, it has tried to overwrite its own primary key field and index in its own database file. Surely it should automatically check which fields, including the primary key indexed fields are already present and not try to recreate them? Also having no data in the table should not be a problem either and I should not have to write a datum with an external database GUI to get mORMot working. Will look at it again tomorrow and see how VirtualTableExternalMap set options can be relevant, and also trace through the mORMot code to where it checks for pre-existing fields and indexes.

Cheers,

Martin

msgendodoc@gmail.com · 2018-02-06 07:58:51

OK fixed it -

In RestServer.pas on ln 63 as the sevrer sets up the TPerson table, changed

aRestServer.CreateMissingTables;

to

aRestServer.CreateMissingTables(0, [itoNoIndex4ID, itoNoIndex4TID]);

So no ID or TID primary field index is created at all. Actually Firebird always creates an index for the primary key field anyway, so mORMot odes not need to create a new one.

Is this OK, or have i missed something?

But what does the first parameter user_version = 0 mean? Is this OK?

Cheers,

Martin

ab · 2018-02-06 08:29:19

Sounds fine - confirmed by the method documentation.

mORMot Open Source

#1 2018-02-03 14:32:08

Converting Sample 28 REST ORM Server from Postgre to Firebird Server

#2 2018-02-03 16:03:33

Re: Converting Sample 28 REST ORM Server from Postgre to Firebird Server

#3 2018-02-04 01:39:10

Re: Converting Sample 28 REST ORM Server from Postgre to Firebird Server

#4 2018-02-04 04:48:16

Re: Converting Sample 28 REST ORM Server from Postgre to Firebird Server

#5 2018-02-04 09:24:40

Re: Converting Sample 28 REST ORM Server from Postgre to Firebird Server

#6 2018-02-04 10:00:49

Re: Converting Sample 28 REST ORM Server from Postgre to Firebird Server

#7 2018-02-04 12:49:43

Re: Converting Sample 28 REST ORM Server from Postgre to Firebird Server

#8 2018-02-04 12:54:10

Re: Converting Sample 28 REST ORM Server from Postgre to Firebird Server

#9 2018-02-05 11:51:23

Re: Converting Sample 28 REST ORM Server from Postgre to Firebird Server

#10 2018-02-05 13:14:12

Re: Converting Sample 28 REST ORM Server from Postgre to Firebird Server

#11 2018-02-06 06:18:54

Re: Converting Sample 28 REST ORM Server from Postgre to Firebird Server

#12 2018-02-06 07:58:51

Re: Converting Sample 28 REST ORM Server from Postgre to Firebird Server

#13 2018-02-06 08:29:19

Re: Converting Sample 28 REST ORM Server from Postgre to Firebird Server

Board footer