WSO2 DSS Update Statement Parameter com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ':'

Question

I am performing the following SQL Update statement in WSO2 DSS

Update Door
Set dcr_messageBox = :msg, dcr_servicesArea = :servArea
Where dcr_regNo = :regNo

I am exposing this query as a rest service. I keep getting the following error

Current Request Name: _putdoorproperty
Current Params: {servArea=21, regNo=313, msg=21}
Nested Exception:-
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ':'.

</soapenv:Text></soapenv:Reason><soapenv:Detail><axis2ns646:DataServiceFault xmlns:axis2ns646="http://ws.wso2.org/dataservice"><axis2ns646:current_params>{servArea=, regNo=3123, msg=}</axis2ns646:current_params><axis2ns646:current_request_name>_putdoctorproperty</axis2ns646:current_request_name><axis2ns646:nested_exception>com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ':'.</axis2ns646:nested_exception><axis2ns646:source_data_service>

Anyone has any ideas what it may be?

Answer 1

I've tried this out, since a colleague of mine also has this problem right now. It seems that the problem is caused because of the carriage returns (enter) in the statement.

Try it like this, without any carriage returns:

Update Door Set dcr_messageBox = :msg, dcr_servicesArea = :servArea Where dcr_regNo = :regNo

This worked for me.

Answer 2

In JDBC, the default placeholder for variables is ?. The common use of named parameters like :msg is not supported (by standalone JDBC)

You'd need to transform your query to

Update Door
 Set dcr_messageBox = ?, dcr_servicesArea =?
  Where dcr_regNo = ?

and then perform on your PreparedStatement

ps.setInt(1, msg);
ps.setInt(2, servArea);
ps.setInt(3, regNo);