JDBC Adapter Call a Table-Valued Function

[former_member301120]

Hello to all,

we're using the JDBC adapter to access a MS SQL-Server 2008.

I have to call a "Table-Valued Function" via JDBC.

Has anybody done this?

I've tried to call it like a stored procedure with the following:

<ns1:LGBuchungSAP_REQ xmlns:ns1="http://xyz.de/pi/mav/kantine">
	<fLGBuchungenSAP>
		<Tablename action="execute">
			<table>dbo.fLGBuchungenSAP</table>
		</Tablename>
	</fLGBuchungenSAP>
</ns1:LGBuchungSAP_REQ>

but I get following error:

com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: 
Error when executing statement for table/stored proc. 'dbo.fLGBuchungenSAP' (structure 'fLGBuchungenSAP'): com.microsoft.sqlserver.jdbc.SQLServerException: 
Fehler bei der Anforderung für 'fLGBuchungenSAP' (Prozedur), weil 'fLGBuchungenSAP' ein Tabellenwertfunktion-Objekt ist.

Calling with a select-statement also fails:

<ns1:LGBuchungSAP_REQ xmlns:ns1="http://xyz.de/pi/mav/kantine">
	<STATEMENT_NAME>
		<Tablename action="SELECT">
			<table>dbo.fLGBuchungenSAP(&apos;20101101 00:00:00&apos;, &apos;20110101 00:00:00&apos;)</table>
		</Tablename>
	</STATEMENT_NAME>
</ns1:LGBuchungSAP_REQ>

with error:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<!-- Eingangs-Message -->
<SAP:Error xmlns:SAP="http://sap.com/xi/XI/Message/30" xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/" SOAP:mustUnderstand="1">
	<SAP:Category>XIAdapterFramework</SAP:Category>
	<SAP:Code area="MESSAGE">GENERAL</SAP:Code>
	<SAP:P1></SAP:P1>
	<SAP:P2></SAP:P2>
	<SAP:P3></SAP:P3>
	<SAP:P4></SAP:P4>
	<SAP:AdditionalText>com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'dbo.fLGBuchungenSAP('20101101 00:00:00', '20110101 00:00:00')' (structure 'STATEMENT_NAME'): java.lang.IndexOutOfBoundsException: Index: 4, Size: 4</SAP:AdditionalText>
	<SAP:Stack></SAP:Stack>
	<SAP:Retry>M</SAP:Retry>
</SAP:Error>

Please advice

Regards

Christian

[former_member301120]

Hello to all,

I solved the problem.

You have to call it this way:

<ns1:LGBuchungSAP_REQ xmlns:ns1="http://xyzde/pi/mav/kantine">
	<STATEMENT>
		<LGBuchungSAP action="SQL_DML">
			<access>select * from dbo.fLGBuchungenSAP(&apos;$DatumVon$&apos;, &apos;$DatumBis$&apos;)</access>
			<key>
				<DatumVon>20101101 00:00:00</DatumVon>
				<DatumBis>20110101 00:00:00</DatumBis>
			</key>
		</LGBuchungSAP>
	</STATEMENT>
</ns1:LGBuchungSAP_REQ>

But I get the next error: "A result set was generated for the update task"

<SAP:Error xmlns:SAP="http://sap.com/xi/XI/Message/30" xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/" SOAP:mustUnderstand="1">
	<SAP:Category>XIAdapterFramework</SAP:Category>
	<SAP:Code area="MESSAGE">GENERAL</SAP:Code>
	<SAP:P1></SAP:P1>
	<SAP:P2></SAP:P2>
	<SAP:P3></SAP:P3>
	<SAP:P4></SAP:P4>
	<SAP:AdditionalText>com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'LGBuchungSAP' (structure 'STATEMENT'): com.microsoft.sqlserver.jdbc.SQLServerException: Es wurde ein Resultset für den Aktualisierungsvorgang generiert.</SAP:AdditionalText>
	<SAP:Stack></SAP:Stack>
	<SAP:Retry>M</SAP:Retry>
</SAP:Error>

[former_member301120]

Hello to all,

you have to pass SQL_QUERY to action attribute and you get the result-table.

Best regards