mORMot Open Source

dualarrow

Member

From: Australia

Registered: 2018-06-28

Posts: 21

Havin some success with ctree odbc interfacing but...

I'm having some success with getting the ctree odbc drivers to work with mormot. I'm sure I have a way to go yet, but I can declare TSQLRecord structures that seem to be working for creating, updating and retrieving records including joins.

For reference, faircom uses double quotes to escape reserved words and the field names/reserved words are not case sensitive.

My current problem is that in one of our tables we have a field named "desc" which is a reserved word in SQL and the name needs to be mapped (we also have a field named "key" that I map to "ID")

The code looks like this

  lOdbc := TODBCConnectionProperties.Create('ctree','MATER','ADMIN','ADMIN');
  lModel := TSqlModel.Create([TSQL_Bank]);
  VirtualTableExternalRegisterAll(lModel, lOdbc);
  RegisterCtreeTables(lModel, lOdbc);
  VirtualTableExternalMap(aModel,TSQL_Bank ,aODBC,'_Bank' ).MapField('id','"key"');
  VirtualTableExternalMap(aModel, TSQL_Bank, aODBC, '_Bank').MapField('description','"desc"');
  lRest := TSQLRestServerDB.Create(lModel);
  lRest.CreateMissingTables;

and the record looks like this

TSQL_Bank = Class(TSQLRecord)
  private
    FCode: String;
    FWhoCreated: Integer;
    FDateCreated: TDateTime;
    FDescription: String;
  Published
    Property Code: String Read FCode Write FCode;
    Property Description: String Read FDescription Write FDescription;
    Property WhoCreated: Integer Read FWhoCreated Write FWhoCreated;
    Property DateCreated: TDateTime Read FDateCreated Write FDateCreated;
  End;

I seem to always get an ODBC exception saying "Column id not found" but only when Desc is mapped as well as Key.

The SQL prepared in the TODBCStatement.Prepare 1st call is

select col,coltype,width,width,1,0 from syscolumns where tbl = '_bank'

which returns the following in the ctree sql explorer

col          | coltype    | width| width|  1|  0| 

status       | integer    |     4|     4|  1|  0| 
key          | integer    |     4|     4|  1|  0| 
code         | character  |    10|    10|  1|  0| 
desc         | character  |    30|    30|  1|  0| 
whocreated   | integer    |     4|     4|  1|  0| 
datecreated  | timestamp  |     8|     8|  1|  0| 
description  | varchar    |    50|    50|  1|  0| 

8 record(s) selected

Key is there and gets mapped ok (I know this as if I remove the definition for desc it works correctly). desc is there too and as its mapped in the same way as key, I would have thought it would be ok.

The TSQLStatementPrepare gets called multiple times. The result above is for the 1st call. In the 2nd call it has SQL that is as follows

select top 1 ID from _Bank

If I remove the definition for Description, the 2nd call results in SQL that looks like this

select top 1 "key" as ID from _Bank

It looks as if without Description the field is correctly mapped, but somehow loses it's mapping when Description is added.

If I reverse the order of the VirtualTableExternalMap then the 3 calls to Prepare produce the following 2 lines of SQL

select col,coltype,width,width,1,0 from syscolumns where tbl = ''_bank''
select top 1 "key" as ID from _Bank

if I then execute

  lBank := TSQL_Bank.Create(lRest, 10);
  lBank.Free;

I get the following line of SQL in the prepare function

select "key" as ID,Code,Description,WhoCreated,DateCreated from _Bank where "key"=?

It's correctly mapped key, but has not mapped desc properly which should have been "desc as Description"

Are you able to offer suggestions on where I should look to resolve this.