Windows服务在MySQL异常中存储日期时间 [英] windows service storing the datetime in MySQL exception

问题描述

我将测试该应用程序是否为Windows应用程序，然后将日期时间存储在MySQL数据库中.当我使用Windows服务启动此应用程序时，将引发此异常.

错误[HY000] [MySQL] [ODBC 3.51驱动程序] [MySqlid -6​​.0.11-alpha-community]第1列的列名称的日期时间值"5/6/2011 9:00:00 AM"不正确

Windows应用程序采用系统格式&我的系统格式是yyyy-MM-dd hh:mm:ss
在Windows服务中使用的格式.

I will be tested the application as windows application then it will be stored the datetime in MySQL data base.When I will be start this application using windows service it will be thrown this exception.

error [HY000][MySQL][ODBC 3.51 Driver] [MySqlid -6.0.11-alpha-community]incorrect datetime value " 5/6/2011 9:00:00 AM" for column column-name at row1

Windows application take the system format & my system format is yyyy-MM-dd hh:mm:ss
in windows service which format is used.

query18 += "select ''" + obj8 + "'' as DTvalue ,''" + date8 + "'' as DTdatelogged1 ,''" + OpcGroup.QualityToString(e8.sts[counter8].Quality) + "'' as DTquality ,''" + DateTime.FromFileTime(e8.sts[counter8].TimeStamp) + "'' as DTtimestamp ,''" + e8.sts[counter8].HandleClient + "'' as DTparamID Union " + Environment.NewLine;

UpdateQuery = Update parameter t Left join + Environment.NewLine;
                    UpdateQuery8 +=  (  + query18 +  ) Temp on" + Environment.NewLine;
                    UpdateQuery8 += t.itemID=Temp.DTparamID+ Environment.NewLine;
                    UpdateQuery8 += set paramvalue=DTvalue, date_logged1=DTdatelogged1,Quality=                         DTquality,date_logged=DTtimestamp   + Environment.NewLine;
                    UpdateQuery8 += where t.groupID=9 and t.itemID=Temp.DTparamID;

我的查询这样的时间戳值为129500892576718750，它将在Windows应用程序中转换为DateTime.FromFileTime()函数转换后的值，例如"2011-05-17 12:30:57"，它将被写入mysql数据库
但在Windows Service转换后的值(如2011/05/17 12:30:57 PM)中，MYSQL数据库将不接受该值，而在Windows Service中将使用相同的东西



请在这方面帮助我.

在此先感谢.

[edit]已添加代码块，忽略HTML ..."选项已禁用-OriginalGriff [/edit]

my query likethis timestamp value is 129500892576718750 it will be convert DateTime.FromFileTime() function converted value like ''2011-05-17 12:30:57'' in windows application it will be write into mysql database
but in windows service converted value like 2011/05/17 12:30:57 PM it will be not accepted by the MYSQL database same thing i will be used in the windows service



Please help me in this regard.

Thanks in Advance.

[edit]Code block added, "Ignore HTML..." option disabled - OriginalGriff[/edit]

推荐答案

SQL DateTime格式为ISO:始终为yyyy- MM-dd HH:mm:ss.
检查您从哪里获取日期字符串:错误消息中的示例不是ISO格式.

将DateTime信息保存在DateTime变量中:不要将其转换为字符串，尤其是不要将其转换为字符串以将其传递给MySQL:使用参数化查询并将其作为DateTime传递.这样就不会有任何格式转换错误！


请查看我的更新"


关于参数化查询我怎么说?

您的代码有问题:

SQL DateTime format is ISO: it is always yyyy-MM-dd HH:mm:ss.
Check where you are getting your date string from: the example in the error message is not in ISO format.

Keep DateTime information in DateTime variables: don''t convert it to string, and especially, don''t convert it to string to pass it to MySQL: use a Parametrized Query and pass it as a DateTime. Then you can''t have any format conversion errors!


"please see my updates"


What did I say about Parametrized Queries?

Your code is the problem:

' as DTquality ,'" + DateTime.FromFileTime(e8.sts[counter8].TimeStamp) + "' as DTtimestamp ,'"

FromFileTime返回一个DateTime，您的代码使该日期引起一个隐式ToString()方法，以便连接字符串.
这就是您的问题所在.

更改构建查询的方式.不要串联字符串:这会浪费内存，并使您无法进行SQL注入攻击，这可能会意外或故意破坏数据库，并导致错误.
如果将其转换为参数化查询，您的错误将消失！

FromFileTime returns a DateTime, which your code causes to have an implicit ToString() method added in order to concatenate the strings.
This is where your problem is.

Change the way you build your query. Do not concatenate strings: it wastes memory and leave you wide open for anm SQL Injection attack which could accidentally or deliberately destroy your database, as well as cause your error.
If you convert this to Parametrized queries, your error will disappear!

这篇关于Windows服务在MySQL异常中存储日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！