Hi, I'm hoping somebody out there in x10 land can help me with this problem.
I wrote a sql statement to join several date fields together. I have to keep the year month and day separate because the dates are for arcaeological sites and mysql can't handle dates before 1.1.1000. So I have 3 fields year, month and day all integers. Then I have a BC/AD binary field. Not all of the fields are populated. In most cases only the year is available.
I am using C# and asp.net.
The sql code is:
When I run it on my PC it works well. However when I try to access the page on X! i Get the following error.
{0} Exception caught.MySql.Data.MySqlClient.MySqlException: FUNCTION pakal_maya.concat does not exist at MySql.Data.MySqlClient.MySqlStream.ReadPacket () [0x00000] at MySql.Data.MySqlClient.NativeDriver.ReadResult () [0x00000]
Any clues as to what is wrong?
Thanks for your help.
Got it!
The problem was I had concat (.....) it should be concat(...) - no space between concat and parentheses. Who would have though such a small thing could cause all that hassle.
I wrote a sql statement to join several date fields together. I have to keep the year month and day separate because the dates are for arcaeological sites and mysql can't handle dates before 1.1.1000. So I have 3 fields year, month and day all integers. Then I have a BC/AD binary field. Not all of the fields are populated. In most cases only the year is available.
I am using C# and asp.net.
The sql code is:
Code:
public String dates = "select d.*, p.nameP, s.SiteName, m.nameM,"
+ " concat (cast(ifnull(dateGRD,' ') as CHAR), ' ', case ifnull(d.dateGRM,' ') when 1 then 'January'"
+ " when 2 then 'February'"
+ " when 3 then 'March'"
+ " when 4 then 'April'"
+ " when 5 then 'May'"
+ " when 6 then 'June'"
+ " when 7 then 'July'"
+ " when 8 then 'August'"
+ " when 9 then 'September'"
+ " when 10 then 'October'"
+ " when 11 then 'November'"
+ " when 12 then 'December'"
+ " else space(1)"
+ " end, ' ', cast(ifnull(dateGRY,' ') as CHAR),"
+ " case ifnull(d.dateGRBCAD,' ') when 0 then 'BC' when 1 then 'AD' else space(1) end) as datefull"
+ " from dates d"
+ " join sites AS s on d.SiteID = s.SiteID"
+ " left join people AS p on d.idpeople = p.idpeople"
+ " left join monuments AS m on d.idmonument = m.idmonuments"
+ " order by dateGRBCAD, dateGRY, dateGRM, dateGRD";
When I run it on my PC it works well. However when I try to access the page on X! i Get the following error.
{0} Exception caught.MySql.Data.MySqlClient.MySqlException: FUNCTION pakal_maya.concat does not exist at MySql.Data.MySqlClient.MySqlStream.ReadPacket () [0x00000] at MySql.Data.MySqlClient.NativeDriver.ReadResult () [0x00000]
Any clues as to what is wrong?
Thanks for your help.
Got it!
The problem was I had concat (.....) it should be concat(...) - no space between concat and parentheses. Who would have though such a small thing could cause all that hassle.
Last edited: