Hi, Gupta.
I wrote a custom control to load metadata of a connection.
I have looked for a API in REST API, but is not available yet, even 11.1.7 FP1.
You can modify this custom control to make a list of tables too.
All you need is the name of connection/schema and the ID schema.
I use a datastore in my report that pass this information for my custom control
That report is based on Excel that has a list of Connection Name, Database name and ID of schema to be loaded.
runLoadMetadata is the method that you looking for.
define(function () {
"use strict";
var g_aDataStores = [];
var cookieXSRF = findCookie("XSRF-TOKEN");
var baseURL = getBaseUrl();
try {
var postXHR = new XMLHttpRequest();
var getXHR = new XMLHttpRequest();
} catch (e) {
try {
var postXHR = new ActiveXObject('Msxml2.XMLHTTP');
var getXHR = new ActiveXObject('Msxml2.XMLHTTP');
} catch (e) {
try {
var postXHR = new ActiveXObject('Microsoft.XMLHTTP');
var getXHR = new ActiveXObject('Microsoft.XMLHTTP');
} catch (e) {
alert('Navegador não suportado. Tente outro mais moderno.');
}
}
}
function LoadMetadata() {};
LoadMetadata.prototype.draw = function (oControlHost) {
var aDataStores = g_aDataStores[0];
var o = oControlHost.configuration;
var aHtml = [];
this.drawDataStore(aDataStores, aHtml);
var el = oControlHost.container;
el.innerHTML = aHtml.join("");
};
LoadMetadata.prototype.drawDataStore = function (oDataStore, aHtml) {
var iColCount = oDataStore.columnCount;
var iRowCount = oDataStore.rowCount;
var status = iRowCount == 1 ? 50.0 : 100.0 / iRowCount;
var elStatus = document.getElementsByClassName('clsStatusLoading')[0];
aHtml.push('<table class="ls" style="border-collapse:collapse; background-color:white;">');
aHtml.push('<tr>');
for (var iCol = 0; iCol < iColCount; iCol++) {
aHtml.push('<td class="clsTitle">' + this.F_HTMLEncode(oDataStore.columnNames[iCol]) + '</td>');
}
aHtml.push('<td class="clsTitle">Status</td>');
aHtml.push('</tr>');
for (var iRow = 0; iRow < iRowCount; iRow++) {
elStatus.innerHTML = 'Carregamento de metadados em ' + status.toFixed(2) + '%';
aHtml.push('<tr>');
for (var iCol = 0; iCol < iColCount; iCol++) {
var sValue = oDataStore.getCellValue(iRow, iCol);
var sFormattedValue = oDataStore.getFormattedCellValue(iRow, iCol);
var sDataType = oDataStore.dataTypes[iCol];
aHtml.push('<td class="' + ((iRow % 2 != 0) ? 'clsLinhaPar' : 'clsLinhaImpar') + '">');
aHtml.push(this.F_HTMLEncode(sFormattedValue));
if (sFormattedValue != sValue) {
aHtml.push(' (' + this.F_HTMLEncode(sValue) + ')');
}
aHtml.push('</td>');
}
//A primeira coluna (index 0) deve ser o nome da conexao e o segunda coluna (index 1) deve ser o ID da conexao
aHtml.push('<td class="' + ((iRow % 2 != 0) ? 'clsLinhaPar' : 'clsLinhaImpar') + '" style="text-align:left">' + runLoadMetadata(oDataStore.getCellValue(iRow, 0), oDataStore.getCellValue(iRow, 1)) + '</td>');
aHtml.push('</tr>');
status += 100.0 / iRowCount;
}
aHtml.push('</table>');
elStatus.parentElement.removeChild(elStatus);
};
LoadMetadata.prototype.setData = function (oControlHost, oDataStore) {
g_aDataStores[oDataStore.index] = oDataStore;
};
LoadMetadata.prototype.F_HTMLEncode = function (s) {
return String(s).replace(/&/g, "&").replace(/</g, "<").replace(/>/g, ">");
};
function runLoadMetadata(datasource, idDatabase) {
var params = 'async=true&connectionSpec={"connections":[{"datasource":"' + datasource + '","connection":"' + datasource + '"}]}';
var url = baseURL + '/bi/v1/metadata/base_modules/' + idDatabase + '?' + params;
var status = '';
try {
postXHR.open("POST", url, false);
postXHR.setRequestHeader("Content-type", "application/json; charset=utf-8");
postXHR.setRequestHeader("X-Requested-With", "XMLHttpRequest");
postXHR.setRequestHeader("X-XSRF-TOKEN", cookieXSRF);
postXHR.onreadystatechange = function () {
if (this.readyState == 4 && this.status == 202) {
var taskHref = JSON.parse(this.responseText).href
do {
//console.log('Checking load metadata ... Database: ' + datasource + ' idDatabase:' + idDatabase + ' min:' + new Date().getMinutes() + ' seg:' + new Date().getSeconds());
status = getStatusLoadMetadata(taskHref);
} while (status.toUpperCase() == 'EXECUTING' || status.toUpperCase() == 'NOT_CANCELLABLE' || status.toUpperCase() == 'PENDING');
} else if (this.readyState == 4 && this.status == 500) {
status = JSON.parse(this.responseText).msg;
} else if (this.readyState == 4) {
status = 'Status ' + this.status + ' ' + this.responseText;
}
};
postXHR.send();
} catch (e) {
console.log("Erro ao executar load.\r\n" + e + "\n" + postXHR.responseText);
status = 'ERROR' + e;
}
return status;
};
function getStatusLoadMetadata(taskHref) {
var url = baseURL + '/bi/v1/metadata' + taskHref;
var status = '';
try {
getXHR.open("GET", url, false);
getXHR.setRequestHeader("X-Requested-With", "XMLHttpRequest");
getXHR.setRequestHeader("X-XSRF-TOKEN", cookieXSRF);
getXHR.onreadystatechange = function () {
if (this.readyState == 4 && this.status == 200) {
status = JSON.parse(this.responseText).state != 'ERROR' ? JSON.parse(this.responseText).state : JSON.parse(this.responseText).errorMessage;
} else {
status = 'Status ' + this.status + ' ' + this.responseText;
}
};
getXHR.send();
} catch (e) {
console.log("Erro ao executar load.\r\n" + e + "\n" + postXHR.responseText);
status = 'ERROR' + e;
}
return status;
};
function getBaseUrl() {
var baseUrl = '';
if (window.location.port == '' || window.location.port == '0' || window.location.port == undefined || window.location.port == '80' || window.location.port == '443') {
baseUrl = window.location.protocol + "//" + window.location.host + "/" + window.location.pathname.split('/')[1];
} else {
baseUrl = window.location.protocol + "//" + window.location.host;
}
return baseUrl;
};
function findCookie(cName) {
var cookieArray = document.cookie.split(';').reduce((cookies, cookie) => {
const [name, value] = cookie.split('=').map(c => c.trim());
cookies[name] = value;
return cookies;
}, {});
return cookieArray[cName];
};
return LoadMetadata;
});
------------------------------
JEAM COELHO
Cognos Solution Architect
LinkedIn:
https://www.linkedin.com/in/jeamcoelho/------------------------------
Original Message:
Sent: Fri October 23, 2020 02:57 PM
From: Buddhi Gupta
Subject: Data Server Table Lists
Hi All
Is there any REST API based solution available to get Dataserver list of tables, loading metadata?
Thanks for any inputs!
------------------------------
Buddhi Gupta
------------------------------
#CognosAnalyticswithWatson