DataProvider get Custom SQL Query via Java SDK

[Former Member]

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.

Regards.

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;
										brOutputLog.append(fWriterOutputLog);
										
										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;
									brOutputLog.append(fWriterOutputLog);
								}
							}
	
						}

[DellSC]

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.

-Dell

[Former Member]

Hello,

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"),
							"application/json");

					JSONObject dataProviderProp = new JSONObject(dp).getJSONObject("dataprovider");
					dataProvidersJSONList.add(dataProviderProp);
				}
				else{
					dataProvidersJSONList.add(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
		e.printStackTrace();
	}
}

Thanks in advance for your help,

Best Regards,

Benjamin.

[Former Member]

Dear All,

Any ideas ???

Best regards,

Benjamin

[Former Member]

Dear all,

Any ideas?

Regards,

Benjamin