Chapter 7 - Data connections and web requests

First draft. This chapter has not been reviewed yet and may contain mistakes. Please read it with that in mind, and tell us about anything that does not match what you see.

On this page7.1 ChangeConnectionUrl 7.2 ChangeSubmitUrl 7.3 GetConnectionUrl 7.4 ExecuteSqlQuery 7.5 ModifySqlQuery 7.6 MakeRequest

These six commands work with the form's data connections and with web services that have no data connection at all: re-point a query or submit connection to another URL, read a connection's current URL, run your own SQL through an SQL connection, add a WHERE clause to an SQL query, and send an HTTP request. Chapter 1 explains the command syntax, dynamic parameter values and where results go; this chapter assumes you know it.

The examples use this main data source and these data connections:

my:myFields
  my:SiteUrl
  my:CustomerId
  my:Region
  my:ResponseText
  my:SqlParams
    my:SqlParam            (repeating)
      my:Name
      my:Value
      my:Type
  my:Headers
    my:Header              (repeating)
      my:Name
      my:Value

Data connections:
  Customers        SQL query
  Settings         XML file (in the template)
  OrderService     web service
  SubmitToLibrary  SharePoint library submit
  Projects         SharePoint list query

Timing. A connection that is re-pointed must not run before the change. Connections with Query during form load switched on run before any rule (see Chapter 1), so switch that off on the connections you re-point. Run ChangeConnectionUrl and ChangeSubmitUrl from QdabraRules > finishedLoading, the first rules to run when the form opens, and put them before any rule that queries or submits. The FormsDesigner User Manual, Chapter 7 describes this start-up order in detail, including the Dynamic Data Connections XTP that runs these commands for you.

7.1 ChangeConnectionUrl

Changes the URL of a data connection at run time. Use it when a template moves between sites (for example from a development site to production) and its queries must follow the site the form is opened on.

Syntax

ChangeConnectionUrl /dsname=<data connection> /url=<URL> [/listguid=<GUID> | /listname=<list name>]

Parameter	Required	Description
`/dsname`	Yes	Name of the data connection, exactly as shown in the designer.
`/url`	Yes	The new URL. For a SharePoint list connection, the URL of the site that holds the list.
`/listguid`	For SharePoint list connections, one of the two	GUID of the list.
`/listname`	For SharePoint list connections, one of the two	Name of the list.

Result: the new URL.

Examples

ChangeConnectionUrl /dsname=OrderService /url=https://services.contoso.com/orders/OrderService.asmx
ChangeConnectionUrl /dsname=Projects /url-xpath=/my:myFields/my:SiteUrl /listname=Projects

The first re-points a web service connection. The second points a SharePoint list query at the site stored in SiteUrl, using the list named Projects on that site.

Notes

Supported connection types: XML file, web service, SharePoint library submit, SharePoint list query and SharePoint list form connections. Any other type, for example an SQL connection, fails with ChangeConnectionUrl is not supported for this connection type.
For a SharePoint list connection you must give /listguid or /listname, or the command fails with You must provide listguid or listname parameter if changing the URL for a SharePoint List data connection.
The command description in the Command Builder says the command cannot be used for SharePoint list connections. It can: the behavior above is what the engine does.
The command only changes the URL; it does not run the query. Follow it with a Query for data action (or a qRules command that queries) when you need the data from the new location.
Any other failure while changing the URL is reported as An error occurred trying to change the connection URL: followed by the underlying message.
The site URL the form is opened from is available with GetInputParameter /key=SPHostUrl; store it in a field and pass it with /url-xpath.

Related: ChangeSubmitUrl, GetConnectionUrl.

7.2 ChangeSubmitUrl

Changes the folder or library that a SharePoint library submit connection submits to. Use it to send forms to a different library, or to the same library on the site the form runs on.

Syntax

ChangeSubmitUrl /submit=<submit connection> /url=<library or folder URL>

Parameter	Required	Description
`/submit`	Yes	Name of the SharePoint library submit connection.
`/url`	Yes	The library or folder URL to submit to.

Result: the new URL.

Example

ChangeSubmitUrl /submit=SubmitToLibrary /url=https://contoso.sharepoint.com/sites/finance/ExpenseReports

Later submits through SubmitToLibrary save the form in the ExpenseReports library of the finance site.

Notes

Only SharePoint library submit connections are accepted. Any other connection fails with Data connection must be a SharePoint library submit connection.
/url is required. The command description in the Command Builder says that without /url the library the form was opened from is used; the engine does not do this, and a missing /url fails with Required parameter unspecified: url.
ChangeConnectionUrl also accepts a SharePoint library submit connection and does the same thing.

Related: ChangeConnectionUrl, GetConnectionUrl.

7.3 GetConnectionUrl

Returns the URL a data connection currently uses. Use it to check what a connection points to, for example while testing a ChangeConnectionUrl rule, or to build another URL from it.

Syntax

GetConnectionUrl /dsname=<data connection>

Parameter	Required	Description
`/dsname`	Yes	Name of the data connection.

Result: the connection's current URL. Success is false if the connection type has no URL.

Example

GetConnectionUrl /dsname=OrderService /resultxpath=/my:myFields/my:SiteUrl

Writes the web service's current URL into SiteUrl.

Notes

For a connection to a file stored inside the template (for example an XML file added as a resource), the template prefix x-soln:/// is removed, so the Result is just the file name, such as Settings.xml.
A connection type without a URL fails with GetConnectionUrl is not supported for this connection type.

Related: ChangeConnectionUrl, ChangeSubmitUrl.

7.4 ExecuteSqlQuery

Runs an SQL statement of your own through an existing SQL data connection, optionally with typed parameters taken from the form. Use it to call a stored procedure, or to run a query that the connection's own command cannot express.

Syntax

ExecuteSqlQuery /dsname=<SQL connection> /sql=<SQL> [/detectname=true] [/parametersxpath=<XPath> /parametersnamepath=<relative XPath> [/parametersvaluepath=<relative XPath>] [/parameterstypepath=<relative XPath>] [/parametersallownullpath=<relative XPath>] [/parametersdsname=<data source>]]

Parameter	Required	Description
`/dsname`	Yes	Name of the SQL data connection to use.
`/sql`	Yes	The SQL to run, for example `EXEC Sales.dbo.GetCustomer @customerId = 12345`.
`/detectname`	No	`true` uses the name of the queried table as the element name of the result rows. Default `false`: the data source's usual row names are used.
`/parametersxpath`	No	XPath to the nodes (usually rows) that describe the query parameters, one node per parameter.
`/parametersdsname`	No	Data source of `/parametersxpath`. Omit for the main data source.
`/parametersnamepath`	With `/parametersxpath`	Path, relative to each parameter node, to the parameter name.
`/parametersvaluepath`	No	Path, relative to each parameter node, to the value. Default `.` (the parameter node itself).
`/parameterstypepath`	No	Path, relative to each parameter node, to the data type: `boolean`, `datetime`, `decimal`, `integer`, `string` or `innerxml` (any case). Default `string` when the path is not given or the type field is empty.
`/parametersallownullpath`	No	Path, relative to each parameter node, to a field that says whether the parameter may be null. Default `false` when the path is not given, the field is missing or empty.

Result: empty on success. The returned rows go into the data source of the SQL connection, as with a normal query.

Examples

ExecuteSqlQuery /dsname=Customers /sql=SELECT * FROM dbo.Customers WHERE Region = 'West'
ExecuteSqlQuery /dsname=Customers /sql=EXEC dbo.GetCustomer @CustomerId /parametersxpath=/my:myFields/my:SqlParams/my:SqlParam /parametersnamepath=my:Name /parametersvaluepath=my:Value /parameterstypepath=my:Type

The first runs a fixed query. The second calls a stored procedure with one parameter per SqlParam row; with a row of Name @CustomerId, Value from the form and Type integer, the value is passed as a typed parameter instead of being pasted into the SQL text.

Notes

Prefer parameters over building the SQL with concat(): a parameter value is never interpreted as SQL, so quotes in user input cannot break or change the statement.
The parameter name is passed exactly as it is stored in the name field.
With type innerxml, the value is the XML content of the value node (its child elements, serialized as text), not its text value.
The SQL replaces the connection's command only for this run; the connection's own command is restored afterwards, whether the query succeeded or not.
The connection must be an SQL connection, or the command fails with ExecuteSqlQuery must be used with an SQL data connection.
Parameter errors: /parametersnamepath is required if /parametersxpath is specified., Failed to select any parameters at followed by the XPath, Could not find query parameter name at XPath followed by the path (also when the name field is empty), Could not find query parameter value at XPath followed by the path, and Incorrect data type value specified for /parameterstypepath: followed by the value and the list of supported types.
A database or connection error is reported as ExecuteSqlQuery error: followed by the error.

Related: ModifySqlQuery, CopyTable.

7.5 ModifySqlQuery

Appends text, usually a WHERE clause, to an SQL connection's existing query and runs it. Use it to filter an SQL query by values in the form without writing the whole statement.

Syntax

ModifySqlQuery /dsname=<SQL connection> /where=<SQL text> [/detectname=true]

Parameter	Required	Description
`/dsname`	Yes	Name of the SQL data connection.
`/where`	Yes	Text appended to the connection's query, including the keyword, for example `WHERE Sales >= 12345`.
`/detectname`	No	`true` uses the name of the queried table as the element name of the result rows. Default `false`.

Result: empty on success. The returned rows go into the data source of the SQL connection.

Examples

ModifySqlQuery /dsname=Customers /where=WHERE Region = 'West'
ModifySqlQuery /dsname=Customers /where-xpath=/my:myFields/my:Region

The first filters the Customers query to the West region. In the second, the Region field holds the complete clause (for example built with a Set a field value and concat()), passed in as it is.

Notes

/where is added to the end of the connection's query, separated by one space. Write the keyword (WHERE, or AND if the query already has a WHERE clause) yourself. If the connection's query already ends with ORDER BY or similar, the appended clause makes the SQL invalid.
The appended text is SQL. A value typed by the user and inserted into it can break the query or change its meaning; for user input, use ExecuteSqlQuery with parameters.
The connection's original query is restored after the run, so each call starts from the designer's query, not from the previous call's.
Errors: ModifySQLQuery is only for use with SQL data connections when the connection is not an SQL connection, and ModifySqlQuery error: followed by the error when the query fails.

Related: ExecuteSqlQuery.

7.6 MakeRequest

Sends an HTTP request (GET, POST, PUT or DELETE) and returns the response body. Use it to call a REST API or any URL that has no data connection in the template.

Syntax

MakeRequest /path=<URL> [/method=GET|POST|PUT|DELETE] [/content=<text>] [/contenttype=<MIME type>] [/username=<user>] [/password=<password>] [/headerspath=<XPath> /headernamepath=<relative XPath> /headervaluepath=<relative XPath> [/headersdsname=<data source>]] [/timeoutms=<n>]

Parameter	Required	Description
`/path`	Yes	The URL to send the request to.
`/method`	No	`GET`, `POST`, `PUT` or `DELETE`. Default `POST` when `/content` is given, `GET` when it is not.
`/content`	No	The request body.
`/contenttype`	No	Content type of the body. Default `text/plain`.
`/username`	No	User name for authentication.
`/password`	No	Password for authentication.
`/headerspath`	No	XPath to repeating rows that each hold one request header.
`/headernamepath`	With `/headerspath`	Path, relative to each header row, to the header name.
`/headervaluepath`	With `/headerspath`	Path, relative to each header row, to the header value.
`/headersdsname`	No	Data source of the header rows. Omit for the main data source.
`/timeoutms`	No	Time limit in milliseconds; the request is stopped if it takes longer.

Result: the response body. If the request fails, Success is false, Error holds the failure message and Result still holds the response body, if any, so you can read an error response from the service.

Examples

MakeRequest /path=https://api.contoso.com/customers/1001 /resultxpath=/my:myFields/my:ResponseText
MakeRequest /path=https://api.contoso.com/orders /content-xpath=/my:myFields/my:ResponseText /contenttype=application/json /headerspath=/my:myFields/my:Headers/my:Header /headernamepath=my:Name /headervaluepath=my:Value /timeoutms=30000

The first reads a customer record with GET and stores the response in ResponseText. The second posts the JSON text in ResponseText (POST because /content is given), with one header per Header row, and gives up after 30 seconds.

Notes

/content decides the default method even when it is empty: /content= alone makes the request a POST. Give /method explicitly when it matters.
/contenttype is the type of the body you send, not the type you expect back.
Header rows whose name or value is empty are skipped without an error.
Header errors: /headernamepath must be specified if headerspath is specified., /headervaluepath must be specified if headerspath is specified., The data source specified for /headersdsname could not be found., and Error: No nodes were found using the specified XPath. when /headerspath selects no rows.
An empty or non-numeric /timeoutms is ignored: the request is sent without a time limit of its own.
A JSON response comes back as text. To work with it as XML, convert it with JsonToXml.
Do not write passwords, API keys or tokens into the command text, where anyone who opens the template can read them. Take them from a field or a secured source at run time with /password-xpath or a header row.

Related: ExecuteSqlQuery, GetConnectionUrl.

Back to the Command Index.

← Form, view and UISharePoint →