Microsoft SQL Server Date issue with Java

Question

I am trying to insert data into a Microsoft SQL database using Java. I have established the connection, but when I try to run the following command:

        stmt = con.createStatement();
        stmt.executeUpdate("INSERT INTO ctf_data "+
        "(ServerAddress,ServerName,HostName,"+
        "UserClaimedServerName,ClaimedDate,CorrectDate,"+
        "isReachable,HTTPUp,HTTPStatus,WebServer,"
        +"poweredBy) VALUES ("+ item.serverAddress +","
        +item.serverName+","+item.hostName+","+
        item.claimedHostName+","+dateTime+","+
        correctDateTime+","+item.isReachable+","+
        item.HTTPWorking+","+item.httpStatusCode+","+
        item.websrv+","+item.poweredBy+")");
        stmt.close();

I get the exception:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '.0'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:642)
at SQLInteractor.write(SQLInteractor.java:68)
at status1.recvStatus(SQLInteractor.java:21)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.google.common.eventbus.EventSubscriber.handleEvent(EventSubscriber.java:74)
at com.google.common.eventbus.SynchronizedEventSubscriber.handleEvent(SynchronizedEventSubscriber.java:47)
at com.google.common.eventbus.EventBus.dispatch(EventBus.java:322)
at com.google.common.eventbus.EventBus.dispatchQueuedEvents(EventBus.java:304)
at com.google.common.eventbus.EventBus.post(EventBus.java:275)
at ServerCheck.run(ServerCheck.java:153)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

I have built the string and everything appears to be correct. Here is my table configuration:

I have also tried the datetime class. I am generating the date/time stamp by:

Timestamp dateTime = new Timestamp(item.dateTime_d.getTime());
Timestamp correctDateTime = new Timestamp(item.correctDateTime_d.getTime());

The date stamp I am given by this method is something like: 2014-11-14 14:35:06.0

Here is the printed out SQL statement:

INSERT INTO ctf_data (ServerAddress,ServerName,HostName,UserClaimedServerName,ClaimedDate,CorrectDate,isReachable,HTTPUp,HTTPStatus,WebServer,poweredBy) VALUES (192.168.0.4,WIN2012SERV,WIN2012SERV,WIN2012SERV,2014-11-14 14:39:37.0,2014-11-14 14:39:42.87,true,true,200,[Microsoft-IIS/8.0],[ASP.NET, PHP/5.6.0])

Does anyone have any idea what I'm doing wrong here? From the exception, it appears that it doesn't like the milliseconds, but I'm not sure how to fix that.

Thanks!

Answer 1

Problem is with quoting, you need to quote the string type data like

INSERT INTO ctf_data (ServerAddress,ServerName,HostName,UserClaimedServerName,
                 ClaimedDate,CorrectDate,isReachable,HTTPUp,HTTPStatus,
                    WebServer,poweredBy) 
VALUES ('192.168.0.4','WIN2012SERV','WIN2012SERV','WIN2012SERV',
        '2014-11-14 14:39:37.0','2014-11-14 14:39:42.87','true',
        'true',200,'[Microsoft-IIS/8.0]','[ASP.NET, PHP/5.6.0]');

Answer 2

You should add single quotes for the varchar and text columns.