Comma decimal separators generates invalid SQL statement

[expired user #9828]'s profile image [expired user #9828] posted 8 years ago in Import/Export Permalink

Hi! Just started using HeidiSQL in a new database project and I ran into this problem. My computer currently uses comma as a decimal separator and there is no way I can change it. The thing is that when exporting to clipboard or file HeidiSQL always generates invalid SQL statements because of the separator (","). For example:

INSERTO INTO "TABLE" ("ID", "NAME", "NUMBER") VALUES ("100", "MY_NAME", 12,10)

SQL seems to understand (as it should) that I'm declaring 3 columns and 4 values because of the "," separator.

I've already searched in the forums and no one seems to propose a good solution besides changing system settings. I can't understand the difficulty of putting a config section to define the language settings for this kind of stuff in the client.

ansgar's profile image ansgar posted 8 years ago Permalink

I cannot reproduce that here, although I'm on a german Windows with comma as decimal separator, just like in your case. The thing is that exporting float values in HeidiSQL does not have anything to do with the Windows locale. Values are exported as they come from MySQL. On my side exporting grid data as SQL Inserts to clipboard looks like this:

INSERT INTO `v2` (`id`, `text`, `text2`, `text3`, `id2`) VALUES (1, '11', 'dd', 'DD', 0.9999);
INSERT INTO `v2` (`id`, `text`, `text2`, `text3`, `id2`) VALUES (2, '0', '0', '0', 0.9999);
INSERT INTO `v2` (`id`, `text`, `text2`, `text3`, `id2`) VALUES (3, '0', '0', '0', 0.9999);

Where the table is defined as follows:

CREATE TABLE `v2` (
    `id` INT(11) NOT NULL,
    `text` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'latin1_swedish_ci',
    `text2` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'latin1_german1_ci',
    `text3` VARCHAR(50) AS (upper(text2)) VIRTUAL COLLATE 'utf16le_bin',
    `id2` DECIMAL(4,4) UNSIGNED ZEROFILL NULL DEFAULT NULL
)
COLLATE='utf16le_bin'
ENGINE=InnoDB

You could also post your CREATE TABLE code here, please.

[expired user #9828]'s profile image [expired user #9828] posted 8 years ago Permalink

Hey, thanks for the reply. I'm using a Windows 10 PT-BR machine. If that's the case you could install a Windows VM and set the language to 'Portuguese/Brazil' so you can reproduce the issue. I think it is ok to show the locale in the datagrid, but by default, float values should always use "." as a separator, otherwise it will generate a invalid sql statement. Maybe there is a way to inforce it when exporting.

Here's the CREATE TABLE script:

-- --------------------------------------------------------
-- Host:                         XX.XXX.XXX.XXX
-- Server version:               Microsoft SQL Server 2014 - 12.0.2000.8
-- Server OS:                    Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
-- HeidiSQL Version:             9.3.0.5078
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES  */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

CREATE TABLE IF NOT EXISTS "RouterInfoLog" (
    "Id" INT(10,0) NOT NULL,
    "MacAddress" NVARCHAR(max) NULL DEFAULT NULL,
    "Ip" NVARCHAR(max) NULL DEFAULT NULL,
    "TimeStamp" DATETIME(3) NULL DEFAULT NULL,
    "Tx" BIGINT(19,0) NOT NULL,
    "Rx" BIGINT(19,0) NOT NULL,
    "Uptime" INT(10,0) NOT NULL,
    "Rssi" INT(10,0) NOT NULL,
    "Coordinates" NVARCHAR(max) NULL DEFAULT NULL,
    "Speed" FLOAT(53) NOT NULL,
    "Altitude" FLOAT(53) NOT NULL,
    "ConnectedUsers" INT(10,0) NOT NULL,
    PRIMARY KEY ("Id")
);

-- Data exporting was unselected.
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
ansgar's profile image ansgar posted 8 years ago Permalink

You could have mentioned that you're using MSSQL, man. In that case I guess it's the server which returns the local formatted float value.

I get this code when exporting grid data to SQL Inserts:

INSERT INTO "RouterInfoLog" ("Id", "MacAddress", "Ip", "TimeStamp", "Tx", "Rx", "Uptime", "Rssi", "Coordinates", "Speed", "Altitude", "ConnectedUsers")
VALUES (1, NULL, NULL, NULL, 2, 1, 1, 1, NULL, 1.3, 1.3, 1);
[expired user #9828]'s profile image [expired user #9828] posted 8 years ago Permalink

Oh, I'm sorry... I didn't realize that this would make any difference. Actually, I don't think this problem is related to the server because when I'm using oracle's sqldeveloper it generates the script just fine:

Insert into RouterInfoLog ("Id","MacAddress","Ip","TimeStamp","Tx","Rx","Uptime","Rssi","Coordinates","Speed","Altitude","ConnectedUsers") values (1508,'00:0D:B9:39:6B:18','192.168.2.1',to_timestamp('2015-12-02 14:57:05.0','null'),990420,3986639,6389,-76,'-20.294825,-40.303038',0.0,-3.1,0);
1 attachment(s):
  • Capturar
kalvaro's profile image kalvaro posted 8 years ago Permalink

I've reproduced the issue, it happens with FLOAT column type and only in Query gris (not in Data grids).

I can't even load the aforementioned CREATE TABLE dump in my SQL Server 2008 instance (it triggers all kind of syntax errors) but I've composed this test case:

CREATE TABLE FOO (
    FOO_ID INT IDENTITY(1, 1) NOT NULL,
    FLOTANTE FLOAT,
    DECIMALES DECIMAL(18,3),
    ENTERO INT,

    CONSTRAINT FOO_PK PRIMARY KEY (FOO_ID)
);
INSERT INTO FOO (FLOTANTE, DECIMALES, ENTERO) VALUES (12.34, 56.78, 910);

... which exports as:

INSERT INTO "foo" ("FOO_ID", "FLOTANTE", "DECIMALES", "ENTERO") VALUES (1, 12,34, 56.78, 910);

My locale is Spanish/Spain and my server version is SQL Server 2008 R2.

ansgar's profile image ansgar posted 8 years ago Permalink

So, the issue seems to be limited to FLOAT columns?

[expired user #9828]'s profile image [expired user #9828] posted 8 years ago Permalink

At first, seems that way

Code modification/commit from ansgarbecker, 8 years ago, revision 5079
Fix wrong FLOAT data type mapping between DB.TFieldType and TDBDatatypeIndex, in TAdoDBQuery.Execute. See http://www.heidisql.com/forum.php?t=21306
ansgar's profile image ansgar posted 8 years ago Permalink

Well, after 2 hours I got it. This was a bug in the detection logic of FLOAT columns in MSSQL query results. ftFloat was mapped to an ENUM datatype, which was totally wrong and does not even exist in MSSQL, as far as I know. r5079 fixes that. Please update.

[expired user #9828]'s profile image [expired user #9828] posted 8 years ago Permalink

Nice, that solved the problem... I'm exporting the grid like this:

INSERT INTO "RouterInfoLog" ("Id", "MacAddress", "Ip", "TimeStamp", "Tx", "Rx", "Uptime", "Rssi", "Coordinates", "Speed", "Altitude", "ConnectedUsers") VALUES (1508, '00:0D:B9:39:6B:18', '192.168.2.1', '2015-12-02 14:57:05.000', 990420, 3986639, 6389, -76, '-20.294825,-40.303038', 0, -3.1, 0);
INSERT INTO "RouterInfoLog" ("Id", "MacAddress", "Ip", "TimeStamp", "Tx", "Rx", "Uptime", "Rssi", "Coordinates", "Speed", "Altitude", "ConnectedUsers") VALUES (1530, '00:0D:B9:39:6B:18', '192.168.2.1', '2015-12-02 14:59:05.000', 29943, 51150, 6509, -76, '-20.294836,-40.303034', 0, -2.4, 0);
INSERT INTO "RouterInfoLog" ("Id", "MacAddress", "Ip", "TimeStamp", "Tx", "Rx", "Uptime", "Rssi", "Coordinates", "Speed", "Altitude", "ConnectedUsers") VALUES (1545, '00:0D:B9:39:6B:18', '192.168.2.1', '2015-12-02 15:01:05.000', 992605, 3988123, 6629, -76, '-20.294842,-40.303031', 0, -2, 0);
INSERT INTO "RouterInfoLog" ("Id", "MacAddress", "Ip", "TimeStamp", "Tx", "Rx", "Uptime", "Rssi", "Coordinates", "Speed", "Altitude", "ConnectedUsers") VALUES (1576, '00:0D:B9:39:6B:18', '192.168.2.1', '2015-12-02 15:03:05.000', 32591, 53067, 6749, -76, '-20.294848,-40.303030', 0, -1.7, 0);

Please login to leave a reply, or register at first.