How Do I Format SQL Commands in Lookout? Primary Software: LookoutPrimary Software Version: 5.1 Primary Software Fixed Version: N/A Secondary Software: N/A
Problem: When using SQL commands in Lookout I have two options, the DSN and the SQL Commands Expression boxes. How should I format my SQL command in order to use it in an expression box? Solution: As far as Lookout is concerned, SQL commands are strings. SQL commands can be built by combining strings and variables using the string functions available in any Expression input (yellow box). There are two Lookout Objects that allow the use of SQL. These objects are the DataTable and the SQLExec objects. There are two methods for entering an SQL Expression in Lookout. The method applies to both objects. Methods for entering SQL commands in a Lookout Expression (this applies to static or non-changing SQL commands): I. Using a TextEntry a Container for your SQL Command: 1. Change to Edit Mode 2. Create a TextEntry box on the process user interface 3. Create a DataTable object 4. In the DataTable object Properties link the SQL Command to the TextEntry from step 2 5. Create a PushButton object 6. Link the value of the PushButton to DataTable.executeSQL 7. Change to Run mode 8. Copy and paste the SQL command into the text box 9. Click the PushButton to execute the SQL command II. Changing your SQL Command to work in a Lookout Expression: 1. Replace all double quotation marks (") with a double set of double quotes ("") 2. Single quotation marks (') should be fine. You do have to end quotes after the single quote, with a double quote, if the element inside the single quote will be a variable. 3. Encapsulate everything that's not a variable in quotation marks 4. Place an ampersand (&) between command strings and Lookout variables to be concatenated (combined) The following expression is formatted as a normal SQL command (Method I): SELECT "\\COMPUTER\SQL_DataTable\SQLNewPot.value" FROM TRACES WHERE LocalTime > 'hh:mm:ss' AND LocalTime < 'hh:mm:ss' The following expression is formatted for the Expression input (Method II): "SELECT ""\\COMPUTER\SQL_DataTable\SQLNewPot.value"" FROM TRACES WHERE LocalTime > '"&TextEntry1&"' AND LocalTime < '"&TextEntry2&"'" Basic Lookout String Functions: & = Concatinate Strings "" = String i.e. The following line contains an expression: The Resulting Text: "Hello " & "World" & Process1.Pot1.value Hello World 5 i.e. The following line contains an expression: The Result Text: " This is ""quotes inside of a lookout expression. "" Good." This is "quotes inside of a lookout expression. " Good. Related Links: Attachments:
Report Date: 05/11/2004 Last Updated: 05/14/2004 Document ID: 39AEPP9U |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
