2

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

Yogi Yang 007
  • 5,147
  • 10
  • 56
  • 77
  • Maybe this could help https://stackoverflow.com/q/10563619/8041231 (query length should not be issue here rather than its execution time). And start using parameters (except that you protect your app. from SQL injection, the performance of the query can be rapidly increased if you're executing this query frequently). – Victoria Jun 07 '17 at 10:29
  • There is no way I can set connection timeout as I am programming in Delphi and not use some third party database management software. – Yogi Yang 007 Jun 07 '17 at 10:33
  • MySQL server that I am trying insert data into is hosted on Go Daddy server. If I use select query I am able to get data from server and things work fine but I am not able to insert or update any data... – Yogi Yang 007 Jun 07 '17 at 10:35
  • Hm, then there is not much here to suggest. You can "tune" the `ReadTimeout` and/or `WriteTimeout` connection string parameters, but there is still a chance that it's the server who cuts you off. Still, first off I would check for why does execution of the query takes so long (how long, any triggers, indices?). If it's a reasonable time I would consult the timeout with the server admin (because FireDAC defaults to sufficient timeouts). And in any case use query parameters. – Victoria Jun 07 '17 at 10:48
  • I tried everything. The query does not time any time. I am able to retrieve data from server using SELECT query. But when I try to inert/update data on server it crashes with above message immediately without any processing time period. I checked and double checked for timeout but that does not happen as the error message is thrown immediately on execution of **FDQuery1.OpenOrExecute**. – Yogi Yang 007 Jun 07 '17 at 11:21
  • Assuming this error is thrown immediately and you have left the query and connection component properties in default configuration, we can try to start _blaming_ the server side. There's a few user comments in [this topic](https://dev.mysql.com/doc/refman/8.0/en/error-lost-connection.html), maybe you could try to consult them with the DB admin. – Victoria Jun 07 '17 at 11:38

1 Answers1

1

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

Yogi Yang 007
  • 5,147
  • 10
  • 56
  • 77