Microsoft Azure SQL

best practice for production apps is to use rest as the data layer to access data and not directly integrate to sql using the sql data provider the sql data provider will be squiggled in blue to indicate it is not recommended, together with a message to use rest docid\ jrbansm ojn3nf4 dn hu instead see rest endpoints from azure sql docid\ eoui2cpyynsdruk tobdp for more information {{jigx}} 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 use the sql data provider to use the sql data provider in {{jigx}} , 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 configuring the sql connection docid\ o8lfnt4lwq1fvfvg1lelh for the solution in {{jigxmanagement}} before adding the jigx cloud ip addresses to the allowlist ip addresses in azure sql define the sql query or stored procedure in a {{jigx}} function in {{jigxbuilder}} navigate to the rest docid\ jrbansm ojn3nf4 dn hu folder in {{jigxbuilder}} use editor docid 5zrvshwiixwud2fbzsybv to configure the sql data provider enter the name of the connection set up in {{jigxmanagement}} 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 {{jig}} s reference the function in your {{jig}} s this step is crucial for integrating the sql data seamlessly into your {{jigx}} solution publish your solution publishing a solution docid\ ajp2syjvipjjdts01hu1t and use the app to interact with the sql data provider test the data provider use {{jigxbuilder}} debugging docid\ psqdlve46kfecb4fzb6tb 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 connections for security, all sql calls from a {{jigxapp}} are routed through the {{jigx}} cloud to the microsoft azure sql instance no data is stored in the {{jigx}} cloud and is only used as a routing proxy for ip allowlisting in microsoft azure sql the configuring the sql connection docid\ o8lfnt4lwq1fvfvg1lelh section explains the steps to configure a connection for the azure sql database in {{jigx}} cloud, as well as configuring the ip allowlisting in azure sql for {{jigx}} cloud {{jigx}} functions 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 {{jigx}} application to fetch data onto the device and into the local sqlite table, {{jigx}} 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 {{jigx}} app on the device as a json array each record in the array is stored as a row in the local sqlite database the {{jigx}} solution uses sql as a query language to access and manipulate data in the local sqlite database once functions are published in a {{jigx}} solution, you can preview the function in {{jigxmanagement}} under the solution's sql functions option see sql functions docid\ ztgsd1mwywsz ajhddyk0 for more information sql function components provider data provider sql for making calls to microsoft azure sql connection provide the name of the connection configured in configuring the sql connection docid\ o8lfnt4lwq1fvfvg1lelh for the azure sql database methods {{jigx}} supports the following methods in the provider execute used to execute a stored procedure query used to write a sql statement such as select, insert, delete, and update the following describes the options available when configuring a sql function call sql function query # jigx sql function executing a query to select all customers from a table provider data provider sql connection customer azure # use manage jigx com to configure a sql connection method query # use sql statements to interact with the data in sql query | select id, first name, last name, email, phone number, address line1, address line2, city, state, zip code, country from customers sql function execute # jigx sql function executing a stored procedure to select all customers from a table provider data provider sql connection customer azure #use manage jigx com to configure a sql connection method execute #provide the sql stored procedure to execute procedure sp getallcustomers parameters function parameters are used to pass data into the function definition from the {{jig}} that uses the function see the datasources docid\ ddg nus2 7g lb9idn4pe 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 function parameter properties location 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 type is specific to the sql call being made most types are defined as strings required the required value is either true or false this determines whether the parameter needs to be set when the function is used in a {{jig}} '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 forrowswithvalues 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 {{jigx}} uses when it adds the result of the sql call to the sqlite table forrowsinrange 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 {{jigx}} uses when it adds the result of the sql call to the table forrowswithmatchingids similar to forrowswithvalue , when forrowswithmatchingids is specified, {{jigx}} 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 conversions 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 docid\ ugaq4ae3icfrecrwfif01 for more information provider data provider sql method query connection customer azure query select top(@top) id, firstname, lastname, avatarbase64, avatardatauri, avatarbuffer from employee parameters top location input required false type number value 10 conversions \ property avatarbuffer from buffer to local uri \ property avatarbase64 from base64 to local uri \ property avatardatauri from data uri to local uri referencing a {{jigx}} function 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 list customers jigx # a sample list jig that uses a sql function to return and display a list of customers from azure sql title list customers description show a list of all customers in a sql database type jig list icon contact \# header section displaying an image at the top of the screen header type component jig header options height medium children type component image options source uri https //images unsplash com/photo 1553413077 190dd305871c?ixlib=rb 4 0 3\&ixid=mnwxmja3fdb8mhxwag90by1wywdlfhx8fgvufdb8fhx8\&auto=format\&fit=crop\&w=1035\&q=80 \# onfocus is triggered whenever the jig is displayed the sync entities action calls the jigx sql function and populates the local sqlite tables on the device with the data returned from azure sql onfocus type action sync entities options provider data provider sql entities \ entity customers function get customers \# the mydata data source selects the data from the local sqlite database datasources mydata type datasource sqlite options provider data provider local entities \ entity customers query | select id, '$ first name', '$ last name', '$ email', '$ phone number', '$ address line1', '$ address line2', '$ city', '$ state', '$ zip code', '$ country' from \[customers] \# the list and its list items are configured below this is a list jig; therefore, its properties, such as data and item, are top level properties \# the data property binds the list to a specific data source data =@ctx datasources mydata \# the item property specifies the list item type and its attributes item type component list item options title =@ctx current item first name & ' ' & @ctx current item last name subtitle =@ctx current item email description | \=@ctx current item address line1 & ' ' & @ctx current item city & ' ' & @ctx current item state & ' ' & @ctx current item zip code label title =@ctx current item country leftelement element avatar \# the text property of the left element is specified using a jsonata expression that builds a two letter string by concatenating the first letters of the customer's first and last names text =$substring(@ctx current item first name,0,1) & $substring(@ctx current item last name,0,1) divider solid 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 examples and code snippets the following examples with code snippets are provided a database scripts docid\ jq3eq0tn7t5yl8vng 7qh 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 list customers (select) docid\ nvjbmsjrmqna u9ubja7x list a single customer (select) docid 3dnsgxfk2dfatknpumaj1 create a customer (insert) docid\ g9nhushuxs4egskv jie4 update a customer (update) docid\ bm53kzrt5qo9lx4rzxqd6 see also file handling docid\ ugaq4ae3icfrecrwfif01 offline remote data handling docid\ h36q0e3t93lcbn9wejmjp