How do I create select queries to retrieve database information?

Top   « Previous  Next »

This section deals with setting up the queries to extract the data from the database for users and then deliver the result to users when they request it.

 

There are 3 parts:

 

1. Properties: Choose a data source, view and what type of query to use.

 

2. Query Builder: Build a query to extract the relevant data.

 

3. Message Builder: Decide how the message will be accessed by the end user and how it will be formatted when retrieved.

 

We will create a simple query to extract customer details based on entering the first letter of their name.

 

 

Properties tab

 

Enter a name and description for the first query to find customer details beginning with the first letter of their name, say 'Customer Names'.

 

Queries - Properties

 

We will use Select Query as this extracts data from the database. Update Query edits existing data and Insert Query adds new data.

 

Next, select the database and view we created. Click Save.

 

Ignore the timeout field for now, as it's only used when we setup for the user to reply to their first request for data if user replies have been implemented. If a user does not respond in a given time frame, the query expires.

 

Queries - Properties2

 

 

Query Builder tab

 

Next, move to the Query Builder Tab. In the Define Criteria window you can write your queries using the easy to understand Query Builder, or if you're comfortable with writing SQL, you can edit it directly below. The SQL Syntax created in the bottom window is always used to run the queries.

 

 

Query builder - overview

 

Click the link <Click here to add a new condition >

 

The query builder uses drop down menus which you select to build your query.

 

The Query Builder writes SQL in the SQL Editor as you create it, and you can also write SQL directly in to the SQL Editor tab below and test it.

 

 

Query builder - 2

 

 

Click the red colored Customers.CustomerID (Table.Field) to select the appropriate field, in this case it will be ContactName

 

Query builder - 3

 

 

Next, select the appropriate operator for this query, in this case it is Starts with

 

Query builder - 4

 

 

Next we select the User Input item from the drop down menu. The User Input item is used for the end-user as their input field when they send their queries from their mobile device. You can add more conditions and use multiple User Input items combined with values or dates.

 

 

Query builder - 5

 

 

Now we have a sentence which reads:

 

Choose records where all of the following apply: Where Customers Contact Names start with [User Input]

 

Note, you can also change how records are chosen with the drop down menu item like below

 

Query builder - 6a

 

 

Another menu is also available by clicking the number next to each condition as shown below.

 

This allows you to Add, Delete, Enable/Disable and Add a bracket to the query.

 

Query builder - 6b

 

 

As the wizard has been completed, the SQL statements have been written automatically below and can be edited directly. Any queries which are run, are always based on the manual SQL statement.

 

Query builder - 6

 

Next, click the Sorting and Grouping of Results tab slider.

 

Here you can specify the order the data is reported back to the end user. For this example we will just order by company name from A to Z (ascending).

 

 

Query builder - 7

 

If you click back to the Define Criteria tab, you'll see the SQL statement has been updated to reflect these new conditions. (The Wizard does not get updated).

 

Query builder - 7a

 

 

Testing the Query

 

Next we will test the query to make sure we get the correct results back.

 

Click the Query Result tab

 

For testing, we'll input the letter a and press the Test button.

 

Query builder - 8

 

You will see a result like below.

 

Query builder - 8a

 

This shows the query is working and sending back the desired results. It lists all the Contact Names beginning with the letter a.

 

Next we will define which email address, or SMS and IM keywords to use for this query and define how the results look when sent back to the end-user.

 

 

Message Separator (Optional)

 

The message separator option lets you choose how each argument of a query will be separated. For simplicity you could choose a space or maybe it's easier to separate your data by commas.

 

Go to the Message separator screen and select how you will separate user inputs for query requests.

eg. [user input 1],[user input 2],....,[user input n] or [user input 1];[user input 2];....;[user input n] etc

 

This can be changed at any time but it does affect all queries on a global scale.

 

messaging separator

 

 

Message Builder tab

 

Click the Message Builder tab

 

Here you select if you want to make available email, sms or IM (or all 3) for accessing this query and if a keyword is used to distinguish between other queries.

 

First, we will attach an email address to this query. The email address we setup earlier appears in the drop down menu and we select that.

 

Note on Keywords: Because we can have unique email addresses for each query, we don't need to assign a keyword to separate this query from others. Although if you only have one email address you would separate each query by a keyword. The same case is for SMPP, but not instant messaging or using a GSM Modem (see below) 

 

eg: contacts@company.com finds contacts, employees@company.com finds employee data, sales@company.com finds sales data

 

Or, use one email address for all queries and separate them by a keyword.

eg. queries@company.com is the only email address used. Keywords are contacts, employees and sales. These are entered before the [User Input} item we setup earlier.

 

The Results area lets you choose how many results are to be returned to the end-user.

 

Query builder - 8.5

 

 

We fill in the rest of the fields if we wish to use SMS and IM.

 

Note: GSM Modem and IM must use keywords to separate each query because there can be only one instance of each running at a time on the host computer. Email and SMPP can be unique for each query, or can use unique keywords for each query.

 

In this example we have used the word cont as a keyword (short for contacts).

 

This means our query will be written like:

 

cont [User Input]

 

Query builder - 8.6

 

 

Click the Response Message Format tab at the bottom of the screen.

 

This screen shows the format of the message the end user will receive when they send their query to MobileDataNow. This information is taken from the View created earlier and is automatically populated for this query.

 

Query builder - 9

 

 

You can add additional text to the message as needed. Data is shown as %Table.Field% and can be deleted as required.

 

Query builder - 9b

 

Test your query in the Message Test Result tab to see the format of the message end-users will see. (Note: Data is often case sensitive!)

 

Query builder - 10

 

The result should look like the following.

 

Query builder - 10b

 

Now, you will need to start the MDN Services.