mORMot Open Source

mingda

Member

Registered: 2013-01-04

Posts: 121

External DB Problem

I want add data in dbgrid, so I change Sample04 Client, such change is ok,

procedure TForm1.Button1Click(Sender: TObject);
var
  SQLTable: TSQLTableJSON;
begin
  SQLTable := Database.MultiFieldValues(TSQLSampleRecord, '', '1=2');
  if Assigned(SQLTable) then
  try
    FreeAndNil(fDataSet);
    fDataSet := TSQLTableToDataSet(Self, SQLTable, Database);
    DataSource1.DataSet := fDataSet;
  finally
    SQLTable.Free;
  end;
end;

But When I Change Sample04 Server to use external MS SQLSerer, such change:

procedure TForm1.FormCreate(Sender: TObject);
begin
  MSSQLConnectionProperties := TOleDBMSSQLConnectionProperties.Create('localhost', 'TestDB', '', '');
  Model := CreateSampleModel;
  VirtualTableExternalRegisterAll(Model, MSSQLConnectionProperties);
  DB := TSQLRestServerDB.Create(Model,ChangeFileExt(paramstr(0),'.db3'));
  DB.CreateMissingTables(0);
  Server := TSQLHttpServer.Create('8080',[DB],'+',useHttpApiRegisteringURI);
  Server.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
end;

When the server run, I can see SampleRecord table in the MSSQL DB,
but when run the change Client, the Client dataset can't create duty server generate such exeception:

---------------------------
Debugger Exception Notification
---------------------------
Project Project04Server.exe raised exception class ESQLite3Exception with message 'no such column: ID'.
---------------------------
Break   Continue   Help   
---------------------------

Thanks!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: External DB Problem

Please ensure you got the LATEST 1.18 source code.
We fixed this issue some days ago, AFAIR.

mingda

Member

Registered: 2013-01-04

Posts: 121

Re: External DB Problem

Client If change :

  SQLTable := Database.MultiFieldValues(TSQLSampleRecord, '', '1=2'); to 
  SQLTable := Database.MultiFieldValues(TSQLSampleRecord, '', 'ID=?', [0]);

if Use TSQLRestServerDB then also ok, but after change server to External DB

  VirtualTableExternalRegisterAll(Model, MSSQLConnectionProperties);

then Client return SQLTable has no column info, then TSQLTableToDataSet will
generate such exception:

---------------------------
Debugger Exception Notification
---------------------------
Project Project04Client.exe raised exception class EDatabaseError with message 'No fields defined.  Cannot create dataset'.
---------------------------
Break   Continue   Help   
---------------------------

Another problem, if first test use sqlite db, after some test, then change to External DB,
seems should manual delete the *.db3 file, from External DB to sqlite db, also need manual
delete the *.db3 file, else will generate such

---------------------------
Debugger Exception Notification
---------------------------
Project Project04Server.exe raised exception class ESQLite3Exception with message 'no such module: External'.
---------------------------
Break   Continue   Help   
---------------------------

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: External DB Problem

Please ensure you got the LATEST 1.18 source code.

We fixed the "unknown column ID" issue some days ago, AFAIR.

About the fact of changing one table type from internal to external, this is due to how SQLite3 works.
A VIRTUAL table need to be created to access the external DB.
If a REAL table with the same name already exists, it won't create the virtual table.
Just DROP the table to ensure the virtual table will be created as expected.

mingda

Member

Registered: 2013-01-04

Posts: 121

Re: External DB Problem

Please ensure you got the LATEST 1.18 source code.
We fixed this issue some days ago, AFAIR.

Yes, since you update very quickly, I alway update before I ask question, use the 35cd8533cb38acd4 build,
download 11/7

thanks

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: External DB Problem

Your .db3 file sounds broken.
As I stated above, you can not change the DB layout from internal to external without dropping any previous internal table.

You have to stick with external tables.
Then you can use an external SQLite3 table, and switch to other DB engine when needed.

mingda

Member

Registered: 2013-01-04

Posts: 121

Re: External DB Problem

Your .db3 file sounds broken.
As I stated above, you can not change the DB layout from internal to external without dropping any previous internal table.

You have to stick with external tables.
Then you can use an external SQLite3 table, and switch to other DB engine when needed.

After my post, I see your post, this is not important, only I learn found,  but this two method,

SQLTable := Database.MultiFieldValues(TSQLSampleRecord, '', '1=2'); //or
SQLTable := Database.MultiFieldValues(TSQLSampleRecord, '', 'ID=?', [0]);

i want return a valid JSON result to create a valid dataset:

fDataSet := TSQLTableToDataSet(Self, SQLTable, Database);

for sqlite3 db, no problem, but for external DB, the two method all failed,
the first method return a nil SQLTable, the second return a SQLTable has no column info.

thanks!

mingda

Member

Registered: 2013-01-04

Posts: 121

Re: External DB Problem

after some debug,

when client run:
SQLTable := Database.MultiFieldValues(TSQLSampleRecord, '', '1=2');

in TSQLRestServer.InternalAdaptSQL

      if (result<>nil) and not result.AdaptSQLForEngineList(SQL) then
        // complex request will use SQlite3 virtual engine module
        result := nil;

here SQL is
'SELECT ID,Time,Name,Question FROM SampleRecord WHERE 1=2;'

when call result.AdaptSQLForEngineList(SQL),  result is false,
so will pass the SQL to TSQLRestServer.EngineList to do this SQL,
then raised exception class ESQLite3Exception with message 'no such column: ID'.
so client will return a nil TSQLTableJSON,

but problem not only this, if comment the result := nil;

        // complex request will use SQlite3 virtual engine module
//        result := nil;

force SQL to deal with TSQLRestServerStaticExternal.EngineList

function TSQLRestServerStaticExternal.EngineList(const SQL: RawUTF8;
  ForceAJAX: Boolean; ReturnedRowCount: PPtrInt): RawUTF8;
var Stmt: ISQLDBStatement;
begin
  if ReturnedRowCount<>nil then
    raise ESQLDBException.Create('TSQLRestServerStaticExternal.EngineList(ReturnedRowCount<>nil)');
  Stmt := PrepareInlinedForRows(SQL);
  if Stmt=nil then
    result := '' else
    Stmt.ExecutePreparedAndFetchAllAsJSON(ForceAJAX or (not NoAJAXJSON),result);
end;

here call result will be '[]'#$A, not the expect
'{"fieldCount":4,"values":["ID","Time","Name","Question"],"rowCount":0}'#$A

so client will return a TSQLTableJSON but with no column info

when client run:
SQLTable := Database.MultiFieldValues(TSQLSampleRecord, '', 'ID=?', [0]);

this will pass the SQL
'SELECT ID,Time,Name,Question FROM SampleRecord WHERE ID=:(0):;'
to TSQLRestServerStaticExternal.EngineList

function TSQLRestServerStaticExternal.EngineList(const SQL: RawUTF8;
  ForceAJAX: Boolean; ReturnedRowCount: PPtrInt): RawUTF8;

but this return the same result '[]'#$A, so client will return a TSQLTableJSON but with no column info,
then when client create dataset will  raise such 'No fields defined.  Cannot create dataset' exception.

Ab, can you look at this, if need I send you testcase, please make a note.

thanks!

mingda

Member

Registered: 2013-01-04

Posts: 121

Re: External DB Problem

about External DB not return column info, after debug, when use sqlite, in TSQLRequest.Execute
after  step, there has

function TSQLRequest.Execute(aDB: TSQLite3DB; const aSQL: RawUTF8; JSON: TStream;
  Expand: boolean=false): PtrInt;
...
    if (result=0) and W.Expand then begin    //  ****** deal with RowCount = 0
      // we want the field names at least, even with no data: we allow RowCount=0 
      W.Expand := false; //  {"FieldCount":2,"Values":["col1","col2"]}
      W.CancelAll;
      for i := 0 to FieldCount-1 do
        W.ColNames[i] := sqlite3.column_name(Request,i);
      W.AddColumns;
    end;
    W.EndJSONObject(0,result);

when use External DB, in TSQLDBStatement.FetchAllToJSON
after step, there is no code deal with RowCount=0,

function TSQLDBStatement.FetchAllToJSON(JSON: TStream; Expanded: boolean;
  DoNotFletchBlobs: Boolean=false): PtrInt;
...
    // write rows data
    while Step do begin
      ColumnsToJSON(W,DoNotFletchBlobs);
      W.Add(',');
      inc(result);
    end;
    //  ****** there is no code deal with RowCount = 0
    W.EndJSONObject(0,result);

so when rowcount=0, the column info is not write.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: External DB Problem

Nice idea.

TSQLDBStatement.FetchAllToJSON will now add column names (in non-expanded JSON format) if no data row is returned - just like TSQLRequest.Execute
See http://synopse.info/fossil/info/44ef602e2c

Thanks for the feedback!

mingda

Member

Registered: 2013-01-04

Posts: 121

Re: External DB Problem

After test, there has some little problem, for SQlite db,

client after call  SQLTable := Database.MultiFieldValues(TSQLSampleRecord, '', '1=2');
the SQLTable result is

((TSQLSampleRecord), 'SELECT ID,Time,Name,Question FROM SampleRecord WHERE 1=2;', (), 0, 0, 4, $FB32C8, (), (), (), (), [], 0, 4, (0, 0, sftUnknown, False), nil, False, 0, '{"fieldCount":4,"values":["ID'#0#0'"Time'#0#0'"Name'#0#0'"Question'#0#0',"rowCount":0}'#$A, ('ID', 'Time', 'Name', 'Question'), 0)

client after call  SQLTable := Database.MultiFieldValues(TSQLSampleRecord, '', 'ID=?', [0]);
the SQLTable result is

((TSQLSampleRecord), 'SELECT ID,Time,Name,Question FROM SampleRecord WHERE ID=:(0):;', (), 0, 0, 4, $FB33C8, (), (), (), (), [], 0, 4, (0, 0, sftUnknown, False), nil, False, 0, '{"fieldCount":4,"values":["ID'#0#0'"Time'#0#0'"Name'#0#0'"Question'#0#0',"rowCount":0}'#$A, ('ID', 'Time', 'Name', 'Question'), 0)

but for External DB,

client after call  SQLTable := Database.MultiFieldValues(TSQLSampleRecord, '', '1=2');
the SQLTable result is nil, because the server raised exception class ESQLite3Exception with message 'no such column: ID'.

client after call   SQLTable := Database.MultiFieldValues(TSQLSampleRecord, '', 'ID=?', [0]);
the SQLTable result is

((TSQLSampleRecord), 'SELECT ID,Time,Name,Question FROM SampleRecord WHERE ID=:(0):;', (), 0, 0, 4, $FB3288, (), (), (), (), [], -1, 1, (0, 0, sftUnknown, False), nil, False, 0, '{"fieldCount":4,"values":["'#0'ID"'#0'",'#0'"Time"'#0'",'#0'"Name"'#0'",'#0'"Question"'#0'"],"rowCount":0}'#$A, ('', ',', ',', ','), 0)

then client will raised exception class EDatabaseError with message 'DS2: Field name missing'.
you can see the below two part is different

["ID'#0#0'"Time'#0#0'"Name'#0#0'"Question'#0#0',"rowCount":0}'#$A    // ******SQLite3
["'#0'ID"'#0'",'#0'"Time"'#0'",'#0'"Name"'#0'",'#0'"Question"'#0'"],"rowCount":0}'#$A   //MS SQL

('ID', 'Time', 'Name', 'Question'),   // ******SQLite3
('', ',', ',', ','),  //MS SQL

So problem perhaps have two, for External DB, where call where '1=2', for system think it's a complex SQl, then routing the SQL to TSQLRestServerDB.EngineList,

function TSQLRestServer.InternalAdaptSQL(TableIndex: integer; var SQL: RawUTF8): TSQLRestServerStatic;
begin
  result := nil;
  if (self<>nil) and (TableIndex>=0) then begin // SQL refers to this unique table
    if fStaticData<>nil then
      // no SQLite3 module available for fStaticData[] -> we need to
      // retrieve manualy any static table from the SQL SELECT statement
      result := fStaticData[TableIndex];
    if (result=nil) and fVirtualTableDirect and (fStaticVirtualTable<>nil) then begin
      result := fStaticVirtualTable[TableIndex];
      // virtual table may need adaptation (e.g. RowID -> ID)
      if (result<>nil) and not result.AdaptSQLForEngineList(SQL) then
        // complex request will use SQlite3 virtual engine module       
        result := nil;  //******** this set 
    end;
  end;
end;

then GetAndPrepareStatement generate exception

function TSQLRestServerDB.EngineList(const SQL: RawUTF8; ForceAJAX: Boolean=false;
...
Req := GetAndPrepareStatement(SQL);    //*******here generate exception

for External DB, where call ID=0, the return SQLTableJSON result is not same as call for the SQLite3.

thanks!

mingda

Member

Registered: 2013-01-04

Posts: 121

Re: External DB Problem

Another problem regard to TSQLTableToDataSet, since the return JSON schema
{"fieldCount":4,"values":["ID","Time","Name","Question"],"rowCount":0} has no column length type and length info,
so when no record return, the create dataset's string field length is alway 1, this make edit dataset impossible, also there has a latency problem, even if there has record return, the fieldlength is get the max length of the return record add one, suppose we have a string field length is 50, but current return record's max length is 20, then we will not able to input length big than 20, If this is the reason forced me to leave mORMot, it will be very sad.

mingda

Member

Registered: 2013-01-04

Posts: 121

Re: External DB Problem

Another problem,

function TSQLTableToDataSet(aOwner: TComponent; aTable: TSQLTable; aClient: TSQLRest
  {$ifndef UNICODE}; aForceWideString: boolean{$endif}): TDataSet;

...
          {$ifdef UNICODE} // for Delphi 2009+ TWideStringField = UnicodeString!
          Add(aFieldName,ftWideString,aTable.FieldLengthMax(F,true));    //here should deal with null string
          {$else}

should deal with null string, else the null string will convert to string 'null'.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: External DB Problem

Use RowID instead of ID if you are using external databases.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: External DB Problem

The just committed TSQLDBStatement.FetchAllToJSON() enhancement was buggy.
It is your ID=? error
It should be fixed now.
See http://synopse.info/fossil/info/7e18d559ed

About "1=2", this is indeed a weird WHERE clause, and you reached the limit of our SQL translator from internal to external engines.
You won't have any problem if you specify the field names, including RowID, as such:

SQLTable := Database.MultiFieldValues(TSQLSampleRecord, 'RowID,Name,Question', '1=2');

This is a workaround, and I'm not able to find an easy solution to such limitations.
In fact, you request was in-between ORM and SQL. You should either use simple syntax (like ID=?), or explicitly use RowID in your more elaborate queries.

If you want to follow mORMot design, you may:
- Either embrace ORM, and use TSQLRecord.[Create]FillPrepare/FillOne or TSQLRest.RetrieveList method to retrieve a TObjectList of TSQLRecord;
- Or use SQL, but on the server side only, e.g. in an interface-based service, then return high-level objects.

Last little detail.
You used the plain TSQLSampleRecord as defined in SampleData.
But this was designed for internal SQLite3 tables only.
You should better use a dedicated definition, using "index ..." to specify the column width:

  TSQLSampleRecord = class(TSQLRecord)
  private
    fQuestion: RawUTF8;
    fName: RawUTF8;
    fTime: TModTime;
  published
    property Time: TModTime read fTime write fTime;
    property Name: RawUTF8  index 100 read fName write fName;
    property Question: RawUTF8 index 500 read fQuestion write fQuestion;
  end;

Otherwise, it will create CLOB, which may be not optimized, especially for the Name property.

mingda

Member

Registered: 2013-01-04

Posts: 121

Re: External DB Problem

The just committed TSQLDBStatement.FetchAllToJSON() enhancement was buggy.
It is your ID=? error
It should be fixed now.
See http://synopse.info/fossil/info/7e18d559ed

Excellent, Now It worked.

If you want to follow mORMot design, you may:
- Either embrace ORM, and use TSQLRecord.[Create]FillPrepare/FillOne or TSQLRest.RetrieveList method to retrieve a TObjectList of TSQLRecord;
- Or use SQL, but on the server side only, e.g. in an interface-based service, then return high-level objects.

I totally agree with you, since this is an old project, can only gradually changed, first I will replace server part, let client remains or feeling similar.

Last little detail.
You used the plain TSQLSampleRecord as defined in SampleData.
But this was designed for internal SQLite3 tables only.
You should better use a dedicated definition, using "index ..." to specify the column width:
Otherwise, it will create CLOB, which may be not optimized, especially for the Name property.

Thank you very much for your wise guidance, so I learned a lot of things!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: External DB Problem

I totally agree with you, since this is an old project, can only gradually changed, first I will replace server part, let client remains or feeling similar.

Progressive conversion to n-Tier is the key for legacy application evolution.
Full rewrite has been shown to be a strategic mistake - we all know http://www.joelonsoftware.com/articles/ … 00069.html

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

Re: External DB Problem

+1 for progressive conversion