0

I use Delphi/NexusDB and I build SQL (about 800 char long) at run time then I pass it to the nexusdb query.sql.text property to execute it but I found error of invalid token on execution.

I pass SQL like this

Query.SQL.Text := VarStrSQL; // <<---- string variable holding the SQL

when I traced I found SQL string in the Query.SQL.Text is trimmed to 326 character !! While the string variable that hold the SQL is complete and fine but when I assign that variable to query.sql.text only 326 character passed and of course this result in an error for invalid SQL syntax

Please advise why the SQL string trimmed like that ?

Update: *I tried memo1.lines.text := VarStrSQL and the memo component also display the string trimmed !! is it possible a character in my string cause that !! a bug in Delphi 2010 that cause TStrings to trim my string ?*

Thanks

zac
  • 4,495
  • 15
  • 62
  • 127
  • `Query.SQL.Text := VarStrSQL;` try breaking your `VarStrSQL` into smaller parts like `Query.SQL.Text:=VarStrSQL1+VarStrSQL2+VarStrSQL3` then try running. – PresleyDias Feb 11 '12 at 04:54
  • try debugging and see what happens when the SQL.Text assignment takes place, I assume that the Query is assigning an event to the TStrings property so it might process your SQL... you might want to do something like Query.SQL.Add(); a few times and see the outcome... –  Feb 11 '12 at 05:34
  • @PresleyDias Your proposal just won't make any difference. – Arnaud Bouchez Feb 11 '12 at 08:44
  • 1
    Assuming `VarStrSQL` is declared as `String` type, You might have `#0` or other "Illegal" chars in your `VarStrSQL`. – kobik Feb 11 '12 at 11:58
  • Try with: Query.SQL.Text := StringReplace(VarStrSQL, #0, '', [rfReplaceAll]); – too Feb 11 '12 at 13:16

2 Answers2

3

Sounds like a bug in DB provider itself. There is no such limitation in TQuery.

My advice shall be to use small SQL, but bound parameters to set the data.

Instead of

Query.SQL.Text := 'INSERT INTO Store_Information (store_name, Sales, Date)
VALUES ('Los Angeles ... ... ...', 900, '10-Jan-1999')';

code

Query.FieldByName('store').AsString := 'Los Angeles ... ... ...'; // here you should have no limitation
Query.FieldByName('sales').AsInteger := 900;
Query.FIeldByName('Date').AsDAteTime := Now;
Query.SQL.Text := 'INSERT INTO Store_Information (store_name, Sales, Date)
VALUES (:store,:sales,:date)';

And your request will be faster, since the statement could be preparated by the engine, then reused.

Arnaud Bouchez
  • 42,305
  • 3
  • 71
  • 159
  • Thanks but I can not use this solution I can not check for every type - I tried memo1.lines.text := VarStrSQL and the memo component also display the string trimmed !! is it possible a character in my string cause that !! a bug in Delphi that cause TStrings to trim my string ? – zac Feb 11 '12 at 10:56
0

I found the problem: It is nxtChar Fields when they are null they have the value #0 and that cause string trimming

however although I check for null like this varisnull() the char fields was able to skip this trap function !!! which makes me go around myself for hours finally I now check them like this

If <nxtChar field> = #0 then <nxtChar field> = '' (or <nxtChar field> = null)
zac
  • 4,495
  • 15
  • 62
  • 127
  • 1
    You might try FieldStr := ReplaceString(FieldStr, #0, ' ', [rfReplaceAll]) (if it works on the null) rather of your code above. – RobertFrank Feb 11 '12 at 17:55