Tips for IBM i Access for Web SQL Request URLs Using Date Parameters

The first key to creating a database request that runs as a URL is to make sure that you do not use the SQL Wizard to create the request. Any request created by using the SQL Wizard with parameters always prompts the user to enter the parameter information.  You cannot provide the parameter data as part of your URL. If you need the Wizard's assistance to create the SQL statement, you can use it, copy the SQL statement out of that request and paste it into a new one.

The document, URL Interfaces to IBM i Access for Web that is on the Resources tab for the IBM i Access for Web product website explains how to create a URL to execute an SQL request and provide parameter data.

As an example, I created a simple table with an integer primary key, a character column, and a date column:

CREATE TABLE MIKSWENS.DATQRY(PK INT, NAME CHAR(75), BDAY DATE, PRIMARY KEY(PK))

I can create an SQL request with the SQL statement:

SELECT NAME FROM MIKSWENS.DATQRY WHERE PK = ?

Save it with the request name SelectByKey then create a URL to run the request and return the name where the primary key value is 1:

http://rchesp2.rchland.ibm.com:22220/webaccess/iWADbExec?request=SelectByKey&iwaparm_1=1

It gets more difficult when you try to pass in a value for the date column though. The reason for that is that the date format you use must match the date format used by the underlying JDBC connection (which is defined in the SQL request). If you do not modify the JDBC connection, the default date type is MDY. Let's use that same table again and this time we save an SQL request for the statement,

SELECT PK, BDAY, NAME FROM MIKSWENS.DATQRY WHERE BDAY >= ?

Saved the request with the name GTBday. I can create a URL like this and it works:

http://rchesp2.rchland.ibm.com:22220/webaccess/iWADbExec?request=GTBday&iwaparm_1=01/01/50

If I want to provide the date in ISO format:

http://rchesp2.rchland.ibm.com:22220/webaccess/iWADbExec?request=GTBday&iwaparm_1=1950-01-01

Then, the query fails with an error: SQL request failed: Data type mismatch. (1950-01-01)

To allow the use of ISO dates, we have to create a JDBC connection that specifies an ISO date format, then edit this request to modify the JDBC connection that it uses and save it.
We start this process by clicking 'Customize' in IBM i Access for Web, then select 'Policies', then click 'Edit Policies' for the default user.
Click the edit link next to 'Database connections'.
Click 'Copy' next to the connection and name it ISODateConnection where it asks for 'New connection:' and add the following to the end of the JDBC URL: date format=iso; Finally, click 'Save Connection'.
Now click the database then 'My requests' and click the edit link next to the GTBday request.
Change the Connection to use the new connection we created, ISODateConnection, and click 'Save Request...' name the new request GTBdayISO.

Now we can create a URL to run this SQL request passing it a date in ISO format:

http://rchesp2.rchland.ibm.com:22220/webaccess/iWADbExec?request=GTBdayISO&iwaparm_1=1950-01-01