<!DOCTYPE html>
<!--=====================================================================================================
<< Fertch a REST Web Service to obtain data from a database
<< Must provide the database connection info in fields user, pswd, db
<< Must provide SQL statements in a field called sql
======================================================================================================-->
<html>
<head>
<title>SamDB Database</title>
<script src=objConvert.js></script>
<script src=table.js> </script>
<style>
@import url("table.css");
fieldset {width:870px; height:520px; background-color:lightgray; border-radius:15px}
input, textarea {border-radius:5px; resize:none}
#button {height:40px; width:220px; color:black; background-color:#999999}
#button:hover {color:white; background-color:#444444}
#divTable {position:absolute; top:250px; left:189px; height:300px; width:695px; background-color:white;
border-style:solid; border-width:2px; border-color:#cccccc; overflow:auto;}
#divURL {position:absolute; top:560px; left:192px; width:700px; color:#999999; overflow:clip}
</style>
</head>
<body bgcolor=lightyellow>
<h1 style="text-indent:450px"
title="SQL Commands Supported:
show databases|tables, db|use dbname, desc tablename
create tablename (colname1, colname2, colname3, etc.)
insert into tablename values(colvalue1, 'colvalue2', 'colvalue3', etc.)
select * | colnames from table1 join table2 on table1.col1=table2.col2 where colname='value'">SamDB</h1>
<fieldset>
<table>
<tbody>
<tr>
<td>User / Pswd / Database...
<td><input type="text" id="_user" value="demo" style='width:215px'> /
<input type="password" id="_pswd" value="" style='width:215px'> /
<input type="text" id="_db" value="data" style='width:217px'>
<tr>
<td title="SQL Commands Supported:
show databases|tables, db|use dbname, desc tablename
create tablename (colname1, colname2, colname3, etc.)
insert into tablename values(colvalue1, 'colvalue2', 'colvalue3', etc.)
select * | colnames from table1 join table2 on table1.col1=table2.col2 where colname='value'">
Enter SQL Statement...</td>
<td><textarea id="_sql" rows="5" style='width:690px'>show tables</textarea>
</tr>
<tr>
<td></td>
<td><input type="submit" value="Execute SQL" id=button onCLick="doRequest()">
Show returned data <input type="checkbox" id="showRaw">
</tr>
</tbody>
</table>
<br><br>
<div id="divTable"></div>
<div id="divURL"> </div>
</fieldset>
<script>
url = "https://storm.cis.fordham.edu/~sultan/sql/demo/SamDB/SamDB.cgi";
//==================================================================================================
// doRequest: fetch the URL with param
// receives a json response
//==================================================================================================
function doRequest() {
var user = document.getElementById("_user").value; //get data from screen
var pswd = document.getElementById("_pswd").value;
var db = document.getElementById("_db").value;
var sql = document.getElementById("_sql").value;
sql = sql.trim()
if (!user || !pswd || !db || !sql)
{
var div = document.getElementById('divTable');
div.innerHTML = 'Please enter User, Password, Database name and SQL Statement';
div.style.color = 'red';
return;
}
if (!pswd || pswd != "demo")
{
var div = document.getElementById('divTable');
div.innerHTML = 'Please enter proper password';
div.style.color = 'red';
return;
}
sql = escape(sql); //URL encode the sql
var param = 'user='+user+'&pswd='+pswd+'&db='+db+'&sql='+sql; //create the query string params
var URLparam = url +'?'+ param
document.getElementById('divURL').innerHTML = unescape(URLparam);
fetch(URLparam) //fetch() a url, and returns a promise
.then(respObj => respObj.json()) //promise.then(receives the response obj => calls the text() method)
.then(respJSON => doResponse(respJSON)) //promise.then(receives the response json => call doResponse() methods)
}
//========================================================================================
// doResponse: receives the json response
// converts json to 2dimensional array
// display in a table format
//========================================================================================
function doResponse(respJSON)
{
if (document.getElementById("showRaw").checked) //if show XML is requested
alert(JSON.stringify(respJSON,null,'\t')); //display raw response in XML format
var arrObj = respJSON;
var array2 = arrObj2arr(arrObj); //convert array of objects to 2 dim array
var headers = array2.shift(); //pop off the 1st array as headers
var data = array2; //the rest is all the raw data
Table(headers, data, -1, 'asc') //call table.js
}
//==============================================================================================
</script>
</body>
</html>