mORMot Open Source

fh

Member

Registered: 2013-04-03

Posts: 7

mORMot.pas GetSQLValues

Hello AB,
I found the GetSQLValues function very useful for generating the insert statement necessary to add records to an inherited table.
However, when the table has a blob field, the syntax is different than what is needed, limiting its usefulness.
Based on what I found in mORMot.pas:

    /// return the UTF-8 encoded SQL source to INSERT the values contained
    // in the current published fields of a TSQLRecord child
    // - only simple fields name (i.e. not TSQLRawBlob/TSQLRecordMany) are updated:
    // BLOB fields are ignored (use direct update via dedicated methods instead)
    // - format is '(COL1, COL2) VALUES ('VAL1', 'VAL2')' if some column was ignored
    // (BLOB e.g.)
    // - format is 'VALUES ('VAL1', 'VAL2')' if all columns values are available
    // - is not used by the ORM (do not use prepared statements) - only here
    // for conveniency
    function GetSQLValues: RawUTF8;

   
function TSQLRecord.GetSQLValues: RawUTF8;
var i: integer;
    V: RawUTF8;
    wasString: boolean;
begin
  result := '';
  if self<>nil then
  with RecordProps do
    if SimpleFields=nil then
      exit else begin
    if HasNotSimpleFields then // get 'COL1,COL2': no 'ID,' for INSERT (false below)
      result := SQLTableSimpleFieldsNoRowID; // always <> '*'
    result := result+' VALUES (';
    for i := 0 to length(SimpleFields)-1 do
    with SimpleFields[i] do begin
      GetValueVar(self,true,V,@wasString);
      if wasString then
        V := QuotedStr(V);
      result := result+V+',';
    end;
    result[length(result)] := ')';
  end;
end;

Since the SQLTableSimpleFieldsNoRowID function returns the list of column names, the result is:
'COL1, COL2 VALUES (' VAL1 ',' VAL2 ')' if some column was ignored
which differs from the definition.
For the correct syntax, as the definition of the GetSQLValues function indicates, it is necessary to enclose this list in parentheses:

    if HasNotSimpleFields then // get 'COL1,COL2': no 'ID,' for INSERT (false below)
      result := '(' + SQLTableSimpleFieldsNoRowID + ')'; // always <> '*'

So the function GetSQLValues could be used in all cases
Regards.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: mORMot.pas GetSQLValues

This function is slower than the existing code since it uses naive concatenation of the result.

Using direct SQL embedding of the data is also a slow and unsafe practice (due to potential injection).

What is wrong with the current methods?
You can retrieve everything, including the blobs, if needed: only specify the good fields.
And for insertion, nothing beats our TRestBatch process.

fh

Member

Registered: 2013-04-03

Posts: 7

Re: mORMot.pas GetSQLValues

Thank you
Nothing is wrong with current methods.
The legacy system only has read access and only allows inserts by sending the sql text to an intermediate service.
That is why we need to generate the sql text.
We will analyze your recommendation.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: mORMot.pas GetSQLValues

So you are in fact re-creating an ORM for your exact purpose.

This is just fine, but my guess is that it would be very specific, and won't be worth changing the main mORMot code for your purpose.

fh

Member

Registered: 2013-04-03

Posts: 7

Re: mORMot.pas GetSQLValues

That´s OK, and yes, it is very specific. Thank you any way, we will explore TJSONObjectDecoder and the Batch process and try to avoid GetSQLValues.