Introduction
TracerPlus now supports the ability to run SQL queries on its internal database. This is a powerful feature that can seem difficult to use if you’re not familiar with SQL syntax or the names TracerPlus uses for its database objects. If that’s the case, this quick primer should get you up and running in no time. If you are familiar with SQL and just need the names of TracerPlus database objects, skip ahead to the TracerPlus database object identifiers and tokens section.
Structured Query Language or SQL for short (pronounced “sequel” colloquially) is a language standard commonly used to access and modify databases and their records. A database engine will accept SQL queries — instructions on what to do — as input, and return an appropriate response depending on the query.
In TracerPlus for Windows Mobile, the SQL engine used is called "SQL CE". For Android, iOS, and TracerPlus PC, the SQL engine used is "SQL Lite". Generally, this does not matter but there are some edge cases where the syntax of your specific SQL Command will differ slightly based on this engine.
Also, with any version of SQL, there are varying levels of complexity supported. For both SQL CE and SQL Lite, the idea of sub-queries and/or data type casting is both generally supported. For example, typecasting can be important if you want to convert a TracerPlus value from a string to an integer. for example.
Technical Support for SQL in TracerPlus
While we can generally offer functional support for custom SQL in TracerPlus, it is generally limited to the higher-level functioning of the SQL command calls and their expected return to TracerPlus. Unfortunately, we are not able to offer general support for complex SQL instructions without an additional services contract or estimate. It is advised to contact your PTS sales representative for any additional options should you wish to utilize our professional services group to help with an especially complex task.
The good news is that for most general SQL questions, the best advice (and result) is to use an internet search to find the specific SQL command to accomplish a specific task for your given SQL Platform (SQL CE or SQL Lite).
Using SQL in TracerPlus
In TracerPlus, you can write your own SQL queries and use them in two ways: assign a field as a SQL variable field, or create a logic item that will run a SQL query given a set of conditions is fulfilled.
Variable
To create a SQL variable field, change the field’s type to Variable. In the new Variable Options tab that appears, you will be able to select the variable’s type; select SQL from the dropdown. Then you’ll be able to choose a Trigger Field; when an afterscan action takes place on the trigger field, the SQL query we define will be run. Finally, notice the Custom SQL text box; here we write our SQL query.
Logic
To create a logic item that performs a SQL query, add a new logic item, and define the following:
1) Event: what needs to happen for this logic item to check its condition(s)
2) Event Control (By ID): which form control TracerPlus should monitor for the Event to take place
3) If/Conditions: define any conditions that need to be met for this logic action to place. This can be left as [undefined] if you want your SQL query to run unconditionally every time the Event happens on the Event Control
4) Action: we want to run a SQL query, so choose ExecuteSQL
5) Action Controls (by ID): which controls to affect with our Action. In the case of ExecuteSQL, this is the control that will display the return value from the SQL query. You can leave this as -1 (further discussed in Closing considerations below)
6) Action Value: define your SQL query here. This query will be run when the logic action takes place.
The Perform Else and Else Value columns are not used in an ExecuteSQL action and are left as n/a. Now that we know the two methods of running SQL queries, let’s discuss how to write a query.
SQL commands
SQL queries (can also be known as statements) mostly comprise of a SQL command, the database object(s) to affect, and the data to insert. There is a wealth of SQL commands to learn and use, but for this tutorial, we will be looking at the most common applications of SQL in TracerPlus:
- SELECT: retrieve a set of data (like a TracerPlus lookup)
- UPDATE: update a set of data (like the update source option for a lookup)
- INSERT: append new data into the specified data set (like a TracerPlus submit)
- DELETE: remove a set of data
SQL sample
Let’s jump right into it. Here is a sample SQL statement:
SELECT Field2 FROM Session2 WHERE Field1=‘[*0*]’;
Let’s go over what that statement is doing.
- The SELECT command is telling the database engine to retrieve data
- Field2 is a unique identifier for a TracerPlus database object. A field is a column: so, TracerPlus will return data from column 2.
- The FROM command tells the SELECT command where to look for the data.
- Session2 is a unique identifier for a TracerPlus database object. A session is a table: so, TracerPlus will return data from table 2.
- The WHERE command tells the SELECT command to filter results to match our filter (defined right after the WHERE command). WHERE commands affect the queried database object (Session2 in this case).
- Field1=‘[*0*]’ is known as a where clause: we are defining our filter so that any data returned must match that filter. Here, we only want data where the data in Field1 is equal to ‘[*0*]’. The last item is a token.
- ‘[*0*]’ is a unique TracerPlus token that replaces the characters between and including the brackets with the data from the specified field on the current form. These tokens are 0-based, meaning counting starts at 0. 0 refers to field 1, 1 refers to field 2, etc, etc. Finally, notice the single quotes. Without these, the database engine would try to parse the actual data as an identifier, and you would see weird things happen. Don’t forget the single quotes.
- The semi-colon; delineates the end of our SQL statement. Some SQL databases require a semi-colon after every statement, some do not. It doesn’t hurt to put it there so we ourselves know that is the end of the statement.
It might seem like a lot but it’s not! All the above amounts to is the following: “return the data in field 2 from session 2 where field 1 (in session 2) is equal to the data in field 1 of my current form.” We’ve just done a lookup! Now that we understand what a SQL query looks like and how it works, let’s get to understand the unique names for TracerPlus database objects.
TracerPlus database object identifiers and tokens
TracerPlus uses the following identifiers for its database objects.
- Session{0}, where {0} determines the session to query. This identifier is case sensitive: you must write "Session" exactly as you see here. Session numbers are 1-based, so counting starts at 1, e.g., Session1 is session 1, Session2 is session 2, etc, etc.
- Field{0}, where {0} determines the field to query. This identifier is case sensitive: you must write "Field" exactly as you see here. This identifier is case sensitive: you must write "Field" exactly as you see here. Field numbers are 1-based, so counting starts at 1, e.g., Field1 is field 1, Field2 is field 2, etc, etc.
- [*{0}*], the [* and *] characters are special parsing characters that let TracerPlus know to grab the data from the field identified by {0}. The {0} identifier is 0-based, meaning 0 will return field 1, 1 will return field 2, etc, etc.
SQL query examples
In this section we’ll take a look at a few more SQL statements and what they mean in the context of TracerPlus.
- UPDATE Session3 SET Field2=‘[*1*]’,Field3=‘[*2*]’ WHERE Field1=‘ATP’
- What this means: update fields 2 and 3 in session 3 with the data in fields 2 and 3, respectively, where the data in field 1 in the current form is equal to ‘ATP’. If you neglect to put a WHERE clause at the end your entire table will be updated!
- INSERT INTO Session4(Field1,Field2,Field3) VALUES(‘A’,‘B’,‘C’)
- What this means: add a new record into session 4 with the values ‘A’, ‘B’, and ‘C’ going to fields 1, 2, and 3 respectively. Notice that we are identifying the columns we want to add to, and then we also define the same number of items to add. If you don’t define which columns to add to, or it doesn’t match with the data you are inserting, you will experience errors.
- DELETE FROM Session1
- Here we see a new character: the wildcard (also known as “star” or “asterisk”). This means match anything to the pattern the wildcard is attached to; since the wildcard is alone, it will match everything. What this means: “delete everything from the session 1 table.” Be careful with this command as it cannot be undone.
Closing considerations
Now that we’ve seen how to use SQL in TracerPlus, you are ready to go. With SQL in TracerPlus, the power is in your hands; but with great power comes great responsibility! Keep these following pointers in mind:
- This tutorial used uppercase for all the SQL commands. SQL is actually case insensitive so you can use select just as well as SELECT. Using all caps makes it easier to see what are SQL commands and what are your identifiers for your database objects.
- With a SQL command that retrieves data, such as SELECT, the action ID field for a TracerPlus logic item decides which control will display the returned data values. With a SQL command that modifies data, such as UPDATE, the action ID control will display the number of rows affected.
- For further learning, the following website is a good place to start: http://www.w3schools.com/sql/default.asp
- Keep in mind TracerPlus uses SQLCE 3.5. If your SQL query doesn’t work, check to make sure it is valid in SQLCE 3.5. Some Android projects might need modification to their SQL queries because TracerPlus Android uses SQL Lite (this is rare).
Comments
0 comments
Article is closed for comments.