SQLSelect() function

The SQLSelect() function retrieves records from a table. When the script containing the SQLSelect() function is processed, the retrieved records are placed in a temporary Results Table in memory. These records can be browsed using the SQLFirst(), SQLLast(), SQLNext() and SQLPrev() functions.

Important: Always call the SQLEnd() function after the script containing the SQLSelect() function ends to free memory used by the Results Table.

Category

SQL

Syntax

[ResultCode=]SQLSelect(ConnectionID,TableName, BindList,WhereExpr,OrderByExpression);

Arguments

ConnectionID

Name of a memory integer tag that holds the number (ID) assigned by the SQLConnect() function to each database connection.

TableName

Name of the database table to access.

BindList

Defines which InTouch tags are used and which database.

WhereExpr

Defines a condition that can be either true or false for any row of the table. The SQLSelect() function extracts data from only those rows in which the WhereExpr condition is true. The expression must be in the following format:

ColumnName comparison_operator expression.

Note: If the comparison is made with a character expression, the expression must enclosed within single quotes.

The following example selects all rows whose name column contains the value EmployeeID:

name='EmployeeID'

The following example selects all rows containing part numbers from 100 to 199:

partno>=100 and partno<200

The following example selects all rows whose temperature column contains a value greater than 350:

temperature>350

WhereExpr - Memory message Tag

OrderByExpr - Memory message Tag

Speed_Input - Memory Real - User Input Analog

Serial_Input - Memory Message - User Input String

Analog Example

WhereExpr = "Speed = " + text
(Speed_Input,"#.##");

Because Speed_Input is a number, it must be converted to text so it can be concatenated to the WhereExpr string.

String Example

WhereExpr = "Ser_No = ‘" +
Serial_input + "’";

Because Serial_Input is a string it must have single quotes around the value for example:WhereExpr = "Ser_No=’125gh’";

String Example using the like statement

WhereExpr = "Ser_No like ‘-’"

When using the Like comparison operator the % char can be used as a wild card.

String and Analog Example using a Boolean AND operator

WhereExpr = "Ser_No = ‘" + Serial_input + "’" + " and " + "Speed = " + text(Speed_Input,"#.##");OrderByExpr = "";

If the order does not matter, use a null string as shown above.

SQLSelect using WhereExpr tag

ResultCode = SQLSelect(Connect_Id,TableName,
BindList,

WhereExpr,OrderByExpr);

Error_msg = SQLErrorMsg( ResultCode );

SQLSelect WhereExpr built in function

ResultCode = SQLSelect(Connect_Id,TableName,
BindList,

"Ser_No = ‘" + Serial_input + "’", OrderByExpr);

Error_msg = SQLErrorMsg( ResultCode );

OrderByExpr

Defines the direction to sort data within a table column. Only column names can be used to sort and the expression must be in this form:

ColumnName [ASC|DESC]

The following example sorts a table in ascending order by the data from the manager column:

"manager ASC"

You can also sort by multi-columns where the expression is in the form:

ColumnName [ASC|DESC],

ColumnName [ASC|DESC]

The next example sorts the selected table by the temperature column in ascending order and the time column in descending order:

"temperature ASC,time DESC"

Examples

The following statement selects records from the BATCH table using a BindList named List1, whose column name type contains the value cookie. It will present the information sorted by the amount column in ascending order and the sugar column in descending order:

ResultCode=SQLSelect(ConnectionID,"BATCH", "List1","type='cookie'","amount ASC,sugar DESC");

The following statement selects all data in the database, do not specify a value for the WhereExpr and OrderByExpr:

ResultCode=SQLSelect(ConnectionID,"BATCH", "List1", "","");

See Also

SQLFirst(), SQLConnect(), SQLLast(), SQLNext(), SQLPrev(), SQLEnd()