Creating a textfield or label with sql

[jmendes]

Hi,
Is there a way that I can create a Genie textfield of label with an SQL statment. Something like a combo box but in a simple label.

The main goal is that I have a field in a 5250 screen an I'd like to get related data to show in Genie without having to touch code in RPG or the screen it self.


Thanks,
João Mendes

[David]

SQL can be used with only certain widget types -- a drop down, text box (auto complete), chart, or a grid.

There is not currently the capability to fetch a record for display in a label.

[jmendes]

Thank you David.

I'll put the field in the screen :(


Thanks
João Mendes

[Scott Klement]

If you can't modify the screen, something like this could be done with a CGI program, right?

[Scott Klement]

For example, you could write an RPG program as follows. I called mine LABELDATA and put it in a library named SKTEST:

Code: Select all

      *  To compile:
      *>     CRTRPGMOD LABELDATA SRCFILE(QRPGLESRC) DBGVIEW(*LIST)
      *>     CRTPGM LABELDATA MODULE(LABELDATA) BNDSRVPGM(QHTTPSVR/QZHBCGI)

     D QtmhWrStout     PR                  extproc('QtmhWrStout')
     D   DtaVar                   32767A   options(*varsize) const
     D   DtaVarLen                   10I 0 const
     D   ErrorCode                32767a   options(*varsize)

     D Err             ds
     D                               10i 0 inz(0)
     D                               10i 0 inz(0)

     D CRLF            C                   x'0d25'
     D data            s          32767a   varying

      /free
          data = 'Content-type: text/plain' + CRLF
               + CRLF;
          QtmhWrStout(data: %len(data): Err );

          data = 'This data goes in the iframe.';
          QtmhWrStout(data: %len(data): Err );

          *inlr = *on;
      /end-free

So the 'Content-type:' line tells the Apache HTTP server that the data is plain text. The line ends with CRLF (hex 0d25). Then I insert an empty line by sendign another CRLF, this tells Apache that I'm ready to begin the actual data. Then, I send a line of text that simply says "This data goes in the iframe". You can write whatever data you want here... it could be an SQL statement to read a database, or it could just be data calculated somehow in your RPG program. The point is... you can calculate any data you like, and it's not that much more difficult than it would be to run an SQL statement.

You'll need to tell Apache that it's okay to call programs in this library by adding the following to your Apache config file:

Code: Select all

ScriptAlias /sktest /QSYS.LIB/SKTEST.LIB
<Directory /QSYS.LIB/SKTEST.LIB>
   Order allow,deny
   Allow from all
</Directory>

My library is named SKTEST. You can name yours whatever you wish by making the appropriate changes to the config, above.

That can be added to a PROFOUNDUI config file if you like. Just add it to the end -- and remember that each time you reinstall ProfoundUI you'll need to go into the advanced options and tell it not to replace your httpd.conf with the one from the installation.

Once you've added those Apache config statements, you need to restart your Apache instance for the changes to take place.

Now in Genie, you can go into the "Containers" widgets in Genie and drag an iframe widget to wherever you want to show the extra label. You can also set the size of the widget, color, font, etc at this time.

iframe1.png (19.01 KiB) Viewed 1720 times

iframe2.png (16.87 KiB) Viewed 1720 times

The most important property, however, is the "iframe Url" property. This is how you tell it which program to call on the server. In my case, my ScriptAlias starts with /sktest, so the URL to call my program must also start with /sktest. The program's name is "labeldata" so using IFS syntax, the program name should be "labeldata.pgm". So my URL would look like this.

iframe3.png (5.24 KiB) Viewed 1720 times

As always with Genie, you have to make sure you set a screen identifier, and save your changes to the server. Once it's all been done, the label will show up on your screen with the data from your program.

I stuck mine on the IBM-supplied command entry screen

iframe4.png (17.99 KiB) Viewed 1720 times

That may seem like a lot of steps -- but once you've done one program, they're really easy (because you only need to update the Apache config once.) It's a really easy way to add data to a screen without having to change the underlying program -- which is especially useful if you don't want, or can't, change the existing display file.