Projects and Research by the Catalina Technology Team
Month: March 2019
Minimum:
db_datawriter
db_datareader
db_ddladmin
If you are going to use our userMaintenance web service, to manage SL user logins, it will also need:
db_securityadmin
db_accessadmin
Also note that by default, we normally install our scripts under the dbo schema. So, you also have to give the user, we are connecting as, execute on that schema.
eg. grant execute on schema :: [dbo] to usernamehere
There have been quite a few requests for the code for my MQTT posting IOT project for testing PH and Temperature of my hydroponic towers. This code is several years old and I haven’t looked at it for at least 2. But, I figured I would put it out on GitHub.
The main purpose of this code is to monitor the PH and temperature in a water tank for my hydroponics and then use MQTT to post to io.adafruit for a dashboard.
The examples below are for the RESTful API version of the Catalina API for SL. If you want to see how you can do similar things in SOAP, you can see an older post here: https://catalinatechnology.wordpress.com/2017/02/06/example-of-building-a-form-client-to-use-the-catalina-quick-query-soap-web-service/
You can page content by passing a pageSize and a currentPageNumber. The below example will retrieve page 5 with a page size of 10 items
{
"filters": [{
"name": "pageSize",
"value": "10",
"comparisonType": "="
},
{
"name": "currentPageNumber",
"value": "5",
"comparisonType": "="
}
]
}
What you will see with this is that in the table results, there will be several fields:
This would then allow you to page through the results and not bring everything down at once. Below is an example of those fields:
{
"counter": 41,
"Account Number": "12313 ",
"Account Type": "1A",
"Active": 1,
"Class ID": "ASSETS ",
"Validate ID": " ",
"totalEntries": 338,
"totalPages": 34,
"errorMessage": null
},
You can also limit records by adding additional filters that you can limit by fields. Here is an example of bringing back all records with a Class ID = “ASSETS”
{
"filters":[
{
"name": "Class ID",
"value":"ASSETS",
"comparisonType":"="
}
]
}
If you wanted to bring back page 4, with a page size of 10, of items that have a Class ID = ‘ASSETS” and are flagged active, you would do this:
{
"filters": [{
"name": "pageSize",
"value": "10",
"comparisonType": "="
},
{
"name": "currentPageNumber",
"value": "4",
"comparisonType": "="
},
{
"name": "Class ID",
"value": "ASSETS",
"comparisonType": "="
},
{
"name": "Active",
"value": "1",
"comparisonType": "="
}
]
}
Some notes about comparisonType. These are the possible values (NOTE: You can add as many comparisons as you want in your filters to get the results you need)
| Type | Description |
| = | Equals Example, if you wanted to retrieve all items with an Account Type equal to 1A, in QQ_Account, you would do the following: { “filters”: [{ “name”: “Account Type”, “value”: “1A”, “comparisonType”: “=” }] } |
| < | Less than Example, if you wanted to retrieve all items with a created date less than 1/2/2000, in QQ_Account, you would do the following: { “filters”: [{ “name”: “Account Type”, “value”: “1/2/2000”, “comparisonType”: “<“ }] } |
| > | Greater than Example, if you wanted to retrieve all items with a created date greater than 1/2/2000, in QQ_Account, you would do the following: { “filters”: [{ “name”: “Account Type”, “value”: “1/2/2000”, “comparisonType”: “>” }] } |
| >= | Greater than or equal Example, if you wanted to retrieve all items that were updated after or equal to 3/24/200, in QQ_Account, you would do the following: { “filters”: [{ “name”: “Last Update Date”, “value”: “3/24/2000”, “comparisonType”: “>=” }] } |
| <= | Less than or equal Example, if you wanted to retrieve all items that were updated before or equal to 3/23/200, in QQ_Account, you would do the following: { “filters”: [{ “name”: “Last Update Date”, “value”: “3/23/2000”, “comparisonType”: “<=” }] } |
| != | Not Equal Example, if you don’t want active accounts returned from QQ_Account, you would do the following: { “filters”: [{ “name”: “Active”, “value”: “1”, “comparisonType”: “!=” }] } |
| LIKE | Like (or contains) – Uses % as a wildcard Example: if you want to retrieve all account numbers that start with 103, from QQ_Account, you would do the following: { “filters”: [{ “name”: “Account Number”, “value”: “103%”, “comparisonType”: “LIKE” }] } |
| IN | Inside a comma delimited list Example: if you want to retrieve just account 1030 and 1031 from QQ_Account, you would do the following: { “filters”: [{ “name”: “Account Number”, “value”: “1030,1031”, “comparisonType”: “IN” }] } |
| NOT IN | Not in a comma delimited list Example: if you want to exclude accounts 1110 and 1115 from QQ_Account, you would do the following: { “filters”: [{ “name”: “Account Number”, “value”: “1110,1115”, “comparisonType”: “NOT IN” }] } |
More information can be found about the RESTful API version of Catalina’s Quick Query API can be found here: https://catalinatechnology.wordpress.com/2017/03/02/dynamics-sl-quick-query-through-catalinas-restful-api/
If you want to see more information about Quick Query (particularly about how you can do it in SOAP), you can view that information here: https://catalinatechnology.wordpress.com/2017/02/06/example-of-building-a-form-client-to-use-the-catalina-quick-query-soap-web-service/
