Page 1 of 1

double -> sql

Posted: Mon 10 Nov 2008 13:57
by Ludek
Hi, what is the best way to use values of type double in TMSQuery.SQL statement?
I always used the str() procedure, but it generates now (d2009) a warning about type conversion string/shortstring.
I can't/don't want to use parameters - the query contains many of such values, more than the allowed number of parameters.
Thanks for your tip, Ludek.

Posted: Tue 11 Nov 2008 14:18
by Dimon
Please give a more detailed description of the problem and used SQL query.

While executing SQL query for data modification parameters usage is an optional way (the way with the highest performance).

Posted: Tue 11 Nov 2008 16:08
by Ludek
I wrote it already, but the message got lost.
so once more: I need to have many thousands double values in one single query. Or, I can't bother with generating parameter names, and assigning them after the query generation to the query, i just need to write them directly to keep my algorithms simple and readable.

just something like
insert into mytable(doublecolumn)
values (1.123)
values (1.134)
....
(thousands of such lines)

Posted: Wed 12 Nov 2008 13:43
by Dimon
To convert a floating-point value to a string you can use the FloatToStr function. Also if you want to use the str() procedure, you should change the used variable type to ShortString or ignore the warning about type conversion.

Note: You can use the TMSLoader component that serves for fast data loading into a server table.
You can find more detailed information about this component in SDAC Help.

Posted: Wed 12 Nov 2008 16:13
by Ludek
FloatToStr is unusable, as it depends on windows locale - i.e. it can generate "," as a decimal separator. and that sql server does not like at all.
it seems i have to start using shortstring. but I don't know, what short string length is the best? the default value of 255 characters is (perhaps) unnecessarily long...?
how do YOU format the double values, that I can see in profiler trace? or is it formatted by OLE DB?

Posted: Thu 13 Nov 2008 12:57
by Dimon
Ludek wrote:it seems i have to start using shortstring. but I don't know, what short string length is the best? the default value of 255 characters is (perhaps) unnecessarily long...?
The str() procedure requires shortstring for resulting string. Therefore if you use string parameter it is implicity converted in shortstring in any case.
Ludek wrote:FloatToStr is unusable, as it depends on windows locale - i.e. it can generate "," as a decimal separator. and that sql server does not like at all...
how do YOU format the double values, that I can see in profiler trace? or is it formatted by OLE DB?
We use the FloatToStr function. To specify needed decimal separator for FloatToStr you can use the following code:

Code: Select all

  DecimalSeparator := '.';

Posted: Fri 21 Nov 2008 13:16
by Ludek
ok.