Recent

Author Topic: SQLite uses "." for Float Lazarus uses ","  (Read 554 times)

Weitentaaal

  • Jr. Member
  • **
  • Posts: 82
SQLite uses "." for Float Lazarus uses ","
« on: December 01, 2020, 10:50:22 am »
Hello,

I'm just searching for a Better Solution.

I'm currently using String Replace to Replace the "," with ".", because Sqlite throws Exception when i use ",".

So is there any way to replace this or do i have to use StringReplace every Time  ?

wp

  • Hero Member
  • *****
  • Posts: 7955
Re: SQLite uses "." for Float Lazarus uses ","
« Reply #1 on: December 01, 2020, 12:00:03 pm »
What exactly are you doing when the issue happens? I am asking because normally SQLite3 stores floatingpoint values in a binary format so that the decimal separator selection in your OS does not matter. Please show the related code (along with all declarations).
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

Thaddy

  • Hero Member
  • *****
  • Posts: 10684
Re: SQLite uses "." for Float Lazarus uses ","
« Reply #2 on: December 01, 2020, 12:00:55 pm »
You can create a local copy of defaultformatsettings (TFormatsettings) and replace , with . for the decimal separator. (You probably also need to change the thousand separator, depending on locale settings)
Code: Pascal  [Select][+][-]
  1. procedure test;
  2. settings:TFormatsettings;
  3. begin
  4.   settings := defaultformatsettings;
  5.   settings.DecimalSeparator := '.';
  6. // etc... Now you will work with the local copy and changed the separator to a dot.
  7. // handle sqlite here.
  8. end;

rvk

  • Hero Member
  • *****
  • Posts: 4452
Re: SQLite uses "." for Float Lazarus uses ","
« Reply #3 on: December 01, 2020, 12:41:22 pm »
I'm currently using String Replace to Replace the "," with ".", because Sqlite throws Exception when i use ",".
It's also usually a better idea to use parameterized sql statements (for insert and update).
In that case you can use TField.asFloat := 2.30 in code or 2,30 in your text field and the conversion is done automatically.

In what piece of code do you get the exception?
Do you use a TDBEdit? Or a TEdit and do you do you insert manually?

Weitentaaal

  • Jr. Member
  • **
  • Posts: 82
Re: SQLite uses "." for Float Lazarus uses ","
« Reply #4 on: December 01, 2020, 12:48:00 pm »
Its not about Storing those values in the db.

When i Try to use Select ... from .. where Value = 2,30

There i Get the Error so i had to Replace the "," with the dot

@Thaddy but isn't this then only for this 1 Method ?

Thanks to All Answers :)

Here u got My Code where the Excpetion was raised (not anymore with Stringreplace it's just anoying to use this every time)

Code: Pascal  [Select][+][-]
  1.  
  2. //WgrExecute works perfectly // dbProf is my TSQLQUERY // Second Parameter is the SQL String
  3. wgrExecute(dbProf, 'SELECT * FROM Geraet WHERE ZEICHNUNG = ' + Trim((ZeichNr).toString) + ' AND KOMP=2 AND ART=0 AND ((X1<= ' + Trim(PkL1.Caption) + ' AND X2>= ' + Trim(PkL1.Caption) + ') OR (X1<= ' + Trim(PkL2.Caption) + ' AND X2>= ' + Trim(PkL2.Caption) + ')) AND Y1 = ' + Trim(StringReplace((YsKlp).toString, ',', '.', [rfReplaceAll])) + ' AND Y2 = ' + Trim(StringReplace((YeKlp).toString, ',', '.', [rfReplaceAll])) + ' AND Z1 = ' + Trim(StringReplace((ZsKlp).toString, ',', '.', [rfReplaceAll])) + ' AND Z2 = ' + Trim(StringReplace((ZeKlp).toString, ',', '.', [rfReplaceAll])));
  4.  
  5.  
  6.  
« Last Edit: December 01, 2020, 12:53:27 pm by Weitentaaal »

rvk

  • Hero Member
  • *****
  • Posts: 4452
Re: SQLite uses "." for Float Lazarus uses ","
« Reply #5 on: December 01, 2020, 01:07:18 pm »
You SQL is really susceptible to SQL injection.
(I can put something in edit PkL2 and ERASE your complete database !!!)

Make use of parameterized SQL statements.

Something like:
Code: Pascal  [Select][+][-]
  1. dbProf.SQL.Text := 'SELECT * FROM Geraet WHERE ZEICHNUNG = :ZeichNr' + #13 +
  2. ' AND KOMP=2 AND ART=0 ' + #13 +
  3. ' AND ((X1<= :PkL1 AND X2>= :PkL1) OR (X1<= :PkL2 AND X2>= PkL2)) ' + #13 +
  4. ' AND Y1 = :sKlp AND Y2 = :YeKlp AND Z1 = :ZsKlp AND Z2 = :ZeKlp';
  5. dbProf.ParamByName('ZeichNr').asInteger := ZeichNr; // or asFloat depending on what ZeichNr is
  6. dbProf.ParamByName('PkL1').asString := PkL1.Caption;
  7. dbProf.ParamByName('PkL2').asString := PkL2.Caption;
  8. dbProf.ParamByName('sKlp').asFloat := sKlp;
  9. dbProf.ParamByName('YeKlp').asFloat := YeKlp;
  10. dbProf.ParamByName('ZsKlp').asFloat := ZsKlp;
  11. dbProf.Open;

In case with parameters you don't need to worry about the , and . They will be automatic because you assign float.

Weitentaaal

  • Jr. Member
  • **
  • Posts: 82
Re: SQLite uses "." for Float Lazarus uses ","
« Reply #6 on: December 03, 2020, 08:23:37 am »
Thanks @rvk

I didn't knew that you could erase the whole db this way
But it's not important because they db i'm writing into is for every User different.
It's the Produkt of A Calculation and has only informations about this Single Project.

So i could just catch exceptions and everything would be fine i guess ? :o

Thanks again Guys :)

rvk

  • Hero Member
  • *****
  • Posts: 4452
Re: SQLite uses "." for Float Lazarus uses ","
« Reply #7 on: December 03, 2020, 08:38:40 am »
So i could just catch exceptions and everything would be fine i guess ? :o
Well, with capturing the exception your program will continue to run but the record isn't written to the database (so what use is the database then?).

Best option is to use parameterized queries like I showed.

Second best is to use Thaddy's suggestion (TFormatSettings).
There is a FloatToStr where you can pass the TFormatSettings.
So in that example you pass settings to FloatToStr.
https://www.freepascal.org/docs-html/rtl/sysutils/floattostr.html

Third option is putting the variable into a string temporally and change the , to a .
Then construct the sql from those temp strings.
Don't do that directly in the sql because it becomes a mess.

Fourth option is like you did it. But putting it all in one statement really makes it unreadable.

Fifth option is capturing the exception.
In which case the record isn't written to the database and the whole DB becomes useless.
« Last Edit: December 03, 2020, 08:40:53 am by rvk »

Zoran

  • Hero Member
  • *****
  • Posts: 1643
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: SQLite uses "." for Float Lazarus uses ","
« Reply #8 on: December 03, 2020, 08:58:09 am »
I didn't knew that you could erase the whole db this way

See Little Bobby tables.

 

TinyPortal © 2005-2018