Using data from SQL Server

AppSheet can build apps from Microsoft SQL Server databases that are hosted in Amazon AWS, Microsoft Azure, or another cloud-hosting provider.

To do so, add a SQL Server data source using the Account>Data Sources pane.

In order to access SQL Server data, the SQL Server instance must be hosted in the cloud (in Amazon AWS, Microsoft Azure, or another cloud-hosting provider). 

You must also make sure that your SQL Server accepts traffic from the IP address 104.40.67.206.  That is the IP that AppSheet will be connecting from, so your firewall must allow connections from that address. In the case of a database running on AWS, please ensure that Network Security option 'Publicly Accessible' is set to Yes and add a Security Group rule for the server that allows the IP address 104.40.67.206/32

To add a SQL Server data source, you will be asked to provide a server name, database name, username and password. The system stores these values securely, and concatenates them to construct a connection string to the database.

  • SQLServer has a default port of 1433. If instead a non-default port number is being used, that should be provided as part of the server name. So for example, if it is running on port 2000, use "mycomputer.test.xxx.com,2000" as the server name.
  • If a non-default instance name is being used for the SQL Server, provide that as part of the server name. So for example, if the instance is called "myInstance", use "mycomputer.test.xxx.com\myInstance" as the server name.

Once a SQL Server data source has been added to your account, you can add SQL Server tables or views to any app. When you choose "Add Table" in your app, you can select the specific data source, and a table or view from that data source.

Once added to the app, AppSheet treats all data sources similarly. In fact, it is common and natural to combine data from a SQL Server data source with data from other sources in the same app. 

 

It is common for a database table to use identity columns:

It is common for a database table to use an identity column as a key. The values of the identity column are auto-incrementing numbers that get automatically inserted by the database. When you use such a table with AppSheet, there can be a problem. By default, SQL does not let an application define the identity column value. However, with AppSheet, new rows are created in the app when it is potentially offline and may only be synced later. So the app needs to be able to assign key/identity values. This problem is not new and every app framework that enables offline access data entry has to deal with it.
 
The best solution is to avoid IDENTITY column keys altogether in your database schema. Instead, use a column that is an nvarchar(8) (or in general an nvarchar of length greater than or equal to 8). In AppSheet's column definition for this key, give it an InitialValue of UNIQUEID(). Now unique key values can be assigned by the app and inserted into the backend database.
 
If the database must use IDENTITY columns, it is preferable to create them with a large initial seed. For example, IDENTITY(100000, 1). In AppSheet's column definition for this key, give it an InitialValue of RANDBETWEEN(10000, 99999). Now any records created from your app will have five digit values that lie randomly in the range 10000 to 99999, while records created directly against the database will have higher values. Please note that when your app is being synced, AppSheet will turn on the IDENTITY_INSERT property for the table, insert a new row and then turn it off again. There are restrictions on how many of these operations can occur concurrently, so if this is a high-traffic app, errors could occur. In these situations, we again advise that an nvarchar(8) column should be used as the key instead.
 
If the database schema cannot be changed and if there is already an IDENTITY column being used with the default initial seed (of 1), we recommend that you follow the same approach as described earlier. However, you can first manaully increment the identity seed as follows:
 
SET IDENTITY_INSERT <tablename> ON; INSERT INTO <tablename>(col1Name, col2Name ...) VALUES(100000, ....); SET IDENTITY_INSERT <tablename> OFF; Delete the row just inserted

This should "re-seed" the identity column to the desired range. Your AppSheet app will insert values in the range specified by the RANDBETWEEN() function in the InitialValue of your AppSheet column definition. 

Working with datetime columns:

There are 2 main data types to store date time values in SQLServer: "datetime" and "datetime2". The type "datetime" only accepts date time values that lie between 01/01/1753 00:00:00 and 12/31/9999 23:59:59. On the other hand, the type "datetime2" allows a much larger range from 01/01/0001 00:00:00 to 12/31/9999 23:59:59. In general, it is recommended that you use "datetime2" instead of "datetime" in your database, especially if you expect your app to work with dates before 01/01/1753. 

Another common pitfall to look out for while using date time in SQLServer is how date time inputs are typed into the app. Some browsers, such as Firefox, do not provider a date time picker. Instead, the app user will have to manually type in date time values. This might lead to typos or formatting errors, which would cause SQLServer to reject the input date time values.

For instance, a common typo is to type "0217" instead of "2017". In this case, if "datetime" is used in the database, the year value will be rejected. If "datetime2" is used in the database, the value will be accepted even though it is incorrect.

Another common formatting error is to type in 2-digit year values, such as "04/17/17" instead of "04/17/2017". In this case, the server will interpret the year to be 0017 instead of 2017. If "datetime" is used in the database, the year value will be rejected. If "datetime2" is used in the database, the value will be accepted even though it is incorrect.

Saving files created in the app:

Database servers differ from the rest of our cloud-storage providers in that they do not have a file system. This leads to a change in AppSheet behavior when saving files (like images and documents). If the app has to capture photographs, they are normally stored in a folder next to the source of data in cloud storage. In the case of a SQL Server table though, image and document files are stored in the main cloud file system associated with the app creator's primary AppSheet account (typically Google Drive/Dropbox/Office 365/Box)

Have more questions? Submit a request

0 Comments

Article is closed for comments.