Overview Query Tester Walter Web Example Walter Windows Example Export to DataTable Export to Excel file Export to Json Export to ListItem array jQuery AutoComplete jQuery DataTable Walter Query Syntax

What is Walter?

Image by Joshua Budich

In the Big Lebowski, Walter Sobchak was The Dude's faithful sidekick. This library is intended to be a faithful sidekick for your AVR projects. Walter's specialty is in executing dynamic, declarative queries that produce read-only lists in a variety of formats for your AVR for .NET applications.

Let's see what Walter can do for you.

Walter overview

Walter is a component that performs dynamic read-only queries against DataGate databases. It works against physical and logical files. While Walter is intended primarily for ASNA Visual RPG, it would also work just fine with VB.NET or C#.

Walter implements a declarative programming model that makes is easy to fetch dynamic query results with a minimal of effort and with high performance. When used against the IBM i, Walter consistently produces output with subsecond response times (usually less than 250 milliseconds). It takes about 1-15 lines of code to configure Walter and produce output with it. Part of the reason I wrote Walter is that I got tired of needing to create an endless supply of custom classes for reading data and producing that data in a variety of formats.

Some of Walter's design goals are:

Allow runtime configuration of queries, including library, file, fields, selection criteria, and sort order with a declarative, logic-free syntax. You only need to tell Walter what you want, you don't need tell how it how to get it.
Provide dynamic, performant query capabilities independent of the availability of explicit access paths provided by logical files. Walter works with the local ASNA DataBase, but really shines when used with the IBM i. On the IBM i, Walter puts OpenQueryFile to good use. Walter also works with ASNA's DataGate for SQL Server, but has been tested the least there so far.
Provide built-in, key-driven paging support. When used against logical files with a known key structure, Walter provides built in forward paging. You don't have to manage "next" key values and Walter tells you when the last page has been read.
Support several standard output types but also provide an plug-in model so that additional output types can easily be added later. Walter's default output type is a System.Data.DataTable, but it includes output adapters to produce an array of ListItems (for populating drop-down lists in Windows and Web applications), Excel spreadsheets (in native XLSX format), and Json for client-side JavaScript use.
Allow a common way to pass query meta data into Walter's output adapters. This allows each output adapter a measure of customization. For example, Walter's Excel output adapter uses this meta data to provide a spreadsheet heading and the worksheet name.
Provide robust error handling so trouble-shooting is easy.
Allow for the easy aliasing of field names so that, for example, when producing an Excel spreadsheet user-friendly column names are available.
Be usable by any .NET application type. Walter works with Windows, ASP.NET and Web services and can be coded in AVR, VB.NET, C# or even JavaScript. Walter makes a great partner for extending ASNA Wings, Monarch and even Mobile RPG applications. Walter can be used directly to create department-level projects or it can be used as a primitive in the creation of more robust database repository class libraries (where it could be tapped to produce lists for those libaries). Walter has a sister project, ASNA.Helpers.ASPNET that provides helper facilities for using Walter in ASP.NET applications.
Provide an interactive query tester for creating and testing queries and generating the AVR, C#, or even JavaScript.
Be formally open sourced so that others can easily extend, enhance, and bugfix Walter.

Output adapters

Walter's query output is driven by output adapters. The default output adapter emits query output as a DataTable. The DataTable can be bound to most Windows and ASP.NET controls (including Windows' DataGridView and ASP.NET's GridView). Walter also includes output adapters to emit query results as:

Json. Walter's Json output can be used in a variety of ways including feeding a JavaScript autocomplete widget (such as jQuery UI's AutoComplete) or feeding a client grid such as the jQuery DataTable grid. Walter includes an HTTP handler to make its Json output easy to use with ASP.NET. A small JavaScript wrapper for the jQuery UI AutoComplete widget is included. Using Walter and its components make it very easy to add AutoComplete to your ASP.NET pages (including Wings and Monarch pages).
Excel. Walter's Excel adapter produces Excel spreadsheets (in XLSX format) on either Windows Web servers or Windows clients, without needing Excel installed. This adapter uses the ClosedXML library (which itself is a wrapper around Microsoft's Open XML SDK).
ListItem array. This adapter produces a ListItem array for populating ASP.NET's DropDownList controls or Windows's ComboBox.

Should you need some other output type, it's easy to write your own output adapters by implementing a simple interface that Walter provides.

Before you dig deeply into Walter's technical details, take a look at the next three sections. They feature three example programs of Walter in action. These examples include a Windows-based query tester for Walter, a browser-based example, and a Windows-based example.

Download the code

All of Walter's code is open source and available on Github. You'll find its various projects at the link below with the other ASNApalooza 2014 code.

You can either download a project's zip file and use it without any Git components installed on your PC. However, if you'd to like contribute features (and bug fixes?!) to Walter, install Git on your PC and clone a project's repository. We'd like very much accept pull requests for Walter and its various components.

All of the code from ASNApalooza 2014

Walter was written with AVR 11.2, Visual Studio 2012, and .NET 4.5. You'll need these versions to use Walter without any additional effort. For at least AVR 10.2, you should be able start an empty project and use Visual Studio's 'Add existing item' feature to pull Walter source files into that project to build your own AVR 10.2 version. For AVR 12.0 users, Walter should simply open and compile. Walter will soon be officially ported to AVR 12.0.

Walter Query Tester

The Query Tester is a Windows program that you can use to interactively create and test Walter queries. With a query defined, you can see its various outputs and the code (in either AVR, C#, or JavaScript) that you can copy to put the query in your appliation. The Query Test limits query parameters, order by fields, and query options to three each. This is a Query Tester limitation—Walter allows as many of each as you need.

Beyond creating and testing queries, the Query Tester also shows you the AVR (or C#) you need to paste into your application to reproduce the current query. The Query Tester is isn't intended to be an end-user application. Rather, it is aimed at programmers to create and test queries--and optionally generate the AVR needed to programmatically reproduce the query.

Figure details

Fig 1. Query definition.
This is the query definition panel. Enter the parameters for a query here. Although the query tester only allows three each of query parameters, order-by fields, and query options, Walter's API allows an unlimited number of them. Walter lets you save and then later open query definitions to your local drive.

The DB Name prompt enumerates the ASNA DataGate database names on your PC. At least one database name must be present for the Query Tester to work.
Fig 2. Query results as a datatable. This shows the query results as a datatable. Note the "Next page" link in the upper right-hand corner. Walter provides built-in "next page" support for paged queries.
Fig 3. Query arguements. In addition to returning the query results, Walter also returns a Json structure that provides meta data about the query. This data includes the query input arguments, its output arguments, and error info. All of these values are surfaced as properties of the Walter query object. These values are surfaced as a Json object for diagnostic and debugging purposes.
Fig 4. Query results as Json. This shows the query result as Json. The pure query results are in the "list" array of objects. The other values (resultRowCount, milliseconds, etc) are meta data about the query.
Fig 5. AVR cut-and-paste datatable code for the query. The code tabs provide the code you need to add the query you defined to your AVR application. The code presented reflects the current definition of the query (that is, this code is dynamically generated every time you tweak the query). Figure 5 shows the AVR code to produce a datatable from your query.
Fig 6. AVR cut-and-paste Json code for the query. The AVR code to produce Json from your query.
Fig 7. AVR cut-and-paste Excel-for-Windows code for the query. The AVR code to produce an Excel spreadsheet for Windows applications from your query.
Fig 8. AVR cut-and-paste Excel-for-Web for the query. The AVR code to produce an Excel spreadsheet for ASP.NET applications from your query.
Fig 9. AVR cut-and-paste ListItem array code for the query. The AVR code to produce an array of ListItems from your query. This is intended primarily for populating dropdown boxes in either Windows or Web applications (although the ListItem is an object belonging to the ASP.NET namespace domain, it works fine in Windows apps--it's just a data structure.) You can use this
Fig 10. C# cut-and-paste datatable code for the query. Walter is an API, it doesn't use any AVR-specific components. For the five types of AVR code shown, the C# equivalent is also available. While not provided, you could also use Walter with VB.NET. (As an aside, Walter's Query Tester was written in C#--as an exercise in using Walter with C#).
Fig 11. Javascript cut-and-paste code for the query. Walter provides a JavaScript interface to enable using Walter queries from the client side of ASP.NET applications. When used from JavaScript, Walter can only produce Json output. Figure 11 shows example JavaScript you'd use to use a Walter query from the client side. The JavaScript code preview is the only Code tab that doesn't dynamically represent the current query definition. (Fixing this is on my to-do list!).
Fig 12. Query results as Excel. Figures 7 and 8 show the code to produce an Excel spreadsheet from a Walter query. Use the QueryTester's View->Show query as Excel to show the spreadsheet Walter produces. The spreadsheet produced isn't a static example, it is produced at runtime from the current query definition.

Download Query Tester from GitHub See the Query Tester project on GitHub

Walter Web example

Walter Windows example

Export to DataTable

This example shows how to use Walter to export query results to a DataTable. This code could be used in either Windows or ASP.NET to bind data to a grid or other control. Exporting to a DataTable uses Walter's default output adapter so you'll notice that no explicit output adapter instancing is required. You'll see that later with the other output adapters.

Don't let the concept of output adapters seem too mysterious. An output adapter is just a class that implements a simple interface. Output adapters get passed a DataTable by Walter's query engine and it's the output adapter's job to translate that incoming DataTable into something else. Walter's default output adapter emits a datatable—and as you can probably guess, it didn't take much code to produce this output adapter. It simply delivers the internal DataTable. More on output adapter internals later. Let's dig into how to use the default DataTable output adapter.

Using ASNA.Helpers.DataServices
  
DclFld db     Type(AdgConnection)
DclFld Query  Type(QueryDriver) 

db = *New AdgConnection("*Public/IBMi") 

Query = *New QueryDriver(db)
Query.Args.Inputs.Library    = "examples"
Query.Args.Inputs.File       = "CMastNewL2"
Query.Args.Inputs.FieldsList = "CMCUSTNO,CMNAME,CMSTATE"
Query.Args.Inputs.Rows       = 1000
Query.Args.Inputs.Query      = "CMSTATE = '{CMSTATE}'"
Query.Args.Inputs.QueryParms.Add(*New QueryField("CMSTATE",State))

grid.DataSource = Query.Exec() *As DataTable

db.Close()

Toggle source code annotation

Line	Description
01	Most of Walter lives in the ASNA.Helpers.DataServices namespace. This using statement makes Walter's QueryDriver, QueryField, and QueryType objects available without needing to qualify them with their namespace.
03	Declare a DataGate DB connection. This is the core DB connection used by AVR's DclDB. Both DB connections participate in DataGate connection pooling and they both use the same Database Name scheme. This DB connection must be instanced before Walter's QueryDriver is instanced.
04	The QueryDriver is Walter's traffic cop—all of Walter's goodness flows through it.When you look at its source code, you'll see there really isn't much to the QueryDriver, it's mostly a convenience class to make it easy to stitch together the class instances that Walter needs. Most of what Walter needs is surfaced through the Query Driver. Its constructor needs to be passed an instanced (but not necessarily connected) DataGate DB connection
06	Instance the database connection with an ASNA DataGate database name.
08	The QueryDriver surfaces a layered set of classes that provides access to Walter's inputs, outputs, and error information. (In retrospect I regret aspects of this layered design and wish I had done something else. That's what I get for coding first and designing second!.) The QueryDriver receives all of its inputs through its Args.Inputs class. You'll later see that by flowing all inputs through the Args class provides another interesting way of instancing Walter (hint: if you serialize the Args.Inputs class to Json, you can easily "hydrate" an instance of Walter by via Json serialization. More on this later.) This line instance the QueryFile object which under the covers instances its Arg.Inputs class—ready to accept query inputs.
09	Specify the library for the query. This can be either a library name or the special value, *LIBL.
10	Specify the file name name for the query. Just a reminder, remember these values are specified at runtime. Unlike the tradition use of AVR's DclDiskFile, Walter has no knowledge of the file you want to use at compile time. (Note: a glaring omision in Walter surfaces itself here: Walter lacks the ability to specify a member name. Walter currently works only with the first member. This is one of the first things to add to Walter after ASNApalooza!).
11	Specify a comma separated list of fields to include in the query results. This is a case-insensitive comma-separated list of field names. Each field name specified must exist in the record format. For basic uses, this list is used as it is shown here. But an alternative syntax is comma-separated list of fieldnames and column aliases. For example, in this list: "CMCUSTNO:Number, CMNAME:Name, CMADDR1:Address" the field name is provided first then a column and the field alias. The use of the field alias is adapter-specific. For example, Walter's Excel output adapter uses field aliases for column names and its Json output adapter uses field aliases to name the field in the resulting Json. Field aliases aren't used by the default DataTable output adapter. The simple delimited field list is that adapter needs. The resulting datatable will include only the field columns specified in this list. Note that even if you don't need the field in the resulting output, if a field is used in the query definition (line 13 below), it must be included in this list.
12	Specify the maximum number of rows for the query to return. A value of -1 means to return all rows that match query specs. Be careful using this option—it's probably not prudent to try to bind 100,000 rows to the ASP.NET grid or
13	Specify the query. This can use AND and OR statements and it specifies tokens that are replaced at runtime within braces. This parameterized query is resolved with values from the QueryParms. Every field in this query must be included in the following QueryParms collection. Note: Watch the placement of the single apostrophes carefully. This query follows open query file rules and for character values the apostrophes are necessary. As a tease, consider this: because Walter's queries are executed on the IBM i through Open Query File, advanced query facilities such as translation tables (for case insensitive searches), 'contains' searches (eg, any address with the word "Avenue" in it), range queries, and wild card queries are possible when Walter is used against the IBM i. Walter surfaces some pretty amazing, and surprising fast, query capabilities.
14	The QueryParms collection provides the field values for the replaceable tokens specified in the Query property. In this example, the replacement token {CMSTATE} is replaced by the value in the State variable at runtime. For example, if the State variable value is 'IN' then the query in line 14 resolves to CMSTATE = 'IN' at runtime. Each field used in the query must be represented by a corresponding field in the QueryParms collection.
16	Call the QueryDriver's Exec() method to execute its query. The default output adapter returns a DataTable and (because other adapters can return other types) the return value from the adapter in use must be cast appropriately. With Walter having produced the DataTable, you can now do anything with it you want. In many cases, it will be bound to either a Windows or ASP.NET grid control.
18	Close the underlying database server connection. This should always be done after you use Walter's QueryDriver's Exec() method. This connection does participate in connection pooling so there is very little overhead in reconnecting.

Queries are independent of their output format so the same core query used to explain the DataTable output adapter will be used to explain the output adapters. The discussion on other output adapters will focus on how the adapters redirect output. Later in this document we'll drill deeper into more details about Walter's query capabilities.

Export to Excel

This example shows how to use Walter to create an Excel spreadsheet in a Windows program and save it to a local file. A later section shows how you can use Walter to produce an Excel spreadsheet in an ASP.NET app.

Walter's Excel output adapter uses the open source ClosedXML library, which provides a rational API over Microsoft's Open XML SDK for Office.. I chose the ClosedXML library because it is an active project, has the features Walter needed, and has pretty good documentation. There are many alternatives for writing Excel spreadsheets in the .NET environment. If Walter's Excel output adapter doesn't suit you, write your own!

Walter's default Excel output adapter accepts two query options (through Walter's Inputs.Options collection) to specify the spreadsheet heading and the worksheet name. Through clever use of the Inputs.Options collection a scheme could probably be produced to specify to the Excel output adapter to create column and row total formulas. This has been on my todo list for months. Alas, that's why Walter is open source! Help please!

Using ASNA.Helpers.DataServices
DclFld db           Type(AdgConnection)
DclFld Query        Type(QueryDriver) 
DclFld ExcelAdapter Type(ToExcelAdapter)

db = *New AdgConnection("*Public/IBMi")

Query = *New QueryDriver(db)
Query.Args.Inputs.Library    = "examples"
Query.Args.Inputs.File       = "CMastNewL2"
Query.Args.Inputs.FieldsList = "CMCUSTNO:Number,CMNAME:Name,+
                                CMADDR1:Address,CMCITY:City,CMSTATE:State"
Query.Args.Inputs.Rows       = 1000
Query.Args.Inputs.Query      = "CMSTATE = '{CMSTATE}'"
Query.Args.Inputs.QueryParms.Add(*New QueryField("CMSTATE",State))

Query.Args.Inputs.Options.Add("heading","Customer Listing")
Query.Args.Inputs.Options.Add("worksheet_name","Customers")

ExcelAdapter = *New ToExcelAdapter(Query.Args) 
Query.OverrideAdapter(*New ToExcelAdapter(Query.Args))
Query.Exec() 
db.Close()
ExcelAdapter.SaveToLocalFile("c:\users\public\documents\test.xlsx")

Toggle source code annotation

Line	Description
01	Make the QueryDriver, QueryField, and QueryType objects available without needing to qualify them with their namespace.
03	Declare a DataGate DB connection.
04	Declare an instance of the QueryDriver.
05	Declare an instance of the standard Excel output adapter. Note that it isn't instanced when you declare it--we'll instance it in a moment.
07	Instance the database connection.
09	Instance the QueryFile object.
10	Specify the library for the query.
11	Specify the file for the query.
12	Specify a comma separated list of fields to include in the query results. In addition to the field name, you can also provide (preceded by a comma) a field name alias. The use of this alias is specific to an output adapter. The Excel output adapter uses these field aliases for column headings in the spreadsheet. For example, The CMCUSTNO field will have a column heading of "Number."
13	Specify the maximum number of rows for the query to return. Be especially careful with this value when using the Excel output adapter. The entire spreadsheet is created in memory before it is produced.
14	Specify the query.
15	The QueryParms collection provides the field values for the Query property. In this example, the replacement token {CMSTATE} is replaced by the value in the State variable (not declared in this code snippet) at runtime. For example, if the State variable value is 'IN' then the query in line 14 resolves to CMSTATE = 'IN' at runtime. Each field used in the query must be represented by a corresponding field in the QueryFields collection.
17	Specify query options. The use of query options is specific to the output adapter being used. The Excel output adapter uses two options. This option defines the heading used in the spreadsheet.
18	This query option specifies the spreadhseet worksheet name. If either the 'heading' or 'worksheet_name' options are omitted when using the Excel output adapter, default values are used.
20	Instance the standard Excel output adapter. Most output adapters require the Query.Args instance as the only constructor argument. This makes the input args available to adapter. How the adapter may use those values is specific to a given output adapter.
21	Override the output adapter with the newly instanced Excel adapter. When the output adapter is overridden no other query results are generated--query results are always passed out of Walter through a single query adapter. For example, having overridden the output adapter with the Excel output adapter the query will not generate a data table.
22	Call the QueryDriver's Exec() method to execute its query. In the case of the standard Excel output adapter, nothing is returned from this method.
23	Close the underlying database server connection. This should always be done after you use Walter's QueryFile.Exec() method. This connection does participate in connection pooling so there is very little overhead in reconnecting.
24	Use the standard Excel output adapter's SaveToLocalFile() method to save the spreadsheet created. This option is only valid for use with Windows applications. Walter also has an ASP.NET helper class that can use the Excel output adapter in an ASP.NET app.

The spreadsheet produced by the code above is shown below:

Export to Json

Walter is a component that performs dynamic read-only queries against DataGate databases. It works against physical and logical files. Walter aims to provide a declarative model that makes is easy to fetch performant dynamic query results with a minimal of effort and with a high . It takes about 12 lines of code to configure Walter and produce output with it. When used against the IBM i, Walter consistently produces output in less than 1/10 of a second.

Using ASNA.Helpers.DataServices

DclFld db     Type(AdgConnection)
DclFld Query  Type(QueryDriver) 
DclFld Json   Type(*String)

db = *New AdgConnection("*Public/IBMi") 

Query = *New QueryDriver(db)
Query.Args.Inputs.Library    = "examples"
Query.Args.Inputs.File       = "CMastNewL2"
Query.Args.Inputs.FieldsList = "CMCUSTNO:number,CMNAME:name,CMSTATE:state"
Query.Args.Inputs.Rows       = -1
Query.Args.Inputs.Query      = "CMSTATE = '{CMSTATE}'"
Query.Args.Inputs.QueryFields.Add(*New QueryField("CMSTATE",State))

Query.OverrideAdapter(*New ASNA.Helpers.DataServices.ToJsonAdapter(Query.Args)) 

Json = Query.Exec() *As *String

Toggle source code annotation

Line	Description
01-15	Line 5 declares a string variable named "Json" to receive Walter's resulting Json. Otherwise lines 1-15 are the same as as the other output types.
17	Override Walter's default output adapter with its standard Json adapter. This Json output adapter uses the field aliases specified on line 12 to name the data keys in the Json (more on this in a moment).
19	Produce the Json.

A sample of Walter's Json is shown below. Note that each field is identified by the keys specified with the field alises in Line 12 of the code above. The "list" node in the Json is the Json list. The other nodes are informational meta data about the query that might come in handy. This query meta will be discussed in more detail later.

{
  "resultRowCount": 4,
  "totalRowCount": 995,
  "rowCountPhrase": "4 of ~995",
  "milliseconds": "41",
  "list": [
    {
      "number": "11200",
      "name": "Marley Services Cooperative Blue Tech",
      "state": "IN"
    },
    {
      "number": "5400",
      "name": "Red Laboratories Inc Computer Inc",
      "state": "IN"
    },
    {
      "number": "10300",
      "name": "Howard Corporation Consulting Assoc Inc",
      "state": "IN"
    },
    {
      "number": "40400",
      "name": "Port Inc Systems Agents Associates",
      "state": "IN"
    }
  ],
  "startingKey": [],
  "endingKey": []
}

Export to ListItem array

This example shows how to use Walter to export query results to an array of ListItems. An array of ListItems would most usually be used to populate ASP.NET DropDownList or the Windows ComboBox.

01 Using ASNA.Helpers.DataServices
Using System.Web.UI.WebControls 
...
DclFld db           Type(AdgConnection)
DclFld Query        Type(QueryDriver) 
DclArray ListItems  Type(ListItem) Rank(1)

db = *New AdgConnection("*Public/IBMi")

Query = *New ASNA.Helpers.DataServices.QueryDriver(db)
Query.Args.Inputs.Library    = "examples
Query.Args.Inputs.File       = "States"
Query.Args.Inputs.FieldsList = "State:text,Abbrev:value"
Query.Args.Inputs.Rows       = -1
Query.Args.Inputs.Query      = "STATE >= 'A'"
Query.Args.Inputs.QueryFields.Add(*New ASNA.Helpers.DataServices.QueryField("STATE",""))

Query.OverrideAdapter(*New ASNA.Helpers.DataServices.ToLisItemArrayAdapter(Query.Args)) 

ListItems = Query.Exec() *As ListItem[]

db.Close()

Toggle source code annotation

Line	Description
01	Add the Make the QueryDriver, QueryField, and QueryType objects available without needing to qualify them with their namespace.
02	Make the ListItem object available to the app.
04	Declare a DataGate DB connection.
05	Declare an instance of the QueryDriver. This is Walter's core query object.
06	Declare an array of ListItems. This will contain the result of Walter's query.
08	Instance the database connection.
10	Instance the QueryFile object.
11	Specify the library for the query.
12	Specify the file name name for the query.
13	Specify the fields from the file to return. The ToListItemArray output adapter requires field aliases to indicate which fields provide the text and values properties for the list item. The syntax of each field then is :.
14	Specify the maximum number of rows for the query to return. -1 means return all of the rows that satisfy the query.
15	Specify the query. This can use AND and OR statements and it specifies tokens that are replaced at runtime. See the next line for more info.
16	The QueryFields collection provides the field values for the Query property. In this example, the query is a static query‐that is there aren't any dynamic parameters needed for the query. However, you still need to provide a QueryField for the fields used in a static query; the value for fields used in a static query aren't used.
18	Override Walter's default output adapter with the ToListItemArray adapter. A reference isn't needed anywhere for this adapter so it is instanced inline.
20	Use the QueryDriver's Exec() method to execute its query, being sure to cast its result to the type returned by the output adapter.
22	Close the underlying database server connection. This should always be done after you use Walter's QueryFile.Exec() method. This connection does participate in connection pooling so there is very little overhead in reconnecting.

The code below shows how to use the array of ListItems to populate a Windows ComboBox. The ListItem object resides in the System.Web.UI.WebControls class. Therefore, when you use Walter to return an array of ListItems for a Windows app, be sure to add a reference to the System.Web assembly and adding a using state for System.Web.UI.WebControls.

Using System.Web.UI.WebControls
...
comboboxStates.Items.Clear()
comboboxStates.Items.AddRange(ListItems) 
comboboxStates.SelectedIndex = 0

The code above shows how to use the Windows ComboBox's AddRange() method to assign the resulting ListItemArray to the ComboBox. Similiar code is used with the ASP.NET DropDownList control.

BegSr comboboxStates_SelectedIndexChanged Access(*Private) +
                      ent(*this.comboboxStates.SelectedIndexChanged)
    DclSrParm sender Type(*Object)
    DclSrParm e Type(System.EventArgs)

    DclFld li Type(ListItem) 

    li = comboboxStates.SelectedItem *As ListItem
    // li.value = item's value.
    // li.text =item's text.
EndSr

Use the code above to fetch the selected ListItem from a Windows ComboBox. The selected ListItem provides the select text and value for the selected item.

jQuery AutoComplete

Prior to the advent of Ajax, doing look-ups for data entry fields in Web pages was a kludgy, nasty thing. We had to launch a new browser window with JavaScript's klunky Window.Open(), which offered very little in the way of programmatic control (eg, it was quite challenging to position the 'lookup' window correctly.

But along came Ajax and with a little CSS help, we can now do some pretty cool lookups. The downside is that the code required can be quite fiddly and you need to program both the client-side and the server-side. Worry not, Walter's data goodness extends to the Web quite nicely. Another part of Walter, ASNA.Helpers.ASPNET provides a JavaScript interface over Walter's query capabilities as well as the HTTP handler and JavaScript you need to very easily hook up an AutoComplete in your ASP.NET Web apps.

The image below shows the jQuery AutoComplete in action with Walter. The jQuery AutoComplete expects a Json structure with 'label' and 'value' nodes for each entry that matches the search.

The label is the text to display and the value is the associated value to return. For example, when looking up states, the full state name would be the label and the state abbreviation would be the value; or when looking up a customer number the customer name could be the label and the customer number could be the value.

jQuery AutoComplete does all the UI work client side and works with either either HTML elements or ASP.NET controls to define the inputs. The code below shows using ASP.NET controls. In this case, we'll display both values, but in the real world, you'd typically stash the value looked up in a hidden input element.

<asp:TextBox ID="textboxCustomerName"   runat="server" 
             EnableViewState="False" ClientIDMode="Static"></asp:TextBox>
<asp:TextBox ID="textboxCustomerNumber" runat="server" 
             EnableViewState="False" ClientIDMode="Static"></asp:TextBox>

Given the elements above, the entire JavaScript you need to provide to AutoComplete an input field is shown shown below. Lines 1-11 show Walter's JavaScript query interface and the line 13 is jQuery AutoComplete-specific.

var ACCustomerName = new ASNAHelpers.QueryInputArgs();
ACCustomerName.url = "../services/jsonservice.ashx";
ACCustomerName.Library = "examples";
ACCustomerName.File = "cmastnewl2";
ACCustomerName.FieldsList = "cmname:label,cmcustno:value";
ACCustomerName.Rows = 12;
ACCustomerName.Query = "CMNAME >= '{CMNAME}'";
ACCustomerName.addQueryParm("CMNAME");
ACCustomerName.addOption("labelTargetId", "textboxCustomerName");
ACCustomerName.addOption("labelValueId", "textboxCustomerNumber");
ACCustomerName.addOption("showLabelOnScroll", true);

ASNAHelpers.autoComplete.registerQuery(ACCustomerName);

Toggle source code annotation

Line	Description
01	Create an instance of ASNAHelpers.QueryInputArgs. This is the JavaScript object that ASNA.Helpers.ASPNET provides to implement the JavaScript interface to Walter's query engine. You need one instance of this object for each input field that needs AutoComplete. Except for minor syntaxes with the way values are added to the QueryParms, OrderBy fields, and query Option collections, the JavaScript required to create a Walter query with JavaScript is nearly the same as it is to create on server side with AVR or C#.
02	Set the URL of Walter's HTTP Handler ASHX file. By default it's located in the 'services' folder in the Web app's root folder.
03	Set the library for the Walter query.
04	Set the file name for the Walter query.
05	Set the FieldsList for the Walter query.
06	Set the number of rows for the Walter to return.
07	Specify the Walter query selection.
08	Set the query parms for the Walter Query. Walter's AutoComplete expects the first field to be the field for which the value the uses enters to be the lookup value. However, additional query parameters could be provided to further scope the query.
09	Walter's AutoComplete implementation expects at least one one query helper to be provided. The first, which provides the 'labelTargetId' is the element ID of the input element to which you want to attach a jQuery AutoComplete.
10	'labelValueId' provides the element Id into which the selected value should be placed..
11	By default, the jQuery AutoComplete wants to display the values as the user scrolls through them. In some cases, though, it might be preferable to show the label as the values are scrolled. Take a look at the AutoComplete it is showing labels, so it has showLabelOnScroll set to 'true'.
13	Once you've defined the Walter query with JavaScript, line 13 registered that query with the jQuery UI AutoComplete.

Walter leans on jQuery's AutoComplete to do much of the heavy lifting, but Walter does kick in his own JavaScript for the task. Let's see how easy it is. First, use the Component Installer to ensure you get all the binaries, Javascript and other files you need.

jQuery DataTable

Coming soon!

Query Syntax

Walter performs two kinds of queries:

Paged queries.Paged queries use RPG's limits processing. The query parameters specified are directly related to the key structure of the physical or logical file specified. Walter uses the keys specified to set limits on the file. The result set is ordered by the input file's key structure. You can't override the sort order on paged queries nor can you filter the results sets. Paged queries are generally used to produce paged queries for grid elements in Windows or ASP.NET programs. Walter's paged queries provide a built-in "next page" capability to make it easy to scroll the "pages" of paged queries. Because they use the underlying access path, paged queries are very fast.
Simple queries.Simple queries use open query file-like queries. These queries are much more free-form that paged queries. They don't have a dependence on the input file's underlying key structure; you can specify a sort order; you can filter the result set; and, when the queried file is on the IBM i, you can use a variety of open query file's operators (such as %WLDCRD and %SST) to further filter the query. The IBM i's query engine is very capable and while for some simple queries the performance won't be as good as it with paged queries, in many cases simple queries performance is nearly equal to that of paged queries. Simply queries don't generally "page" data, but rather you use query paramters to scope the query results as needed.

Paged queries

Let's take a closer look at paged queries. The file described below is a logical file over a customer master file. It is keyed on customer name (CMName) and customer number (CMCustNo). We'll use it to consider paged queries.

----------------------------------------------------------------------------
File definition: Examples/CMastNewL2
----------------------------------------------------------------------------
Library.......: Examples
File..........: CMastNewL2
Key field(s)..: CMName, CMCustNo

Field name           Data type   Length  Decimals  Description
----------------------------------------------------------------------------
 CMCustNo            Packed          9        0
 CMName              Char           40
 CMAddr1             Char           35
 CMCity              Char           30
 CMState             Char            2
 CMCntry             Char            2
 CMPostCode          Char           10
 CMActive            Char            1
 CMFax               Packed         10        0
 CMPhone             Char           20
----------------------------------------------------------------------------

Let's say you want to provide a paged query with a "position to" feature. The query below gets a 12-row "page" of data from Walter. An annotated code description is below the code.

Using ASNA.DataGate.Client
...
DclFld Name Type(*String)
DclFld Number Type(*Integer4)

Name = "Smi"
Number = 0 
...

DclConst DBNAME   Value("*Public/Cypress")

DclFld db         Type(ASNA.DataGate.Client.AdgConnection)        
DclFld Query      Type(ASNA.Helpers.DataServices.QueryDriver)
DclFld dt         Type(DataTable) 

db = *New AdgConnection(DBNAME) 
Query = *New ASNA.Helpers.DataServices.QueryDriver(db)

Query.Args.Inputs.Library      = "examples"
Query.Args.Inputs.File         = "cmastnewl2"
Query.Args.Inputs.FieldsList   = "CMCustNo,CMName, CMCity,CMState"
Query.Args.Inputs.Rows         = 12

Query.Args.Inputs.QueryParms.Add(*New ASNA.Helpers.DataServices.QueryField("CMNAME",Name))
Query.Args.Inputs.QueryParms.Add(*New ASNA.Helpers.DataServices.QueryField("CMCUSTNO",Number))

dt = Query.Exec() *As DataTable

...
...
...

dt = Query.NextPage()

Toggle source code annotation

Line Number	Description
6-7	The code below directly assigns values to the Name and Number fields, but in a real program these values would be been fetched from a user interface.
10-17	Declare and instance Walter's query object.
19-20	Assign the library and file name.
21	Assign the query fields. Any fields used as query parameters must be declared here. You can optionally provide aliases for any field list in any query.
22	Assign the maxinum number of rows in the result set. This defines the "page" size of a paged query.
24-25	Assign the query parameters and the parameter values. The field names specified here must be provided in the field list specified in line 21. For paged queries, the query parameters can nearly be thought of as a key list that will be used to impose limits for the record selection. You don't have to specify all of the key values as query parameters, but (as you're used to with SETLL or SETGT processing), you can only omit fields from the end of the key list.
27	Execute the query. This fetches the first page of results. This example is using Walter's default DataTable output adapter. However, you can use any output adapter with either paged or simple queries.
33	With the query object still in scope (or having been 'refreshed' with its QueryState variable--see the ASP.NET section for more on this) you can fetch subsequent pages of data with the NextPage() method. The NextPage() method is valid only with paged queries. An exception is thrown if you use it with a simple query. The Walter.Web.Demo and Walter.Windows.Demo both show NextPage() in action.

Simple queries

Coming soon!