mORMot Open Source

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

negative ID again

Hello,

I see in mORMot.pas next rows:

function TSQLRestClient.Retrieve(aID: integer; Value: TSQLRecord;
  ForUpdate: boolean=false): boolean;
var Resp: RawUTF8;
    TableIndex: integer;
begin
  result := false;
  if (self=nil) or (aID<=0) or (Value=nil) then
    exit;

Looks like mORMot can not retrieve values with negative ID.

Can I change it with rows below?

  if (self=nil) or (Value=nil) then
    exit;

Is it very dangerous for the project?

The point is my database really contains special rows with negative ID.
Is it good or not... The question is not for me because the database is inherited from other developers.

Thanks a lot.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: negative ID again

I see now it is dangerous.

LocalClient.RetrieveList<TTransfer>('id=?', [msg.Transfer.Id]);

works good with negative ID.

Thanks

Last edited by alexdmatveev (2015-02-11 22:14:24)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: negative ID again

Perhaps just aID=0 is to be checked.
0 is invalid for the framework and sqlite3.

itSDS

Member

From: Germany

Registered: 2014-04-24

Posts: 516

Re: negative ID again

I think 0 is nearly the same meaning as null
Negative ID can happen cause it is Integer and you loose the High Bit.

Last edited by itSDS (2015-02-12 15:32:36)

---

Rad Studio 12.1 Santorini

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: negative ID again

In the ORM, the ID are defined as TID, i.e. as Int64.
So sign change due to over range is not likely.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: negative ID again

Hello,

Let's go on with the discussion
At first, thanks a lot to everybody for the help.

Next question here is:

How to DELETE a record with NEGATIVE ID?

Please note for code snippets below that offlineTransferID < 0 (is negative).

The code below says that column ID does not exist.

FConnectionManager.LocalClient.Execute(Format('delete from transfer where id=%d', [offlineTransferID]));

The next code is incorrect in mormot framework because it generates wrong URI:

FConnectionManager.LocalClient. Delete(TTransfer, offlineTransferID]);

So... what is the way?

Thanks.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: negative ID again

It is not supported yet.

The ORM always the ID to be > 0.
Any value <= 0 means "error" or "invalid".
The IDs generated by the ORM are always > 0.

For direct SQL, use RowID column name, and do not forget to use FormatUTF8() instead of Format(), if you can.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: negative ID again

ok,
should I make it so

  FConnectionManager.LocalClient.Execute(FormatUTF8('delete from transfer where RowId=?', [offlineTransferID]));

?

I ask because I do not get wished result.

Please write me correct way...

Is it possible at all with MySQL?

Or RowId is only for SQLite?

Thanks a lot.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: negative ID again

So... is there a way to use RowID with MySQL to delete a record with negative ID?
Does anybody know a solution?

Thanks.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: negative ID again

AFAIR you could use ID=? in your SQL query.
And use ExecutFmt() method.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: negative ID again

hmmm...

What do I do wrong?
I have spent already lot of time to solve the task...

I tryed now your offered way again.

  LocalClient.ExecuteFmt('delete from transfer where ID=?', [], [offlineTransferID]);

Result:

First chance exception at $74D5B727. Exception class ESQLite3Exception with message 'Error SQLITE_ERROR (1) using 3.8.10.2 - 'no such column: ID' extended_errcode=1'. Process mednet.exe (4220)

Then I tryed:

LocalClient.ExecuteFmt('delete from transfer where RowID=?', [], [offlineTransferID]);

Result:

First chance exception at $74D5B727. Exception class ESQLite3Exception with message 'Error SQLITE_ERROR (1) using 3.8.10.2 - 'SQL logic error or missing database' extended_errcode=1'. Process mednet.exe (12440)

What do I do wrong?

Is it possible on client side at all? Or do you mean with "direct SQL" only server side?

Thanks again for the help.

Last edited by alexdmatveev (2015-07-02 12:27:34)