Perform common SQL operations in InTouch

InTouch uses SQL Access functions to interact with information stored in a database. These SQL Access functions enable you to write scripts that select, modify, insert, or delete database records.

SQL actions are synchronous. When you run a database QuickScript from an InTouch application, control does not return to InTouch until the database action requested by the function is complete.

SQL Access functions adhere to punctuation standards that describe the type of arguments associated with a function. When an argument is entered in a script string surrounded by quotation marks ("Arg1") that exact string is used. If no quotation marks are used, the argument value is assumed to be a tag name and the current value of the tag is associated with the argument.

Most SQL functions return a result code. If the result code is non-zero, the function failed and other actions should be taken. The result code can be used by the SQLErrorMsg() function.

You insert SQL functions in your QuickScripts using the InTouch QuickScript editor. The general procedure to insert a SQL function into a script includes the following steps:

Add a SQL function to a script

1. Start InTouch WindowMaker.

2. Open the QuickScript with the QuickScript editor.

3. Place the cursor in the script where you want to insert the SQL function.

4. In the Functions area, select Add-ons to show the Choose function dialog box.

5. Select on the SQL function that you want to insert into the QuickScript. The script updates and shows the SQL function that you inserted.

The arguments associated with SQL Access functions consist of the following:

• BindList

  Corresponds to a Bind List name defined in the SQL.DEF file.

• ConnectionID

  The ConnectionID argument refers to the name of a memory integer tag that holds the number (ID) assigned by the SQLConnect() function to each database connection.

• ConnectString

  The ConnectString identifies the database system and any additional logon information. It is entered in the following format:

  "DSN=data source name[;attribute=value

  [;attribute=value]...]"

  Microsoft SQL Server Connection Strings

  • Microsoft OLE DB Provider for SQL Server (recommended use).

    "Provider=SQLOLEDB.1;User ID=sa; Password=;Initial Catalog=MyDB;Data Source=MyServer;"

    The OLE DB Provider for SQL Server is sqloledb.

  • Microsoft OLE DB Provider for SQL Server (recommended use)

    "Provider=SQLOLEDB.1;uid=sa;pwd=;Database=MyDB"

  • Microsoft OLE DB Provider for ODBC (using the default provider MSDASQL for SQL Server):

    "DSN=Pubs;UID=sa;PWD=;"

  • Microsoft OLE DB Provider for ODBC (using the default provider MSDASQL for SQL Server):

    "Data Source=Pubs;User ID=sa;Password=;"

    Oracle Connection Strings

  • Microsoft OLE DB Provider for Oracle (recommended use)

    "Provider=MSDAORA;Data Source=ServerName;User ID=UserIDStr; Password=PasswordStr;"

    Microsoft Access Connection Strings

    Microsoft OLE DB Provider for Microsoft Jet (recommended use). Microsoft.Jet.OLEDB.4.0 is the native OLE DB Provider for Microsoft Jet (Microsoft Access Database engine).

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\DBName.mdb;User ID=UserIDStr;Password=PasswordStr;"

    Microsoft OLE DB Provider for ODBC (using the default provider MSDASQL for MS Access):

    "Provider=MSDASQL;DSN=DSNStr;UID=UserName; PWD=PasswordStr;"

• ErrorMsg

  Message variable containing a text description of the error.

• FileName

  Name of the file in which the information is contained.

• MaxLen

  Maximum size of the column associated with a parameter. This argument determines whether the data is of varying character or long varying character type. If MaxLen is less than or equal to the largest character string allowed by the database, then the data is varying character type. If greater, then the data is long varying character type.

• OrderByExpression

  Defines the columns and either ascending or descending sort order. Only column names can be used to sort. The expression must be formatted:

  ColumnName [ASC|DESC]

  To sort the selected table by a column name in ascending order:

  "manager ASC"

  To sort by multi-columns, the expression is formatted:

  ColumnName [ASC|DESC],

  ColumnName [ASC|DESC]

  To sort a selected table by one column name (for example, temperature) in ascending order and another column name (for example, time) in descending order:

  "temperature ASC, time DESC"

• ParameterNumber

  Actual parameter number in the statement.

• ParameterType

  Data type of the specified parameter. Valid values are:

  Type	Description
  Char	Blank padded fixed length string
  Var Char	Variable Length String
  Decimal	BCD Number
  Integer	4-byte signed integer
  Small integer	2-byte signed integer
  Float	4-byte floating point
  Double Precision Float	8-byte floating point
  DateTime	8-byte date time value
  Date	4-byte date time value
  Time	4-byte date time value
  No Type	No data type

• ParameterValue

  Actual value to set.

• Precision

  Is the decimal value's precision, the maximum size of the character, or the length in bytes of the date-time value.

• RecordNumber

  Actual record number to retrieve.

• ResultCode

  Integer variable returned from most SQL functions. ResultCode is returned as zero (0) if the function is successful and a negative integer if it fails.

• Scale

  Is the decimal value's scale. This value is required only if applicable to the parameter being set to null.

• StatementID

  When using the advanced functionality statements, SQL returns a StatementID, which it uses internally.

• SQLStatement

  Actual statement, for example:

  ResultCode=SQLSetStatement(ConnectionID, "Select LotNo, LotName from LotInfo");

• TableName

  The TableName parameter contains the name of the table you want to access or create in the database.

• TemplateName

  The TemplateName parameter is the name of the template in the SQL.DEF file that defines the table.

• WhereExpr

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

  ColumnName comparison_operator expression

  Note: If the column is a character data type, the expression must be enclosed within single quotation marks.

  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 records whose temperature column contains a value greater than 350:

  temperature>350