SQL-based Grid specifying QTEMP library and Date Formatting

[Shon]

I have 2 issues:
1. I created an SQL-based Grid. It specifies file (DISHSTPW) in the database file property and the fields to be used. My program does not open the Display File until after the program has created the file (DISHSTPW) to be used in QTEMP and overridden the file to QTEMP. When the program runs, it does not seem to populate the SQL-based Grid with the data in QTEMP/DISHSTPW, but rather from data in the next library in the library list. If I specify the library (QTEMP/DISHSTPW) in the database file property, then the program loads nothing. I have checked and there is indeed data in the file at the time that the display file is opened.

2. This same SQL-based Grid displays dates and times in ISO format. Is there a simple way to override this to USA format. Normally I would set that on the individual field properties, but since an SQL-based Grid doesn't have individual field properties, I am unsure how to change it.

[Scott Klement]

Hiya Shon,

Profound UI performs the SQL queries in a separate job from your RPG program. So your RPG program is running one job (the applicaton job) and the SQL query is running separately ("the SQL query server job").

The QTEMP library is a special library that is separate for each job. So something in QTEMP in one job will not exist in QTEMP in another.

Consequently, your scenario won't work. The file generated in QTEMP in the application job won't exist in the SQL job, so you end up with nothing working. Putting an override in the RPG program also will not work, since overrides do not affect other jobs, they only affect the job they are running in.

To solve your problem, you could do one of the following:

• Instead of an SQL data driven grid, you could use a normal subfile grid. This way, the RPG program is doing the database logic, and therefore it's running within the same job, so there's no problem with QTEMP
• or, you can use a "normal" library instead of QTEMP. In this case, the SQL statement will work fine. But, here you have to watch out for conflicts with other jobs rebuilding the file, unless you use a unique filename.

Let me know if this helps!

[Shon]

I wasn't aware that they ran in seperate jobs. Thank you for the info. I understand now about why it won't work. I am still curious about the answer to my second question. Is there a simple way to override the date and time format for an SQL-based grid?

[Scott Klement]

I'm sorry... I missed your 2nd question, somehow. Unfortunately, we do not currently have a way to set the date/time format globally. I think I'll enter that into our system as a feature request. I can't promise that the team will agree -- but I'd like to see it as a feature.

For now, you can use the SQL CHAR() built-in function to control the date format.

For example, I have a table with a date-type column named DT. I can code CHAR(DT,USA) for that column and the SQL engine will convert it to USA format.

charDate.jpg (29.04 KiB) Viewed 1002 times

Or, you could do the same thing, but use the "custom sql" property. This might make it easier to understand?

charDate2.jpg (40 KiB) Viewed 1003 times

Does that help?

[Shon]

It does help thank you.

[David]

For the next update to Profound UI, we have added the ability to configure the date/time format and separator globally for database-driven components.

For different formatting on individual widgets, the technique Scott recommended here can be used.