how to use the function "copy" to assign a UTF-8 value to a variable

Question

I use the following code to assign text to an SQL parameter:

quInsert.Parameters.ParamByName('veh_type').value := copy(s,11,1);

Sometimes the text in the .txt file I am reading from is in UTF-8, which has characters using two bytes. How can I change the following code to accept UTF-8 data?

procedure TPrepareform.Button1Click(Sender: TObject);
var
  f : textfile;
  s : string;
  i : integer;
  IsAnsiString : Boolean;
begin
  with quInsert do
  begin
    close;
    sql.clear;
    sql.add('insert into v_info_2018');
    sql.add('(record_type, plate_group, plate_no, v_type, v_type_cn,');
    sql.add('v_type_pt, v_type_pt, v_brand, engine_no, ct_tax_amount');
    sql.add('inspect_date, record_date');
    sql.add('values (:record_type, :plate_group, :plate_no, :v_type, :v_type_cn, :v_type_pt, :v_type_pt,');
    sql.add(':v_brand, :engine_no, :ct_tax_amount,');
    sql.add(':inspect_date, :record_date');
  end;
  i := 0;
  assignfile(f,edMasterName.Text);
  reset(f);
  while not eof(f) do
  begin
    readln(f,s);
    inc(i);
  end;
  prInsert.Max := i;
  i := 0;
  reset(f);
  VRS_Main.StatusBar1.Panels[5].Text:='0';
  while not eof(f) do
  begin
    inc(i);
    prInsert.StepIt;
    readln(f,s);
    quInsert.Parameters.ParamByName('record_type').value := copy(s,1,1);
    quInsert.Parameters.ParamByName('plate_group').value := copy(s,2,1);
    quInsert.Parameters.ParamByName('plate_no').value := copy(s,3,8);
    quInsert.Parameters.ParamByName('v_type').value := copy(s,11,1);
    quInsert.Parameters.ParamByName('v_type_cn').value := UTF8Decode(copy(s,12,50));
    quInsert.Parameters.ParamByName('v_type_pt').value := copy(s,62,50);
    quInsert.Parameters.ParamByName('v_brand').value := copy(s,112,30);
    quInsert.Parameters.ParamByName('engine_no').value := copy(s,142,50);
    quInsert.Parameters.ParamByName('ct_tax_amount').value := copy(s,192,9);
    quInsert.Parameters.ParamByName('inspect_date').value := copy(s,201,50);
    quInsert.Parameters.ParamByName('record_date').value := copy(s,251,8);
    quInsert.ExecSQL;
    VRS_Main.StatusBar1.Panels[5].Text:=inttostr(strtoint(VRS_Main.StatusBar1.Panels[5].Text)+1);
  end;
end;

What encoding does the database use? Is it even configured to accept Unicode? — David Heffernan, Aug 24 '17 at 02:54
Your SQL is malformed. On these two lines: `sql.add('inspect_date, record_date');` and `sql.add(':inspect_date, :record_date');`, you are missing a closing `)` after `record_date` and `:record_date`, respectively. Your SQL looks like this when executed: `'insert into v_info_2018 ( values ('`, when it should look like this instead: `'insert into v_info_2018 () values ()'` I'm surprised you are not getting a runtime error from the database about this. — Remy Lebeau, Aug 25 '17 at 03:32

score 0 · Accepted Answer · answered Aug 24 '17 at 03:03

0

Store the UTF-8 data in a UTF8String instead of a standard String, and then call either UTF8Decode() or Utf8ToAnsi() and store that result in the database.

answered Aug 24 '17 at 03:03

Remy Lebeau

555,201
31
458
770

hi, dear Remy, thank you for your answer, that's useful. tempstring := copy(s,12,50); tempstring := UTF8Decode(tempstring); quInsert.Parameters.ParamByName('veh_type_cn').value := tempstring; the second line temstring show the excatly "chinese text" and when in the third line, quInsert.Parameters.ParamByName('veh_type_cn').value still got the strange text, could u know why? – kaihong Aug 25 '17 at 01:56
`UTF8Decode()` returns a UTF-16 `WideString`. Don't assign that back to your `tempString` (which is clearly an `(Ansi/UTF8)String`), that will just perform an unwanted UTF16->Ansi conversion. Assign the `WideString` directly to the `Value` and let the database handle any conversions it needs, eg: `quInsert.Parameters.ParamByName('v_type_cn').value := UTF8Decode(copy(s,12,50));` (BTW, in your question, you are calling `UTF8Encode()` instead of `UTF8Decode()`. Don't double-encode UTF data!) – Remy Lebeau Aug 25 '17 at 02:13
https://i.stack.imgur.com/oP3l3.png hi Remy, could u kindly help to see this pic, seems the value is still '?' – kaihong Aug 25 '17 at 02:34
You are evaluating `Value` (a `Variant`) in the debugger. Maybe the tooltip evaluator in Delphi 6 doesn't handle Unicode strings very well? Who knows. You haven't shown the actual data you are working with, or what it looks like at each step. Did you validate that the `WideString` itself contains the correct data? What about the database after the insert? And you didn't answer David's questions. What database are you using? What character encoding does it use? These are important things to know. Your data is going through several layers, data loss could occur at any one of them. – Remy Lebeau Aug 25 '17 at 02:46
it is windows sql server 2008 R2 – kaihong Aug 25 '17 at 03:14

how to use the function "copy" to assign a UTF-8 value to a variable

1 Answers1