Pages: 1

#1 2014-10-27 16:50:56

fabvit
Member
Registered: 2014-10-27
Posts: 9

CreateMissingTables does not create tables in external FirebirdDB

I've modified the sample Project04Server (HTTP) to use external tables.

My goal is to setup a mini project with a client and a server to explore remote ORM.

But as soon as I call CreateMissingTables, tables are created in the internal SQlite database instead of the external FirebirdDB.

What is wrong with my picture?

The following code is excerpted from the server:

procedure TForm1.FormCreate(Sender: TObject);
var
  FirebirdServerIP: RawUTF8;
  FirebirdFDBFile: RawUTF8;
begin
  FirebirdServerIP := '127.0.0.1';
  FirebirdFDBFile  := 'C:\Users\fabio.SFERACONSULTING\Desktop\Mormot Samples Fabio\04 - HTTP Client-Server\data\MORMOT_CLIENT_SERVER.FDB';

  DeleteFile(UTF8ToString(FirebirdFDBFile));

  // 0. Create an AnyDAC Physical DriverLink for Firebird (or drop a TADPhysIBDriverLink on the form)
  TADPhysIBDriverLink.Create(Application).VendorLib := 'fbclient.dll';

  // 1. Populate the AnyDAC/FireDAC Firebird ConnectionProperties
  fConnectionProps := TSQLDBFireDACConnectionProperties.Create('IB?CreateDatabase=Yes',
                                                               FirebirdServerIP + ':' + FirebirdFDBFile,
                                                               'SYSDBA','masterkey'
                                                              );

  // 2. Connecting has the side effect of creating the database file
  fConnectionProps.ThreadSafeConnection.Connect;

  // 3. Create the Model
  Model := CreateSampleModel;

  // 4. Register all external tables (*BEFORE* calling TSQLRestClientDB/TSQLRestServerDB.Create!)
  VirtualTableExternalRegister(Model, TSQLSampleRecord, fConnectionProps, 'SampleRecord');

  DB := TSQLRestServerDB.Create(Model, ChangeFileExt(paramstr(0), '.db3'), true);

  DB.CreateMissingTables(0);  <======= Tables are not created in external Firebird DB, but instead are created in  to the SQlite database

  Server := TSQLHttpServer.Create('8080',[DB],'+',useHttpApiRegisteringURI);
  Server.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
end;

Offline

#2 2014-10-27 18:04:18

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: CreateMissingTables does not create tables in external FirebirdDB

Is not the main SQLite3 already existing?
You have to start from a void SQlite3 main DB - perhaps even use VirtualTableExternalRegisterAll() and SQLITE_MEMORY_DATABASE_NAME as main database name.

AFAIR initial fConnectionProps.ThreadSafeConnection.Connect is not needed: it will be made the first time the ORM would use the external DB.

Offline

#3 2014-10-28 08:39:51

fabvit
Member
Registered: 2014-10-27
Posts: 9

Re: CreateMissingTables does not create tables in external FirebirdDB

I've modified my code as for your suggestion.
Now the problem is that I get the following exception:

exception class   : ESQLite3Exception
exception message : TSQLSampleRecord: unable to create external missing field SampleRecord.Time - SQL="ALTER TABLE SampleRecord ADD Time BIGINT".

As far as I can see, the firebird database is created but it seems that there is an attempt to alter table SampleRecord  *before* having first created it.
In fact the firebird database is empty after I run the program.

Any ideas?

procedure TForm1.FormCreate(Sender: TObject);
var
  FirebirdServerIP: RawUTF8;
  FirebirdFDBFile: RawUTF8;
begin
  FirebirdServerIP := '127.0.0.1';
  FirebirdFDBFile  := 'C:\data\MORMOT_CLIENT_SERVER.FDB';

  DeleteFile(UTF8ToString(FirebirdFDBFile));

  // 0. Create an AnyDAC Physical DriverLink for Firebird (or drop a TADPhysIBDriverLink on the form)
  TADPhysIBDriverLink.Create(Application).VendorLib := 'fbclient.dll';

  // 1. Populate the AnyDAC/FireDAC Firebird ConnectionProperties
  fConnectionProps := TSQLDBFireDACConnectionProperties.Create('IB?CreateDatabase=Yes',
                                                               FirebirdServerIP + ':' + FirebirdFDBFile,
                                                               'SYSDBA','masterkey'
                                                              );

  // 2. Connecting has the side effect of creating the database file ( *if* 'IB?CreateDatabase=Yes')
  //
  // N.B. AFAIR initial fConnectionProps.ThreadSafeConnection.Connect is not needed:
  //      it will be made the first time the ORM would use the external DB.
  // fConnectionProps.ThreadSafeConnection.Connect;

  // 3. Create the Model
  Model := CreateSampleModel;

  // 4. Register all external tables (*BEFORE* calling TSQLRestClientDB/TSQLRestServerDB.Create!)
  // VirtualTableExternalRegister(Model, TSQLSampleRecord, fConnectionProps, 'SampleRecord');
  VirtualTableExternalRegisterAll(Model, fConnectionProps, false);

  // 5. Create missing tables
  DB := TSQLRestServerDB.Create(Model, SQLITE_MEMORY_DATABASE_NAME, true);
  DB.CreateMissingTables();

  Server := TSQLHttpServer.Create('8080',[DB],'+',useHttpApiRegisteringURI);
  Server.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
end;

Last edited by fabvit (2014-10-28 08:41:22)

Offline

#4 2014-10-28 14:39:47

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: CreateMissingTables does not create tables in external FirebirdDB

Just ignore this exception, which is run only at startup (due to some missing metadata from FireDac), and is catched by the code.
AFAIR it doe not stop the process.

OR perhaps I did miss something, but I'm not able to reproduce the issue here.

Offline

#5 2014-10-28 14:51:09

fabvit
Member
Registered: 2014-10-27
Posts: 9

Re: CreateMissingTables does not create tables in external FirebirdDB

>Just ignore this exception,

but... no tables are created in the external firebird database after calling CreateMissingTables

Do you think the external tables will be created when I will add some data to them from the client?

Offline

#6 2014-10-28 14:54:26

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: CreateMissingTables does not create tables in external FirebirdDB

No, the tables are created at startup only.

But I'm not able to reproduce the issue here.
I just tried to use FireBird via FireDAC, and it worked as expected - see http://synopse.info/fossil/finfo?name=S … rebird.dpr

Perhaps try to create the DB first, then close the connection, and re create a new connection properties without the "CreateDatabase=Yes" parameter.

Offline

#7 2014-10-28 15:04:00

fabvit
Member
Registered: 2014-10-27
Posts: 9

Re: CreateMissingTables does not create tables in external FirebirdDB

Ok thanks: I will try to follow your suggestion but I will first try to update from GitHub my local  mormot repository to a more recent version than the one I've installed on my pc.
As a side note, I'm using the last stable release of AnyDAC not FireDAC and this may be something that I've to further investigate.

I'll surely let you know my findings.

thank you!

fabio

Offline

#8 2014-10-28 16:56:27

fabvit
Member
Registered: 2014-10-27
Posts: 9

Re: CreateMissingTables does not create tables in external FirebirdDB

Hi Arnaud: finally I've founded the problem!!!!

In unit SampleData.pas is defined the TSQLSampleRecord class.

This class had a property defined as follow:

property Time: TModTime read fTime write fTime;


Guess what? "Time" is a reserved word in Firebird, hence when CreateMissingTables() is called there is an error creating a table with a column that has a reserved word name!

I changed the above line as follow and voila the problem disappeared.

property ModTime: TModTime read fModTime write fModTime;

May be it would be useful to change the SampleData.pas in the public mormot repository to avoid other user the same problem when they try to use external tables *AND* Firebird.

thank you!

fabio

Offline

#9 2014-10-28 19:31:40

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: CreateMissingTables does not create tables in external FirebirdDB

I have created a feature request for this.
See http://synopse.info/fossil/tktview/7fbbd53966

IMHO such fields should be auto-mapped when used on an external DB.
For instance, "Time" property name may be mapped as "_Time" column name for FireBird.

Offline

#10 2014-10-28 19:44:36

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: CreateMissingTables does not create tables in external FirebirdDB

Note:

Zeos provides a huge ',' delimited String for SQLKeyWords in any kind:

IZConnection.GetMetadata.GetDatabaseInfo.GetSQLKeywords: String;

So you could prevent such issues...

Zeos FireBird wrote:

{**
  Gets a comma-separated list of all a database's SQL keywords
  that are NOT also SQL92 keywords.
  @return the list
}
function TZInterbase6DatabaseInfo.GetSQLKeywords: string;
begin
  Result := 'ACTIVE,AFTER,ASCENDING,BASE_NAME,BEFORE,BLOB,' +
    'CACHE,CHECK_POINT_LENGTH,COMPUTED,CONDITIONAL,CONTAINING,' +
    'CSTRING,DATABASE,RDB$DB_KEY,DEBUG,DESCENDING,DO,ENTRY_POINT,' +
    'EXIT,FILE,FILTER,FUNCTION,GDSCODE,GENERATOR,GEN_ID,' +
    'GROUP_COMMIT_WAIT_TIME,IF,INACTIVE,INPUT_TYPE,INDEX,' +
    'LOGFILE,LOG_BUFFER_SIZE,MANUAL,MAXIMUM_SEGMENT,MERGE, MESSAGE,' +
    'MODULE_NAME,NCHAR,NUM_LOG_BUFFERS,OUTPUT_TYPE,OVERFLOW,PAGE,' +
    'PAGES,PAGE_SIZE,PARAMETER,PASSWORD,PLAN,POST_EVENT,PROTECTED,' +
    'RAW_PARTITIONS,RESERV,RESERVING,RETAIN,RETURNING_VALUES,RETURNS,' +
    'SEGMENT,SHADOW,SHARED,SINGULAR,SNAPSHOT,SORT,STABILITY,STARTS,' +
    'STARTING,STATISTICS,SUB_TYPE,SUSPEND,TRIGGER,VARIABLE,RECORD_VERSION,' +
    'WAIT,WHILE,WORK,VALUE,POSITION,USER,CURRENCY,OPTION,DATE,START,END,USER,' +
    'READ,PARENT,TYPE'+
    {Ticket #63: http://sourceforge.net/p/zeoslib/tickets/62/}
    ',DEC,TIME,MIN,MAX'+
    {FireBird 3.0}
    ',DETERMINISTIC,OVER,RETURN,SCROLL,SQLSTATE';
end;

Last edited by EgonHugeist (2014-10-28 19:47:51)

Offline

#11 2014-10-28 20:24:48

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: CreateMissingTables does not create tables in external FirebirdDB

Nice!
smile

Offline

#12 2014-10-29 08:43:17

fabvit
Member
Registered: 2014-10-27
Posts: 9

Re: CreateMissingTables does not create tables in external FirebirdDB

Great!
Thank you!

fabio

Offline

#13 2014-11-11 17:42:13

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: CreateMissingTables does not create tables in external FirebirdDB

I've added a new TSQLModelRecordPropertiesExternal.MapAutoKeywordFields method.
See http://synopse.info/fossil/info/c6453db6e2

The corresponding documentation is available at
http://synopse.info/files/html/Synopse% … #TITLE_187

Offline

#14 2014-11-11 20:21:50

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: CreateMissingTables does not create tables in external FirebirdDB

@ab

After last changes:

12 - SynDB Explorer
Error: TSQLConnection.Database field name conflicts with a SQL keyword
on startup.

mORMotr 1.18.496 Delphi XE2-32

Michal

Offline

#15 2014-11-12 09:20:05

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: CreateMissingTables does not create tables in external FirebirdDB

We have let SQL keywords check be a little less restrictive in TSQLRecord, so that SynDBExplorer runs without exception.
See http://synopse.info/fossil/info/f2c2e35a50

Thanks for the feedback!

Offline

Pages: 1

Board footer

Powered by FluxBB