HOWTO: Using OPNQRYFILE (Open Query File) in AVR

The information in this article applies to:

• ASNA Visual RPG, Release 3.5 and Higher
• Acceler8DB, Release 4.7 and Higher
• DataGate/400, Release 4.7 and Higher
• DataGate for SQL Server (DSS), Release 5.0 - Requires ASNA Visual RPG Release 4.0 and Higher
• Microsoft SQL Server2000
• Windows

SUMMARY:

This article will discuss a way in to use ASNA's Open Query File (OPNQRYFILE) to allow users to create Queries on Acceler8DB, SQL Server2000, and iSeries database files.

NOTE: Figure 1 below shows the Acceler8DB's Open Query File Dialogue.

Figure 1

MORE INFORMATION:

NOTE:  These methods listed below are examples.  There might be other methods in which this feature can be incorporated into an AVR application that is not mentioned in this article.

Requirements: 

• A F-Spec (DclDiskFile) defined to accept required OPNQRYFILE Parameters (e.g. QRYKYFLDS, QRYFILENAME, QRYSELECT)

Database Query Fundamentals:

Acceler8DB creates Query files in QTEMP. Understanding how Acceler8DB creates Query files in QTEMP, will allow you to create complex Open Query File search arguments (i.e., Select / Omit Expressions) quickly.

NOTE: using QTEMP within DSS is not supported, so you can not work with these steps when using SQL via DSS.  You can utilize SQL Query Analyzer to determine the WHERE statement, which is the QRYSELECT portion within your AVR Program. Review Figures 6 through 8 for this.

To access the Query Options, to perform a manual Query, go to:

1. Acceler8DB Manager;
2. Open your Database (e.g. "ASNA Local DB");
3. Double-Click on your File's Member to open ASNA's File Editor.

Once the File Editor is opened, go to

1. The File menu;
2. Select Open Query File Read Only (we don't want to modify any records).
3. Browse to the File and Database you wish to perform the Query, then click OK.
4. Figure 1 should now appear.

NOTE: Figure 2 below shows a way in which someone can get the files with a Customer Number between the ranges of 400 - 1000.  Figure 3 shows the results.

Figure 2

Figure 3

While having the Query results on the screen, you can go to QTEMP in Acceler8DB Manager.  There will be a file in QTEMP with a Unique Name that corresponds to the file that the Query created.  You can view the Select/Omit Expression (See Figure 4).

Figure 4

Create multiple Queries on different types of data types (e.g. Packed, Character, ISO Date, etc.) to see how the Select/Omit is specified in the file created in QTEMP.  Understanding the Select/Omit Expression will be key in creating them dynamically through an AVR Application. (See  Figure 5)

Figure 5

Figure 6

Figure 7

Figure 8

Query Example Program details:

This example assumes a user has the ability to specify the type of Query to obtain the data from Acceler8DB or DataGate/400.

NOTE: Figure 9 below shows how a form can be designed to allow users to create their Queries.

Note there is an I/O Field allowing the user to specify their Search String.  Also note there are three buttons, one to allow the user to Cancel or Unload the form, one to open the file, and one to load the subfile.  These two buttons (one to Open and one to Load) can be simplified to have it open the file once the Query parameters have been specified, then load the subfile based upon those records.

Figure 9

Note Figure 10 and Figure 11 show the settings for the Subfile.

Here is the code that is needed for the user to be able to Query the Acceler8DB file's records in this example (See Figure 9).

Note that the QRYFILENAME uses *UNIQUE to create unique names.  This will guarantee that there will be no duplicate file names in QTEMP.  You will receive errors if there are duplicate file names in QTEMP. 

Note that the file is Opened Explicitly by the User (i.e., User Controlled or IMPOPEN(*NO)).  This is a requirement for Open Queries, otherwise, the file will be opened without the QRYSELECT Parameter will be disregarded.

NOTE For AVR 4.0 and higher Developers: Notice that there is NO Random DB Processing when we changed the Keys at runtime by specifying the QRYKEYFLDS on the F-Spec..  This application just reads the file.

DCLDISKFILE NAME(SalesQry) TYPE(*INPUT) ORG(*INDEXED) IMPOPEN(*NO) DclFileCont QRYFILENAME('*UNIQUE') // Create Unique names in QTEMP DclFileCont QRYSELECT(('CMNAME = "' + Iofield1.Text + '" ')) DclFileCont QRYKEYFLDS('CMNAME') Random(*NO) DclFileCont DBDESC('ASNA Local DB') DclFileCont FILEDESC('*LIBL/CMMASTERL2') BEGSR NAME(btnOpen) EVENT(Click)        If SalesQry.IsFileOpen = '1'          Close SalesQry        Endif     OPEN FILE(SalesQry) ERR(*IN90)        If *IN90 = '1'          Msgbox 'File Could not be Opened'        Else          Label2. visible = 1          Label2 = 'File''s Open..Load Subfile'        Endif ENDSR  BEGSR NAME(btnLoad) EVENT(Click)     rrn = 0     Subfile1.ClearObj     label2.visible = 0     *in99 = '0'         DOWXX F1(*IN99) RELOP( = ) F2(*OFF)           READ FROM(SalesQry) EOF(*IN99)              IF *IN99 = '1'                LEAVE               ENDIF            ADD   F2(1)   RESULT(RRN)           WRITE   FILE(SUBFILE1)       ENDDO      Read   SalesQry    EOF(*IN01) ENDSR  BEGSR NAME(btnCancel) EVENT(Click)     Unload form1 ENDSR

TIP:  DataGate/400 and the OS/400 prefer the use of "named qualifiers" in the comparison expression.  For example, use "*NE" or "*GT" instead of "=" and ">", respectively.

TIP: We recommend the use of QRYFILENAME('*UNIQUE') when working with Web Applications.  Web Applications run under one user, the user that is specified in Acceler8DB Work with Database Names dialogue.  Web Applications usually store Runtime Errors and MsgBoxes in $$AVRERR.TXT.

TIP: Accessing SQL Server via DSS requires the use of apostrophes: ' not double qoutes: " when specifying a character string. In addition, SQL requires the use of the words "AND", "OR", "NOT", not the use of "*AND", "&", "|", "*OR", etc...

To help understand the QRYSELECT at work in this example, create a String Variable that will contain the expression and use a MsgBox (See Figure 9) to display the contents of the String Variable.  The use of the MsgBox to see the final output of the Query String will also help in troubleshoot how to form your QRYSELECT syntax:

DCLDISKFILE NAME(SalesQry) TYPE(*INPUT) ORG(*INDEXED) IMPOPEN(*NO)  DclFileCont QRYFILENAME('*UNIQUE')  DclFileCont QRYSELECT(MySelectVar)  DclFileCont QRYKEYFLDS('CMNAME')  Random(*NO)  DclFileCont DBDESC('ASNA Local DB')  DclFileCont FILEDESC('*LIBL/CMMASTERL2')  DclFld  MySelectVar  Type(*String)   MySelectVar = ('CMNAME = "' + Iofield1.Text + '" ')   Msgbox MySelectVar  // We can use this for testing the QRYSELECT Output   Open SalesQry   // More code here

Figure 12

Various QRYSELECT Examples:

• Query on Packed Field:

DCLDISKFILE NAME(SalesQry) TYPE(*INPUT) ORG(*INDEXED) IMPOPEN(*NO) DSG(*FULL)  DclFileCont QRYFILENAME('*UNIQUE')  DclFileCont QRYSELECT("(CSCUSTNO = " + Iofield1 + ")")  DclFileCont QRYKEYFLDS('CSCUSTNO CSYEAR') Random(*NO)  DclFileCont DBDESC('ASNA Local Db')  DclFileCont FILEDESC('*LIBL/CSMASTERL1') // DclFileCont QRYSELECT('CSCUSTNO = ' + Iofield1.TEXT) // SQL Syntax for numbers   /* This MsgBox example below show two different types of Queries based upon how the QRYSELECT is constructed.  You can play with the use of quotes ( " ) or apostrophes ( ' ) in the QRYSELECT to get a feel for how the final Query will be constructed. */ MsgBox msg('(CSCUSTNO = " + Iofield1 + ")') //Shows the type of Query requested BEGSR NAME(btnOpen) EVENT(Click)        If SalesQry.IsFileOpen = '1'           Close SalesQry        Endif       OPEN FILE(SalesQry) ERR(*IN90)         If *IN90 = *On             MsgBox 'File Could not be Opened'         Endif   // More code here

• Complex Query using an  ISO Date Range and a particular User:

// Example Code for Query in Web Application // Query will be based upon the Date Range and User Specified DclDiskFile NoteHeader Type(*Input) Org(*Indexed) ImpOpen(*No) +                DbDesc('*Public/TSAR') +                FileDesc('NoteHeader') +                 RnmFmt(RNoteHeader, RDiskNoteHeader) +                QryFileName('*UNIQUE') +                QrySelect(SelectExpression) +                QryKeyFlds("AccNum *Ascend NoteType *Ascend NoteDate *Ascend NoteTime *Ascend") Random(*NO)  DclFld SelectExpression Type(*String)  DclFld DateRange        Type(*String)  DclFld UserList         Type(*String)  DclFld HTMLStartDate    Type(*String) Scope(*Public) // Given in ISO Format  DclFld HTMLEndDate      Type(*String) Scope(*Public) // Given in ISO Format  DclFld HTMLUser         Type(*String) Scope(*Public) // Result are all characters BEGSR MyQuery  Scope(*Public)      UserList = "("                                                            // NoteUser is a Database File Field, CHAR      UserList = UserList + "NoteUser = '" + HTMLUser + "'" // HTMLUser specified by User      UserList = UserList + ")"                  // NoteDate is a Database File Field Name, in ISO Format      DateRange = "(NoteDate >= '" + HTMLStartDate + "' & NoteDate <= '"            If (HTMLEndDate <> *Blanks)               DateRange = DateRange + HTMLEndDate + "')"            Else               DateRange = DateRange + HTMLStartDate + "')"            Endif      SelectExpression = DateRange + "&" + UserList // QRYSELECT Expression after it is built          // We will use this for troubleshooting the problems if they arise. // Msgbox SelectExpression  Title("Select Expression") // Msgbox DateRange         Title("DateRange") // Msgbox UserList          Title("UserList")      Open NoteHeader Err(*IN99)           If *IN99 = *On      Response.write("<p>Error opening file. Please Try again or contact your Application Admin.</p>")         // This error will be logged in $$AVRERR.TXT file in ASNA Shared      Msgbox msg("Error on NoteHeader Open: " + *Err.Description) title(*Err.Source)      Close  NoteHeader      Return           Endif      Read NoteHeader      // More Code Here      Close NoteHeader // We will close the file so we can create a new Query ENDSR

• DataGate for SQL Server Syntax - Basic Example:

• DataGate for SQL Server Syntax - More Complex:

Other ASNA KB Articles:

• INFO: Difference in selection processing in Acceler8DB and DataGate400
• Consecutive vs. Random Access for Disk Files

Other IBM Articles:

• OS/400 OPNQRYF (Open Query File)

Keywords: develop, example, IDE, OPNQRYFILE, Query, Select, Omit, Queries, QRYSELECT, QRY, Random, DSS, SQL