Automating query

You can use VB to query AS400 data--I do it all the time.  If you use ODBC, you'll need to make sure that you have the iSeries drivers installed on your development machine as well as the machine that will be running the app.  Otherwise, I just use OleDB connection strings.

could you please give an example of how to get the data using VB?

Query/400. Currently I run them manually with WRKQRY cmd then export the result to Excel with the Add-in that comes with the iSeries package

What data changes do you need to make?  Just change the date to the current date, or is it more complex?

You have 2 options:
1. you can use configure the query in excel with VB, and have the excel run the query on the AS400 and refresh the data.
2. On the AS400 you can call the query from a program, using the program to change the date parameters. This assumes that the date in the query is a function the the date (last month)

@Gary: for now I would like the program to be able to change the date on the query to the date of previous month, run the query, and give me a result. So let's say now is August I would like the program to be able to change the date in the query to something like:

INVDT RANGE 20110701 20110731

so I will get the date from July, then it will give me results

You mentioned that you aren't too familiar with the AS/400.  Get someone that is to help you modify the query (it will be stored in a source member and can be edited using whatver programmer editor you prefer: SEU, Rational, etc.).

SEU has excellent built-in help.  The STRSEU command can be used to start SEU and load the source member you created that contains the QMQRY source code.

Once in the editor, position the cursor in various areas of the screen and press F1 for context-sensitive help.

- Gary Patterson

Technically, Query/400 does allow you to specify external variables such as dates. There are a couple considerations.

First, the query must be defined as a "dependent" query. The given date or other selection value is then a "dependent value".

But then, the RUNQRY command cannot run the query unless the RCDSLT(*YES) parameter is used and the dependent value is supplied that way. Unfortunately, that's usually not what's needed since that's an interactive action.

So, instead of RUNQRY, the STRQMQRY command must still be used with ALWQRYDFN(*YES), and the dependent value is supplied with SETVAR() just as a normal QM query would be.

Of course, it's likely that the better course is to convert the queries to QM queries and simply give up the old Query/400 product entirely.

Tom

Sorry guys, I'm still trying to understand this. I never worked deep with AS400 so this is all new to me. I still don't understand the function of sourcemember (QDDSSRC), etc. Any good source that I can read to understand the basic of AS400?

A "source physical file" (or source file) is a specialized DB2/400 database file designed to hold source code for programs, scripts, text, compilable SQL statements, etc.  Source physical files usually contain multiple sub-components called "members".  Each member typically contains the source code for one object (a screen, a file, a program, a script, etc.).  Source physical files can be stored in any library, and can be named any way you like, but there are some very common standard names:

QDDSSRC (Data Description Source Code)
QRPGLESRC (ILE RPG Source Code)
QCLSRC (Command Language Source Code)
QTXTSRC (Text - often documentation)

To compare to a PC file system, the source physical file is sort of like a specialized folder that can only hold text files, but nothing else.  The source members are like individual text files containing source code.

So: MYLIB/QSQLSRC(MYSCRIPT) on the AS/400 refers to the source member MYSCRIPT in source file QSQLSRC, which is in library MYLIB.  

The equivalent on Windows might be:

c:\myfolder\SQL Source Code\MYSCRIPT.SQL

There are lots of good AS/400 primers (disclaimer: I wrote one of them, so I'm biased!):

http://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Daps&field-keywords=iseries+and+as%2F400+operations&rh=i%3Aaps%2Ck%3Aiseries+and+as%2F400+operations&ajr=0

- Gary Patterson

"I run them manually with WRKQRY cmd then export the result to Excel with the Add-in that comes with the iSeries package . "

There is an Excel add-in that allows you to query i-series data direct. maybe you could use that instead of WRKQRY.
If I can find a link I'll add it.

In case ASmith_SBS can't find the links:

Excel Add-in for V5R4 and earlier: http://www-03.ibm.com/systems/i/software/toolbox/perfhints.html
Excel Add-in for V6R1 and later: https://www-304.ibm.com/support/docview.wss?uid=nas14921b1637a7c7e7f86257495006f8373

- Gary Patterson