Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL)

[Guest.Visitor]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
I have been using FROG for the iSeries for the past year and love it. Check it out at http://www.innovativesys.net/. No web server required. No SQL required on your AS/400 and more. It is a GREAT tool and it's free.

[Guest.Visitor]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
Hi Steven, Great program .... All the best - Jan

[S.Goetjen]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
Yes, I have tried Frog, WinSQL and Toad. They are all great products, but I think you still need IBM Client Access (ODBC) to connect. STRCGISQL does not.

[deangriffiths]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
Love it - only issue I cannot resolve - if I have a record with a date value '0001-01-01' - nothing gets returned. Anyone any ideas?

[S.Goetjen]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
The problem is that IBM’s SQL can not display dates out of the range of 1940 – 2039. You can see this when you run the same select statement under the STRSQL command and the dates are displayed as “++++++”. The fix involves changing the FixTheNulls subroutine (new subroutine is posted below) to update the 0001-01-01 dates to 1940-01-01. Unfortunately, your output will display 1940-01-01. Good Luck, Steve <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6b3cdbf9/5')

[Guest.Visitor]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
By default when I create a directory on the IFS, it is created under the directory structure: /home/PEC1234/MFGPECCGI/HTML. Programs are in library MFGPECCGI. How does this affect the HTTP config. Currently when I run STRCGISQL it displays the prompt screen. When I type a 'select * from F4101' and press Enter, I get 'Page Cannot be Displayed'. The HTTP config: 12 ScriptAlias /cgi-bin/ /QSYS.LIB/MFGPECCGI.LIB/ 13 Alias /html/ /home/PEC1234/MFGPECCGI/html/ Any help would be appreciated.

[S.Goetjen]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
Since you are getting the prompt, the HTTP configuration is probably correct. But, the executing program should return the same URL with the result. Take a look at the URL on the 'Page Cannot be Displayed' browser and see if it matches the initial URL. If not, look at the GetURL function in the program it is not rebuilding your URL correctly. A work around is to hardcode your URL (http://yourserver/cgi-bin/strcgisql.pgm) instead of using the function and recompile. Also, Make sure that the authority to the PSCSPARM file is *PUBLIC *ALL Good Luck.

[Guest.Visitor]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
Hi Steve I've typed "select * from library/file" It comes out "* * * SQL SYNTAX ERROR * * *" And the "/" comes out as a box character How to settle this? thankshttp://www.mcpressonline.com/images/fbfiles/files/6b3cfc2b_core.GIF

[Guest.Visitor]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
Araman wrote: > I've typed "select * from library/file" > It comes out "* * * SQL SYNTAX ERROR * * *" > And the "/" comes out as a box character > How to settle this? Araman, Try using the SQL syntax instead of the System syntax: Select * from library.file Bill

[Guest.Visitor]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
Hi Bill Thanks for the reply I've tried that also, "SELECT * FROM LIBRARY.FILE" Still => * * * SQL SYNTAX ERROR * * * But this time the "." doesn't get changed to the box character. How? Regards Aramanhttp://www.mcpressonline.com/images/fbfiles/files/6b3cfddd_Domino.GIF

[Guest.Visitor]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
Hi RD Thanks for the reply. I agree. It is something to do with the CCSID. I've experience it before. But never become familiar with it. Yes, you are right!, I've checked the PSCSPARM. The "/" was changed into other invalid character. I've checked the iSeries system value, it is already 65535. Not sure about the other CCSID (compiled, batch). Do I need to do some modification on the STRCGISQL program? (Steve?) Is it possible that the IE or the Windows settings has to do with this? I tried on many PC, still have the same result. (eg: Keyboard) Regards Araman

[Guest.Visitor]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
Pardon my lack of knowledge in this area. I guess you are right. Anyway, to narrow things down, I've checked the PSCSPARM. The CCSID is 37. Tried to change it to 65535 but still not successful. Regarding recompiling the program with CCSID 37, I didn't find any parameter in the CRTSQLRPGI or CRTPGM that has CCSID mentioned to be change. (Do correct me..) But the compilation log of CRTSQLRPGI mentioned : "Source file CCSID.........37 " "Job CCSID.................65535 " and CRTPGM mentioned : "CCSID . . . . . . . . . . . . : 37" DSPFD of PSCSPARM mentioned: "Coded character set identifier . . . . . . : CCSID 37 " I'm not good at reading the hex value (never use it for troubleshooting before). Please refer to DSPPFM. Any clue? Anything else I can do? Thanks RD Araman. <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6b3cdbf9/14')

[Guest.Visitor]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
Wow! You sure know this stuff very well. It looks like I'm out of luck. (Just when I about to get excited about it!) I'm on V5R2. Eventhough we have V5R4 on another machine, the CRTSQLRPGI compiler is not installed. Well, so much for that.(Giving up already?) Wondering if the STRCGISQL compiled (with LOCALETYPE(*LOCALEUTF) & TGTRLS V5R2)in your machine can be saved in a SAVF (with a V5R2 TGTRLS) and I'll try to restore it on my V5R2. See if it works.. (Wonder if its only works on V5R4!).. Thanks again RD, Araman.

[S.Goetjen]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
The HTTP server converts the ascii characters to ebcdic using the QEBCDIC and QASCII conversion tables. This table defines the ebcdic equilvent to the Ascii "/" slash character. To correct this, you would have to change the conversion table to convet the "/" character to the correct x'61'. You can check to see if this is the problem by retreiving the the conversion table source. RTVTBLSRC TBL(QEBCDIC) SRCFILE(QGPL/QCLSRC) Now use SEU to edit the source file members created by RTVTBLSRC. Check the value in Line(2),position(31),length(2). It should be '61'. If you think that this is the problem then you should work with your system admin to change/create the conversion table. Warning: Don't change these tables without fully understanding them. Another (safer) work around would be to set your library list in the STRCGISQL program (via a CL command call) to avoid using the library (and the /) in your SQL statement.

[S.Goetjen]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
The HTTP server converts the ascii characters to ebcdic using the QEBCDIC and QASCII conversion tables. This table defines the ebcdic equilvent to the Ascii "/" slash character. To correct this, you would have to change the conversion table to convet the "/" character to the correct x'61'. You can check to see if this is the problem by retrieving the the conversion table source. RTVTBLSRC TBL(QEBCDIC) SRCFILE(QGPL/QCLSRC) Now use SEU to edit the source file members created by RTVTBLSRC. Check the value in Line(2),position(31),length(2). It should be '61'. If you think that this is the problem then you should work with your system admin to change/create the conversion table. Warning: Don't change these tables without fully understanding them. Another (safer) work around would be to set your library list in the STRCGISQL program (via a CL command call) to avoid using the library (and the /) in your SQL statement.

[Guest.Visitor]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
The HTTP server converts the ascii characters to ebcdic using the QEBCDIC and QASCII conversion tables. Are you sure? TCP/IP sockets normally use the QTCPEBC and QTCPASC tables. (HTTP normally uses port 80 or 443). Just asking... Chris

[Guest.Visitor]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
you need to have the same date format that your file has let's say you're keeping your date in your file in *ISO then if you're using interactive sql, start it with STRSQL DATFMT(*ISO), or if you're using embedded sql in an rpg program, try this before executing any sql statement C/exec sql C+ Set Option C+ Datfmt = *ISO C/end-exec and if you're using another method, try locating the parameter to change the date format martin

[Guest.Visitor]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
Hi RD After few days, I decided to come back to the source. Based on your advice, (refer the code) 1) I've duplicated few lines of code that remarked "Clean the less-then character (<) and replace it with its replacement &lt" 2) Place it before "Eval X_SQLSTM = SQLSTM" 3) Replace "RtnStr" with "SQLSTM" 4) Replace "<" with "x'2f'" 5) Replace "&lt" with "x'61" 6) Compile the program and run. It worked!! just as you've said. Thanks RD. <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6b3cdbf9/24')

[scott@sysimp.com]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
I receive the following error message: call strcgisql Length or start position is out of range for the string operation. Any idea what I'm doing wrong?

[R.Daugherty]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
Hi Araman, Well, RPG-CGI was on my agenda to learn, and this got me started. I downloaded Steven's source to my PC and took a look. Thanks Steven. It appears to me Araman that there is a mismatch somewhere in CCSID, among the interactive job CCSID, compiled program CCSID, and batch job CCSID, where one of them is not 65535 or 037. I'm guessing the interactive job CCSID is set to an international set which has different hex values for special characters than 037. It looks like you can determine the exact hex value being sent in for the slash by doing a DSPPFM on file PSCSPARM and changing to hex mode. The SQL statement as entered is stored in field SQLSTM. I would also make a slight modification to the program and insert into '* * * SQL SYNTAX ERROR ' + %char(SQLCOD) + ' * * *' the character format of SQLCOD. For example, it may be returning 331 Character cannot be converted error, or when you tried dot notation, perhaps a syntax error code. From the SQL manual, it doesn't look like SET OPTION NAMING = *SQL can be done with prepared statements to temporarily try the dot naming. But even then that just gets around one special character. Others would not convert either if the problem is conversion for special characters between different EBCDIC CCSID code sets. At least that should narrow the problem down. rd

[R.Daugherty]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
It's an environment situation, Araman, not particular to this program, so any similar exercise will have the same problem with this setup. I ran across this problem several years ago, which in that situation required physical files to be CCSID 037, but it was not acceptable to recompile them. That was before IBM provided for changing CCSID attribute of an object, but they had it planned at the time, and now you can. But looking at the current situation, I did some Googling. The system CCSID value of 65535 is do not convert, so it's sort of agnostic about the whole thing, but under some conditions when a CCSID is determined to be used it defaults to 037, US English EBCDIC. Each job and object has a CCSID. Basically we are dealing with string literals from two environments being converted incorrectly, or more probably, not being converted, before being compared. The slash is not being changed to something wrong, it's not being changed at all. Get the hex value of the slash with DSPPFM and that will allow you to identify or narrow down the CCSID of the characters being presented to the program to process. If a DSPPGM and DSPFD were done on your new objects, I would expect they have the system value of 65535. Normally, I would suggest recompiling the program with CCSID 037, and if that doesn't work, the CCSID of the incoming character set once you determined it from the slash hex value. The physical file CCSID of 65535 of do not convert should be fine in that it just stores whatever bits are given to it. But I also see (thanks Google, you make IBM doc almost usable) that IBM has a page on the HTTP Server for i5/OS. They say: As of V5R4 the IBM HTTP server for iSeries is now compiled in UTF-8 (CCSID 1208). You can leverage this in your CGI programs by compiling and working with UTF-8 data. So perhaps trying a program CCSID 1208 would be better in that it is inline with the web server providing the data. The IBM page on HTTP Server for i5/OS goes on to talk about leveraging *LOCALEUTF and new CCSID conversion API's with a compile parm of LOCALETYPE(*LOCALEUTF), but I'm not sure if applicable here. rd

[R.Daugherty]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
Ok, Araman, that was all the information needed to identify the situation. The slash is hex 2F. Looking across the DSPPFM, for example, the "40 5C 40" for example is the " * " in the "select * from". Going on across you can count the letters and get the 2F where the slash goes. Looking at an EBCDIC table, a very good one provided by http://www.legacyj.com/cobol/ebcdic.html , I read the hex characters for "select * from araman" but the next character, / , has a standard EBCDIC code of hex 61. So it's partly what I expected, different hex value for the slash than in the EBCDIC character set, but the * is the correct value, so it's not all special characters. Also period worked properly when you tried it. I don't know why just slash would not work. My guess was an international EBCDIC set, such as 273. But a search for CCSID tables didn't turn up a set of tables posted on the internet. There is a set of conversion tables in QUSRSYS, but I don't have access to an AS/400 for personal use. I'll have to get back around to getting an account I checked on last year. But I did find something useful. There is an interesting utility at http://www.legacyj.com/cobol/ebcdic.html (powered by iSockets and RPG xTools, from the web page) that gives the hex values of entered text with selected CCSID codes. I entered / and selected 273 and got the standard hex 61, so that wasn't it. Then I tried 1208 and bingo, that was it. But when I had looked at 1208 earlier, I saw it was based on ASCII, and 2F did sound familiar, and sure enough, hex 2F is ASCII for /. Then I realized that the announcement from IBM means they're running their HTTP Server for i5/OS in internationally standard expanded ASCII. For those that have posted that the AS/400 should run on ASCII, the web server is now, and it looks like they are providing all support necessary for CGI programs to do likewise. Just saw the one page and references to new API's in V5R4, but that seems to be the gist of it. So what we have here, Araman, is incoming text being converted to standard EBCDIC but the slash remaining ASCII. Holy cow. Here's a page on HTTP Server for i5/OS Compatability Considerations. http://www-03.ibm.com/servers/eserver/iseries/software/http/product/compatibility.html I don't know if you're on 5 4 yet, which is what this applies to, but if possible I would recompile the program with the LOCALETYPE(*LOCALEUTF) I mentioned before. This expands on it: As of V5R4, third party modules must be recompiled with locale object type *LOCALEUTF. This creates an environment where locale-dependent C runtime functions assume that string data is encoded in UTF-8. APR and HTTP APIs expect data in UTF-8 and will return data in UTF-8. Note that several APIs have additional functions that allow a CCSID to be set to indicate the encoding of the parameters being passed. Conversion functions between UTF-8 and EBCDIC have been added. Be sure to review APIs used by your module to be aware of current changes. If you have that option in your compile, I would give it a try. Let us know what happens! :) rd

[R.Daugherty]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
I was a PC assembler programmer for 10 years back in the dawn of time (the 80's :), so I lived in hex. No, not being on 5 4 changes direction somewhat. For example, the CCSID 1208 business and all the references I cited was as of 5 4. It's a little simpler now in that it can be assumed it's just a failure to convert at least one ASCII special character to EBCDIC, and that others trying this don't have this problem because the system and job CCSID values are 37, that is, convert to EBCDIC, while yours is 65535, do not convert, which can be important when some jobs are using perhaps an international CCSID set. I'm not knowledgeable about the possibilities and reasoning for that. I'm just a programmer. :) Given that the program is already CCSID 37, the next least disruptive thing is to get the job running the CGI program to run with a CCSID of 37 instead of 65535. From IBM doc http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic///nls/rbagsjobdefaultccsid.htm Job default coded character set identifier A job attribute, job default CCSID (DFTCCSID), is created for jobs with a CCSID of 65535. The DFTCCSID value is used by a system code when a CCSID other than 65535 is needed. This would seem to me to be an attribute of the CGI job launched by the web server. The IBM doc for V5R2 web server is http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/rzaie/rzaiemain.htm Not sure if anything there is this specific. From a search on DFTCCSID on IBM Infocenter, If the job CCSID is 65535, the DFTCCSID value is based on an appropriate value derived from the job language identifier (LANGID). Google shows some questions about default CCSID, but the link is to the generic IBM Infocenter blackhole link. A googlebot blindly finds everything, but cannot link back to it, and mere mortals using the IBM web interface cannot find it again. :) That is why I say Google makes IBM web doc almost usable. I see under the V5R3 announcement # CGI enhancements for running an initialization URL at startup and ability to set CGI jobs' library list. That looks like something along the lines of what you need to change the CGI job CCSID. It will be something in the area of job description and user profile used to get the job CCSID set to 37, I think, without that initialization URL capability. Hopefully that's enough info to find a solution, Araman. :) rd

[R.Daugherty]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
btw, Araman, you have the source, you can work around this and see how far you get. After the return from API call that populates the PSCSPARM DS (i.e., APICVTDB), do a substring replacement in SQLSTM for hex 2F with hex 61. An example character hex definition in the program is NewLine x'15'. That should allow the SQL processor to parse the SQL statement. But also insert the %char(SQLCOD) in the error message as I noted in a previous post so if it does fail you know the specific reason. As long as you're in between the web page and processing, you can do anything needed. rd

[MCWebsite.Staff]

** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
** This thread discusses the Content article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=1184) **
0

[R.Daugherty]

Glad to hear it, Araman. Shows the power of open source, too. rd