Skip to Content

DataProvider get Custom SQL Query via Java SDK

Nov 03, 2017 at 08:23 AM


avatar image

Dear all,

I'm trying to retrieve custom SQL Script queries from Webi documents using the java SDK.
Already tried to retrieve it using the RESTful WebService but not successfully (RESTful only working for standard generating script)

Please see the code here below. For me, the code is not retrieving anything, SQL Viewing not supported (process always going into the "else" condition while testing sqlDataProv.isSupported(DataProviderFeature.VIEW_SQL) ). I think it's because WebI documents have been created in a former version of Web Intelligence application.

Have you got any idea on how to get the custom SQL scripts using Java SDK?

Thanks in advance for your support.


if (dataProvider instanceof SQLDataProvider){
							SQLDataProvider sqlDataProv = (SQLDataProvider) dataProvider;
							if ( sqlDataProv.isSupported(DataProviderFeature.VIEW_SQL) ) {
								ArrayList<TreeNode> nodes = getListOfTreeNodes(sqlDataProv.getSQLContainer(), true);
								for (TreeNode node : nodes) {
									if (node instanceof SQLSelectStatement) {
										SQLSelectStatement query = (SQLSelectStatement)node;
										fWriterOutputLog = "SQL Retrieved" + NEW_LINE_SEPARATOR
												+ query.getSQL() + NEW_LINE_SEPARATOR;
										sqlQuery += query.getSQL() + NEW_LINE_SEPARATOR;
							else {
								if ( verboseExec == 1){
									fWriterOutputLog = "DataProvider " + dataProvider.getID() 
													 + " doesn't support viewing its SQL with CMS access."
													 + NEW_LINE_SEPARATOR;
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Dell Stinnett-Christy Nov 07, 2017 at 09:09 PM

What version of BOBJ are you using? If it's 4.0 or newer, you will have to use the Web Intelligence RESTful Web Services SDK to get this information. It looks like you're using Rebean, which has been deprecated in the newer versions.


Show 2 Share
10 |10000 characters needed characters left characters exceeded


Thanks a lot for your answer.

BOBJ version is 4.1 sp7
I already tried to use the RESTful Web Service without success for these queries (manual scripts).
Please see the code used to retrieve queries here below using RESTful Web Service.

Code to get DataProviders:

String dps;
dps = connection.query("GET", "/documents/" + docID + "/dataproviders", "application/json");	
JSONObject objectDPs = new JSONObject(dps);

if (objectDPs.has("dataproviders")) {

	JSONObject dataProviders = new JSONObject(dps).getJSONObject("dataproviders");

	if (dataProviders.has("dataprovider")){
		Object DataProviderObj = dataProviders.get("dataprovider");
		if (DataProviderObj instanceof JSONArray){
			JSONArray dataProviderArray = new JSONObject(dps).getJSONObject("dataproviders").getJSONArray("dataprovider");

			for (int j = 0; j < dataProviderArray.length(); j++) {
				JSONObject dataProvider = dataProviderArray.getJSONObject(j);

				if (!dataProvider.getString("dataSourceType").contains("bex")){
					String dp = connection.query("GET", "/documents/" + docID
							+ "/dataproviders/" + dataProvider.getString("id"),

					JSONObject dataProviderProp = new JSONObject(dp).getJSONObject("dataprovider");


Code to get DataProvider Queries:

for (JSONObject dataProvJSON: dataProvidersJSONList) {
	try {
		String queryParsed = "";
		fWriterLineCSV = "";

		if (dataProvJSON.has("properties")){
			JSONArray dpPropertiesJSON = dataProvJSON.getJSONArray("properties");
			queryParsed = dpPropertiesJSON.toString();
		} else if (dataProvJSON.has("query")){
			queryParsed += dataProvJSON.getString("query").replaceAll("[\n\r]", "");
	} catch (JSONException e) {
	// TODO Auto-generated catch block

Thanks in advance for your help,

Best Regards,



Dear All,

Any ideas ???

Best regards,


Benjamin Trudu Nov 07, 2017 at 09:34 AM

Dear all,

Any ideas?



10 |10000 characters needed characters left characters exceeded