Path: api/v1/custom/getdata
Method: GET
Example: /api/v1/custom/GetData?queryname=GetCardFiles&qp1=&pageno=0&pagesize=25
#
# Generic Custom URL Handler script for allowing read access to the database in Jim2
#
# WARNING:
# this script is not suited for large datasets (responses) because the http response
# is constructed as an object, then serialised to string, then returned to the caller
#
# This table utilises the table JimWebAPIGetData. Schema (at time of this comment) is as follows:
# QueryName The name of the query
# System Indicator if the query name is a system query or not
# SQL The SQL to be executed. NB multiple results sets and parameters are supported - refer query parameters below
# Format Flag to indicate if SQL needs to have format called. The following parameters are available for the formating:
# {0} is the pagination (eg OFFSET 10 ROWS FETCH NEXT 6 ROWS ONLY substituted in)
#
# End point must be configured in the JimScriptURLHandler table (as per any other url handler script)
#
# Query (url) parameters supported:
# queryname The name of the SQL query to execute as per JimWebAPIGetData table
# system Set to 0 or 1. Indicates if a system script is to be executed or not. If left out either will be used, with preference given to system if both exist
# qp1, qp2 etc Query parameters. These are always pass through to the query as strings, so it is up the the SQL to manage conversion (see examples below)
# pagesize The page size (number of rows per page). Only supported if Format is 1 and SQL supports the {0} format parameter
# pageno The page number - 0 is the first page.
#
# re: Pagination
# The caller must keep calling unless less rows that the page size or no rows are returned if the full database is to be queried.
# NOTE: if the underlying data is changed afer the first call (row added or removed) between calls then a row may be missed or duplicated.
#
#
# Examples (note these examples will only work when system mode is Happen due to security restrictions):
#
# QueryName 'test', Sql 'select * from JimB2BState', Format 0
# curl http://localhost/jimtest/api/v1/custom/getdata?queryname=test
#
# QueryName 'test2', Sql 'select * from JimB2BState where State=@qp1 or State=@qp2', Format 0
# curl "http://localhost/jimtest/api/v1/custom/getdata?queryname=test2&qp1=1&qp2=2"
# NOTE: this example has an implicit conversion from the query parameter string to int (State is [int] column).
# For more complex conversions it is up to the SQL to manage the conversion
#
# QueryName 'test3',
# Sql 'select InvNo,CardCode,InvAmt,InvDate
# from JimInv i
# join JimCardFile cf on cf.CardNo = i.CustNo
# where InvNo > CONVERT(int,@qp1);
# select JobNo,CardCode,DateIn
# from JimJob j
# join JimCardFile cf on cf.CardNo = j.CustNo
# where DateDue > CONVERT(DATETIME, @qp2);'
# Format 0
# curl "http://localhost/jimtest/api/v1/custom/getdata?queryname=test3&qp1=0&qp2=2020-1-1%2014:00"
#
# QueryName 'test4',
# Sql 'select * from JimLog where ObjectType=@qp1 ORDER BY rowid {0}'
# Format 1
# curl "http://localhost/jimtest/api/v1/custom/getdata?queryname=test4&qp1=Job&pageno=0$pagesize=25"
# curl "http://localhost/jimtest/api/v1/custom/getdata?queryname=test4&qp1=Job&pageno=1$pagesize=25"
Further information
Jim2 API Current API Capabilities