ForceID parameter inside TSQLRest

Cahaya · 2015-07-19 15:36:00

Hi AB,

How to use ForceID parameter inside, function TSQLRest.Add(Value: TSQLRecord; SendData: boolean;
ForceID,DoNotAutoComputeFields: boolean): TID;

Can I obey ID field inside table ? I try to send True to ForceID, but still get an error.

Thank you very much.

ab · 2015-07-19 18:18:30

ID should be set in Value.ID, then you should be able to set ForceID=true.

Cahaya · 2015-07-20 01:07:49

Hi AB,

I still got an error,

20150720 08001859  B EXC   ESQLite3Exception {"ErrorCode":1,"SQLite3ErrorCode":2,"Message":"Error SQ
LITE_ERROR (1) using 3.8.10.2 - 'no such table: Countries' extended_errcode=1"} at 00498E1A  stack t
race 400030FC 40003473 400074C7 40007236 0049A4A4 00452895 0045B5CB 00456699 0045677E 00470D4B 00424
85C 75851148 004243C3 004243F0 00424439 0042447A 4003C067 40006DC6 7585337A 77C492E2 77C492B5

How to set ID property ? My TSQLRecord is,

  TSQLCountries = class(TSQLRecord) // TSQLRecord has already ID: integer primary key
  private
    fCountryID : RawUTF8;
    fName : RawUTF8;
    fRegionID : integer;
  published
    /// ORM will create a NAME VARCHAR(80) column
    property Country_Id : RawUTF8 index 2 read fCountryID write fCountryID;
    property Country_Name : RawUTF8 index 40 read fName write fName;
    property Region_Id : integer read fRegionID write fRegionID;
  end;

My client code is,

  vClient := Nil;
  vCountries := TSQLCountries.Create;
  try
    vClient := TSQLHttpClient.Create(Edit1.Text,PORT_NAME,fData);
    try
      vCountries.IDValue := 1;
      vCountries.Country_Id := StringToUTF8('ID');
      vCountries.Country_Name := StringToUTF8('Indonesia');
      vCountries.Region_Id := 3;
      vClient.Add(vCountries,True,True);
      Showmessage('Succeeded.');
    finally
      vCountries.Free;
    end;
  finally
    vClient.Free;
  end;

ID property is readonly.

Thank you.

ab · 2015-07-20 05:56:02

'no such table: Countries' means that no table has been created for the TSQLCountries class.
Nothing related with ForceID.

Please ensure TSQLCountries is in your fData: TSQLModel, and do not forget to call CreateMissingTables on server side.

Cahaya · 2015-07-20 13:24:26

Here server code, please advice me.

var
  aData       : TSQLModel;
  aHTTPServer : TSQLHttpServer;
  aProps      : TSQLDBConnectionProperties;
  aRestServer : TSQLRestServerDB;
begin
  with TSQLLog.Family do begin
    Level := LOG_VERBOSE;
    EchoToConsole := LOG_VERBOSE;
    PerThreadLog := ptIdentifiedInOnFile;
  end;
  AllocConsole;
  TextColor(ccLightGray);
  aData  := DataModel;
  aProps := TSQLDBOracleConnectionProperties.Create('localhost','XE','hr','oracle');
  try
    VirtualTableExternalRegisterAll(aData,aProps);
    aRestServer := TSQLRestServerDB.Create(aData,':memory:',false);
    try
      aRestServer.AcquireExecutionMode[execORMGet]   := amBackgroundORMSharedThread;
      aRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;
      aRestServer.CreateMissingTables;
      aHTTPServer := TSQLHttpServer.Create('888',[aRestServer],'+',useHttpApiRegisteringURI);
      try
        aHTTPServer.AccessControlAllowOrigin := '*'; // for AJAX requests to work
        Sleep(200); // allow all HTTP threads to be launched and logged
        writeln(#10'Background server is running.'#10);
        writeln('Press [Enter] to close the server.'#10);
        ConsoleWaitForEnterKey;
      finally
        aHTTPServer.Free;
      end;
    finally
      aRestServer.Free;
    end;
  finally
    aData.Free;
  end;
end.

My table desc,

SQL> desc countries
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COUNTRY_ID                                NOT NULL CHAR(2)
 COUNTRY_NAME                                       VARCHAR2(40)
 REGION_ID                                          NUMBER

A new error,

20150720 20181013  B EXC   ESQLDBException {"Message":"TSQLRestStorageExternal.J
SONDecodedPrepareToSQL(TSQLCountries): No column for \"ID\" field in table Count
ries"} at 004A51C0  stack trace API 00444C96 00402E60 77C6B67F 77C20163 004A51C0
 004A4E55 004A2F45 0048E05B 00441C38 0041DFE7 00403706 7585337A 77C492E2 77C492B
5
20150720 20181013  $ EXC        ESQLDBException {"Message":"TSQLRestStorageExter
nal.JSONDecodedPrepareToSQL(TSQLCountries): No column for \"ID\" field in table
Countries"} at 00441A74  stack trace 77C3E3CC 004B1C4D 004A9613 004AD181 0041DFE
7 00403706 7585337A 77C492E2 77C492B5

RestServer always assume it needs ID field. I thought, forceID parameter is an option to use ID or not.

Thank you again.

ab · 2015-07-20 14:14:53

From the ORM point of view, the ID field is mandatory!

If you want to access a table without any ID: integer field, you just can't use the ORM.

What you can easily do is encapsulate your manual SQL calls within a service.
See https://tamingthemormot.wordpress.com/2 … databases/

mORMot Open Source

#1 2015-07-19 15:36:00

ForceID parameter inside TSQLRest

#2 2015-07-19 18:18:30

Re: ForceID parameter inside TSQLRest

#3 2015-07-20 01:07:49

Re: ForceID parameter inside TSQLRest

#4 2015-07-20 05:56:02

Re: ForceID parameter inside TSQLRest

#5 2015-07-20 13:24:26

Re: ForceID parameter inside TSQLRest

#6 2015-07-20 14:14:53

Re: ForceID parameter inside TSQLRest

Board footer