mORMot Open Source

mvddoes

Member

Registered: 2012-08-11

Posts: 6

Insert with using Database.ExecuteList([], sSQL);

Hello,

I am trying to insert 2 values in a table with 2 columns (+ ID). The columns where the values are to be stored do both have a foreign key relation; both to some other table.

Why does the code below not work?

        in declaration: data: TSQLTable;

        sSQL := 'insert into PatientAppointments (patient_id, appointment_id) values (' +
        IntToStr(aPatient_id) + ', ' + IntToStr(aAppointment_id) + ')';
        data:=Database.ExecuteList([], sSQL);
        Database.Commit();

Please don't tell me to use the function 'ManyAdd' as I do not have columns named Dest and Source....

Regards,
Maurijn

Last edited by mvddoes (2012-08-16 23:02:22)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,007

Website

Re: Insert with using Database.ExecuteList([], sSQL);

The framework is best to use with its ORM methods, not by hand SQL statements.

As stated several times in this forum, and the documentation, ExecuteList() method is by default protected and won't allow such plain remote execution, for obvious security reasons.
If you do not find it obvious, please be sure that it is a common and required practice for any serious client-server application to have such patterns.

Please read the ORM part of the documentation - in http://synopse.info/files/pdf/Synopse%2 … 201.17.pdf - and use CRUD methods instead of manual statements.
"Think ORM, not DB"!!!!

Some code may be:

aPatientAppointements := TSQLPatientAppointements.Create;
try
  aPatientAppointements.aPatient_id := aPatient_id;
  aPatientAppointements.aAppointement_id := aAppointment_id;
  Database.Add(aPatientAppointements);
finally
  aPatientAppointements.Free;
end;

You can re-use the aPatientAppointements instance if needed.

And the Database.Commit() is not to be used alone: it expects a previous TransactionBegin() call.

Using ORM will provide better speed, since the generated SQL statements will be prepared and reused.

You can also use a BATCH mode if you have several insert to make to the DB.
Use BatchStart / BatchAdd / BatchSend methods within a transaction.
Amazing speed will be achieved for massive updates/insertions.

Or consider create services on the server side instead of putting your logic on the client side.
It is the best design today, both scaling and safe.

mvddoes

Member

Registered: 2012-08-11

Posts: 6

Re: Insert with using Database.ExecuteList([], sSQL);

Hi again,

I was trying to think ORM and did what you write above: Database.Add(aPatientAppointements)...

But then an error occured: unrecognized token: "{"

any idea what this error causes?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,007

Website

Re: Insert with using Database.ExecuteList([], sSQL);

Without code to reproduce, impossible to say....

:-)

mvddoes

Member

Registered: 2012-08-11

Posts: 6

Re: Insert with using Database.ExecuteList([], sSQL);

It now suddenly works... :-)
Possibly because I downloaded the newest version of the framework...