JDBC receiver adapter to call MS SQLServer stored procedure with parameters

[former_member282228]

We are trying to use the JDBC receiver adapter to call a stored procedure in MS SQLServer with parameters. According to the help documentation for the JDBC receiver adapter for action=EXECUTE, "The elements within the stored procedure are interpreted as parameters" and "The parameter names must be identical to those of the stored procedure definition". The parameters within a MS SQLServer stored procedure are required to begin with the '@' symbol. The element names within a XML document i.e. used to call the stored procedure can not contain a special character such as '@' in the first position. For all of the tests we have done where the parameter name in the XML document omits the '@' character, the parameters are not being received by the stored procedure. Is there a way around this problem?

Thank you,

Harold

[Former Member · Accepted solution]

Hi,

Please can you check on thei SAP note for special characters: 873938

Regards

Vijaya

[former_member282228 · Accepted solution]

Hi Vijaya

Thank you for your reply. I do not believe this will solve the problem. It is not that we are getting special characters in a response from i.e. a stored procedure select. The problem is that we are required to have the name of the stored procedure parameter as an XML element in XI. The XML element can not begin with '@'. The name of the stored procedure in MS SQLServer is required to begin with '@'.

Thank you,

Harold

[bhavesh_kantilal · Accepted solution]

Hi Harold,

Am not sure but, just try not to send the parameter without the @ symbol from XI. Just send it by ignoring the inital @ and see if it works.. Maybe SQL server inserts tghe @ by itself..!!

Regards,

Bhavesh

[bhavesh_kantilal · Accepted solution]

Hi Harold,

<i>The name of the stored procedure in MS SQLServer is required to begin with '@'.</i>

Just read this.. Well this can be acheive by passing the name of the stored procedure as a constant as shown below..!!

<i><root>

<StatementName5>

<storedProcedureName action=” EXECUTE”>

<table><b>@realStoredProcedureeName</b></table>

<param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName >

</StatementName5>

<root></i>

In this case, the tag <b><storedProcedureName action=” EXECUTE”></b> will be a dummy. Just pass the stored procedure name with the @ as a vlue to the <b><table></b>

Will do the trick.

Regards,

Bhavesh

[former_member282228 · Accepted solution]

Hi Bhavesh

Thank you for your reply. The approach you have suggested will work for sending a stored procedure name with special characters. The problem in this case is with passing parameters i.e. param1 below. The parameter name, from my understanding, must be a XML element/tag, and cannot be between tags i.e. <table>procedureName </table> as it is with the stored procedure name.

Harold

[former_member282228 · Accepted solution]

Hi Bhavesh

We tried using a parameter name that is the same as the MS SQLServer name except for the initial '@', but the parameter was still not recognized.

Thank you,

Harold

[bhavesh_kantilal · Accepted solution]

Hi Harold,

If you want to use a special character in the Param, i don't think it will work

Can you dig into your SQL server and see if there is somehow you can avoid this @ symbol? Ask your DB team to hgelp you in this regard to coz , I have not seen this same problem occuring in any other forum posts.

There has to bve some way out, and if it is not possible in SQL server, OSS is the way to go.

Regards,

Bhavesh

[former_member282228 · Accepted solution]

Hi Bhavesh

The stored procedure call is working now. I had my parameters at the wrong level in the XML document. It appears that the names of the parameters are not important. They can have any name and do not need to match the names in the stored procedure. Only the number of parameters and the direction (in/out) must match.

Thank you for your help,

Harold

[bhavesh_kantilal · Accepted solution]

Hi harold,

That was really interesting. Need to once again do some R and D on it. Thanks for the update.

Regards,

Bhavesh

[bhavesh_kantilal]

Hi Harold,

ASFAIK, an Element in your XMl cannot begine with an @ symbol . The only symbols allowed are A-Z,0-9,_.

One option that you can try is to use an XSD as your Datatype. I have not tried it, but create an XSD with an XML tool and import it into your IR.

And, even if that doesn't help, explore the option of changing the parameter in your Stored Proc.

Regards,

Bhavesh

[former_member282228]

Hi Bhavesh

Thank you for your reply. We tried to put the '@' sign in the XSD and import it. The import was not successful, because the element name that begins with '@' makes it an invalid XSD. I have been told that MS SQLServer can only declare a parameter using the '@' in the beginning.

Thank you,

Harold

[Former Member]

Harold,

Could you please mail/post your Stored procedure Message type in XI and Stored Procedure.

It will greatly help me in my next scenario.

Thanks

Venu G. Mustyala

[Former Member]

Hello Harold - I am facing the EXACTLY SAME problem.Pls let me know how did you fix this problem ?

This is the message I am passing on to the DB SP:

******************************************************

<?xml version="1.0" encoding="UTF-8"?>

<MRIRequestInbound>

<StatementName>

<prc_FC_InsertStagingJournalEntries action="EXECUTE"/>

<JournalData isInput="true" type="STRING">

<NewDataSet><Table ITEM = "" ENTITYID = "" PERIOD = "" ACCTNUM = "" DEPARTMENT = "" JOBCODE = "" AMT = "" REF = "" DESCRPN = "" ENTRDATE = "" BASIS = " " BALFOR = "N" REQUESTNUM = "" ACCTNAME = "" TYPE = "" DESCRPTN = "" GDEP_DESCRPN = "" GJOB_DESCRPTN = "" JOBTYPE = "" /></NewDataSet>

</JournalData>

</StatementName>

</MRIRequestInbound>

******************************************************

Out of which,

<NewDataSet> tag contains the value of the parameter in the SP. So, my value to the SP's parameter is :

*******************************************************

<NewDataSet><Table ITEM = "" ENTITYID = "" PERIOD = "" ACCTNUM = "" DEPARTMENT = "" JOBCODE = "" AMT = "" REF = "" DESCRPN = "" ENTRDATE = "" BASIS = " " BALFOR = "N" REQUESTNUM = "" ACCTNAME = "" TYPE = "" DESCRPTN = "" GDEP_DESCRPN = "" GJOB_DESCRPTN = "" JOBTYPE = "" /></NewDataSet>

*******************************************************

Any clue ?

Cheers,

Amrish.

[Former Member]

Never mind Harold, I fixed my problem . The solution was similar to you . I had to change my Data type to work it correctly exactly as you did .

Cheers,

Amrish.