MySQL writing into BLOB error: ""is invalid float

[rvk]

Yes, and according to the rest, the columns should match:
The INSERT SQL had 50 fields, and the table also has 50 fields.
So they should match.

Code: Pascal  [Select][+][-]

1. last_insert_id(),        | es_id              
2. :ES_Index,               | idx                        SaveQuery.ParamByName('ES_Index').asString := ES_Index.Text;
3. :fdate,                  | date                       SaveQuery.ParamByName('fdate').asDateTime := StrToDateTime(Date_Time.Text);
4. :customer_es_name,       | cust_station_name          SaveQuery.ParamByName('customer_es_name').asString := customer_es_name.Text;
5. :ASMI_Operator,          | operator                   SaveQuery.ParamByName('ASMI_Operator').asString:=ASMI_Operator.Text;
6. :operator_phone,         | operator_phone             SaveQuery.ParamByName('operator_phone').asString:=operator_phone.Text;
7. :srb_doc,                | srb_doc                    SaveQuery.ParamByName('srb_doc').asString:=srb_doc.Text;
8. :Satellite,              | satellite                  SaveQuery.ParamByName('Satellite').asString:=Satellite.Text;
9. :SatLongitude,           | satellite_longitude        SaveQuery.ParamByName('SatLongitude').asFloat:=StrToFloatDef(SatLongitude.Text,12);
10. :ChooseTP,               | co_tp_name                 SaveQuery.ParamByName('ChooseTP').asString:=ChooseTP.Text;
11. :Co_Up_pol,              | co_tp_pol                  SaveQuery.ParamByName('Co_Up_Pol').asString:=Co_Up_pol.Text;
12. :CX_tp,                  | cx_tp_name                 SaveQuery.ParamByName('CX_tp').asString:=CX_tp.Text;
13. :Cx_Up_pol,              | cx_tp_pol                  SaveQuery.ParamByName('Cx_Up_pol').asString:=Cx_Up_pol.Text;
14. :Station_Own,            | es_owner                   SaveQuery.ParamByName('Station_Own').asString:=Station_Own.Text;
15. :User_Operator,          | contact_person             SaveQuery.ParamByName('User_Operator').asString:=User_Operator.Text;
16. :User_Cont_Phone,        | contact_phone              SaveQuery.ParamByName('User_Cont_Phone').asString:=User_Cont_Phone.Text;
17. :ES_location,            | es_location                SaveQuery.ParamByName('ES_location').asString:=ES_location.Text;
18. :ES_latitude,            | es_latitude                SaveQuery.ParamByName('ES_Latitude').asFloat:=StrToFloatDef(ES_latitude.Text,12);
19. :ES_longitude,           | es_longitude               SaveQuery.ParamByName('ES_Longitude').asFloat:=StrToFloatDef(ES_longitude.Text,12);
20. :Ant_Diameter,           | es_ant_diam                SaveQuery.ParamByName('Ant_Diameter').asFloat:=StrToFloatDef(Ant_Diameter.Text,12);
21. :Ref_Station,            | ref_station                SaveQuery.ParamByName('Ref_Station').asString:=Ref_Station.Text;
22. :F_up,                   | frq_isol_up                SaveQuery.ParamByName('F_up').asFloat:=StrToFloatDef(F_up.Text,12);
23. :F_dn,                   | frq_isol_dn                SaveQuery.ParamByName('F_dn').asFloat:=StrToFloatDef(F_dn.Text,12);
24. :CO_EIRP_f,              | co_eirp_calculated         SaveQuery.ParamByName('CO_EIRP_f').asFloat:=StrToFloatDef(CO_EIRP_f.Text,12);
25. :CPI_es_f,               | cpi_es_calculated          SaveQuery.ParamByName('CPI_es_f').asFloat:=StrToFloatDef(CPI_es_f.Text,12);
26. :sp,                     | dir_spectrum               SaveQuery.ParamByName('sp').asString:=sp;
27. :frq,                    | dir_frq                    SaveQuery.ParamByName('frq').asString:=frq;
28. :eirp,                   | dir_eirp                   SaveQuery.ParamByName('eirp').asString:=eirp;
29. :comment,                | comment                    SaveQuery.ParamByName('comment').asString:=Comment.Text;
30. :Pot_f,                  | Pot                        SaveQuery.ParamByName('Pot_f').asFloat := StrToFloatDef(Pot_f.Text,12); // or StrToFloatDef if Text can be empty or invalid)
31. :Pxt_f,                  | Pxt                        SaveQuery.ParamByName('Pxt_f').asFloat := StrToFloatDef(Pxt_f.Text,12);
32. :Por_f,                  | Por                        SaveQuery.ParamByName('Por_f').asFloat := StrToFloatDef(Por_f.Text,12);
33. :Pxr_f,                  | Pxr                        SaveQuery.ParamByName('Pxr_f').asFloat := StrToFloatDef(Pxr_f.Text,12);
34. :G_CO_Max,               | G_CO_Max                   SaveQuery.ParamByName('G_CO_Max').asFloat := StrToFloatDef(G_CO_Max.Text,12);
35. :Got_f,                  | Got                        SaveQuery.ParamByName('Got_f').asFloat := StrToFloatDef(Got_f.Text,12);
36. :Gor_f,                  | Gor                        SaveQuery.ParamByName('Gor_f').asFloat := StrToFloatDef(Gor_f.Text,12);
37. :Gxt_f,                  | Gxt                        SaveQuery.ParamByName('Gxt_f').asFloat := StrToFloatDef(Gxt_f.Text,12);
38. :Gxr_f,                  | Gxr                        SaveQuery.ParamByName('Gxr_f').asFloat := StrToFloatDef(Gxr_f.Text,12);
39. :Gxtx_f,                 | Gxtx                       SaveQuery.ParamByName('Gxtx_f').asFloat := StrToFloatDef(Gxtx_f.Text,12);
40. :CPIsum_f,               | CPIsum                     SaveQuery.ParamByName('CPIsum_f').asFloat := StrToFloatDef(CPIsum_f.Text,12);
41. :CPIsat_f,               | CPIsat                     SaveQuery.ParamByName('CPIsat_f').asFloat := StrToFloatDef(CPIsat_f.Text,12);
42. :descr_cust              | cust_fullname              SaveQuery.ParamByName('descr_cust').asString:=descr_cust.Text;
43. :City_Full_f,            | city_full                  SaveQuery.ParamByName('City_Full_f').asString:=City_Full_f.Text;
44. :begtime,                | t_strt                     SaveQuery.ParamByName('begtime').asTime:=BegTime.Time;
45. :endTime,                | t_end                      SaveQuery.ParamByName('endTime').asTime:=EndTime.Time;
46. :SpentTime,              | SpentTime                  SaveQuery.ParamByName('SpentTime').asFloat:=StrToFloatDef(SpentTime.Text,12);
47. :F_Centre,               | fcenter                    SaveQuery.ParamByName('F_Centre').asFloat:=StrToFloatDef(F_Centre.Text,12);
48. :BW,                     | band                       SaveQuery.ParamByName('BW').asFloat:=StrToFloatDef(BW.Text,12);
49. :st_co,                  | spect_co                   SaveQuery.ParamByName('st_co').asString := st_co;
50. :st_cx                   | spect_cx                   SaveQuery.ParamByName('st_cx').asString := st_cx;

You can do it another way using the automatic insert-generator of FPC.
I consider it a bit of cheating but you can examine the SQL in the Showmessage for InsertSQL to see what's different.

Code: Pascal  [Select][+][-]

1.   SaveQuery.SQL.Text := 'SELECT * FROM work_sp';
2.   SaveQuery.Active := True; // <-- this will fill in SaveQuery.
3.  
4.   ShowMessage(SaveQuery.UpdateSQL.Text); // <-- This will show the insert statement
5.  
6.   SaveQuery.Insert; // <-- this switches the dataset to insert
7.  
8.   SaveQuery.FieldByName('ES_Index').AsString := ES_Index.Text;
9.   SaveQuery.FieldByName('fdate').AsDateTime := StrToDateTime(Date_Time.Text);  // depending if Date_Time has a Date property or is just text
10.   SaveQuery.FieldByName('customer_es_name').AsString := customer_es_name.Text;
11.   SaveQuery.FieldByName('ASMI_Operator').AsString := ASMI_Operator.Text;
12.   SaveQuery.FieldByName('operator_phone').AsString := operator_phone.Text;
13.   SaveQuery.FieldByName('srb_doc').AsString := srb_doc.Text;
14.   SaveQuery.FieldByName('Satellite').AsString := Satellite.Text;
15.   SaveQuery.FieldByName('SatLongitude').AsFloat := StrToFloatDef(SatLongitude.Text, 12);
16.   SaveQuery.FieldByName('ChooseTP').AsString := ChooseTP.Text;
17.   SaveQuery.FieldByName('Co_Up_Pol').AsString := Co_Up_pol.Text;
18.   SaveQuery.FieldByName('CX_tp').AsString := CX_tp.Text;
19.   SaveQuery.FieldByName('Cx_Up_pol').AsString := Cx_Up_pol.Text;
20.   SaveQuery.FieldByName('Station_Own').AsString := Station_Own.Text;
21.   SaveQuery.FieldByName('User_Operator').AsString := User_Operator.Text;
22.   SaveQuery.FieldByName('User_Cont_Phone').AsString := User_Cont_Phone.Text;
23.   SaveQuery.FieldByName('ES_location').AsString := ES_location.Text;
24.   SaveQuery.FieldByName('ES_Latitude').AsFloat := StrToFloatDef(ES_latitude.Text, 12);
25.   SaveQuery.FieldByName('ES_Longitude').AsFloat := StrToFloatDef(ES_longitude.Text, 12);
26.   SaveQuery.FieldByName('Ant_Diameter').AsFloat := StrToFloatDef(Ant_Diameter.Text, 12);
27.   SaveQuery.FieldByName('Ref_Station').AsString := Ref_Station.Text;
28.   SaveQuery.FieldByName('F_up').AsFloat := StrToFloatDef(F_up.Text, 12);
29.   SaveQuery.FieldByName('F_dn').AsFloat := StrToFloatDef(F_dn.Text, 12);
30.   SaveQuery.FieldByName('CO_EIRP_f').AsFloat := StrToFloatDef(CO_EIRP_f.Text, 12);
31.   SaveQuery.FieldByName('CPI_es_f').AsFloat := StrToFloatDef(CPI_es_f.Text, 12);
32.   SaveQuery.FieldByName('sp').AsString := sp;
33.   SaveQuery.FieldByName('frq').AsString := frq;
34.   SaveQuery.FieldByName('eirp').AsString := eirp;
35.   SaveQuery.FieldByName('comment').AsString := Comment.Text;
36.   SaveQuery.FieldByName('Pot_f').AsFloat := StrToFloatDef(Pot_f.Text, 12);  // or StrToFloatDef if Text can be empty or invalid)
37.   SaveQuery.FieldByName('Pxt_f').AsFloat := StrToFloatDef(Pxt_f.Text, 12);
38.   SaveQuery.FieldByName('Por_f').AsFloat := StrToFloatDef(Por_f.Text, 12);
39.   SaveQuery.FieldByName('Pxr_f').AsFloat := StrToFloatDef(Pxr_f.Text, 12);
40.   SaveQuery.FieldByName('G_CO_Max').AsFloat := StrToFloatDef(G_CO_Max.Text, 12);
41.   SaveQuery.FieldByName('Got_f').AsFloat := StrToFloatDef(Got_f.Text, 12);
42.   SaveQuery.FieldByName('Gor_f').AsFloat := StrToFloatDef(Gor_f.Text, 12);
43.   SaveQuery.FieldByName('Gxt_f').AsFloat := StrToFloatDef(Gxt_f.Text, 12);
44.   SaveQuery.FieldByName('Gxr_f').AsFloat := StrToFloatDef(Gxr_f.Text, 12);
45.   SaveQuery.FieldByName('Gxtx_f').AsFloat := StrToFloatDef(Gxtx_f.Text, 12);
46.   SaveQuery.FieldByName('CPIsum_f').AsFloat := StrToFloatDef(CPIsum_f.Text, 12);
47.   SaveQuery.FieldByName('CPIsat_f').AsFloat := StrToFloatDef(CPIsat_f.Text, 12);
48.   SaveQuery.FieldByName('descr_cust').AsString := descr_cust.Text;
49.   SaveQuery.FieldByName('City_Full_f').AsString := City_Full_f.Text;
50.   SaveQuery.FieldByName('begtime').asTime := BegTime.Time;
51.   SaveQuery.FieldByName('endTime').asTime := EndTime.Time;
52.   SaveQuery.FieldByName('SpentTime').AsFloat := StrToFloatDef(SpentTime.Text, 12);
53.   SaveQuery.FieldByName('F_Centre').AsFloat := StrToFloatDef(F_Centre.Text, 12);
54.   SaveQuery.FieldByName('BW').AsFloat := StrToFloatDef(BW.Text, 12);
55.   SaveQuery.FieldByName('st_co').AsString := st_co;
56.   SaveQuery.FieldByName('st_cx').AsString := st_cx;
57.  
58.   SaveQuery.Post;
59.   SaveQuery.ApplyUpdates
60.   SQLTransaction.Commit;

Although you need to handle the last_insert_id() first and put that manually in es_id.

B.T.W. the showmessage will give you the full format with all fields specified so you could use that one too now because you shouldn't get column count mismatch anymore:

Code: SQL  [Select][+][-]

1. INSERT INTO work_sp (field1, field2) VALUES (:field1, :field2)

[syalyshev]

Yes, and according to the rest, the columns should match:
The INSERT SQL had 50 fields, and the table also has 50 fields.
So they should match.

but they don't match!
Of course the "parameter" method will let me to pinpoint the field that causes ""is invalid float, but how to make it work!

[rvk]

Wait, you said you had the following message:
"MySQL50Connection: error exequting query: Column count doesn't match value count at row 1."

So what message do you really get.

You get set a breakpoint and step through the code until you get the error.

[syalyshev]

I really get "MySQL50Connection: error exequting query: Column count doesn't match value count at row 1." message. The break point was on the

Code: Pascal  [Select][+][-]

1. SaveQuery.ExecSQL

statement

[rvk]

I really get "MySQL50Connection: error exequting query: Column count doesn't match value count at row 1." message. The break point was on the

Code: Pascal  [Select][+][-]

1. SaveQuery.ExecSQL

statement

Yeah, then it's not the "is invalid float" you mentioned a few posts ago.

What does this show you?

Code: Pascal  [Select][+][-]

1. SaveQuery.SQL.Text := 'SELECT * FROM work_sp';
2. SaveQuery.Active := True; // <-- this will fill in SaveQuery.
3. ShowMessage(SaveQuery.InsertSQL.Text); // <-- This will show the insert statement

[syalyshev]

Dear rvk! It is my fault with bad explanation of the problem that caused misunderstanding.
I'll explain it from the beginning. There was table work_sp that was filled in with program using SaveQuery like

Code: Pascal  [Select][+][-]

1. SaveQuery.SQL.ADD('SELECT * from  work_sp');
2. SaveQuery.Active:=true;
3. SaveQuery.Insert;
4. SaveQuery.FieldByName('idx').asString := ES_Index.Text;
5. SaveQuery.FieldByName('date').asDateTime := StrToDateTime(Date_Time.Text);
6. SaveQuery.FieldByName('cust_station_name').asString := customer_es_name.Text;
7. SaveQuery.FieldByName('operator').asString:=ASMI_Operator.Text;
8. SaveQuery.FieldByName('operator_phone').asString:=operator_phone.Text;                  
9. ....
10. SaveQuery.FieldByName('city_full').asString:=City_Full_f.Text;
11. SaveQuery.FieldByName('t_strt').asString:=TimeToStr(BegTime.Time);
12. SaveQuery.FieldByName('t_end').asString:=TimeToStr(EndTime.Time);
13. SaveQuery.FieldByName('SpentTime').asFloat:=StrToFloatDef(SpentTime.Text,12);
14. SaveQuery.Post;
15. SaveQuery.ApplyUpdates;
16.  

It works wery well, saves evry needed value without any error messages.
Then I modified the table work_sp by adding four extra fields: fcenter (decimal(15,6), band (decimal(15,6), spect_co (MEDIUMBLOB or MEDIUMTEXT) and spect_cx (MEDIUMBLOB or MEDIUMTEXT).
Sure I modified SaveQuery with

Code: Pascal  [Select][+][-]

1. SaveQuery.FieldByName('fcenter').asFloat:=StrToFloatDef(F_Centre.Text,12);
2. SaveQuery.FieldByName('band').asFloat:=StrToFloatDef(BW.Text,12);
3. SaveQuery.FieldByName('spect_co').asString := st_co;
4. SaveQuery.FieldByName('spect_cx').asString := st_cx;

[syalyshev]

And then I began to obtain ""is invalid float. So, adding four fields caused this. But recording into table is correct, even BLOBS are correct!
Further I tried to use parameters like you advised for to pinpoint the operator that causes ""is invalid float. It gives "Column count doesn't match value count at row 1."

[rvk]

You still didn't show what the showmessage(SaveQuery.InsertSQL.Text); shows you.

Furthermore... you have a SpentTime defined as varchar(15) and you are trying to put a float (with asFloat) into that field 

So if you use the insert string you need to build it exactly as the showmessage shows you. You shouldn't rely on the default number of fields in the table. Name the fields you are going to update (which InsertSQL.Text will show you).

With the '' is invalid float... there is some line where you try to put a empty text-string into a float (which the message suggest). If you use that method you could set a breakpoint and go over every line until you get that error. The invalid float error should show up directly and not when ExecSQL is done. Only the column mismatch error is given during the ExecSQL.

So what method do you want to use?
1) Insert statement with Parameters?
(then show what the showmessage of insertSQL.Text gives you on Select * from table)

2) Insert statement with hard coded fields?
In that case you need to be very careful how you include your fields (and parameters will work better then)

3) SELECT * FROM table and .Insert
In that case FieldByName can be used and you don't have any problem because FPC constructs the InsertSQL.Text for you.

PS. StrToFloat(F_Centre.Text) will give an error '' is invalid float if F_Centre.Text is empty. That's why you would use StrToFloatDef() to give a default value if a invalid float is in F_Centre.Text. Or are you sure F_Centre.Text is filed with valid floats (with point decimals or comma decimals depending on you locale)?

[syalyshev]

You wrote:

You still didn't show what the showmessage(SaveQuery.InsertSQL.Text); shows you.

It shows blank message.

[rvk]

You wrote:

You still didn't show what the showmessage(SaveQuery.InsertSQL.Text); shows you.

It shows blank message.

Ok, I thought FPC would fill it dynamically but apparently it's only filled in internally when doing the actual insert.

For the column problem you could do something like this (with supplying the fields to insert too):

Code: Pascal  [Select][+][-]

1. SaveQuery.SQL.Add('INSERT INTO work_sp (');
2. SaveQuery.SQL.Add('es_id, idx, date, cust_station_name, operator, operator_phone, ');
3. SaveQuery.SQL.Add('srb_doc, satellite, satellite_longitude, co_tp_name, co_tp_pol, cx_tp_name, cx_tp_pol, es_owner, ');
4. SaveQuery.SQL.Add('contact_person, contact_phone, es_location, es_latitude, es_longitude, es_ant_diam, ');
5. SaveQuery.SQL.Add('ref_station, frq_isol_up, frq_isol_dn, co_eirp_calculated, cpi_es_calculated, dir_spectrum, dir_frq, dir_eirp, comment, Pot, Pxt, Por, ');
6. SaveQuery.SQL.Add('Pxr, G_CO_Max, Got, Gor, Gxt, Gxr, Gxtx, CPIsum, CPIsat, cust_fullname, ');
7. SaveQuery.SQL.Add('city_full, t_strt, t_end, SpentTime, fcenter, band, spect_co, spect_cx ');
8. SaveQuery.SQL.Add(') VALUES (');
9. SaveQuery.SQL.Add('last_insert_id(), :idx, :date, :cust_station_name, :operator, :operator_phone, ');
10. SaveQuery.SQL.Add(':srb_doc, :satellite, :satellite_longitude, :co_tp_name, :co_tp_pol, :cx_tp_name, :cx_tp_pol, :es_owner, ');
11. SaveQuery.SQL.Add(':contact_person, :contact_phone, :es_location, :es_latitude, :es_longitude, :es_ant_diam, ');
12. SaveQuery.SQL.Add(':ref_station, :frq_isol_up, :frq_isol_dn, :co_eirp_calculated, :cpi_es_calculated, :dir_spectrum, :dir_frq, :dir_eirp, :comment, :Pot, :Pxt, :Por, ');
13. SaveQuery.SQL.Add(':Pxr, :G_CO_Max, :Got, :Gor, :Gxt, :Gxr, :Gxtx, :CPIsum, :CPIsat, :cust_fullname, ');
14. SaveQuery.SQL.Add(':city_full, :t_strt, :t_end, :SpentTime, :fcenter, :band, :spect_co, :spect_cx);');

And then fill the :fields with ParamByName() (except es_id which is filled with last_insert_id()).

Do note that you should use asFloat := StrToFloatDef(xx,0) because otherwise you get a '' is invalid float when the xx-string is empty.

[syalyshev]

Dear rvk! Thanks again for your work for me. I really got to know much new thanks to you!
The problem was fixed very simply. I just made the new table field by field manually first had removed SQLTransaction.commit statement after SaveQuery.ApplyUpdates; SaveQuery.ExecSQL; saving each time the new field was added to the table. No "" is invalid float occur! Before and after excluding the transaction commisson with old table I checked saving query and  "" is invalid float occured in both cases. I desided that the old table was damaged and I commited to create the new table as described above. Then I changed the old table for just created one.
SQLTransaction.Commit is a strange thing in Lazarus and I still don't understand completely how it works. At the beginnin of the program there is the procedure of connecting to Database containing the SQLTransaction.Active:=true; statement. The repetition of transaction with commit causes "" is invalid float. That's what I think. But I can be mistaken.

[rvk]

Commit is for the database engine what ApplyUpdates is for your client/computer.

• When filling fields you eventually do a Post. This will put all the fields in the record (simply put) but does nothing else.
• The ApplyUpdates sends this record to the database. At that moment the actual UPDATE command is send to the server. In Delphi the ApplyUpdates is done automatically.
• And finally Commit does the real closing of the transaction.

Note that when using ExecSQL with an INSERT or UPDATE statement, you don't need to use ApplyUpdates because ExecSQL already send the command to the server. ApplyUpdates is only used for "live datasets" i.c.w. .Post, .Delete etc.

The repetition of transaction with commit causes "" is invalid float.

Now that you say it like that, there might be something else going on with your code.

When you do a Commit, it also closes all the datasets you might have open with that same transaction-control. If you have a component which does not react well to a closed dataset, this error could come from that.

Try using a CommitRetaining.