CODEWITHSUNDEEP
mysqlsqldelphifiredac
Yogi Yang 007
Yogi Yang 007

Reputation: 5251

Delphi XE10.x FireDAC - Error lost connection to MySQL server during query

I am building this simple query using following code:

FDQuery1.SQL.Text := 'INSERT INTO album SET customer_id=' + Chr(39) + IntToStr(CustomerID) + Chr(39) + ',lab_id=' + Chr(39) + IntToStr(LabID) + Chr(39) +
        ', album_name_c = ' + Chr(39) + ProjectFolderName + Chr(39) +
        ', album_name_s = ' + Chr(39) + ProjectFolderNameOnServer + Chr(39) +
        ' ,album_size=' + Chr(39) + txtAlbumSize.Text + Chr(39) +
        ', album_paper=' + Chr(39) + txtPaperFinish.Text + Chr(39) +
        ', album_cover=' + Chr(39) + txtCover.Text + Chr(39) +
        ', album_binding=' + Chr(39) + txtBinding.Text + Chr(39) +
        ', album_coating=' + Chr(39) + txtCoating.Text + Chr(39) +
        ', starts_from=' + Chr(39) + BoolToStr(chkRight.Checked) + Chr(39) +
        '; SELECT LAST_INSERT_ID() AS RecID;';

But when I call FDQuery1.OpenOrExecute I get error message as stated in subject of this post.

Current query length is around 299 characters.

I read somewhere on internet that the Query length should be less than 255 characters, so I have also tried to shorten the query but keep its length below 255 characters.

But still I get the same error message.

What wrong am I doing here please guide me.

TIA

Yogi Yang

Upvotes: 2

Views: 1266

Answers (1)

Yogi Yang 007
Yogi Yang 007

Reputation: 5251

Ok finally I found the actual problem and solved it...

It so happens that the dumb FireDAC's Query component does not support query in the format stated in original post. I had to build traditional Insert query as per SQL norms.

Though query with SET keyword is supported by MySQL and I have been using it in my PHP projects so there is not question of the query being wrong.

Ok what I did with suggestion/help of other developers in another forum was build a parameterized query and use it like below:

FDQuery1.SQL.Text := 'INSERT INTO album(customer_id, album_name_c, album_name_s, album_size, album_paper, album_cover, album_binding, album_coating, starts_from, total_files, lab_id, upload_date, album_uploaded) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?);';

        FDQuery1.Params.Items[0].AsInteger := CustomerID;
        FDQuery1.Params.Items[1].AsString := ProjectFolderName;
        FDQuery1.Params.Items[2].AsString := ProjectFolderNameOnServer;
        FDQuery1.Params.Items[3].AsString := AlbumSize;
        FDQuery1.Params.Items[4].AsString := PaperFinish;
        FDQuery1.Params.Items[5].AsString := Cover;
        FDQuery1.Params.Items[6].AsString := Binding;
        FDQuery1.Params.Items[7].AsString := Coating;

        if chkLeft.Checked then
          FDQuery1.Params.Items[8].AsString := '1'
        else
          FDQuery1.Params.Items[8].AsString := '0';

        if chkRight.Checked then
          FDQuery1.Params.Items[8].AsString := '2'
        else
          FDQuery1.Params.Items[8].AsString := '0';

        FDQuery1.Params.Items[9].AsInteger := lstFiles.Count;
        FDQuery1.Params.Items[10].AsInteger := LabID;
        FDQuery1.Params.Items[11].AsDate := Now;
        FDQuery1.Params.Items[12].AsString := '1';

        FDQuery1.Execute;

Thanks to this parameterized query I did not have to add single quotes to varchar and data fields data using Chr(39).

I hope this will help other developers solve similar problems that they may face.

Regards,

Yogi Yang

Upvotes: 1

Related Questions