Demo
https://www.screencast.com/t/wEBTrj9WWeHn
You have to restart your WAS after Publish any changes with API Servicesm EACH TIMES!
GET, POST, DELETE, PUT sample code
using northwind database
for delete I can make it work by
- check option has custom heading,
- build header
- set these:
- Header variable name: CustomerID
- Value: {arguments.CustomerID}
Code:
define class REST::myRestAPI
'GET ----------------------------------------------------------
function getCustomer as p (customerId as c )
dim ops as p
ops.connectionString = "::Name::northwind"
dim ops.sql as c
ops.json = .t.
'define the nested sql queries and query properties. each level of indentation denotes a parent-child relationship
ops.sql = <<%xstr%
{sql: 'SELECT CUSTOMERID, COMPANYNAME, CONTACTNAME, CONTACTTITLE, CITY, COUNTRY, POSTALCODE, PHONE, FAX FROM customers WHERE customerid = :customerid',name: 'Top',sqlSecurityJSON: ''}
{sql: 'SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM orders',name: 'Orders',parentKey: 'CUSTOMERID',key: 'CustomerID',sqlSecurityJSON: ''}
{sql: 'SELECT order_details.OrderID, order_details.ProductID, products.ProductName, order_details.UnitPrice, order_details.Quantity FROM [order details] order_details INNER JOIN products products ON order_details.ProductID = products.ProductID',name: 'Details',parentKey: 'OrderID',key: 'order_details.OrderID',sqlSecurityJSON: ''}
%xstr%
dim args as sql::arguments
args.add("customerid",customerId)
dim p as p
'execute the definition to get the JSON data
p = a5_sql_nested_query_to_json_document(ops,args)
if p.hasError = .f. then
dim json as c
json = p.json
getCustomer = json_parse(json)
else
'there was an error
dim errorText as c
errorText = p.errortext
dim pout as p
pout.error = .t.
pout.errorText = errorText
getCustomer = pout
end if
end function
'DELETE ----------------------------------------------------------
function deleteCustomer as c(CustomerID as c )
'dim CustomerID as c = deleteCustomer.CustomerID
dim cn as sql::connection
dim flag as l
flag = cn.open("::Name::northwind")
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
end
end if
'turn on portable SQL
cn.PortableSQLEnabled = .t.
dim SQL as c
sql = "DELETE FROM customers WHERE CustomerID = :CustomerID"
dim args as sql::arguments
args.add("CustomerID", CustomerID)
'execute the SQL
flag = cn.execute(sql,args)
end function
'UPDATE ----------------------------------------------------------
function updateCustomer as p (updateCustomer as REST::myRestAPI::updatePCustomer)
dim CustomerID as c = updateCustomer.CustomerID
dim CompanyName as c = updateCustomer.CompanyName
dim ContactName as c = updateCustomer.ContactName
dim ContactTitle as c = updateCustomer.ContactTitle
dim ContactTitle as c = updateCustomer.ContactTitle
dim Address as c = updateCustomer.Address
dim City as c = updateCustomer.City
dim Region as c = updateCustomer.Region
dim PostalCode as c = updateCustomer.PostalCode
dim Country as c = updateCustomer.Country
dim Phone as c = updateCustomer.Phone
dim Fax as c = updateCustomer.Fax
dim cn as sql::connection
dim flag as l
flag = cn.open("::Name::northwind")
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
end
end if
'turn on portable SQL
cn.PortableSQLEnabled = .t.
dim SQL as c
sql = "UPDATE customers SET CompanyName= :CompanyName, ContactName= :ContactName, ContactTitle= :ContactTitle, Address= :Address ,City= :City ,Region= :Region ,PostalCode= :PostalCode, Country= :Country, Phone= :Phone, Fax= :Fax WHERE CustomerID = :CustomerID"
dim args as sql::arguments
args.add("CustomerID", CustomerID)
args.add("CompanyName", CompanyName)
args.add("ContactName", ContactName)
args.add("ContactTitle", ContactTitle)
args.add("Address", Address)
args.add("City", City)
args.add("Region", Region)
args.add("PostalCode", PostalCode)
args.add("Country", Country)
args.add("Phone", Phone)
args.add("Fax", Fax)
'execute the SQL
flag = cn.execute(sql,args)
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
cn.close()
end
end if
dim rowsInserted as n
'get the number of rows that were inserted
rowsInserted = cn.CallResult.rowsaffected
cn.close()
end function
'CREATE ----------------------------------------------------------
function createCustomer as p (newCustomer as REST::myRestAPI::newPCustomer)
dim CustomerID as c = newCustomer.CustomerID
dim CompanyName as c = newCustomer.CompanyName
dim ContactName as c = newCustomer.ContactName
dim ContactTitle as c = newCustomer.ContactTitle
dim ContactTitle as c = newCustomer.ContactTitle
dim Address as c = newCustomer.Address
dim City as c = newCustomer.City
dim Region as c = newCustomer.Region
dim PostalCode as c = newCustomer.PostalCode
dim Country as c = newCustomer.Country
dim Phone as c = newCustomer.Phone
dim Fax as c = newCustomer.Fax
dim cn as sql::connection
dim flag as l
flag = cn.open("::Name::northwind")
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
cn.close()
end
end if
'turn on portable SQL
cn.PortableSQLEnabled = .t.
dim SQL as c
sql = "INSERT INTO customers(CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax) VALUES (:CustomerID,:CompanyName,:ContactName,:ContactTitle,:Address,:City,:Region,:PostalCode,:Country,:Phone,:Fax)"
dim args as sql::arguments
args.add("CustomerID", CustomerID)
args.add("CompanyName", CompanyName)
args.add("ContactName", ContactName)
args.add("ContactTitle", ContactTitle)
args.add("Address", Address)
args.add("City", City)
args.add("Region", Region)
args.add("PostalCode", PostalCode)
args.add("Country", Country)
args.add("Phone", Phone)
args.add("Fax", Fax)
'execute the SQL
flag = cn.execute(sql,args)
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
cn.close()
end
end if
dim rowsInserted as n
'get the number of rows that were inserted
rowsInserted = cn.CallResult.rowsaffected
end function
end class
'CLASS ----------------------------------------------------------
define class REST::myRestAPI::newPCustomer
dim CustomerID as c
dim CompanyName as c
dim ContactName as c
dim ContactTitle as c
dim Address as c
dim City as c
dim Region as c
dim PostalCode as c
dim Country as c
dim Phone as c
dim Fax as c
end class
define class REST::myRestAPI::updatePCustomer
dim CustomerID as c
dim CompanyName as c
dim ContactName as c
dim ContactTitle as c
dim Address as c
dim City as c
dim Region as c
dim PostalCode as c
dim Country as c
dim Phone as c
dim Fax as c
end class
How to edit my old post instead create new one reply in the forum?
Leave a comment: