Hello. I need to send xml for the procedure in SQLServer 2012. I do this:

Connection con = null; Statement stmt = null; ResultSet rs = null; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); con = DriverManager.getConnection(connectionUrl); String SQL = "declare @response varchar(8000);<xml><action>login</action><login>test</login><password>147852</password></xml>, @response output; select convert(text,@response)"; stmt = con.createStatement(); rs = stmt.executeQuery(SQL); // Iterate through the data in the result set and display it. /*while (rs.next()) { System.out.println(rs.getString(4) + " " + rs.getString(6)); }*/ }

And I get this:

 com.microsoft.sqlserver.jdbc.SQLServerException: Неправильный синтаксис около конструкции "<". at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:677) at pakkket.MainClassDbo.main(MainClassDbo.java:28) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)

Help .. what am I doing wrong? Could this be due to the JDBC version?

  • Well, make an input parameter in your stored procedure and pass in your @xml - Nick Proskuryakov
  • I do not see in your SQL code ... - Pavel Mayorov
  • @Nick Proskuryakov there is a procedure that handles this xml - EmErIx_007
  • @Pavel Mayorov yes. I must pass just the lines in tags. Exactly in this form. - EmErIx_007
  • one
    @ EMER_X_007 first of all you have to call the store in order to be able to transfer anything to it ... - Pavel Mayorov

1 answer 1

In general, everything should look like this:

Your procedure in MS SQL:

  create procedure MY_PROCEDURE(@xml varchar(max)) begin --обработка получаемого XML end

Your java code is:

  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); try (Connection con = DriverManager.getConnection(connectionUrl);){ String xml = "<xml><action>login</action><login>test</login><password>147852</password></xml>"; try(CallableStatement callableStatement = connection.prepareCall("{call MY_PROCEDURE(?)}");){ callableStatement.setString(1, xml); callableStatement.execute(); } }

I apologize for the syntax, wrote in a notebook.

  • Thank you ... earned. - EmErIx_007