Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    ** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) **
    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.
    Code

    Comment


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

      ** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) **
      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/eserve...atibility.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

      Comment


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

        ** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) **
        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.

        Comment


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

          ** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) **
          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/infoce...faultccsid.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/iserie.../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

          Comment


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

            ** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) **
            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

            Comment


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

              ** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) **
              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.

              Comment


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

                ** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) **
                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.

                Comment


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

                  ** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) **
                  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

                  Comment


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

                    ** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) **
                    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

                    Comment


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

                      ** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) **
                      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?

                      Comment


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

                        ** This thread discusses the article: Make DB2 Data Downloads Easy with Start CGI SQL Session (STRCGISQL) **
                        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.
                        Code

                        Comment

                        Working...
                        X