error while sending data from SAPUI5 application to SQL procedure via odata producer

[Former Member]

Dear Experts,

To create entries in a SQL Anywhere table from an SAPUI5 application, I have created an odata producer:

service namespace "test" {
 serviceop post "cpadmin"."InvoiceCreate" as "create" returns multiplicity "0";
 serviceop get  "cpadmin"."InvoiceCreate" returns multiplicity "0";
}

The procedure InvoiceCreate has an import (IN) parameter: ALTER PROCEDURE "cpadmin"."InvoiceCreate"(IN data1 long varchar)

From the SAPUI5 application the call is made to the odata producer with the parameter for data1:

data1 = "hello";
this.oDataModel.create('/create', data1, null, 
  function(oData, response){
    sap.m.MessageBox.show("Invoice " + oData.ivnum + " has been created", {
      icon : sap.m.MessageBox.Icon.SUCCESS, // default
      title : "Success"});},
  function(oError){
    var test_str = oError.response.body;
    var start_pos = test_str.indexOf('value') + 8;
    var end_pos = test_str.indexOf('innererror', start_pos) - 4;
    var text_to_get = test_str.substring(start_pos, end_pos);
    sap.m.MessageBox.show(text_to_get, {
      icon : sap.m.MessageBox.Icon.ERROR, // default
      title : "Error"});
  })

When I am calling the SAPUI5 application, I get the error: "Wrong number of parameters for Service Operation"

If I do no pass any parameter from the SAPUI5 application, it works fine. How can I pass data from the SAPUI5 application to this procedure. Kindly suggest.

[philippefbertrand]

Could you please try your service call with LogVerbosity=4 and add the results (just the part where it processes your service call request) to your question ?

[Vlad]

I can add that "data1" should be an object with the properties and values to be posted to the server. E.g.

let data1 = { name: "hello", dest: "Vlad" };

Because OData works with objects, not with plain values. You can use (as an option) RESTClient-like software to experiment with requests/responses.

Oh, I have forgot to add - you can call the GET service, and specify the object's key, e.g.

GET yourEntitySet(1)

it will return you the OData entity in JSON (hopefully), and if you take it from the response, clean from the garbage that OData has (such as useless URLs), you should be able to submit this entity with POST. Please remember, that key is usually generated on the server side.

[Former Member]

Hi,

The results after call with

LogVerbosity=4 and data1 = { name: "hello", dest: "Vlad" };

are as follows:

R. 2017-05-03 17:52:29. [test:4] Request GET http://192.168.1.86:8080/connectedpharmacy/test/ D. 2017-05-03 17:52:29. [test:4] DataServiceVersion 2.0 Response 2.0 R. 2017-05-03 17:52:29. [main] 192.168.1.74 - - [03/May/2017:17:52:29 +0530] "POST /connectedpharmacy/test/create HTTP/1.1" - 0 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.96 Safari/537.36" "-" R. 2017-05-03 17:52:29. [test:5] Request POST http://192.168.1.86:8080/connectedpharmacy/test/create D. 2017-05-03 17:52:29. [test:5] DataServiceVersion 2.0 Response 2.0 D. 2017-05-03 17:52:29. [test:5] Executing SQL Statement: CALL "cpadmin"."InvoiceCreate"( "data1" = ? ); D. 2017-05-03 17:52:29. [test:5] database error: "[SAP][JDBC Driver][SQL Anywhere]Syntax error near ')' on line 1 ". E. 2017-05-03 17:52:29. [test:5] (HTTP 500 Internal Server Error - 30000) An unexpected error occurred in the producer and was written to file "C:\\ProgramData\\SQL Anywhere 17\\diagnostics\\ODP17_20170503_175229_592.txt"

The file ODP17_20170503_175229_592.txt has following contents: database error: "[SAP][JDBC Driver][SQL Anywhere]Syntax error near ')' on line 1 ". at com.sybase.odata.producer.database.sqlanywhere.SASQLExceptionInterpreter.interpret(SASQLExceptionInterpreter.java:85) at com.sybase.odata.producer.database.InvokeRequest.execute(InvokeRequest.java:119) at com.sybase.odata.producer.handler.AbstractHandler.handleInvokeRequest(AbstractHandler.java:1178) at com.sybase.odata.producer.handler.PostHandler.handleRequestImpl(PostHandler.java:141) at com.sybase.odata.producer.handler.AbstractHandler.handleRequest(AbstractHandler.java:1047) at com.sybase.odata.producer.servlets.ODataServlet.doPost(ODataServlet.java:315) at javax.servlet.http.HttpServlet.service(HttpServlet.java:644) at com.sybase.odata.producer.servlets.ODataServlet.service(ODataServlet.java:465) at javax.servlet.http.HttpServlet.service(HttpServlet.java:725) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:800) at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:587) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1125) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1059) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:215) at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:110) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97) at org.eclipse.jetty.server.Server.handle(Server.java:497) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:248) at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:620) at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:540) at java.lang.Thread.run(Thread.java:745)

[Vlad]

Wait a second, are you using OData model, but your web service awaits for a single parameter?

Could you please show me $metadata that the web server produces? I'd like to look at your schema.

The error you got from me is because my example was artificial, I don't know what exactly you have designed. When you use ODataModel.create, it is assumed that you define the entity set, not a custom web service.

[Former Member]

This XML file does not appear to have any style information associated with it. The document tree is shown below. <edmx:edmx xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx" version="1.0"> <edmx:dataservices xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" m:dataserviceversion="1.0"> <schema xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://schemas.microsoft.com/ado/2008/09/edm" namespace="SAPSQLOData"> <entitytype name="ivtg01">...</entitytype> <entitytype name="nrange">...</entitytype> <entitytype name="ivti01">...</entitytype> <entitytype name="cumg01">...</entitytype> <entitytype name="itmg01">...</entitytype> <entitytype name="immg01">...</entitytype> <entitytype name="itmg02">...</entitytype> <entitytype name="HELLO">...</entitytype> <entitycontainer name="test_Container" m:isdefaultentitycontainer="true">...</entitycontainer> </schema> </edmx:dataservices> </edmx:edmx>

[Vlad]

In the $metadata output I see neither InvoiceCreate, nor InvoiceCreate/create. Does it exist?

[philippefbertrand]

There seems to be an issue with the stored procedure you are trying to access.

Executing SQL Statement: CALL "cpadmin"."InvoiceCreate"( "data1" = ? );
database error: "[SAP][JDBC Driver][SQL Anywhere]Syntax error near ')' on line 1 ".

What version of SQL Anywhere are you using (including build number)? How is the OData service launched?

Is there something else in the background that could be altering or removing the stored procedure? The metadata you posted does not include any reference to InvoiceCreate - are you sure the database hasn't been altered?

First try to get the stored procedure to work using dbisql as cpadmin (in case there is something wrong with the procedure itself) then try a browser (like chrome) to access the metadata and try calling the service operation. Once all that is working, you can worry about SAPUI5.

Note: if you change the database schema (add, alter tables and procedures), the OData server has to be restarted to reflect the changes.

(I confess I am not knowledgeable in UI5 so I'm speaking from a pure OData perspective)

[Former Member]

We are using SQL Anywhere 17 17.0.0.1062. The Odata producer is exposing a procedure for POST operation. How can I view this in the metadata? InvoiceCreate is a procedure and create is the alias for this.