[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1274
  • Last Modified:

Delphi Tmemo with more than one line into Mysql

Dear Experts,

I have some fields on a form, let's say one Tedit and Tmemo fileds.

Edit1 has as text "Customer 1"
Memo1 has as text

"south street 1
Las vegas"

Now I want to insert them into MySQL and I use the following statement:

query.SQL.text := 'insert into test (name, address) VALUES ("'+edit1.text + '","' + Memo1.Text + "'";

But I always get an error that the query was not successfull... getting the SQL string it shows:

'insert into test (name, address) VALUES ("Customer1",""south street1
Las Vegas"");

It is like when the memo contains more than one line then getting the memo text will add quotes at the
beginning and at the end...

Any idea on how to solve this issue?

Thank you.
Dave

0
dcrudo
Asked:
dcrudo
  • 5
  • 5
2 Solutions
 
Pierre CorneliusCommented:
Your apostrophe marks are wrong.

You have:  query.SQL.text := 'insert into test (name, address) VALUES ("'+edit1.text + '","' + Memo1.Text + "'";
should be: query.SQL.text := 'insert into test (name, address) VALUES ("'+edit1.text + '","' + Memo1.Text + '"';

I'm surprised the compiler didn't give you an error.
0
 
dcrudoAuthor Commented:
Hi,

sorry... it was a mistake in writing the string ... I didn't copy paste...just copy...because my query is a little more complex ;)

yes, my string is delimited as you wrote... and it gives me the error above...

any idea?

Thank you!!
0
 
Pierre CorneliusCommented:
What are the field dtypes for name and address?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dcrudoAuthor Commented:
in the database the fields types are:

name : varchar 255
address : text

to give you a better idea let me explain more:

The following is the resulting query:

insert into ana_customers (id,firstname, lastname, civils, address, zip, city, tel_home,tel_mobile, tel_fax, email, no_avs, doctor_name, insurance_name, insurance_number, treatment_def, diagnosis, agreed_hours, def_positions, active,birth, treatment_start, treatment_end) VALUES ("109","Dave","Ohara","Married",""Las vegas"","","","","","","","","","","","","","90","","-1","1980-05-25","0000-00-00","0000-00-00")

as you see near 'Las Vegas' quotes are doubled... this is the only field of type MEMO... to insert those fields i use the following function:

Function TDM1.dbins(tablex,variablex : String; valuex: Variant): string;
  var sql : string;

Begin

    valuex := ReplaceStr(valuex,'''',''''''); // Double quotes in case one field contains them
    valuex := ReplaceStr(valuex,char(186),'","'); //use ASCII code 186 to delimit fields

    sql := 'insert into ' + tablex + ' ('+ variablex + ') VALUES ("' + valuex + '")';
    qry_dbins.SQL.Text := sql;

    Data.DM1.InfoBox(sql,1,1);

    try
     begin
      qry_dbins.Execute;
      result := '0';
     end;
    Except On E:Exception Do
      begin
        Data.DM1.InfoBox('SQL Error: '+E.message,1,0);
        Data.DM1.InfoBox('------------------------------------------------------------------',1,0);
        Data.DM1.InfoBox(sql,1,1);

        result := '1';
      end;
    end;
end;


To collect all information from the form, I put all fields in a TstringList:

procedure TForm1.suiButton3Click(Sender: TObject);
var strlVal : TstringList;
    x : Integer;
    Fields : String;

begin
// Start Customer Insert

strlVal := TStringList.Create;
strlVal.Clear;
strlVal.Delimiter := char(186);

strlVal.Add(Data.DM1.nextId('cus'));
strlVal.Add(ed_c_firstname.Text);
strlVal.Add(ed_c_lastname.Text);
strlVal.Add(ed_c_civils.Text);
strlVal.Add(ed_c_address.EditingValue);
strlVal.Add(ed_c_zip.Text);
strlVal.Add(ed_c_city.Text);
strlVal.Add(ed_c_tel_home.Text);
strlVal.Add(ed_c_tel_mobile.Text);
strlVal.Add(ed_c_tel_fax.Text);
strlVal.Add(ed_c_email.Text);
strlVal.Add(ed_c_no_avs.Text);
strlVal.Add(ed_c_doctor_name.Text);
strlVal.Add(ed_c_insurance_name.Text);
strlVal.Add(ed_c_insurance_number.Text);
strlVal.Add(ed_c_treatment_def.Text);
strlVal.Add(ed_c_diagnosis.Text);
strlVal.Add(ed_c_agreed_hours.Text);
strlVal.Add(ed_c_def_positions.Text);
strlVal.Add(BoolToStr(ed_c_active.Checked));


strlVal.add(FormatDateTime('yyyy/mm/dd',ed_c_birth.CurrentDate));
strlVal.Add(FormatDateTime('yyyy/mm/dd',ed_c_treatment_start.CurrentDate));
strlVal.Add(FormatDateTime('yyyy/mm/dd',ed_c_treatment_end.CurrentDate));


Fields := 'id,firstname, lastname, civils, address, zip, city, tel_home,'+
          'tel_mobile, tel_fax, email, no_avs, doctor_name, insurance_name, '+
          'insurance_number, treatment_def, diagnosis, agreed_hours, def_positions, active,'+
          'birth, treatment_start, treatment_end';


try
  Begin
   Data.DM1.dbins('ana_customers',Fields, strlVal.DelimitedText);
  end;
except
  Begin
   
  end;
end;

end;
0
 
Pierre CorneliusCommented:
Is ed_c_address a TMemo? I'm not familiar with the "EditingValue". Should this not be

strlVal.Add(ed_c_address.Text);
0
 
dcrudoAuthor Commented:
With:

strlVal.Add(ed_c_address.Text);

I get the same result... the quotes are doubled... I wonder if this could be an effect of the TStringList where I store the variables... that when it finds a 'new line' than it will add the quotes to everything...

any idea?

thank you!
0
 
Pierre CorneliusCommented:
Looks fine to me. Only thing I can think of is that the string you add to the Stringlist contains the "

After this call: strlVal.Add(ed_c_address.EditingValue);

Check if StrlVal[4]
contains this: "Las vegas"
or this: Las vegas
0
 
Pierre CorneliusCommented:
Can you show me your ReplaceStr function
0
 
JaccoCommented:
Hi there,

It is a better idea to work with parameters.

I used the BDE as a sample because I don't know which components to use for MySQL.

If you make the datamodule look like this:

object DM1: TDM1
  OldCreateOrder = False
  Left = 302
  Top = 213
  Height = 177
  Width = 263
  object qry_dbins: TQuery
    SQL.Strings = (
      'insert into ana_customers ('
      '   id'
      '  ,firstname'
      '  ,lastname'
      '  ,civils'
      '  ,address'
      '  ,zip'
      '  ,city'
      '  ,tel_home'
      '  ,tel_mobile'
      '  ,tel_fax'
      '  ,email'
      '  ,no_avs'
      '  ,doctor_name'
      '  ,insurance_name'
      '  ,insurance_number'
      '  ,treatment_def'
      '  ,diagnosis'
      '  ,agreed_hours'
      '  ,def_positions'
      '  ,active'
      '  ,birth'
      '  ,treatment_start'
      '  ,treatment_end'
      ') values ('
      '   :id'
      '  ,:firstname'
      '  ,:lastname'
      '  ,:civils'
      '  ,:address'
      '  ,:zip'
      '  ,:city'
      '  ,:tel_home'
      '  ,:tel_mobile'
      '  ,:tel_fax'
      '  ,:email'
      '  ,:no_avs'
      '  ,:doctor_name'
      '  ,:insurance_name'
      '  ,:insurance_number'
      '  ,:treatment_def'
      '  ,:diagnosis'
      '  ,:agreed_hours'
      '  ,:def_positions'
      '  ,:active'
      '  ,:birth'
      '  ,:treatment_start'
      '  ,:treatment_end'
      ')')
    Left = 16
    Top = 8
  end
end

Then you can call the Query like this:

procedure TForm1.StoreRecord;
begin
  with DM1.qry_dbins do
  begin
    ParamByName('id'               ).AsInteger  := DM1.nextId('cus');
    ParamByName('firstname'        ).AsString   := ed_c_firstname.Text;
    ParamByName('lastname'         ).AsString   := ed_c_lastname.Text;
    ParamByName('civils'           ).AsString   := ed_c_civils.Text;
    ParamByName('address'          ).AsString   := ed_c_address.EditingValue;
    ParamByName('zip'              ).AsString   := ed_c_zip.Text;
    ParamByName('city'             ).AsString   := ed_c_city.Text;
    ParamByName('tel_home'         ).AsString   := ed_c_tel_home.Text;
    ParamByName('tel_mobile'       ).AsString   := ed_c_tel_mobile.Text;
    ParamByName('tel_fax'          ).AsString   := ed_c_tel_fax.Text;
    ParamByName('email'            ).AsString   := ed_c_email.Text;
    ParamByName('no_avs'           ).AsString   := ed_c_no_avs.Text;
    ParamByName('doctor_name'      ).AsString   := ed_c_doctor_name.Text;
    ParamByName('insurance_name'   ).AsString   := ed_c_insurance_name.Text;
    ParamByName('insurance_number' ).AsString   := ed_c_insurance_number.Text;
    ParamByName('treatment_def'    ).AsString   := ed_c_treatment_def.Text;
    ParamByName('diagnosis'        ).AsString   := ed_c_diagnosis.Text;
    ParamByName('agreed_hours'     ).AsInteger  := StrToIntDef(ed_c_agreed_hours.Text, 0); // or .AsString  := ed_c_agreed_hours.Text;
    ParamByName('def_positions'    ).AsString   := ed_c_def_positions.Text;
    ParamByName('active'           ).AsBoolean  := ed_c_active.Checked;                    // or .AsString := BoolToStr(ed_c_active.Checked);
    ParamByName('birth'            ).AsDateTime := ed_c_birth.CurrentDate;                 // or .AsString := FormatDateTime('yyyy/mm/dd',ed_c_birth.CurrentDate);
    ParamByName('treatment_start'  ).AsDateTime := ed_c_treatment_start.CurrentDate;       // or same as comment above
    ParamByName('treatment_end'    ).AsDateTime := ed_c_treatment_end.CurrentDate;         // or same as comment above
    ExecSQL;
  end;
end;

I also changed some types to one other than varchar 255. I have put in the comment what you shut put there if you want to keep the date fields inside varchar fields.

Hope this helps. Let me know which component you use for accessing MySQL then I will create a sample of how to do this with that component.

Regards Jacco
0
 
dcrudoAuthor Commented:
Problem solved!

This is what happens:

when a TStringlist contains a new line like the following:

Street 1
las vegas

quotes are automatically added to the string (it does not happens if the line is only one!)

To solve it:

I've found this property on the TstringList object: StringList1.QuoteChar

StringList1.Quotechar := char(187);

and then when I have the complete insert statement ready, before executing the query I do:

ReplaceStr(valuex,char(187),'');

and will do the trick...

char 187 is a symbol that usually is not used when writing an addres ;)

Thank you very much!!

Dave.
0
 
dcrudoAuthor Commented:
Jacco,

thx a lot for your time too!!

I will assign to both of you some points... thank you very much!!

Dave.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now