AV in GetTableIndex when trying to save unique TSQLRecord

jlc · 2019-08-15 22:14:16

Hello,
I have following code

  TSQLNamesRecord = class(TSQLRecord)
  private
    FName: RawUTF8;
  published
    property Name: RawUTF8 read FName write FName stored AS_UNIQUE;
  end;

At the beginning, the NewName does not exist and it gives '' as Name property

  // DBcon is TSQLRestClientDB
  NameRec := TSQLNamesRecord.CreateAndFillPrepare(DBCon, 'Name = ?', [NewName], 'Name');  // <-- is it good to use 'Name' here or is
it not needed as the record has only one entry anyway?

then I try to add it with

    if NameRec.Name = '' then // <- which one is the correct way to use it?
    //if not NameRec.FillOne then // <- this is already done by CreateAndFillPrepare or?
    begin
      NameRec.Name := StringToUTF8(NewName);
      DBcon.Add(NameRec, True);
    end;

but it always ends in an access violation in TSQLModel.GetTableIndex function:

  if c^=aTable then

Thanks in advance!

jlc · 2019-08-16 08:30:53

Looks like the issue occurred because I haven't had my TSQLModel as global variable; instead I created it before TSQLRestClientDB and freed it after calling TSQLRestClientDB.Create.
Haven't read in documentation that it must exist for the whole lifetime of the application, maybe that could be improved? Or it could do a copy of the object into its own TSQLRestClientDB object?

Vitaly · 2019-08-16 09:39:11

You can just add

 YourTSQLModel.Owner := YourTSQLRest;

So YourTSQLModel will be freed automatically with YourTSQLRest destroying. See: https://synopse.info/files/html/api-1.1 … ODEL_OWNER

Or you can do the same through the constructor: https://synopse.info/files/html/api-1.1 … THOWNMODEL

jlc · 2019-08-16 11:18:23

Vitaly wrote:

You can just add
 YourTSQLModel.Owner := YourTSQLRest; 
So YourTSQLModel will be freed automatically with YourTSQLRest destroying. See: https://synopse.info/files/html/api-1.1 … ODEL_OWNER

Missed that but seems to be not that clean...

Vitaly wrote:

Or you can do the same through the constructor: https://synopse.info/files/html/api-1.1 … THOWNMODEL

This constructor is not available for TSQLRestClientDB

Vitaly · 2019-08-16 12:42:13

ah, sorry - I didn't notice that you are working with TSQLRestClientDB, although you've mentioned it several times
Then I guess the first variant will work for you

Last edited by Vitaly (2019-08-16 12:45:33)

Vitaly · 2019-08-16 13:14:39

I reread your messages and found more questions

jlc wrote:

NameRec := TSQLNamesRecord.CreateAndFillPrepare(DBCon, 'Name = ?', [NewName], 'Name'); // <-- is it good to use 'Name' here or is
it not needed as the record has only one entry anyway?

I don't think, that you need to point custom fields here:
- default aCustomFieldsCSV='' will retrieve all simple table fields

jlc wrote:

if NameRec.Name = '' then // <- which one is the correct way to use it?
//if not NameRec.FillOne then // <- this is already done by CreateAndFillPrepare or?

If you want to fill your TSQLRecord object published fields, you have to call FillOne first after CreateAndFillPrepare. And FillOne will tell you, if there is any record retrieved resulting boolean value.
So, I would quickly correct a part of your code like this (if I understood your desires right):

  NameRec := TSQLNamesRecord.CreateAndFillPrepare(DBCon, 'Name = ?', [NewName]);
   if not NameRec.FillOne then
    begin
      NameRec.Name := StringToUTF8(NewName);
      DBcon.Add(NameRec, True);
    end;

jlc · 2019-08-16 15:31:20

Vitaly wrote:

jlc wrote:
NameRec := TSQLNamesRecord.CreateAndFillPrepare(DBCon, 'Name = ?', [NewName], 'Name'); // <-- is it good to use 'Name' here or is
it not needed as the record has only one entry anyway?
I don't think, that you need to point custom fields here:
- default aCustomFieldsCSV='' will retrieve all simple table fields

I've read that but as I may extend my record and just need to know if the entry already exists, it's enough to get 'Name' field only. That's why I think leaving it with 'Name' is fine.

Vitaly wrote:

jlc wrote:
if NameRec.Name = '' then // <- which one is the correct way to use it?
//if not NameRec.FillOne then // <- this is already done by CreateAndFillPrepare or?
If you want to fill your TSQLRecord object published fields, you have to call FillOne first after CreateAndFillPrepare. And FillOne will tell you, if there is any record retrieved resulting boolean value.
So, I would quickly correct a part of your code like this (if I understood your desires right):
  NameRec := TSQLNamesRecord.CreateAndFillPrepare(DBCon, 'Name = ?', [NewName]);
   if not NameRec.FillOne then
    begin
      NameRec.Name := StringToUTF8(NewName);
      DBcon.Add(NameRec, True);
    end;

Yes, your absolutely right. I noticed it after having the TSQLModel alive. Ended in the code you proposed :-)

Is there any other way than always doing a TSQL*Record.CreateAndFillPrepare() with maybe 'NewName' item and FillOne to check if it already exist?
I have another class now which has a reference via AsTSQLRecord to the Name record. When extending it with another few record classes, I'll end up in many calls to TSQL*Record.CreateAndFillPrepare() and FillOne to get the ID if existing or add the item to get in the end the proper reference via AsTSQLRecord. Is there any smarter way?

Vitaly · 2019-08-16 20:25:21

jlc wrote:

I've read that but as I may extend my record and just need to know if the entry already exists, it's enough to get 'Name' field only. That's why I think leaving it with 'Name' is fine.

Well, afaik aCustomFieldsCSV is responsible only for the list of selected fields (forming TSQLTable). You are using CreateAndFillPrepare only for understanding, whether such a record exists or not. Following this task, you will change only where clause, if your record will be extended with other fields.
In fact retrieving only id values may be even a bit more reasonable in case of many heavy fields in table and many checking operations:

NameRec := TSQLNamesRecord.CreateAndFillPrepare(DBCon, 'Name = ?', [NewName], 'ID');

Hope, somebody will correct me, if I'm wrong

jlc wrote:

Is there any other way than always doing a TSQL*Record.CreateAndFillPrepare() with maybe 'NewName' item and FillOne to check if it already exist?
I have another class now which has a reference via AsTSQLRecord to the Name record. When extending it with another few record classes, I'll end up in many calls to TSQL*Record.CreateAndFillPrepare() and FillOne to get the ID if existing or add the item to get in the end the proper reference via AsTSQLRecord. Is there any smarter way?

I hardly can imagine another way of forming dictionaries with unique values in DB I guess, you will always have to check the existing of value in some layer.
Of course, there may be some thoughts, which can make code shorter, like making a child class of some TSQLRest... with some custom adding functions, f.ex. But it totally depends on the situation.
Maybe somebody can suggest something, I'll wait, too

mORMot Open Source

#1 2019-08-15 22:14:16

AV in GetTableIndex when trying to save unique TSQLRecord

#2 2019-08-16 08:30:53

Re: AV in GetTableIndex when trying to save unique TSQLRecord

#3 2019-08-16 09:39:11

Re: AV in GetTableIndex when trying to save unique TSQLRecord

#4 2019-08-16 11:18:23

Re: AV in GetTableIndex when trying to save unique TSQLRecord

#5 2019-08-16 12:42:13

Re: AV in GetTableIndex when trying to save unique TSQLRecord

#6 2019-08-16 13:14:39

Re: AV in GetTableIndex when trying to save unique TSQLRecord

#7 2019-08-16 15:31:20

Re: AV in GetTableIndex when trying to save unique TSQLRecord

#8 2019-08-16 20:25:21

Re: AV in GetTableIndex when trying to save unique TSQLRecord

Board footer