cancel
Showing results for 
Search instead for 
Did you mean: 

SAP BO4.2 FreeHandSQL : Error while creating DataProvider for FHSQL

Former Member
0 Kudos

Hi,

I am getting below 400 error while saving document after creating Data Provider for FHSQL.

Server returned HTTP response code: 400 for URL: http://10.103.21.216:6405/biprws/raylight/v1/documents/7529

please find below flow where i am creating data Provider with FHSQL and saving document.

 

input == >

<dataprovider><name>Success(2016/08/24 16:53:36)</name><dataSourceId>6267</dataSourceId><properties><property key='sql'>SELECT  Outlet_Lookup.State as 'State', Outlet_Lookup.City as 'City', Sum(Shop_facts.Amount_sold) as 'Sales revenue' FROM Shop_facts INNER JOIN Outlet_Lookup ON (Outlet_Lookup.Shop_id=Shop_facts.Shop_id) GROUP BY  Outlet_Lookup.State,  Outlet_Lookup.City</property></properties></dataprovider>

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

<success>

    <message>The resource of type "Data provider" with identifier "DP0" has been successfully created.</message>

    <id>DP0</id>

</success>

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

     Saving Document as below

URL url = new URL(COGNOS_BO_Constant.BO_WEB_SERVICE_URL +"/biprws/raylight/v1/documents/"+docId);

            HttpURLConnection conn = (HttpURLConnection) url.openConnection();

            conn.setDoOutput(true);

            conn.setRequestMethod("PUT");

            conn.setRequestProperty("Accept","application/xml");

            conn.setRequestProperty("Content-Type", "application/xml");

            conn.setRequestProperty("X-SAP-LogonToken", "\""+token+"\"");

gives below error.

java.io.IOException: Server returned HTTP response code: 400 for URL: http://10.103.21.216:6405/biprws/raylight/v1/documents/7529

Thanks,
Amol

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Eric/Dan,

please find below stack trace, this stack trace m getting while saving document after DP create with FHSQL.

I have used same code which is available in Raylight Sample for FHSQL and changed connection with my connection.

Code used As below :


   String fhsqlDocName = "[Generated] FreeHandSQL Sample (" + Now() + ")";

        String folderID = getIdFromCUID(CUID_DESTINATION_FOLDER);

        String fhsqlDocId = createDocument(fhsqlDocName, folderID);

        //renameReport(fhsqlDocId, ID_INITIAL_REPORT, "My FreeHandSQL");

        renameReport(fhsqlDocId, "4", "My FreeHandSQL");

        if (getTypeOfConnection(getIdFromCUID(CUID_SUCCESS_CONNECTION)).equals("Relational")) {

            this.sql.add("SELECT  Outlet_Lookup.State AS 'State', Outlet_Lookup.City AS 'City', SUM(Shop_facts.Amount_sold) AS 'Sales revenue'"

                    + " FROM Shop_facts INNER JOIN Outlet_Lookup ON (Outlet_Lookup.Shop_id=Shop_facts.Shop_id) "

                    + " GROUP BY  Outlet_Lookup.State,  Outlet_Lookup.City");

                    System.out.println("************************************************************************* \n SQL == > \n "+this.sql);

            addDataProvider(fhsqlDocId, CUID_SUCCESS_CONNECTION, this.sql);

            refreshDocWithoutChanges(fhsqlDocId);

            saveDocument(fhsqlDocId);

please find below out put after data provider created, refreshed document and while saving document it throws error 400 as below


[GET] http://10.103.21.216:6405/biprws/raylight/v1/connections/8514

| === Request content ===

| null

| === Response code ===

| 200

| === Response message ===

| OK

| === Response content ===

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

| <connection type="Relational">

|     <id>8514</id>

|     <cuid>Af5L7PHb5k9Bk86WKdXAy60</cuid>

|     <name>Success</name>

|     <folderId>558</folderId>

|     <path>Connections</path>

|     <updated>2016-08-30T10:56:32.000-04:00</updated>

|     <createdBy>Administrator</createdBy>

|     <database>MS SQL Server 2008</database>

|     <networkLayer>ODBC</networkLayer>

| </connection>

|

| === Headers ===

| null: [HTTP/1.1 200 OK]

| Date: [Tue, 30 Aug 2016 15:17:48 GMT]

| Content-Length: [419]

| Content-Type: [application/xml]

| Server: [Apache-Coyote/1.1]

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

SQL == >

[SELECT  Outlet_Lookup.State AS 'State', Outlet_Lookup.City AS 'City', SUM(Shop_facts.Amount_sold) AS 'Sales revenue' FROM Shop_facts INNER JOIN Outlet_Lookup ON (Outlet_Lookup.Shop_id=Shop_facts.Shop_id)  GROUP BY  Outlet_Lookup.State,  Outlet_Lookup.City]

| [GET] http://10.103.21.216:6405/biprws/infostore/cuid_Af5L7PHb5k9Bk86WKdXAy60

| === Request content ===

| null

| === Response code ===

| 200

| === Response message ===

| OK

| === Response content ===

| <entry xmlns="http://www.w3.org/2005/Atom"><author><name>Administrator</name><uri>http://10.103.21.216:6405/biprws/infostore/12</uri></author><id>tag:sap.com,2010:bip-rs/Af5L7PHb5k9Bk86WKdXAy60</id><title type="text">Success</title><updated>2016-08-30T14:56:32.561Z</updated><link href="http://10.103.21.216:6405/biprws/infostore/558" rel="up" /><content type="application/xml"><attrs xmlns="http://www.sap.com/rws/bip"><attr name="cuid" type="string">Af5L7PHb5k9Bk86WKdXAy60</attr><attr name="name" type="string">Success</attr><attr name="description" type="string" null="true" /><attr name="id" type="int32">8514</attr><attr name="type" type="string">CCIS.DataConnection</attr></attrs></content><link href="http://cldx-1610-1408:8080/BOE/OpenDocument/opendoc/openDocument.jsp?sIDType=CUID&iDocID=Af5L7PHb5k9..." rel="http://www.sap.com/rws/bip#opendocument" title="OpenDocument" /></entry>

| === Headers ===

| null: [HTTP/1.1 200 OK]

| Date: [Tue, 30 Aug 2016 15:17:48 GMT]

| Content-Length: [904]

| Content-Location: [http://10.103.21.216:6405/biprws/infostore/8514]

| Content-Type: [application/xml]

| Server: [Apache-Coyote/1.1]

| [GET] http://10.103.21.216:6405/biprws/infostore/cuid_Af5L7PHb5k9Bk86WKdXAy60

| === Request content ===

| null

| === Response code ===

| 200

| === Response message ===

| OK

| === Response content ===

| <entry xmlns="http://www.w3.org/2005/Atom"><author><name>Administrator</name><uri>http://10.103.21.216:6405/biprws/infostore/12</uri></author><id>tag:sap.com,2010:bip-rs/Af5L7PHb5k9Bk86WKdXAy60</id><title type="text">Success</title><updated>2016-08-30T14:56:32.561Z</updated><link href="http://10.103.21.216:6405/biprws/infostore/558" rel="up" /><content type="application/xml"><attrs xmlns="http://www.sap.com/rws/bip"><attr name="cuid" type="string">Af5L7PHb5k9Bk86WKdXAy60</attr><attr name="name" type="string">Success</attr><attr name="description" type="string" null="true" /><attr name="id" type="int32">8514</attr><attr name="type" type="string">CCIS.DataConnection</attr></attrs></content><link href="http://cldx-1610-1408:8080/BOE/OpenDocument/opendoc/openDocument.jsp?sIDType=CUID&iDocID=Af5L7PHb5k9..." rel="http://www.sap.com/rws/bip#opendocument" title="OpenDocument" /></entry>

| === Headers ===

| null: [HTTP/1.1 200 OK]

| Date: [Tue, 30 Aug 2016 15:17:48 GMT]

| Content-Length: [904]

| Content-Location: [http://10.103.21.216:6405/biprws/infostore/8514]

| Content-Type: [application/xml]

| Server: [Apache-Coyote/1.1]

| [POST] http://10.103.21.216:6405/biprws/raylight/v1/documents/8974/dataproviders

| === Request content ===

| <dataprovider><name>Success(2016/08/30 11:18:19)</name><dataSourceId>8514</dataSourceId><properties><property key='sql'>SELECT  Outlet_Lookup.State AS 'State', Outlet_Lookup.City AS 'City', SUM(Shop_facts.Amount_sold) AS 'Sales revenue' FROM Shop_facts INNER JOIN Outlet_Lookup ON (Outlet_Lookup.Shop_id=Shop_facts.Shop_id)  GROUP BY  Outlet_Lookup.State,  Outlet_Lookup.City</property></properties></dataprovider>

| === Response code ===

| 200

| === Response message ===

| OK

| === Response content ===

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

| <success>

|     <message>The resource of type "Data provider" with identifier "DP0" has been successfully created.</message>

|     <id>DP0</id>

| </success>

|

| === Headers ===

| null: [HTTP/1.1 200 OK]

| Date: [Tue, 30 Aug 2016 15:17:49 GMT]

| Content-Length: [207]

| Content-Type: [application/xml]

| Server: [Apache-Coyote/1.1]

---------------------------------------------------------------------------------------------------

| [PUT] http://10.103.21.216:6405/biprws/raylight/v1/documents/8974/parameters

| === Request content ===

| null

| === Response code ===

| 200

| === Response message ===

| OK

| === Response content ===

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

| <success>

|     <message>The resource of type "Document" with identifier "8974" has been successfully updated.</message>

|     <id>8974</id>

| </success>

|

| === Headers ===

| null: [HTTP/1.1 200 OK]

| Date: [Tue, 30 Aug 2016 15:17:50 GMT]

| Content-Length: [204]

| Content-Type: [application/xml]

| Server: [Apache-Coyote/1.1]

| [GET] http://10.103.21.216:6405/biprws/raylight/v1/documents/8974

| === Request content ===

| null

| === Response code ===

| 200

| === Response message ===

| OK

| === Response content ===

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

| <document>

|     <id>8974</id>

|     <cuid>Acr_4DK4_kJNuJ9p4hT2SAM</cuid>

|     <name>[Generated] FreeHandSQL Sample (2016/08/30 11:18:17)</name>

|     <folderId>5248</folderId>

|     <path>Public Folders/Web Intelligence Samples</path>

|     <updated>2016-08-30T11:17:48.416-04:00</updated>

|     <scheduled>false</scheduled>

|     <state>Modified</state>

|     <createdBy>Administrator</createdBy>

|     <lastAuthor>Administrator</lastAuthor>

|     <size>14646</size>

|     <refreshOnOpen>false</refreshOnOpen>

| </document>

|

| === Headers ===

| null: [HTTP/1.1 200 OK]

| Date: [Tue, 30 Aug 2016 15:17:50 GMT]

| Content-Length: [557]

| Content-Type: [application/xml]

| Server: [Apache-Coyote/1.1]

| [PUT] http://10.103.21.216:6405/biprws/raylight/v1/documents/8974

| === Request content ===

| <document><name>[Generated] FreeHandSQL Sample (2016/08/30 11:18:17)</name><folderId>5248</folderId></document>

| === Response code ===

| 400

| === Response message ===

| Bad Request

| === Response content ===

| <error>

|     <error_code>WSR 00103</error_code>

|     <message>The query for data provider(s) "[DP0]" is missing or invalid.</message>

|     <stack_trace>com.sap.webi.raylight.RaylightException: The query for data provider(s) "[DP0]" is missing or invalid.&#13;

|     at com.sap.webi.raylight.context.Messenger.createException(Messenger.java:59)&#13;

|     at com.sap.webi.raylight.context.RaylightContext.saveDocument(RaylightContext.java:536)&#13;

|     at com.sap.webi.raylight.actions.document.UpdateDocumentAction.execute(UpdateDocumentAction.java:52)&#13;

|     at sun.reflect.GeneratedMethodAccessor82.invoke(Unknown Source)&#13;

|     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)&#13;

|     at java.lang.reflect.Method.invoke(Method.java:497)&#13;

|     at com.sap.webi.raylight.actions.ActionInvoker.invokeWith(ActionInvoker.java:123)&#13;

|     at com.sap.webi.raylight.actions.ActionDispatcher.invoke(ActionDispatcher.java:105)&#13;

|     at com.sap.webi.raylight.AbstractRaylightServiceDocument.updateDocument(AbstractRaylightServiceDocument.java:191)&#13;

|     at sun.reflect.GeneratedMethodAccessor81.invoke(Unknown Source)&#13;

|     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)&#13;

|     at java.lang.reflect.Method.invoke(Method.java:497)&#13;

|     at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:173)&#13;

|     at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:89)&#13;

|     at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:164)&#13;

|     at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:242)&#13;

|     at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:242)&#13;

|     at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:91)&#13;

|     at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:58)&#13;

|     at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)&#13;

|     at java.util.concurrent.FutureTask.run(FutureTask.java:266)&#13;

|     at org.apache.cxf.workqueue.SynchronousExecutor.execute(SynchronousExecutor.java:37)&#13;

|     at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:106)&#13;

|     at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:255)&#13;

|     at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:113)&#13;

|     at org.apache.cxf.transport.servlet.ServletDestination.invoke(ServletDestination.java:102)&#13;

|     at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:464)&#13;

|     at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:149)&#13;

|     at com.sap.bip.rs.server.servlet.BIPServletController.invoke(BIPServletController.java:93)&#13;

|     at org.apache.cxf.transport.servlet.AbstractCXFServlet.invoke(AbstractCXFServlet.java:148)&#13;

|     at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:179)&#13;

|     at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doPut(AbstractHTTPServlet.java:120)&#13;

|     at javax.servlet.http.HttpServlet.service(HttpServlet.java:649)&#13;

|     at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:159)&#13;

|     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)&#13;

|     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)&#13;

|     at com.businessobjects.sdk.actionfilter.WorkflowFilter.doFilter(WorkflowFilter.java:45)&#13;

|     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)&#13;

|     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)&#13;

|     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)&#13;

|     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)&#13;

|     at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)&#13;

|     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)&#13;

|     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)&#13;

|     at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)&#13;

|     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)&#13;

|     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)&#13;

|     at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)&#13;

|     at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)&#13;

|     at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)&#13;

|     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)&#13;

|     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)&#13;

|     at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)&#13;

|     at java.lang.Thread.run(Thread.java:812)&#13;

| </stack_trace>

| </error>

|

| === Headers ===

| null: [HTTP/1.1 400 Bad Request]

| Date: [Tue, 30 Aug 2016 15:17:50 GMT]

| Content-Length: [5200]

| Connection: [close]

| Content-Type: [application/xml]

| Server: [Apache-Coyote/1.1]

| [POST] http://10.103.21.216:6405/biprws/logoff

| === Request content ===

| null

| === Response code ===

| 200

| === Response message ===

| OK

| === Response content ===

|

| === Headers ===

| null: [HTTP/1.1 200 OK]

| Date: [Tue, 30 Aug 2016 15:17:50 GMT]

| Content-Length: [0]

| Content-Type: [text/xml]

| Server: [Apache-Coyote/1.1]

daniel_paulsen
Active Contributor
0 Kudos

Hi Amol,

The problem with the last request depends on what it is you are trying to do, either save the document back, just updated it or copy it to a different folder.

  1. If you want to save the document back to the CMS with the changes, then the method needs to be a POST and you cannot change the folder ID in the request body.  Remove this tag.


  2. If you want to update the document, since its currently in a "modified" state, then use PUT without a body or use just a <document><state>Unused</state></document> body to save and close the document.  Do not specify a folderId or document name.


  3. If you want to copy the document to another folder then use the POST method with the body you have provided with the new folderID.  I'm not 100% sure, but you will probably have to save the document first before copying to another folder.

Dan

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Dan,

Thanks a lot, this works.

Amol

eric_festinger
Contributor
0 Kudos

hello Amol,

In addition to Dan's recommandation regarding WACS traces, could you please copy/paster the output coming along the HTTP error 400?

eric

Former Member
0 Kudos

Hi Eric,

Out put when i ran same program is as below :

createDataProvider For FHSQL

input == ><dataprovider><name>Success1</name><dataSourceId>6267</dataSourceId><properties><property key='sql'>SELECT  Outlet_Lookup.State as 'State', Outlet_Lookup.City as 'City', Sum(Shop_facts.Amount_sold) as 'Sales revenue' FROM Shop_facts INNER JOIN Outlet_Lookup ON (Outlet_Lookup.Shop_id=Shop_facts.Shop_id) GROUP BY  Outlet_Lookup.State,  Outlet_Lookup.City</property></properties></dataprovider>

java.io.IOException: Server returned HTTP response code: 400 for URL: http://10.103.21.216:6405/biprws/raylight/v1/documents/8077/dataproviders

    at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1626)

    at com.lnt.rkonvert.report.client.UpdateDataProviderSpecification_working.createDataProviderForFHSQL(UpdateDataProviderSpecification_working.java:351)

    at com.lnt.rkonvert.report.client.CreateReportWithTable.createReport(CreateReportWithTable.java:162)

    at com.lnt.rkonvert.report.client.CreateReportWithTable.main(CreateReportWithTable.java:102)

Output from Server ....

I think it complains as 400 error while creating data Provider only and not at saving document.

Thanks,

Amol

eric_festinger
Contributor
0 Kudos

hi Amol,

By "output", I didn't mean the output of your program, but the output returned by WebIntelligence RESTful web service.

You need to read the HttpURLConnection.getErrorStream()

eric

daniel_paulsen
Active Contributor
0 Kudos

Hi Amol,

Can you enable "Show Error Stack" on the WACS server and post the full stack trace?

Also, just as a quick test, try simplifying the name of the DP to just "Success1" to remove any special characters (paranthesis, slashes and colons) to see if that makes any difference.

Dan

Former Member
0 Kudos

Hi Daniel,

Have try with changing DP name as Success1 but getting same error.

please find attached WACS Trace log.

Thanks,
Amol.

daniel_paulsen
Active Contributor
0 Kudos

HI Amol,

the logs seem to be complaining about two things:

  • [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'revenue'..

So make sure the FHSQL works when creating a new document in Launchpad.

The other is with JDBC "Class not found"

  • The following database error occurred: (CS) "Java Class not found in classpath : com.microsoft.sqlserver.jdbc.SQLServerDriver"

if you are using a JDBC connection, make sure this works and refreshes correctly when creating a new report in Launchpad.

Dan

Former Member
0 Kudos

Hi Dan/ Eric,

Thanks for reply.

Log statements which you have highlighted was old log as when i Have created report in Launchpad with FHSQL it is working correctly for same JDBC/ODBC connection.

At today i have deleted all logs from folder and ran same program then it not creates wacs_trace logs.

Out put when i ran same program is as below :

createDataProvider For FHSQL

input == ><dataprovider><name>Success1</name><dataSourceId>6267</dataSourceId><properties><property key='sql'>SELECT  Outlet_Lookup.State as 'State', Outlet_Lookup.City as 'City', Sum(Shop_facts.Amount_sold) as 'Sales revenue' FROM Shop_facts INNER JOIN Outlet_Lookup ON (Outlet_Lookup.Shop_id=Shop_facts.Shop_id) GROUP BY  Outlet_Lookup.State,  Outlet_Lookup.City</property></properties></dataprovider>


java.io.IOException: Server returned HTTP response code: 400 for URL: http://10.103.21.216:6405/biprws/raylight/v1/documents/8077/dataproviders

    at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1626)

    at com.lnt.rkonvert.report.client.UpdateDataProviderSpecification_working.createDataProviderForFHSQL(UpdateDataProviderSpecification_working.java:351)

    at com.lnt.rkonvert.report.client.CreateReportWithTable.createReport(CreateReportWithTable.java:162)

    at com.lnt.rkonvert.report.client.CreateReportWithTable.main(CreateReportWithTable.java:102)

Output from Server ....

I think it complains as 400 error while creating data Provider only and not at saving document.

Thanks,

Amol