* * *

Author Topic: MySQL writing into BLOB error: ""is invalid float  (Read 425 times)

syalyshev

  • New member
  • *
  • Posts: 11
MySQL writing into BLOB error: ""is invalid float
« on: October 13, 2017, 12:33:52 pm »
Could anyone kindly help me.
When attempting to insert string variable into blob field of MySQL table the error message ' ""is invalid float' is obtained. Query formed is like this
Code: Pascal  [Select]
  1. ...
  2.   SaveQuery.SQL.Add(''''+F_Centre.Text+''',');
  3.   SaveQuery.SQL.Add(''''+BW.Text+''',');
  4.   SaveQuery.SQL.Add(''''+st_co+''',');
  5.   SaveQuery.SQL.Add(''''+st_cx+''')');
  6.  
  7.   SaveQuery.ExecSQL;
  8.   SQLTransaction.Commit;
  9. ...  
  10.  
st_co and st_cx are strings inserted into blobs.
All records in the table are written correctly. Is it possible not to show such a message?

rvk

  • Hero Member
  • *****
  • Posts: 2869
Re: MySQL writing into BLOB error: ""is invalid float
« Reply #1 on: October 13, 2017, 01:18:34 pm »
st_co and st_cx are strings inserted into blobs.
All records in the table are written correctly. Is it possible not to show such a message?
Yes, don't insert text when your database/SQL expects a float value.

It's impossible to see because you didn't include the whole SQL statement but in the fields-selection you are trying to insert is a float and you are trying to put text into that (which results in this message).

Check the order of the fields of your entire SQL-statement or put it here with the complete table description.

syalyshev

  • New member
  • *
  • Posts: 11
Re: MySQL writing into BLOB error: ""is invalid float
« Reply #2 on: October 13, 2017, 02:34:06 pm »
Thanks a lot for your reply!
Well, the whole SQL Statement (if nesessary) looks like this:
Code: Pascal  [Select]
  1. ...
  2.   SaveQuery.SQL.Add('INSERT INTO  work_sp');
  3.   SaveQuery.SQL.Add('VALUES (last_insert_id(),'''+ES_Index.Text+''',');                                   {index}
  4.   SaveQuery.SQL.Add(''''+FormatDateTime('yyy.mm.dd/hh:mm:ss',StrToDateTime(Date_Time.Text))+''',');       {date}
  5.   SaveQuery.SQL.Add(''''+customer_es_name.Text+''',');                                                    {cust_es}
  6.   SaveQuery.SQL.Add(''''+ASMI_Operator.Text+''',');                                                       {ASMI_Operator}
  7.   SaveQuery.SQL.Add(''''+operator_phone.Text+''',');                                                      {operator_phone}
  8.   SaveQuery.SQL.Add(''''+srb_doc.Text+''',');                                                             {srb_doc}
  9.   SaveQuery.SQL.Add(''''+Satellite.Text+''',');                                                           {Satellite}
  10.   SaveQuery.SQL.Add(''''+SatLongitude.Text+''',');                                                        {Sat Longitude}
  11.   SaveQuery.SQL.Add(''''+ChooseTP.Text+''',');                                                            {co_tp_name}
  12.   SaveQuery.SQL.Add(''''+Co_Up_pol.Text+''',');                                                           {co_up_pol}
  13.   SaveQuery.SQL.Add(''''+CX_tp.Text+''',');                                                               {cx_tp_name}
  14.   SaveQuery.SQL.Add(''''+Cx_Up_pol.Text+''',');                                                           {cx_up_pol}
  15.   SaveQuery.SQL.Add(''''+Station_Own.Text+''',');                                                         {es_owner}
  16.   SaveQuery.SQL.Add(''''+User_Operator.Text+''',');                                                       {contact_person}
  17.   SaveQuery.SQL.Add(''''+User_Cont_Phone.Text+''',');                                                     {contact_phone}
  18.   SaveQuery.SQL.Add(''''+ES_location.Text+''',');                                                         {es_location}
  19.   SaveQuery.SQL.Add(''''+ES_latitude.Text+''',');                                                         {es_latitude}
  20.   SaveQuery.SQL.Add(''''+ES_longitude.Text+''',');                                                        {es_longitude}
  21.   SaveQuery.SQL.Add(''''+Ant_Diameter.Text+''',');                                                        {es_ant_diam}
  22.   SaveQuery.SQL.Add(''''+Ref_Station.Text+''',');                                                         {ref_station}
  23.   SaveQuery.SQL.Add(''''+F_up.Text+''',');                                                                {frq_isol_up}
  24.   SaveQuery.SQL.Add(''''+F_dn.Text+''',');                                                                {frq_isol_dn}
  25.   SaveQuery.SQL.Add(''''+CO_EIRP_f.Text+''',');                                                           {co_eirp_calculated}
  26.   SaveQuery.SQL.Add(''''+CPI_es_f.Text+''',');                                                            {cpi_es_calculated}
  27.   SaveQuery.SQL.Add(''''+sp+''',');                                                                       {dir_spectrum}
  28.   SaveQuery.SQL.Add(''''+frq+''',');                                                                      {dir_frq}
  29.   SaveQuery.SQL.Add(''''+eirp+''',');                                                                     {dir_frq}
  30.   SaveQuery.SQL.Add(''''+'0'+''',');                                                                      {Comment}
  31.   SaveQuery.SQL.Add(''''+Pot_f.Text+''',');                                                               {Pot}
  32.   SaveQuery.SQL.Add(''''+Pxt_f.Text+''',');                                                               {Pxt}
  33.   SaveQuery.SQL.Add(''''+Por_f.Text+''',');                                                               {Por}
  34.   SaveQuery.SQL.Add(''''+Pxr_f.Text+''',');                                                               {Pxr}
  35.   SaveQuery.SQL.Add(''''+G_CO_Max.Text+''',');                                                            {G_CO_Max}
  36.   SaveQuery.SQL.Add(''''+Got_f.Text+''',');                                                               {Got}
  37.   SaveQuery.SQL.Add(''''+Gor_f.Text+''',');                                                               {Gor}
  38.   SaveQuery.SQL.Add(''''+Gxt_f.Text+''',');                                                               {Gxt}
  39.   SaveQuery.SQL.Add(''''+Gxr_f.Text+''',');                                                               {Gxr}
  40.   SaveQuery.SQL.Add(''''+Gxtx_f.Text+''',');                                                              {Gxtx}
  41.   SaveQuery.SQL.Add(''''+CPIsum_f.Text+''',');                                                            {CPIsum}
  42.   SaveQuery.SQL.Add(''''+CPIsat_f.Text+''',');                                                            {CPIsat}
  43.   SaveQuery.SQL.Add(''''+descr_cust.Text+''',');                                                          {cust_full}
  44.   SaveQuery.SQL.Add(''''+City_Full_f.Text+''',');                                                         {City_Full}
  45.   SaveQuery.SQL.Add(''''+TimeToStr(BegTime.Time)+''',');                                                  {BegTime}
  46.   SaveQuery.SQL.Add(''''+TimeToStr(EndTime.Time)+''',');                                                  {EndTime}
  47.   SaveQuery.SQL.Add(''''+SpentTime.Text+''',');                                                           {SpentTime}
  48.   SaveQuery.SQL.Add(''''+F_Centre.Text+''',');                                                            {central FRQ}
  49.   SaveQuery.SQL.Add(''''+BW.Text+''',');                                                                  {Band Width}
  50.   SaveQuery.SQL.Add(''''+st_co+''',');                                                                    {Spectrum values CO}
  51.   SaveQuery.SQL.Add(''''+st_cx+''')');                                                                    {Spectrum values CX}
  52.  
  53.   SaveQuery.ExecSQL;
  54.   SQLTransaction.Commit;
  55. ...  
Table structure as per attached table_structure.jpg.
I have just changed types of fields st_co and ct_cx from MEDIUMBLOB to MEDIUMTEXT for to confirm that string was expected, not float! The result was the same :(
What was my mistake? The message '""is invalid float' though not having influence on the result of recording to database annoys very much!

rvk

  • Hero Member
  • *****
  • Posts: 2869
Re: MySQL writing into BLOB error: ""is invalid float
« Reply #3 on: October 13, 2017, 02:47:51 pm »
Table structure as per attached table_structure.jpg.
I have just changed types of fields st_co and ct_cx from MEDIUMBLOB to MEDIUMTEXT for to confirm that string was expected, not float! The result was the same :(
What was my mistake? The message '""is invalid float' though not having influence on the result of recording to database annoys very much!
The fields st_co and ct_cx are not your problem.

If you look at the following fields... you can see you pass them as TEXT (with quotes around it). But according to your DB-design these should be DECIMAL(8,2)s. So you need to pass them as values/valid floats (so remove the extra '). And if one of these field.Text is empty string you should convert it to 0 or NULL. A '' can't be stored in a DECIMAL(8,2).

Code: Pascal  [Select]
  1.   SaveQuery.SQL.Add(''''+Pot_f.Text+''',');                                                               {Pot}
  2.   SaveQuery.SQL.Add(''''+Pxt_f.Text+''',');                                                               {Pxt}
  3.   SaveQuery.SQL.Add(''''+Por_f.Text+''',');                                                               {Por}
  4.   SaveQuery.SQL.Add(''''+Pxr_f.Text+''',');                                                               {Pxr}
  5.   SaveQuery.SQL.Add(''''+G_CO_Max.Text+''',');                                                            {G_CO_Max}
  6.   SaveQuery.SQL.Add(''''+Got_f.Text+''',');                                                               {Got}
  7.   SaveQuery.SQL.Add(''''+Gor_f.Text+''',');                                                               {Gor}
  8.   SaveQuery.SQL.Add(''''+Gxt_f.Text+''',');                                                               {Gxt}
  9.   SaveQuery.SQL.Add(''''+Gxr_f.Text+''',');                                                               {Gxr}
  10.   SaveQuery.SQL.Add(''''+Gxtx_f.Text+''',');                                                              {Gxtx}
  11.   SaveQuery.SQL.Add(''''+CPIsum_f.Text+''',');                                                            {CPIsum}
  12.   SaveQuery.SQL.Add(''''+CPIsat_f.Text+''',');                                                            {CPIsat}
  13.  
  14.   SaveQuery.SQL.Add(''''+F_Centre.Text+''',');                                                            {central FRQ}
  15.   SaveQuery.SQL.Add(''''+BW.Text+''',');                                                                  {Band Width}

syalyshev

  • New member
  • *
  • Posts: 11
Re: MySQL writing into BLOB error: ""is invalid float
« Reply #4 on: October 13, 2017, 03:19:00 pm »
Thanks again for your reply!
I followed your recommendations and obtained no effect. No change.
One thing puzzles... Until I addded fields: fcenter, band (float!) and spect_co, spect_cx (blob or text!), everything worked, records were written without rough. Adding these fields changed the structure of the table dramatically!

Thanks a lot anyway! Going to think over the issue further!

rvk

  • Hero Member
  • *****
  • Posts: 2869
Re: MySQL writing into BLOB error: ""is invalid float
« Reply #5 on: October 13, 2017, 03:34:47 pm »
What are st_co and st_cx for strings. Are they multiline strings? In that case you can't add them with just the INSERT statement and you need to work with parameters (which is better anyway).

I would change the whole SaveQuery.SQL.Text to use parameters.

Something like this:
Code: Pascal  [Select]
  1.   SaveQuery.SQL.Add('INSERT INTO work_sp');
  2.   SaveQuery.SQL.Add('VALUES (last_insert_id(),:ES_Index, :fdate, :customer_es_name, :ASMI_Operator, :operator_phone,');
  3.   SaveQuery.SQL.Add(':srb_doc, :Satellite, :SatLongitude, :ChooseTP, :Co_Up_pol, :CX_tp, :Cx_Up_pol, :Station_Own,');
  4.   SaveQuery.SQL.Add(':User_Operator, :User_Cont_Phone, :ES_location, :ES_latitude, :ES_longitude, :Ant_Diameter,');
  5.   SaveQuery.SQL.Add(':Ref_Station, :F_up, :F_dn, :CO_EIRP_f, :CPI_es_f, :sp, :frq, :eirp, 0, :Pot_f, :Pxt_f, :Por_f,');
  6.   SaveQuery.SQL.Add(':Pxr_f, :G_CO_Max, :Got_f, :Gor_f, :Gxt_f, :Gxr_f, :Gxtx_f, :CPIsum_f, :CPIsat_f, :descr_cust');
  7.   SaveQuery.SQL.Add(':City_Full_f, :begtime, :endTime, :SpentTime, :F_Centre, :BW, :st_co, :st_cx);');
After that you can do:
Code: Pascal  [Select]
  1. SaveQuery.ParamByName('ES_Index').asInteger := IntToStrDef(ES_Index.Text, 0);
  2. SaveQuery.ParamByName('fdate').asDateTime := Date_Time.Value; // depending if Date_Time has a Date property or is just text
  3. SaveQuery.ParamByName('customer_es_name').asString := customer_es_name.Text;
  4. //...
  5. SaveQuery.ParamByName('Pot_f').asFloat := StrToFloat(Pot_f.Text); // or StrToFloatDef if Text can be empty or invalid)
  6. //...
  7. SaveQuery.ParamByName('st_co').asString := st_co;
  8. SaveQuery.ParamByName('st_cx').asString := st_cx;
  9. SaveQuery.ExecSQL

Another option could be:
Code: Pascal  [Select]
  1. SaveQuery.SQL.Add('SELECT * FROM work_sp');
  2. SaveQuery.Insert;
  3. SaveQuery.FieldByName('ES_Index').asInteger := IntToStrDef(ES_Index.Text, 0);
  4. //... etc
  5. SaveQuery.Post;
« Last Edit: October 13, 2017, 03:36:41 pm by rvk »

syalyshev

  • New member
  • *
  • Posts: 11
Re: MySQL writing into BLOB error: ""is invalid float
« Reply #6 on: October 13, 2017, 03:41:36 pm »
Many thanks for the idea! I'd never thought of such thing as parameter. I'll try it but not today!
Thanks for your big work for me!

syalyshev

  • New member
  • *
  • Posts: 11
Re: MySQL writing into BLOB error: ""is invalid float
« Reply #7 on: October 18, 2017, 11:09:54 am »
Followed all your variants. Result was the same :(
Many thanks anyway!
Will think further...

rvk

  • Hero Member
  • *****
  • Posts: 2869
Re: MySQL writing into BLOB error: ""is invalid float
« Reply #8 on: October 18, 2017, 11:19:56 am »
If you used the parameter method you can exactly pinpoint where the error-message is coming from.

If you still get the  ""is invalid float' you are still trying to put an empty string into a float somewhere, which isn't possible.

If you used StrToFloat(Pot_f.Text); and Pot_f.Text is an empty string, you should use StrToFloatDef(Pot_f.Text, 0); so that an empty string (or invalid float) is automatically converted to 0.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus