mORMot Open Source

alpinistbg

Member

Registered: 2014-11-12

Posts: 124

Exception with external DB access

Hello,

I have a nicely working  app with sqlite, but when I registered it to work with external MSSQL DB (via ODBC) it throws an exception and I can't figure what can be the reason.

ESQLite3Exception {"ErrorCode":1,"SQLite3ErrorCode":"secERROR","Message":"Error SQLITE_ERROR (1) [SELECT DonorKey FROM DataJournal WHERE DonorSiteIdentifier=? AND TableIdentifier=? ORDER BY ID DESC LIMIT 1] using 3.28.0 - no such column: ID, extended_errcode=1"} [] at ...

It says "no such column: ID, extended_errcode=1" but the column ID is there.

Here is how it is registered:

procedure RegisterExtDBTables(AModel: TSQLModel; AConnString: AnsiString);
var
  T: TSQLRecordClass;
  I: Integer;
begin
  ModelProps := TODBCConnectionProperties.Create('', AConnString, '', '');

  for I := Low(AModel.Tables) to High(AModel.Tables) do
  begin
    T := AModel.Tables[I];
    VirtualTableExternalRegister(AModel, T, ModelProps,
      'XT' + GetDisplayNameFromClass(T));
    AModel.Props[T].ExternalDB.MapAutoKeywordFields;
  end;
end;

 ...
  ConnStr := Format('DRIVER={SQL Server Native Client 10.0};' +
      'SERVER=%s;DATABASE=%s;UID=%s;PWD=%s;WSID=%s;APP=%s', [AServer, ADbName,
      AUser, APwd, ServerDB.Site, ExtractFileName(ParamStr(0))]);

  ExtModel := TSQLModel.Create(ClientModel);
  RegisterExtDBTables(ExtModel, ConnStr);
 ...

  Svr := TSQLRestServerDB.Create(ExtModel, 'epark_ext_work.ext.db3');
 ...

Here is the row where the exception rises:

OneFieldValue(TSQLDataJournal, 'DonorKey',
      'DonorSiteIdentifier=? AND TableIdentifier=? ORDER BY ID DESC', [],
      [ADId, TableIndex], Result);

I am on Win10 with Lazarus 1.9.0 r63034 FPC 3.1.1 i386-win32-win32/win64

Best regards,

Below is the log file.
https://gist.github.com/synopse/3bda4a9 … 07544d6512

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Exception with external DB access

Please follow the forum rules and don't post huge piece of code or log into the forum threads.

alpinistbg

Member

Registered: 2014-11-12

Posts: 124

Re: Exception with external DB access

Please follow the forum rules and don't post huge piece of code or log into the forum threads.

Apologize for that. Just thought the log can be helpful but I didn't found other way to attach it.

danielkuettner

Member

From: Germany

Registered: 2014-08-06

Posts: 357

Re: Exception with external DB access

Did you call MapField('ID', 'YourExternalPrimaryKeyIDField')?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Exception with external DB access

OK - so just try to replace ID with RowID in your query.

SQLite3 doesn't have any ID column in its virtual table.
It only knows RowID.

Please also ensure you have the latest version of the source code and the SQLite 3 static files, because your SQlite3 version seems outdated.

alpinistbg

Member

Registered: 2014-11-12

Posts: 124

Re: Exception with external DB access

Did you call MapField('ID', 'YourExternalPrimaryKeyIDField')?

Actually, I relied on mORMot itself to create all needed tables and fields.

OK - so just try to replace ID with RowID in your query.

SQLite3 doesn't have any ID column in its virtual table.
It only knows RowID.

But how can I make it "transparent"? I would like to use it with SQLite3 or MSSQL alternatively. Is there some way to overcome that? FYI, I use 'ORDER BY ID DESC' to get the most recent row.

Please also ensure you have the latest version of the source code and the SQLite 3 static files, because your SQlite3 version seems outdated.

I browsed through the commit hstory for hours before posting, but didn't found changes related to that.
I'd prefer not to update too frequently, sometimes regressions broke out

Thanks for your replies,

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Exception with external DB access

I don't know about your source code, but the SQlite3 static binaries are logged as 3.28.0 which is more than 2 years ago.
https://www.sqlite.org/releaselog/3_28_0.html

RowID will work with SQlite3 or MSSQL.

alpinistbg

Member

Registered: 2014-11-12

Posts: 124

Re: Exception with external DB access

OK - so just try to replace ID with RowID in your query.

SQLite3 doesn't have any ID column in its virtual table.
It only knows RowID.

Does that mean I can't do:

SQL := 'SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.LinkID = Table2.ID WHERE ...';
TSQLRestClientURI.URI('root', 'GET', @Resp, Nil, @SQL); 

  -- or --

R := TSQLTable2.Create(SQLRestClientURI, 'ID=?', [AnID]);

  -- or even --

R := TSQLTable2.Create(SQLRestClientURI, AnID); // perhaps this will use RowID?

As long as the tables were externally registered?