Information Systems:Debugging SQL statements in WebSmart programs

From uniWIKI
Revision as of 14:31, 17 November 2016 by Norwinu (talk | contribs) (Created page with "=Overview= (~~~~): One of the most frustrating things I've experienced thus far while developing in WebSmart is getting the desired SQL statement to run correctly. I've exper...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Overview

(norwizzle (talk) 13:31, 17 November 2016 (PST)): One of the most frustrating things I've experienced thus far while developing in WebSmart is getting the desired SQL statement to run correctly. I've experienced many SQL problems related to:

  1. Inherent limitations or quirks with the DB2 flavor of SQL (DB2 SQL).
  2. Related to the previous point - syntax differences in DB2 SQL.
  3. Quirks with the DB2 database itself (object permissions, data permissions, object locks).
  4. System authority permissions (WebSmart runs as user QTMHHTTP).
  5. Ditzy syntax errors in the SQL statement itself.
  6. Having to work with a cursor (SQL Cursors is a slightly more advanced SQL topic).
  7. The idiosyncrasies of PML to RPG translation.
  8. The SQL PML functions (sqlexec, sqlquery).

This article outlines some of the ways to prevent pulling out your hair in the case a compile fails or your SQL program produces 0 or erroneous results.

Tips and notes

Writing out the sqlcode

The program field pgmf_sqlcode can be your best friend. Inserting a wrthtml(<field name=pgmf_sqlcode>) at different points within your program logic can reveal the problem fairly quickly. This will report the sqlcode of the most recent query, so place it soon after you run one.

Writing out the SQL statement

The SQL statement will likely be stored in a string and will likely undergo a few string operations (concatenating where clause etc.). Output this string to see the final statement that is run.

Running the statement manually

WebSmart has a tool to run SQL statements, but you can also use the 'Run SQL Scripts' utility within IBM i Access Client Solutions. Doing this will help confirm the validity of the statements. Keep in mind, WebSmart programs run under either the production or development environment, and SQL will query tables accordingly. Ensure that the statement being tested references the same library as the one that will be referenced at runtime.

Checking the job log

Another very insightful area to help debug efforts is the job log. It might be a pain hunting down the particular job that ran your program request (the loading of the web app you're on), but once you do, press F10 to display details and F6 for bottom.

Checking out the generated RPG

Sheila pointed out that the RPG generated by WebSmart was uninterpretable garbage, but somewhere along some recent version, it got pretty good. Looking at the RPG sources (in libraries WEBPRDP or WEBDEVP), it all seems to be done now in free-form RPGLE, with extensive comments in the code! A lot of it is actually readable and insightful. The RPG source will always have the same name as the WebSmart program (PGM) that is generated.

Retrieved from "https://owl.unipharm.com/mediawiki/index.php?title=Information_Systems:Debugging_SQL_statements_in_WebSmart_programs&oldid=5752"