The Query definition window is now resizable to make the entry complex queries a lot easier to manage and write.

It is also possible to grab between the Keys and the Query and move the screen area up or down.

The Designer for Web Services uses SQL to select rows and columns from tables in databases. However, there are some differences to note from the standard SQL syntax.

For entering Simple Queries a Wizard has been provided, if you plan to enter the SQL string manually then please read the following instructions before proceeding:

If you are using a Stored Procedure or are trying to connect to an Access query refer to the section on Stored Procedures on the correct syntax to be used.

Entering a Query manually.

Suppose you want to select Employees from the Employee table in the Northwind database.
 
The SQL string must be entered as follows.
 
SELECT EmployeeId,LastName,FirstName FROM Employees Where LastName = '~plastname~'

For Integers the quotes are not required ,so for example to select EmployeeId you would enter;

SELECT EmployeeId,LastName,FirstName FROM Employees Where EmployeeId = ~pemployeeid~

In the above example the parameters would appear as Web Service parameters as input capable fields.

N.B. Do not use * to select all fields as this is not allowed in the Designer for Web Services and it is not considered good practise.

Use of Tildes (~) when entering SQL.

In previous versions of Flynet's DWS when entering column or field names following the SELECT statement, the column or field had  to be enclosed by the ~ tilde character. This is no longer required.However they must be used when entering parameters (as shown above) or entering certain SQL functions such as SUBSTRING or SUM.

N.B - Please be aware that if you change the SQL string after initial entry and you enter a tilde in the select statement DWS will rebuild the grid, you will have to check your data types again to ensure they are correct.

Using SQL functions.

SQL functions are supported within the DWS, but take care that the field used to hold the result is configured correctly.The example SQL string below shows the correct way of specifying the SUM and SUBSTRING functions.

SELECT  CustomerId , SUM(Freight) as ~freight~ from dbo.Orders Group by Customerid Order by CustomerId

Note that the result field is enclosed in tildes , it is recommended that you specify result fields holding totals as a decimal data type.

SELECT  CustomerId , substring(CustomerId,1,3) as ~Cus1~ from dbo.Customers

 

The above example shows how to specify the SUBSTRING function.Both examples can be demonstrated using the MS SQL server Northwind database.

Table or Column names containing spaces or non-standard characters.

If the table name has a space embedded in the name or any non standard character such as $ @ etc. , e.g Order Details in Northwind , then you should enclose the Table name in square brackets [ ].

e.g,  [Order Details]. You should also apply the same rule to any reserved words that are column names.
 
You will notice that once the keys are defined there is an option to determine the direction of the parameter within the web service (see below). Keys with a direction of "Out" represent web service output fields; "In" keys represent web service input fields i.e. web service parameters.
 
In the screen below, two fields are returned, note that you must enclose all fields with the ~ character and separate each field with a comma.
 
Any string values should be enclosed in single quotes ' as shown below, do not use double quotes " .
 

Once defined the Query can now be tested, hit the "Test" button, data should be returned as follows.

Using the Simple Query Wizard

A simple query wizard has been added to assist in the generation of simple queries only, more complex
queries have to be manually entered.

N.B Please refer to the Syntax rules above , for more complex queries defined using the wizard , some manual

editing of the SQL generated by the wizard will be required
 
Press the button to initiate the first screen.

Select the desired table from the drop down box.

Click to go to the next screen.

Select the columns required and click .

Click the options from the drop-down menus to build the query as follows.

Click , the query is now defined.