What You Don’t Know About SQL Won’t Hurt You
July 14, 2014 Dan Burger
|
Not all IBM i programmers speak SQL. It’s not even close, but it is, by most accounts a growing number. You don’t have to look under rocks to find programmers focused on leveraging SQL whether they are building applications for the Web or for green screens. This new product from Cozzi Productions, called Query File, should catch the attention of programmers regardless of their SQL skill level. It’s sort of a two-trick pony. One trick is for the people who know Query/400, but are strangers to SQL. The other trick is for those who know SQL but are pretty clueless when it comes to Query/400. That’s the overview, but it’s also just the beginning of the story. The main benefit is that Query File provides the capability to use standard SQL to do a query, then save it, store it in a CL program, or put it behind a menu option and reuse it to get reports, inquiries, or other outputs without making changes to it or rewriting an RPG program each time you want to use it. “This is an SQL tool that lets you specify the type of output you want,” explains Bob Cozzi, owner of Cozzi Productions. “It could be a normal interactive display, a 132-column report, but it could also output to PDF, a plain text file, or CSV, which is used for Microsoft Excel output. Query File includes two CL commands. The first is RUNQRYF (Run SQL using Query File), which allows users to specify any database file name and generate output. The other command is QRYF (Query File), which unlocks the SQL language to the user. RUNQRYF is designed with novice SQL users in mind. It’s built to store ad hoc reports/inquiries behind a menu option. QRYF allows adept SQL users to use it interactively or embed it in a CL program. Think of RUNQRYF as the basic command. It uses the power of SQL to retrieve and create its output. It includes several optional parameters to customize the output–specifically the capability to specify fields, select or omit criteria, and choose output sequence. When inserted behind a menu option, it can provide ad hoc reports. The advanced QRYF command allows users with intermediate or advanced SQL skills to specify full SQL statements–such as SELECT, INSERT, and UPDATE–including subqueries from Command Entry or within a CL program. Although QRYF was designed for more sophisticated SQL users, it can also be used within CL programs to perform database modifications, generate reports, produce PDFs, display database inquiries or generate other files using SQL. Query File’s OUTPUT parameter permits interactive or print capabilities and the same query to create a PDF, an ASCII text file, or another database file. For those who are familiar with Cozzi’s rants about the green screen being the downfall of the IBM i, the green-screen technology that is the foundation of Query File will come as a surprise. “While that is sort of a taboo subject,” he says, “the harsh reality is that are still a large number of green-screen applications being used in virtually every shop, every day. So not everything can be Web-based right out of the box. Eventually we intend to get there, but right now we just want to solve this particular problem.” Query File runs on IBM i 6.1 and newer versions of the operating system and is currently available for $2,449. RELATED STORIES Copy Source Members Between Systems Without FTP Encoding XML (Or HTML) From Within RPG What’s Happening In IBM i App Dev? Write Your Own SCANRPL Built-In Function
|
