Microsoft Azure SQL
integrates with Microsoft Azure SQL through the SQL data provider, allowing you to select or insert data from an Azure SQL database. This includes Microsoft Azure SQL and Microsoft SQL Server on-premise.
To use the SQL data provider in , follow these high-level steps:
- Choose your Azure SQL database
- Identify the SQL table you will use as your data source. Ensure you understand its structure and data.
- Configure the SQL connection
- Configure a new Azure SQL connection for the solution in before adding the Jigx cloud IP addresses to the allowlist IP addresses in Azure SQL.
- Define the SQL query or stored procedure in a function in
- Enter the name of the connection set up in .
- Define data methods in the function:
- Configure a method to use to interact with the data. There are two options:
- EXECUTE - used with stored procedures
- QUERY - used to write SQL queries
- For each method, create a new function file.
- Reference the Jigx functions in s:
- Reference the function in your s. This step is crucial for integrating the SQL data seamlessly into your solution.
- Publish your solution:
- Test the Data Provider:
- Use developer tools to test the data provider configurations. Check if the data provider can connect to SQL successfully and perform operations like SELECT, INSERT, UPDATE or DELETE data.
Following these steps, you can effectively integrate external Azure SQL data into your Jigx solutions, allowing you to enhance your apps with data and functionalities from diverse external sources.
For security, all SQL calls from a are routed through the cloud to the Microsoft Azure SQL instance. No data is stored in the cloud and is only used as a routing proxy for IP allowlisting in Microsoft Azure SQL. The Configuring the SQL Connection section explains the steps to configure a connection for the Azure SQL database in cloud, as well as configuring the IP allowlisting in Azure SQL for cloud.
Data from remote data sources such as Azure SQL or REST web services are stored in a local SQLite database on the device from where it is used in the application.
To fetch data onto the device and into the local SQLite table, executes a function that sends an SQL command to Azure SQL. This command can include an SQL statement that will be executed or a stored procedure.
The function's result is returned to the app on the device as a JSON array. Each record in the array is stored as a row in the local SQLite database.
The solution uses SQL as a query language to access and manipulate data in the local SQLite database.
Once functions are published in a solution, you can preview the function in under the solution's SQL functions option. See Viewing and testing SQL data using the Jigx Management for more information.
| |
---|---|
Provider | DATA_PROVIDER_SQL for making calls to Microsoft Azure SQL. |
Connection | Provide the name of the connection configured in Jigx Management for the Azure SQL database. |
Methods | supports the following methods in the provider:
|
The following describes the options available when configuring a SQL function call.
Function parameters are used to pass data into the function definition from the that uses the function. See the Datasources section of the documentation. Parameters are defined by naming them and describing the properties of that parameter. Parameter names are used when the parameters are referred to in the SQL query.
The location determines how the parameter will be applied in the SQL call:
- input: the parameter is used as an input, for example when creating a record.
- output: the parameter is used as an output.
- both: the parameter is used as an input and output.
Type is specific to the SQL call being made. Most types are defined as strings.
The required value is either true or false. This determines whether the parameter needs to be set when the function is used in a 's datasource. This determines if the SQL call requires this parameter. If so, set this property to true, alternatively if the parameter is optional, you can set it to false.
By default the return SQL call replaces previous data in the SQLite database. The forRowsWithValues property allows you to update specific values in the SQLite database instead of replacing all rows, providing a better user experience. The forRowsWithValues property specifies a key-value pair where the key is a json_extract() column in the SQLite table that will be matched by the value. Only rows that match these criteria will be updated. The object will be added as a new row to the collection if a match isn't found. You can have multiple key-value pairs specified under forRowsWithValues. Think of this as a WHERE clause that uses when it adds the result of the SQL call to the SQLite table.
Similar to forRowsWithValue but instead of matching rows by value the forRowsInRange specifies a key-value pair where the key is a json_extract() column in the table that a value range will match. Only rows that match these criteria will be updated. The object will be added as a new row to the collection if a match isn't found. You can have multiple key-value pairs specified under forRowsInRange. Think of this as a WHERE clause with a BETWEEN that uses when it adds the result of the SQL call to the table.
Similar to forRowsWithValue, when forRowsWithMatchingIds is specified, will perform an upsert on a specific id. The outputTransform MUST contain a field called id. This id will be used to match the id column in the database; if a record with this id exists, it will be updated. If no match is found, the record will be inserted. No deletion is performed when forRowsWithMatchingIds is used.
Jigx stores files as local files on the device and only saves the file URI to the file in the datastore/state. When a component needs the binary data, it can read the local file from the file URI. To enable the handling of files, you can convert files from base64, data-uri, or buffer to local-uri. See File handling for more information.
Here is an example of a Jigx solution screen that calls the function in the OnFocus event with a sync-entities action to sync the data from SQL to the local SQLite database, which returns the customers' details.
We recommend navigating to the Management Console to test your function at this point. This allows you to ensure that the function is configured correctly, connected to SQL Server, and returns results. You can find out more about capabilities for viewing and testing SQL functions from the Management Console at this location. Viewing and testing SQL data using the Jigx Management Console
The following examples with code snippets are provided:
- A SQL database script to create the tables and stored procedures used in the example. These scripts should be executed against an existing database in your Azure SQL environment.

