TableLookupEx

You can use this function for Optiva Workflows, Copy Methods, and Equations.

Purpose

Executes a SQL Query with the supplied parameters. Returns an ADO.NET data table object that is filled with columns and rows from the query. The return is a complete data table, and should be defined as DataTable type.

The "Imports System.Data" declaration can also be added at the beginning of the script. This avoids adding a prefix to each line in your script, where needed, with “System.Data.DataTable”. From there, use DataTable functions to count or specify table positions. These are standard VB.net concepts you can find in MSDN.

You can use this function in most scripting scenarios. This function allows for flexible post-query filtering, sorting and manipulations of the DataTable. You can also access all of the data returned from the database. This means you can access any data row at any time.

This function is heavy on memory usage. The DataTable object is a complete in-memory copy of all the rows and columns that the SQL returned. You could consume all available memory.

Syntax


Dim variable As Object = TableLookupEx(QueryName, DataTableName[, paramN])  

As an alternative, you can use this syntax. This syntax supports named query arguments.


Dim MyDataTable As DataTable = TableLookupEx(QueryName, DataTableName, QueryArgsNamed[, paramN])

Arguments

Part Description
QueryName Name of the query in FSQUERY to be executed. The tokens are replaced by the system in the SQL for these names:
  • [%USER]
  • [%LAB]
  • [%GROUP]
  • [%%CURRENT_DB_NAME]
  • [%%SEARCHTABLE] - This token is only available during the Search's PostSearch event.
DataTableName Optional. The name of the returned ADO.NET data table. The default is “TableLookup”. To draw an analogy, a DataTable is a sheet in an Excel workbook. It has the columns and rows from the SELECT statement you used to fill that table. The object is in-memory and not directly attached to the database. You can update values on this datatable object and it does not update the database. You can filter or sort the data in this table. The DataTableName argument that is in the TableLookupEx workflow function is optional.

The default value is “TableLookup”. Whatever you have for that value becomes the name of the DataTable object.

It is not likely that you are going to change this value. Most of the time you are not working with the name of the DataTable. You are working with the DataTable object itself.

You can do this command multiple times in your script, each time returning a DataTable object. To merge all of these tables into a single DataSet object, you may want each DataTable to have a unique table name. Or, each table can be merged into a single DataTable object.

See more information about this at the Microsoft help link: http://msdn.microsoft.com/en-us/library/y4b211hz(v=VS.90).aspx

paramN Optional. The parameters can be in the form of an array or a comma-delimited string of the individual arguments of the query.
QueryArgsNamed Enables you to use custom named query arguments in your SQL.

Numbered query arguments, which are used by the QueryArgs argument. The arguments are wrapped with single quotes in the SQL.

Named query arguments are not wrapped with quotes automatically. This can be useful when you pass a numeric value to your SQL.

When you use the QueryArgsNamed argument, add the namespace, Imports System.Collections.Generic, to the top of your script .

Example 1


Dim rv As DataTable = TableLookupEx("MyCustomQueryCode", "TABLELOOKUP", 
arg2, arg3…)

or


Dim argArray as Object = array(arg1, arg2, arg3)
Dim rv As DataTable = TableLookupEx("MyCustomQueryCode", "TABLELOOKUP", 
argArray)

Example 2

This example uses named query arguments.


Dim NamedArgs As New Dictionary(Of String, String) From {{"%TOKEN1", 
"VALUE1"}, {"%TOKEN2", "VALUE2"}}
NamedArgs.Add("%TOKEN3", "VALUE3")
Dim MyDataTable As DataTable = TableLookupEx("MYQUERYCODE", NamedArgs, 
arg1, arg2, arg3)