TechTip: SQL2CSV and SQL2XML

[longoc@yahoo.com]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
Thanks Giuseppe for the great tool, it helps me a lot. I have 2 issues however: First, it seems does not work if there is constant value in SQL statement, for instance, I cannot have this statement to run: SQL2XML SQLSTMT('Select cusno as [CustomerNumber], + cname as [CustomerName], case when goldac + = ''Y'' then ''GOLD'' when teamac = ''Y'' + then ''TEAM'' else ''STANDARD_COST'' end + as [AccountType], case when activ = ''1'' + then ''E'' else ''D'' end as [Status] + from custmast') + Another issue is the column name is cut off for the name longer than 18 characters, in my SQL I have [BillingAddressNumber] as column name but it shown only the first 18 characters in XML file below: 288017 Any ideas? thanks again for sharing the excellent tool.

[gcostagliola@tin.it]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
Hi, 1- to get "constant value" that are returned as Varchars please add the code below. 2- If you specify COLHDRS(*FLDNAM) you get the entire field. The COLHDRS(*SQLLABEL) returns only a part as the SQL reference manual at page 853 says: "For a label, the database manager sets this to the first 20 bytes of the label". Giuseppe.

---

Code

[longoc@yahoo.com]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
Thanks for your help, the problem with the long column name is solved. For the constant value in the SQL, I have made the change as you suggested but the SQL2XMLR program seems cannot fetch the SQL records, when I debug at the statements below, the SQLCODE field has the value of -000000501 and the program ended. // fetch records function = '5'; QSQPRCED (SQLCA:SQLDA:sqformat:sqlp0100:apierror); dow SQLCODE = 0; Following is shown in my job log: 4 > call sql2xmlcl STATEMENT TEXT FOUND : Select cusno as "CustomerNumber", cname as "CustomerName", case when aging = 'Y' then 'GOLD' when flag1 = 'Y' then 'TEAM' else 'STANDARD_COST' end as "AccountType", case when activ = '1' then 'E' else 'D' end as "Status" from mfcmp100. STATEMENT TEXT NOT FOUND : Select cusno as "CustomerNumber", cname as "CustomerName", case when aging = 'Y' then 'GOLD' when flag1 = 'Y' then 'TEAM' else 'STANDARD_COST' end as "AccountType", case when activ = '1' then 'E' else 'D' end as "Status" from mfcmp100. PREPARE of statement XML completed. STATEMENT NAME FOUND : XML. DESCRIBE of prepared statement XML completed. Length in a varying-length or LOB host variable not valid. Cursor CURSOR not open. and the extended help on ...LOB host ... explains: Message ID . . . . . . : SQL0311 Date sent . . . . . . : 09/08/04 Time sent . . . . . . : 08:46:52 Message . . . . : Length in a varying-length or LOB host variable not valid. Cause . . . . . : Host variable *N was specified. The value in the length portion of the variable length or LOB host variable is either negative or greater than the declared length. If the host variable is graphic the length should be the number of DBCS characters. The host variable number is 3. The specified length is 16448. The variable is declared to have length 13. Recovery . . . : Change the length portion of the varying-length or LOB host variable to a valid positive number or zero. Try the request again.

[gcostagliola@tin.it]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
Maybe you forgot something. I tested this way: == create table mfcmp100 (cusno dec(7), cname char (20), aging char, flag1 char, activ char) == insert into mfcmp100 values(1, 'MCPRESS', 'Y', ' ', '1') == ===> SQL2XML SQLSTMT('Select cusno as [CustomerNumber], cname as [CustomerName], case when aging = "Y" then "GOLD" when flag1 = "Y" then "TEAM" else "STANDARD_COST" end as [AccountType], case when activ = "1" then "E" else "D" end as [Status] from mfcmp100') TOXML('/home/costagliol/mfcmp100.xml') ROOT(MFCMP100) ENTITY(CUSTOMER) == This is the result: 1 MCPRESS GOLD E == Giuseppe.

[longoc@yahoo.com]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
I will verify to see what I did wrong, thanks a lot for your time helping me.

[WoofDog]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
Paul, your code works perfectly. Only, for my use the standard 6 digits after the decimal point isn't really enough. But that was easily fixed by changing the format string to '%-2.12g', which gives me up to 12 digits after the decimal point - and drops all those useless zeros. At least in my case this works perfectly. Thanks a lot. Regards, Kaj

[preid@ix.netcom.com]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
Kaj Thanks for the credit, but the fix was Giuseppe's. :-) Paul

[WoofDog]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
Sorry about that! But that's just me. Give me a choice of two names and I will invariably choose the wrong one! Thank you, Giuseppe, thank you. See, I can do it right!

[shahar_mor@yahoo.com]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
All tools are great helpful and easy to use. In SQL2XLS the excel file is created in "correct" ccsid for me (916 is windows Hebrew) however when opening the file Hebrew characters appear incorrect. Where is the conversion from file CCSID to excel ccsid take place, can we influence it ? Thanks again for a fine job.

[gcostagliola@tin.it]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
The instruction to add should be: cell.setEncoding(HSSFCell.ENCODING_UTF_16). Very soon I will publish a "pure java" version where you can easily add this instruction and try. If it works you could port the method to RPG. Giuseppe.

[trevor.lazarus@ssa.gov]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
Can't get the SQL2XML to generate a file. The failure is here: QSQPRCED (SQLCA:SQLDA:sqformat:sqlp0100:apierror); If ApiErrMsg <> ' '; *inlr = *on; return; APIerrmsg comes back with SQL7023 suggestions? Trevor

[dacust]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
I have binding directory QC2LE, but not CGI. Does this mean I am missing a product on my 400? -dan

[gcostagliola@tin.it]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
The SQL2CGI can be used to send back the xml to the web. I have some examples in as400/php. If you are interested just ask. ---------- You can create your own CGI bnddir and add the following entry: -> Object:QTMHCGI Type:*SRVPGM Library:QTCP Giuseppe.

[gcostagliola@tin.it]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
This part of the code is exactly the same as SQL2XLS and SQL2CSV. Make sure that when you moved the source from ifs to srcfile it has not been altered. If you see that everything is fine, you can put the program in debug and inspect the SQLDA, SQLCA and sqlp0100 right before calling the api. (You can send me a copy of these fields). Check also if there are other messages in the joblog. Giuseppe.

[trevor.lazarus@ssa.gov]

TechTip: SQL2CSV and SQL2XML

** This thread discusses the article: TechTip: SQL2CSV and SQL2XML **
Here's the command used: SQL2XML SQLSTMT('select * from ddfpymv0 where mvmvno = ''0000001''') TOXML('/home/tlazar/vendor.xml') ROOT(VENDOR) ENTITY(VENDOR) and the only joblog message: Ownership of object XML in QTEMP type *USRSPC changed. the parms passed to QSQPRCED

 SQLERRP OF SQLCA = ' ' SQLER1 OF SQLCA = 077952576. SQLERRD OF SQLCA = ' ' SQLER2 OF SQLCA = 077952576. SQLER3 OF SQLCA = 077952576. SQLER4 OF SQLCA = 077952576. SQLER5 OF SQLCA = 077952576. SQLER6 OF SQLCA = ' ' SQLWARN OF SQLCA = ' ' SQLSTATE OF SQLCA = ' ' SQFORMAT = 'SQLP0100 ' FUNCTION OF SQLP0100 = '1' PKGNAME OF SQLP0100 = 'SQL2XMLR ' PKGLIB OF SQLP0100 = 'QTEMP ' MAINPGM OF SQLP0100 = 'SQL2XMLR ' MAINLIB OF SQLP0100 = 'TREVOR ' STMNAME OF SQLP0100 = 'XML ' CURNAME OF SQLP0100 = 'CURSOR ' OPENOPT OF SQLP0100 = ' ' CLAUDESC OF SQLP0100 = 'A' COMMIT OF SQLP0100 = 'N' DATEFMT OF SQLP0100 = 'ISO' DATESEP OF SQLP0100 = '-' TIMEFMT OF SQLP0100 = 'ISO' TIMESEP OF SQLP0100 = '.' NAMINGOPT OF SQLP0100 = 'CON' DECPOS OF SQLP0100 = '.' BLOCK OF SQLP0100 = 0000. SQLSTMTL OF SQLP0100 = 0000. SQLSTMT OF SQLP0100 = 'select * from ddfpymv0 where mvmvno = '0000001' ' APIERRLP OF APIERROR = 000000120. APIERRLA OF APIERROR = 000000043. APIERRMSG OF APIERROR = ' ' OF APIERROR = '0' APIERRDTA OF APIERROR = 'SQL2XMLR QTEMP SQLPKG ' 
 Trevor