- July 22, 2019
- Posted by: Sibi Elamparithi
- Categories: APEX, Technology
Apex, by default supports only Oracle database. There isn’t any default options to display data from other databases like SQL Server. But, we can use python to create a webservice with SQL Server data and call the webservice in Apex using Ajax.
Step 1 : Run the following python script in any python IDE like spyder or IDLE
import pyodbc
from flask import Flask
from flask_cors import CORS
import json
app = Flask(__name__)
CORS(app)
@app.route(“/”)
def index():
con = pyodbc.connect(‘DRIVER={SQL Server};SERVER=<your_host>;DATABASE=<your_db_name>;UID=<your_user>;PWD=<your_password>’)
cur = conn.cursor()
cur.execute(”’select * from dept”’)
row_headers=[x[0] for x in cur.description]
rv = cur.fetchall()
json_data=[]
for result in rv:
json_data.append(dict(zip(row_headers,result)))
return json.dumps({‘ITEMS’:json_data})
if __name__ == ‘__main__’:
app.run(debug = True
Step 2 : Once the script is executed, a webservice is created and the webservice URL will be generated as output. If you run the URL in a browser you can find the output as below.
Step 3 : Create a Page in the Apex application. Create a hidden item. Paste the below ajax code in the Execute on Page Load section.
var getval ;
$.ajax({ url:”http://localhost:5000″, // give the url of webservice
type: ‘GET’,
responseType:’application/json’,
success: function (response)
{
getval = response;
apex.item( “P6_JSON_VAL” ).setValue( getval ); //give name of your hidden item
apex.server.process(‘DUMMY’,
{
pageItems: ‘#P6_JSON_VAL’ //give name of your hidden item
},
{
dataType: ‘text’
});
apex.event.trigger( “#jsonrpt”, “apexrefresh” );
},
error: function (error)
{
console.log(error);
}
});
Step 4 : Create a classic report and give static id as “jsonrpt”. Use the following report query.
WITH test_user AS (
SELECT
:P6_JSON_VAL user_json
FROM
dual
) SELECT
d.*
FROM
test_user p,
JSON_TABLE ( p.user_json,’$’
COLUMNS
NESTED PATH ‘$.ITEMS[*]’
COLUMNS (
DEPTNO VARCHAR2 ( 30 CHAR ) PATH ‘$.DEPTNO’,
DNAME VARCHAR2 ( 30 CHAR ) PATH ‘$.DNAME’,
LOC VARCHAR2 ( 30 CHAR ) PATH ‘$.LOC’
)
)
d
Save the changes. Now you can view the data from SQL Server in Apex
