Data Commands


Performs a command on data (both binary and text strings). The data may be stored in a database, registry, text, or INI file. It may also be a value stored by a variable.

For SQL-type databases, executes a SQL (Structured Query Language) query or command against an ODBC database such as Oracle or MS SQL Server. Any database that has an ODBC driver available will work. All major databases are supported, including Oracle, SQL Server, Access, MySQL, Sybase, FoxPro, Paradox, etc. You can also create a connection and query Excel spreadsheets.

An existing ODBC data source must be used for the connection (configured in the Control Panel). To create a new data source, click the "Open ODBC Data Administrator" link (or use the Open Data Administrator action):







The available commands are:

Execute SQL Query   Get Columns   Get Tables   Get Data Sources   Open Data Administrator   Read Registry   Write Registry   Copy Registry   Delete Registry   Read INI Data   Write INI Data   Delete INI Data   Encrypt Text   Decrypt Text   Sort   Binary Data   Get Length   Count Items   Find   Get Sub String   Mid String   Chop   Replace   Insert   Random Text

  Execute SQL Query
Runs a SQL statement against a database.

Any valid SQL statement can be used, such as SELECT, INSERT, UPDATE, or DELETE. When combined with user variables (in the query or to store the results), dynamic and powerful database entries are possible (such as data-driven test cases).

An example of a SQL query is:

SELECT * FROM Employee WHERE LastName = 'Smith' ORDER BY HireDate
After executing the query, you can save the output results to the same locations as for any command: clipboard, variable, file, or not saved.

A sample output could be:




  Get Columns
Retrieves a list of all the COLUMNS from the desired table.
You can format the output any way you wish by using the "Separator" option. For example, you can place each column on a new line, or separate them with a comma, or any other delimiter that you need. A common delimiter is ",{newline}", which will place a comma after each column, with every entry on its own line (this is a SQL standard).

This list can also include the "Column Attributes", which will include the data type and size of the field. You can use this information in a CREATE TABLE statement to generate the table.

A sample output could be:



Here is the output without the "Column Attributes" option:




  Get Tables
Retrieves a list of all the TABLES from the desired database.
You can format the output any way you wish by using the "Separator" option. For example, you can place each entry on a new line, or separate them with a comma, or any other delimiter that you need.
A sample output could be:




  Get Data Sources
Retrieves a list of all the ODBC Data Sources currently configured on your computer. Use the Open Data Administrator action to configure new sources.

You can format the output any way you wish by using the "Separator" option. For example, you can place each entry on a new line, or separate them with a comma, or any other delimiter that you need.
A sample output could be:



Using this functionality, you can make a short script to confirm that the correct data source is installed:




  Open Data Administrator
Launches the ODBC Data Administrator program. The script continues running without waiting for the ODBC window to close.
In this program you can add and delete data sources that can be used for SQL Queries in your scripts.




  Read Registry
Reads a setting from the Windows system registry. The entire key, including the top-level and final key name, should be put together:

HKEY_CURRENT_USER\SOFTWARE\vTask\Sample\Key

vTask automatically determines the data type of the key. String (REG_SZ, REG_EXPAND_SZ), Multiple String Arrays (REG_MULTI_SZ), and number values (REG_DWORD) are supported. Multiple String Arrays (REG_MULTI_SZ) are converted into a single string with each entry separated by newlines (CR+LF).

vTask supports backslashes in the key name. Use the industry standard "double backslash" to include a single embedded backslash. The end of the registry branch path must also be a backslash, therefore if your key begins with a backslash, there will be 3 in a row. Example:

HKEY_LOCAL_MACHINE\HARDWARE\DEVICEMAP\VIDEO\\\Device\\Video0


To determine the correct key name, use the REGEDIT program (Start button - Run - "regedit.exe"). When you find the key that you want to access, right mouse click in the left pane and choose "Copy Key Name", which saves you from having to type the entire key name.


  Write Registry
Creates or updates a setting in the Windows system registry. If the key does not exist when you call Write Registry Key, it is automatically created, including all sub-keys.

The following data types are available:

• SZ (null-terminated text string)
• MULTI_SZ (multiple text strings, separated by CR + LF [13, 10] )
• EXPAND_SZ (null-terminated text string with expansion)
• DWORD (4-byte number, signed value also works)
• BINARY (raw binary data)

It is outside of the scope of this document to describe the data types available in the Windows Registry. If you are unsure about which data type to choose, use the REG_SZ default or, choose the "Let vTask decide" option.


  Copy Registry
Copies Windows system registry settings from one tree to another. The original tree/key is left without modification. This action is very powerful, because it can duplicate entire tree (hive) structures, including sub-trees and all key/value combinations.


  Delete Registry
Permanently removes a Windows system registry tree, key, or value.

WARNING!   Be cautious when using Delete Registry Key, since it is able to delete entire tree structures (including all sub-keys). No confirmation prompt is displayed prior to deletion, and there is no undo. Improper use of this command could make the computer totally NON-FUNCTIONAL.

  Read INI Data
Retrieves data from an initialization file (INI).

The basic format of an .INI file is:

[section]
key=string

Here's another sample from a Windows application:

[boot loader]
timeout=30
Another Value=Hello World



  Write INI Data
Sets data inside of an initialization file (INI). If the INI file or any of its keys do not exist, they are created.

See the Read INI action for a description of the INI file format.


  Delete INI Data
Removes a key or entire section from an initialization file (INI).

See the Read INI action for a description of the INI file format.


  Encrypt Text
Allows you to encrypt a variable's contents so that it cannot be easily read. The key can be any value that you wish (numeric or string). The same key must be used to decrypt the text.

The alphanumeric key is used to scramble the text with a simple cipher. The longer the key, the more secure the encryption is. In theory, if the key is random and as long as the text, it is unbreakable.

Tip: The basic encryption methods employed by vTask are considered to be low-security. The main purpose of this command is to provide a more secure alternative than just clear text for storing information. In other words, vTask text encryption is only meant to deter easy human interpretation of data.

Any text can be passed to and stored in a variable, including file contents and user input, so it is possible to encrypt anything that you need to. Once a variable is encrypted, its text will be unreadable until it is decrypted.

vTask complies with all U.S. national and international export laws concerning encryption.

Encryption Scheme Overview:

Encryption Scheme	Description
ASCII Shift	This encoding works by shifting the individual bits of each character by the amount set with the key. The advantage to this method is that the encoded text always remains in alphanumeric format in the range of 32 - 127. It also does not create NULL characters (0), which makes this method the preferred choice for use in vTask.
Bit Shift	This encoding works by shifting the individual bits of each character by the amount set with the key. With this method, the encoded text will not include any NULL characters (0).

  Decrypt Text
Allows you to decrypt (unlock) a variable's contents after it has been encoded. The same key must be used to decrypt the text.

For more information concerning encrypting, please see the Encrypt Text entry.


  Sort
Orders a string or group of strings. The possible options are:

• Ascending:   A - Z
• Descending:   Z - A
• Length (Shortest):   Shortest-to-Longest
• Length (Longest):   Longest-to-Shortest
• Characters A-Z:   abcd...
• Characters Z-A:   zyxw...

Without "Column List Output":
  AAA BBB CCC

With "Column List Output":
  AAA
  BBB
  CCC

Sort Type	Original String	Sample Output
Ascending	bb;cc;aa	aa;bb;cc
Descending	bb;cc;aa	cc;bb;aa
Length (Shortest)	cc;bbbb;aaa	cc;aaa;bbbb
Length (Longest)	cc;bbbb;aaa	bbbb;aaa;cc;
Characters Z-A	hello	ehllo
Characters Z-A	hello	ollhe

  Binary Data
Performs an action on a set of binary data. "Binary Data" is information that contains non-alphanumeric characters, such as "00" (null), "09" (tab), or "FF" (ascii 255). vTask is capable of handling binary data internally, however this function permits access and conversion routines for binary information inside of XML scripts.

*Tip:  To convert into binary data (i.e., to create bytes), use the vTask Binary System Variables such as "{\FF}".

Binary Data	Original Data	Result
Get Byte Length	ABC + (bytes)00 01 FF	6 (entire length is 6 bytes)
Convert Binary -> {\\XX}	ABC + (bytes)00 01 FF (the original binary data could come from a source such as Read File action, with the output saved in a variable)	ABC{\00}{\01}{\FF}
Convert Binary -> Hex	ABC + (bytes)00 01 FF (the original binary data could come from a source such as Read File action, with the output saved in a variable)	4142430001FF (41 42 43 00 01 FF) A=41, B=42, etc
Convert Binary -> ASCII (text)	00 65 01 66 FF Strips out non-alpha characters (works for converting Unicode to normal text)	"AB" (65=A, 66=B)

  Get Length

*Tip:  For the "inline version," which can be nested and is compatible with Excel formulas, see the LEN( ) function.

Returns the length (the number of characters) of the text string.

Get Length can also return the length of binary bytes, such as the contents read from a file.

Example:

Original Text	Output Result
"test"	4
"Hello World"	12

  Count Items

*Tip:  For the "inline version," which can be nested and is compatible with Excel formulas, see the COUNTIF( ) function.

Counts all unique occurrences of a substring inside of source data. By default, the search is case insensitive (both "AA" and "aa" match "Aa").

Example:

Original Text	Text to Count	Output Result
"aa,bb,cc"	","	2
"aa,bb,cc"	"z"	0

  Find

*Tip:  For the "inline version," which can be nested and is compatible with Excel formulas, see the FIND( ) function.

*Tip:  Previous versions of vTask had a "Find Text" function, which returned a string rather than a position. Find Text is still supported for backwards compatibility, however the new Find function provides more capabilities.

Locates a sub string inside of text, and returns the 1-based position of its location. This function returns the position, rather than a string, because the position allows you to perform a larger number of actions, and also avoids unnecessary string parsing.

If the "Stop Position" is before the "Start Position", the entire string is searched. If the string cannot be found, "0" is returned.

If "Case Sensitive" is chosen, an exact case match must exist ("aa" will not match "AA").

*Tip:  The Find command can also locate binary values (bytes), as well as values with embedded NULLs. For these cases, be sure to: 1) choose the "Binary" search option, and 2) use the vTask byte codes, such as {\00} for NULL and {\FF} for ASCII 255.

Example:

Original Text	Text to Search For	Starting Position	Output Result	Explanation
"The quick brown fox jumped over the lazy programmer"	"the"	1	1	Found at position #1
"The quick brown fox jumped over the lazy programmer"	"the"	10	33	Found at position #33
"The quick brown fox jumped over the lazy programmer"	"the"	40	0	Not Found

  Get Sub String

*Tip:  For the "inline version," which can be nested and is compatible with Excel formulas, see the CHOOSE( ) function.

Saves a portion of the original text in a new variable. The portion which is located and retrieved is based on a substring (such as a delimiter). This function works well for parsing strings that are delimited by separators.

Example:

Original Text	Delimiter	Item	Output Result
"aa,bb,cc"	","	2	"bb"
"aa,'bb,cc',dd"	","	2	"bb,cc" (if allow quotes)

  Mid String

*Tip:  For the "inline version," which can be nested and is compatible with Excel formulas, see the MID( ) function.

Returns any internal portion of a string. Starting and ending positions that are beyond the start or end of the source string are set to the limits of the source.


Example:

Original Text	Start Position	Length	Output Result
"test"	2	2	"es"

  Chop

*Tip:  For the "inline version," which can be nested and is compatible with Excel formulas, see the CHOP( ) function.

Truncates (shortens) a string at a given location.
Note that this is different than Trim, which only removes trailing (or beginning) characters from a string. Chop can truncate a string at any position. This is also different than the Perl Chop function, which acts like a Trim.

Also see the Replace function, which can remove inner strings inside of text (by using an empty replacement string).

Example:

Original Text	Text to Chop	Chop Method	Output Result
"fish,cat,dog"	"cat"	Chop Before	"cat,dog"
"fish,cat,dog"	"cat"	Chop Before and Including	",dog"
"fish,cat,dog"	"cat"	Chop After	"fish,cat"
"fish,cat,dog"	"cat"	Chop After and Including	"fish,"
"test"	"s"	Chop Everything After	"te"
"test"	"t"	Chop Everything After	""
"test"		First Character	"est"
"test"		Last Character	"tes"

  Replace

*Tip:  For the "inline version," which can be nested and is compatible with Excel formulas, see the REPLACE( ) and SUBSTITUTE( )functions.

Changes substrings inside of text. This function can also be used to delete substrings by using a blank replacement string. You can alternatively replace on the first occurrence of the text.

Replace can also change binary values (bytes). For these cases, be sure to: 1) choose the "Binary" match option, and 2) use the vTask byte codes, such as {\00} for NULL and {\FF} for ASCII 255.

Example:

Original Text	Replace	Replace With	Output Result
"test 123"	"123"	"replaced"	"test replaced"
"test 123"	"123"	""	"test "
"test 123"	"t"	"Z"	"ZesZ 123"
"aaa bbb ccc"	"bbb"	""	"aaa  ccc"

  Insert
Puts text at a certain position inside of other text. The "Position" field is 1-based (1 inserts at the beginning). You may insert text past the end of the target string, and the output string will be lengthened to accommodate the desired size.

Example:

Original Text	Position	Insert	Output Result
"test"	2	X	"tXest"
"test"	10	X	"test     X"

  Random Text
Generates a string of random characters. The "Length" value is the desired size of the output string.

There are numerous formatting choices for the random characters:

Character Range	Sample Output
a-z	"fefmt"
A-Z	"EFJGK"
a-z, A-Z	"wKdmFG"
a-z, A-Z, 0-9	"sZ8gRbm3T"
a-z, A-Z, 0-9, \r\n	"r5WKs{newline}aFs3"
0-9	"192765"
(any)	"4;#ks.9Eqt"