MSSQL Cursor issue

[Former Member]

Hi Guys,

we are using JDBC driver to send data from SAP PI 7.1 to MS SQL 2012.
The data is passed as an XML file, as below

<ns0:MTORDER xmlns:ns0="http://order">
  <LOADING_UPDATE>
  <sp_order action="EXECUTE">
  <table>SP_ORDER</table>
  <i_status_load type="VARCHAR">1</i_status_load>
  <ordercursor isOutput="TRUE" type="CURSOR"></ordercursor>
  <item isOutput="TRUE" type="CURSOR"></item>
   </sp_biz_order_loading_update>
  </LOADING_UPDATE>
</ns0:MTORDER>

We got following message;

java.sql.SQLException: Unsupported parameter type 'CURSOR' for parameter 'ordercursor' found

I believe that MS JDBC driver should support this.

Is this possible?

PM

[iaki_vila]

Hi Peterm

According with MSSQL documentation: http://docs.oracle.com/cd/E13157_01/wlevs/docs30/jdbc_drivers/mssqlserver.html the point about  SelectMethod and the note 831162 - FAQ: XI 3.0 / PI 7.0 / PI 7.1 / PI 7.3 JDBC Adapter point 15 your can try to add the SelectMethod clausule in the connection URL like:

jdbc:microsoft:sqlserver://dbhost:1433;databaseName=example;SelectMethod=Cursor

Regards.

[Former Member]

SelectMethod Cursor is for MS SQL 2005

For MS SQL 2000:

Driver Class name: com.microsoft.jdbc.sqlserver.SQLServerDriver

URL: jdbc:microsoft:sqlserver://xiveri-mssql.wdf.sap.corp:1433;user=xiveri;password=sapsap;databaseName=XIVERI;SelectMethod=Cursor

For MS SQL 2005:

Driver Class name: com.microsoft.sqlserver.jdbc.SQLServerDriver

URL: jdbc:sqlserver://localhost;user=xiveri;password=sapsap;database=AdventureWorks;integratedSecurity=true;

[Former Member]

İs there any workaround to solve this issue?

[Former Member]

CURSOR (output; only in conjunction with the Oracle JDBC driver)

.