Valentina Report Server - REST Access

This section describes how to setup access to Valentina Report Server via its REST API.

Let you have some mySQL / postgreSQL / etc Server with your database(s).

Let you want to use Valentina Report Server to produce reports using these databases.

Let you want use REST API to do this. You may want this to be able access Valentina Report Server from programming languages and OS that do not have native Valentina ADKs, e.g. Python, Android, WinOS.

It is a good idea to read article: REST for Valentina Server v6.0

• Install Valentina Server to any computer you want to use. This computer must have network access to your DB servers.

• Install Valentina Studio Pro to be able design your report. You can install it on another computer. This can be for example your notebook.

• Make sure that ini file of your Valentina Server has enabled REST port.

• Use Valentina Studio Pro to create new Valentina Project (*.vsp) under Valentina Server.

• Specify your postgreSQL / mySQL database servers as datasource when you will be asked.

• Use Valentina Studio Pro to check that your report works. You can see its preview and generate e.g. PDF file

• Prepare your code with REST API usage.

• IMPORTANT NOTE: currently REST API can use 'Valentina' or 'SQLite' database providers. Working with REST and Reports and some non-Valentina DB, you should still specify provider as “Valentina”, and the empty name of database “”.

• Let we have postgreSQL Server with the following parameters:
  • IP = 192.168.2.50
  • user = postgres
  • passw = postgres
  • database_name = postgredb

• Let we have Valentina Server with:
  • user name = sa
  • password = sa
  • project name = my_project.vsp
  • report name = report_22

Below you can see Python script, which uses REST access to Valentina Report Server.

Please note that

• it uses 'REPORT command' of Valentina SQL.
• This command contains SQL query in your DB Server syntax (!)

Valentina Server do the following job:

• REPORT command is sent to vdb_empty Valentina database.
• it is parsed by Valentina Server using Valentina SQL parser.
• then SELECT query is executed by your DB Server and VServer get back cursor with data for report.
• then Report engine builds report as e.g. PDF.
• PDF is returned by REST.

#!/usr/bin/python
# -*- coding: utf-8 -*-
 
import urllib2
import json
import hashlib
import string
import base64
 
# **********************************************************************************************
gHost = "http://localhost:8080"
gUser = "sa"
gPassword = hashlib.md5("sa").hexdigest()
gSQLQuery = """REPORT my_project.report_22 FROM
                 'postgresql://host=192.168.2.50, port=5432, dbname=postgredb, user=postgres, password=postgres, timeout=0'
                  USING 'SELECT * FROM T1 WHERE f1 > ?' """
 
# **********************************************************************************************
def open_connection(host, user, pwd):
    """Open connection and return session URL."""
 
    # Set the request authentication headers
    headers = {'Accept': 'application/json', 'Content-Type': 'application/json'}
    body = "{ \"User\" : \"" + user + "\", \"Password\" : \"" + pwd + "\" }"
 
    url = host + '/rest'
 
    # Send the POST request
    request = urllib2.Request(url, body, headers)
    request.get_method = lambda: 'POST'
 
    # Read the response
    opener = urllib2.build_opener(urllib2.HTTPHandler)
    response = opener.open(request)
    assert(response.code == 201)
    info = response.info()
    return info['Set-Cookie'], info['Location']
 
 
# **********************************************************************************************
def close_connection(host, session_id, session_url):
    """Close connection."""
 
    if session_id is not None:
        # Set the request authentication headers
        headers = {'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id}
        url = host + session_url
 
        # Send the DELETE request
        request = urllib2.Request(url, None, headers)
        request.get_method = lambda: 'DELETE'
 
        # Read the response
        opener = urllib2.build_opener(urllib2.HTTPHandler)
        response = opener.open(request)
        assert(response.code == 204)
    return None
 
 
# **********************************************************************************************
def get_info(host, session_id, url):
    """Return GET info."""
 
    # Set the request authentication headers
    headers = {'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id}
    url = host + url
 
    # Send the GET request
    request = urllib2.Request(url, None, headers)
    request.get_method = lambda: 'GET'
 
    # Read the response
    opener = urllib2.build_opener(urllib2.HTTPHandler)
    response = opener.open(request)
    assert(response.code == 200)
    return response.read()
 
 
# **********************************************************************************************
def do_sql(host, session_id, sql_url, sql_database, sql, params = None):
    """Perform SQL and return queryResult URL."""
 
    # Set the request authentication headers
    headers = {'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id}
    body = "{ \"vendor\" : \"Valentina\",  \"database\" : \"" + sql_database + "\" ,\"Query\" : \"" + sql + "\""
    if params is not None:
        body = body + ",\"Params\" : " + params
    body = body + " }"
    url = host + sql_url
 
    # Send the POST request
    request = urllib2.Request(url, body, headers)
    request.get_method = lambda: 'POST'
 
    # Read the response
    opener = urllib2.build_opener(urllib2.HTTPHandler)
    response = opener.open(request)
    assert(response.code == 201)
    return response.info()['Location']
 
 
# **********************************************************************************************
def doReport(inFileName, inExt, inQuery, inQuery_Params = None):
    session_id = None
    session_url = None
    try:
        # Open connection
        session_id, session_url = rest_common.open_connection(gHost, gUser, gPassword)
        assert (session_id != "")
 
        # Get connection info and interpret the JSON response
        connection_info = json.loads(rest_common.get_info(gHost, session_id, session_url).decode('utf8'))
 
        # SQL query
        if 'html' == inExt:
            query_result_url = rest_common.do_sql(
                gHost, session_id, connection_info['SQL_Location'], "", inQuery + "AS HTML", inQuery_Params)
        else:
            query_result_url = rest_common.do_sql(
                gHost, session_id, connection_info['SQL_Location'], "", inQuery + "AS PDF", inQuery_Params)
 
        # Get queryResult info and interpret the JSON response
        query_result_info = json.loads(rest_common.get_info(gHost, session_id, query_result_url).decode('utf8'))
        if 'Cursor' in query_result_info:
            # Get cursor data and interpret the JSON response
            cursor_data = json.loads(rest_common.get_info(
                gHost, session_id, query_result_info['Cursor']['Records']).decode('utf8'))
 
            if 'html' == inExt:
                # Dirty trick to get some of "control" characters back.
                # Probably some better way exists...
                print open(inFileName+"."+inExt, "wb").write(string.replace(cursor_data['records'][0][0], '\\n', '\n'))
            else:
                print open(inFileName+"."+inExt, "wb").write(base64.b64decode(cursor_data['records'][0][0]))
 
 
    except urllib2.HTTPError as httpErr:
        if httpErr.code == 400:
            err_body = json.loads(httpErr.read().decode('utf8'))
            print("Error - " + err_body['Error'])
        else:
            print("Error - " + str(httpErr))
    except urllib2.URLError as urlErr:
        print(urlErr.reason)
 
    # Close connection
    session_id = rest_common.close_connection(gHost, session_id, session_url)
    assert (session_id is None)
 
 
# **********************************************************************************************
def main():
    doReport("report_res", "pdf", gSQLQuery, "[1]")
    doReport("report_res", "html", gSQLQuery, "[1]")
 
# **********************************************************************************************
main()