Created attachment 174499 [details] Database with a macro example Version: 7.1.5.2 / LibreOffice Community Build ID: 85f04e9f809797b8199d13c421bd8a2b025d52b5 CPU threads: 4; OS: Linux 5.11; UI render: default; VCL: gtk3 Locale: de-AT (en_US.UTF-8); UI: en-US Calc: threaded ================================================================== Have a look at my locale settings above. No matter, if I use a constant with decimal POINTS or a numeric property of an object, that numeric values are converted - in my case to German format with decimal COMMA. The problem in this case is: I use that kind of values in a SQL statement inside the macro, which leads to misinterpretation. (see my attached Database with macro in it. ================================================================== WORKAROUND: using the "replace" function to replace comma by point. BUT: I have to handle every possible locale setting ================================================================== SUGGESTION: Inside a macro the format of numbers should always convert to internal native format by default. For converting to locale format there should be a conversion function. Have a look at the macro in the attachment
tested again with Version: 7.4.2.3 / LibreOffice Community Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: de-AT (en_US.UTF-8); UI: en-US Calc: threaded ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+ See my settings: Locale: de-AT (en_US.UTF-8); UI: en-US ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ In the attached database the testcase is "Sub DBOpen". You can run it with "F5" The other modules are for displaying the behaviour. Have a look at the values: 1111.11 and 12345.678 (with a decimal DOT) These constants are converted to 1111,11 and 12345,678 (wit a decimal COMMA) You can see it in the message box In the following command "oResult = oSQL_Command.executeQuery(stSQL)" the commas of the "stSql" string are interpreted as separators and the error message shows, that there are 3 columns, but 5 values
(In reply to Richard Demattio from comment #1) > tested again with > Version: 7.4.2.3 / LibreOffice Community > Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf > CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3 > Locale: de-AT (en_US.UTF-8); UI: en-US > Calc: threaded > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+ > > In the following command "oResult = oSQL_Command.executeQuery(stSQL)" > the commas of the "stSql" string are interpreted as separators > and the error message shows, that there are 3 columns, but 5 values I can confirm the conversion to the comma as decimal point, and the corresponding column miscount error from Firebird when executing the macro. However, if I surround the constant values provided in the macro with double-quotes, the conversion still occurs, but at least it doesn't produce a Firebird column mismatch error. Tested on macOS Silicon M1 Ventura Version: 7.4.1.2 / LibreOffice Community aarch64 Build ID: 3c58a8f3a960df8bc8fd77b461821e42c061c5f0 CPU threads: 8; OS: Mac OS X 13.0.1; UI render: default; VCL: osx Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR Calc: threaded I'm not a macro language expert, but does the property "localeSensitive" have anything to do with the introduction of a comma decimal separator into the constructed SQL statement, or does that serve some other purpose ?
Version: 7.6.4.1 (X86_64) / LibreOffice Community Build ID: e19e193f88cd6c0525a17fb7a176ed8e6a3e2aa1 CPU threads: 4; OS: Linux 6.5; UI render: default; VCL: gtk3 Locale: de-AT (en_US.UTF-8); UI: en-US Calc: threaded continues to be reproducable