ODBC

DbfScript lets you retrieve data from external data sources that support ODBC, which includes other databases running SQL Server, MySQL and Oracle Database.

var #result = data:RunODBC ( connection, command )

The connection parameter is the ODBC connection string, and the command parameter is the text of the query command to send to the ODBC data source. Typically this would be a SQL statement.

This function returns an untyped list, which you can loop through and then reference each entry as a record. Typically you can use this to look up data in scripts, and even have it populate a list in your WorkflowFirst application on-demand by using it in an Access Script.

var #result = data:RunODBC("Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;", "select  *  from myTable")
loop through #result as #record
    log #record/Column1
    log #record/Column2
end loop

JSON Web Services

If you need to connect to external web services, you can use the LoadUrl(url, timeout) or PostToUrl(url, nameValuePairs, timeout) functions. Both of these return text strings. You can then convert that from JSON into a record you can access using the RecordFromJSON(json, null) or ListFromJSON(json, null) functions. If you assign that to a variable, you can access the JSON like you would a regular DbfScript record - eg. #rec/SomeField.

It's often a good idea to test this out in Admin Tools first, so you can log out the result.

OAuth Web Services

These days many web services require OAuth to access them. WorkflowFirst has built-in support for authenticating with OAuth 2.0 services, opening up access to many rich web services such as Facebook and Google.

To set this up in your own app, you will need to do a bit of preparation.

1. Firstly, under the Configuration tab of your application, go to the Web Services list and add an entry for your web service, with the relevant settings such as the API Key and the Client Secret. This list is only available to admins. Make sure that "For Login" is No (it should only be set to Yes if you want that service to be used for Single-Sign On).

2. To connect to the service from your app, you should add a global action (eg. called "Connect Calendar"), and at the beginning of the action you should put something like:

call WebServiceAuth("Calendar", #input)

...this will display the prompt to authorize access to the service, and once confirmed will redirect to a special page that will re-run the action.

The authorization should only occur once for each user. 

3. After that, you can access the web service by using something like:

var #p = WebServiceGet("Calendar", "https://www.googleapis.com/calendar/v3/users/me/calendarList")
loop through RecordFromJSON(#p, null)/items as #cal
    ...
end loop

This presumes a REST interface to access the data that requires the OAuth tokens, which will be supplied by WorkflowFirst automatically.