mORMot Open Source

aruncs08

Member

Registered: 2014-10-13

Posts: 27

'TSQLRecord.CreateAndFillPrepare' returns null values while retrieving

Hi!

I am working with a small application in which I have to retrieve my existing Table records from Server to Client using mORMot. I have followed Sample 28, which is a bit similar to my application.

Problem:
Now I have a problem while retrieving my table records. I am using 'TSQLRecord. CreateAndFillPrepare' pattern, but it returns a NULL value in all the fields of my table records.

Below I have shared my Client and REST Model source code in which I think I have some issues. The Server Source Code is working fine.

Client Source Code:

var
  aList: TObjectList<TRows>;
begin
  aModel := fetchModel;
  try
    aClient := TSQLHttpClientWinHTTP.Create('localhost', SERVER_PORT, aModel);
    aRow := TRows.CreateAndFillPrepare(aClient, 'Side LIKE ?', ['A']);                            // while Debugging, all the fields in aRow contains null value in this line.
    try
      while aRow.FillOne do
        ShowMessage('Row ID: ' + IntToString(aRow.RowsID));
    finally
      aRow.Free;
    end;
  finally
    aClient.Free;
  end;

REST Model Source Code:

TRows = class(TSQLRecord)
function fetchModel: TSQLModel;
begin
  Result:= TSQLModel.Create([TRows],SERVER_ROOT);
  TRows.AddFilterOrValidate('RowsID', TSynValidateText.Create);
  TRows.AddFilterOrValidate('PositionX', TSynValidateText.Create);
  TRows.AddFilterOrValidate('PositionY', TSynValidateText.Create);
  TRows.AddFilterOrValidate('Side', TSynValidateText.Create);
end;

Kindly waiting for some solutions to this issue. Please have patience with me!

Thanks in advance.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: 'TSQLRecord.CreateAndFillPrepare' returns null values while retrieving

I just tested it.
No problem on my side.

First of all, there is NO Value just after FillPrepare.
By design: you did not iterate on it yet, so there is no data in the fields.
Data is still in the hidden FillTable: TSQLTable instance.

Does the "while FillOne" loop work as expected?
If not:
1. Are you sure you are using the latest 1.18 nightly build version of the source code?
2. Just use the IDE debugger, and find out what happens in your case:
- what is the computed SQL?
- what is the returned JSON?
-...

aruncs08

Member

Registered: 2014-10-13

Posts: 27

Re: 'TSQLRecord.CreateAndFillPrepare' returns null values while retrieving

Thanks for your reply. I am using the latest 1.18 nightly build version of the source code.

In the IDE debugger, it says JSON Format is False and I couldn't see computed SQL and JSON.

Problem:
For your information, the Table which I am accessing is not created through mORMot (REST Model). It means I am trying to access the existing Table (which I have created manually) from my DB.

Working Scenario:
If I am retrieving a Table record which is created through mORMot (REST Model), then I can retrieve those table records.

Kindly let me know if I am confusing or you couldn't understand. Thanks in advance.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: 'TSQLRecord.CreateAndFillPrepare' returns null values while retrieving

Did you call CreateMissingTables method as explained by the doc and shown in the samples?

aruncs08

Member

Registered: 2014-10-13

Posts: 27

Re: 'TSQLRecord.CreateAndFillPrepare' returns null values while retrieving

Yes, I call CreateMissingTables method in my Server side. Actually, I retained most of the source code from the sample 28 Rest Server.

Question:
How/Where can I cross check whether the application reads my table records from the DB?

aruncs08

Member

Registered: 2014-10-13

Posts: 27

Re: 'TSQLRecord.CreateAndFillPrepare' returns null values while retrieving

@Admin: Now I have found the exact problem of not fetching from the existing table.

Problem:
In my existing table I don't have the Column name 'ID', which the REST server is expecting. While debugging in the Server side, I got to know this problem.

Question:
Can't we have a table without an 'ID' column (which is automatically created by TSQLRecord in a REST Model)?  If so, how could I do it?

Thanks in advance.

Last edited by aruncs08 (2014-11-07 14:35:27)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: 'TSQLRecord.CreateAndFillPrepare' returns null values while retrieving

No, the primary key should be an INTEGER ID column, by design.
This is a restriction of SQlite3 virtual tables, which we use for our ORM kernel, and also due to the fact that we map one-to-many or one-to-one relationship by using a TSQLRecord published field, which would be stored as an INTEGER In the DB.

aruncs08

Member

Registered: 2014-10-13

Posts: 27

Re: 'TSQLRecord.CreateAndFillPrepare' returns null values while retrieving

Thank you very much for your reply Admin.

So, whichever table I am using should contain the primary key INTEGER ID column?

Last edited by aruncs08 (2014-11-07 15:08:43)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: 'TSQLRecord.CreateAndFillPrepare' returns null values while retrieving

The table primary index should be an INTEGER, but for an external table you can change the field name from ID to whatever you need by using field mapping.
It would be still ID in the Delphi code, but whatever you want on the external DB side.
See http://synopse.info/files/html/Synopse% … l#TITL_120

aruncs08

Member

Registered: 2014-10-13

Posts: 27

Re: 'TSQLRecord.CreateAndFillPrepare' returns null values while retrieving

@Admin: Thanks for your quick reply. It works like a charm now. And your new HTML documentation is much comfortable than the PDF, well done work