mORMot Open Source

noobies

Member

Registered: 2011-09-13

Posts: 139

FormatUTF8 QuoteChar

i connect to MSSQL server and run query

  s := 
    'SELECT distinct st.StateNumber ' +
    'FROM T_STATE st ' +
    'LEFT JOIN T_LPU_MAIN lm on st.LpuMain = lm.LpuID ';
  s := FormatUTF8(s + sql_where + 'lm.TFomsCode = ? AND lm.TFomsCode <> ?', [], [TFomsCode, '101'], True);
  qry := database_oledb.MainConnection.NewStatementPrepared(s, True);

and get error "TFomsCode" and "101" not column in database. I search source and find function QuotedStrJSON

procedure QuotedStrJSON(const aText: RawUTF8; var result: RawUTF8);
var i: integer;
begin
  for i := 1 to length(aText) do
    case aText[i] of
    #0..#31,'\','"':
      with TTextWriter.CreateOwnedStream do
      try
        Add('"');
        AddJSONEscape(pointer(aText));
        Add('"');
        SetText(result);
        exit;
      finally
        Free;
      end;
    end;
  // if we reached here, no character needs to be escaped in this string
  result := '"'+aText+'"';
end;

problem in quotechar = ", if replace last result to

result := string(aText).QuotedString;

or

result := ''+aText+'';

all work fine, ab can u add in procedure FormatUTF8 parameter QuoteChar?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: FormatUTF8 QuoteChar

I don't understand what you mean...
Sorry

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: FormatUTF8 QuoteChar

  s := FormatUTF8(SELECT * from lm where code = ?', [], ['101'], False);
  //s = 'SELECT * from lm where code = :(''101''):'
  qry := database_oledb.MainConnection.NewStatementPrepared(s, True);

  //not work, reason - not MSSQL syntax
  //error Project client.exe raised exception class EOleDBException with message 'TOleDBConnection: OLEDB Error 80040E14 - Incorrect syntax near the structure ":".'.

  s := FormatUTF8(SELECT * from lm where code = ?', [], ['101'], True);
  //s = 'SELECT * from lm where code = "101"'
  qry := database_oledb.MainConnection.NewStatementPrepared(s, True);

  //not work, reason - different quotechar
  //error Project client.exe raised exception class EOleDBException with message 'TOleDBConnection: OLEDB Error 80040E14 - Invalid column name "101".'.

if change source to ' quotechar query fork fine

procedure QuotedStrJSON(const aText: RawUTF8; var result: RawUTF8);
var i: integer;
begin
  for i := 1 to length(aText) do
    case aText[i] of
    #0..#31,'\','"':
      with TTextWriter.CreateOwnedStream do
      try
        Add('"');
        AddJSONEscape(pointer(aText));
        Add('"');
        SetText(result);
        exit;
      finally
        Free;
      end;
    end;
  // if we reached here, no character needs to be escaped in this string
  result := ''+aText+''; //<= CHANGED
end;

i wont use FormatUTF8 and get string s = 'SELECT * from lm where code= ''101'''

Last edited by noobies (2017-02-27 10:38:58)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: FormatUTF8 QuoteChar

Here, quoting are used internally, and work as expected.

If you use inline parameters from a SQL inlined query, you should use
- TSQLDBConnectionProperties.PrepareInlined() or
- TSQLDBConnectionProperties.ExecuteInlined()
overloaded methods.

See for instance  SQLite3\Samples\16 - Execute SQL project.
And https://synopse.info/files/html/Synopse … ml#TITL_74