mORMot Open Source

wienani

Member

Registered: 2012-08-31

Posts: 29

Problem no such table in ExecureList

Hallo ab, I tried two code

VirtualTableExternalRegister(FModel, TmoSysRole, AConProps, 'SYS_ROLE');
...
Result := FDatabase.MultiFieldValues(TmoSysRole, AFieldsOrder, AWhereClause);

Success

VirtualTableExternalRegister(FModel, TmoSysRole, AConProps, 'SYS_ROLE');
...
Result := FDatabase.ExecuteList([TmoSysRole],
    'SELECT * FROM SYS_ROLE'
);

Fail with error: no such table: SYS_ROLE, look like that sqllite error message

Why ? table SYS_ROLE is already in database.
I'm using SynDBZeos with Firebird

Thank you

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,657

Website

Re: Problem no such table in ExecureList

FDatabase accesses the "internal" SQLite3 engine, not the external engine.
Therefore, from the "internal" engine, the table is a virtual table, which name is probably MOSYSROLE (from TmoSysRole).

But you should better not use such SQL statement, but rely on ORM methods, which will also faster, since they are able to detect that the table is external, therefore will directly access FireBird, and bypass the internal SQlite3 virtual table.

wienani

Member

Registered: 2012-08-31

Posts: 29

Re: Problem no such table in ExecureList

FDatabase accesses the "internal" SQLite3 engine, not the external engine.
Therefore, from the "internal" engine, the table is a virtual table, which name is probably MOSYSROLE (from TmoSysRole).

But you should better not use such SQL statement, but rely on ORM methods, which will also faster, since they are able
to detect that the table is external, therefore will directly access FireBird, and bypass the internal SQlite3 virtual table.

Actually i do want make complex query join, as you suggest from several post ini this forum
to use ExecuteList, but you are right i just know that virtual table create using class name TmoSysRole --> MOSYSROLE

But after i change to TSysRole, then resulting AV Error for join query

  Result := FDatabase.ExecuteList([TSYS_ROLE, TSYS_USER],
    'SELECT a.ID, a.ROLENAME, a.POLICY, a.NOTES, a.CREATEDATE, a.EDITDATE, ' +
           'b.USERNAME AS CREATENAME, c.USERNAME AS EDITNAME ' +
    'FROM SYS_ROLE a LEFT JOIN SYS_USER b ON a.CREATEUSERID=b.ID ' +
                    'LEFT JOIN SYS_USER c ON a.EDITUSERID=c.ID'
  );

Is there another function like ExecuteList that directly access to Firebird ? because i want to use EXECUTE BLOCK
feature in firebird.

Thank you.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,657

Website

Re: Problem no such table in ExecureList

For such a query, do not use FDatabase, but the TSQLDBZEOSConnectionProperties instance used to initialize the external table.

You have full and direct access to the FireBird engine with it, via the NewThreadSafeStatementPrepared() method.
The statement is able to return the whole JSON content from ISQLDBRows.FetchAllAsJSON().

Or you can use FDatabase.StaticDataServer[TmoSysRole].ExecuteList() which should work in your case, with an "pure Firebird" SQL statement.

wienani

Member

Registered: 2012-08-31

Posts: 29

Re: Problem no such table in ExecureList

Thank You ab..

wienani

Member

Registered: 2012-08-31

Posts: 29

Re: Problem no such table in ExecureList

For such a query, do not use FDatabase, but the TSQLDBZEOSConnectionProperties instance used to initialize the external table.

You have full and direct access to the FireBird engine with it, via the NewThreadSafeStatementPrepared() method.
The statement is able to return the whole JSON content from ISQLDBRows.FetchAllAsJSON().

Or you can use FDatabase.StaticDataServer[TmoSysRole].ExecuteList() which should work in your case, with an "pure Firebird" SQL statement.

I tried both and the result same and not handling field alias (sql AS statement).

Result "b.USERNAME AS CREATENAME, c.USERNAME AS EDITNAME " is USERNAME and USERNAME_1 not CREATENAME and EDITNAME

Is there anything that can handle AS statement ?

Thank you

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,657

Website

Re: Problem no such table in ExecureList

Sounds like an issue at Zeos level...

wienani

Member

Registered: 2012-08-31

Posts: 29

Re: Problem no such table in ExecureList

Sounds like an issue at Zeos level...

I found this in SynDBZeos.ExecutePrepared

   ...

    for i := 1 to fResultInfo.GetColumnCount do begin
      name := fResultInfo.GetColumnName(i);   --> change to GetColumnLabel(i) then OK
      if name='' then
        name := fResultInfo.GetColumnLabel(i);
      PSQLDBColumnProperty(fColumn.AddAndMakeUniqueName(StringToUTF8(name)))^.
        ColumnType := Props.TZSQLTypeToTSQLDBFieldType(fResultInfo.GetColumnType(i));
    end;
   ...

if I change to GetColumLabel(i) then result is right for alias/label.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,657

Website

Re: Problem no such table in ExecureList

You are perfectly right.

Now "AS" renamed columns be retrieved for Zeos/ZDBC access layer.
See http://synopse.info/fossil/info/21cd17f204

I want missleaded by the ZEOS method naming... difficult to guess what it does with so little documentation.
Thanks for the feedback.