#1 2021-05-04 12:57:50

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

Offline

#2 2021-05-04 13:08:00

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
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.

Offline

#3 2021-05-04 13:19:11

alpinistbg
Member
Registered: 2014-11-12
Posts: 124

Re: Exception with external DB access

ab wrote:

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.

Offline

#4 2021-05-04 14:23:55

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 330

Re: Exception with external DB access

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

Offline

#5 2021-05-04 14:31:34

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
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.

Offline

#6 2021-05-04 14:52:05

alpinistbg
Member
Registered: 2014-11-12
Posts: 124

Re: Exception with external DB access

danielkuettner wrote:

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

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

ab wrote:

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.

ab wrote:

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 sad

Thanks for your replies,

Offline

#7 2021-05-04 17:17:47

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
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.

Offline

#8 2021-05-05 09:13:54

alpinistbg
Member
Registered: 2014-11-12
Posts: 124

Re: Exception with external DB access

ab wrote:

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?

Offline

Board footer

Powered by FluxBB