| Bug #46601 | Concat gives ?????? as result in field (vb6) | ||
|---|---|---|---|
| Submitted: | 7 Aug 2009 13:57 | Modified: | 24 Aug 2009 7:24 |
| Reporter: | Jackson Kimani | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | MYsql 5.1.5 odbc 5.01.05 | OS: | Windows (XP) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | concat | ||
[7 Aug 2009 14:13]
Jackson Kimani
Description:
OS= WINDOWS XP, vISUAL Basic 6,MYsql 5.1.5 , Mysql odbc 5.01.05
After fetching a recordset, it returns a recordset with the concat field
(Costing_sheet_hd_id) result as ?????? . The other fields remain fine.
After removing Costing_sheet_hd_id (primary key) , the concat field returns valid data.
How to repeat:
"select concat('CST', DATE_FORMAT(TRANS_DATE, '%d') , DATE_FORMAT(TRANS_DATE,
'%y') , Costing_sheet_hd_id ) as filerefs from costing"
CREATE TABLE `costing` (
`Costing_sheet_hd_id` int(11) NOT NULL AUTO_INCREMENT,
`TRANS_DATE` date DEFAULT NULL,
PRIMARY KEY (`Costing_sheet_hd_id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=latin1;
/*Data for the table `costing` */
insert into `costing`(`Costing_sheet_hd_id`,`TRANS_DATE`) values (64,'2009-08-07');
[24 Aug 2009 7:24]
Tonci Grgin
Hi Jackals and thanks for your report. This is not a bug and for reasons I explained many many times already. Please first see DATE_FORMAT page in manual (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format): Before 5.1.15, the return value is a binary string. Then see CONCAT page in manual (http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_concat): Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example: So, actually, your data is returned in for of 0xYour_actual_concat_result and is correct. Please use explicit CAST (as described in manual) and/or check "Always handle binary function results as character data" configuration option (Flags1). I described the origin of problem in my post [20 Mar 2007 10:48] Tonci Grgin in Bug#10491.
[24 Aug 2009 7:25]
Tonci Grgin
s/in for of/in form of
Description: OS= WINDOWS XP, vISUAL Basic 6,MYsql 5.1.5 , Mysql odbc 5.01.05 After fetching a recordset, it returns a recordset with the concat field (Costing_sheet_hd_id) result as ?????? . The other fields remain fine. After removing Costing_sheet_hd_id (primary key) , the concat field returns valid data. How to repeat: "select concat('CST', DATE_FORMAT(TRANS_DATE, '%d') , DATE_FORMAT(TRANS_DATE, '%y') , Costing_sheet_hd_id ) as filerefs from costing_sheet_hd" /*Table structure for table `costing_sheet_hd` */ DROP TABLE IF EXISTS `costing_sheet_hd`; CREATE TABLE `costing_sheet_hd` ( `Costing_sheet_hd_id` int(11) NOT NULL AUTO_INCREMENT, `day` int(11) DEFAULT NULL, `date` date DEFAULT NULL, `route` int(11) DEFAULT NULL, `roomtype` int(11) DEFAULT NULL, `halfdouble` int(11) DEFAULT NULL, `srs` int(11) DEFAULT NULL, `thirdadult` int(11) DEFAULT NULL, `childsharing` int(11) DEFAULT NULL, `childown` int(11) DEFAULT NULL, `rate_hd_id` int(11) DEFAULT NULL, `accomodation_hd_id` int(11) DEFAULT NULL, `TRANS_DATE` date DEFAULT NULL, `client` varchar(50) DEFAULT NULL, `AGENT` int(11) DEFAULT NULL, `ARRIVAL_DATE` date DEFAULT NULL, `ARRIVAL_TIME` int(4) DEFAULT NULL, `DEP_TIME` int(4) DEFAULT NULL, `DEP_DATE` date DEFAULT NULL, `PAX` int(11) DEFAULT NULL, `noofchildren` int(2) DEFAULT NULL, `consultant` int(2) DEFAULT NULL, `ARRIVAL_POINT` varchar(100) DEFAULT NULL, `markup` smallint(4) DEFAULT NULL, `kmrate` decimal(5,2) DEFAULT NULL, `driversallowancerate` smallint(4) DEFAULT NULL, `additionalservices` smallint(4) DEFAULT NULL, `startingpoint` int(3) DEFAULT NULL COMMENT 'stores the point of reference(STARTING POINT) of the PARKS KM', `active` smallint(1) DEFAULT NULL, `dollareurorate` decimal(5,2) DEFAULT NULL, `status` int(1) DEFAULT '1' COMMENT '2=confirmed,1=waitlisted,3=cancelled', `cost` decimal(10,2) DEFAULT NULL, `expend` decimal(10,2) DEFAULT NULL, `proloss` decimal(10,2) DEFAULT NULL, `DEPARTURE_POINT` varchar(100) DEFAULT NULL, PRIMARY KEY (`Costing_sheet_hd_id`) ) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=latin1; /*Data for the table `costing_sheet_hd` */ insert into `costing_sheet_hd`(`Costing_sheet_hd_id`,`day`,`date`,`route`,`roomtype`,`halfdouble`,`srs`,`thirdadult`,`childsharing`,`childown`,`rate_hd_id`,`accomodation_hd_id`,`TRANS_DATE`,`client`,`AGENT`,`ARRIVAL_DATE`,`ARRIVAL_TIME`,`DEP_TIME`,`DEP_DATE`,`PAX`,`noofchildren`,`consultant`,`ARRIVAL_POINT`,`markup`,`kmrate`,`driversallowancerate`,`additionalservices`,`startingpoint`,`active`,`dollareurorate`,`status`,`cost`,`expend`,`proloss`,`DEPARTURE_POINT`) values (64,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2009-08-07','Safari Crazy',NULL,'2009-08-07',NULL,NULL,'2009-08-09',2,1,21,'391',NULL,'0.55',20,NULL,NULL,NULL,'0.00',1,'3587.68','2511.38','1076.30','391'); Suggested fix: Since this shows correctly in mysql query views eg sqlyog or navicat. So maybe the problem is the odbc connector