MySQL VARCHAR missing value content
in [Cobol]
|
From: Alistair on 19 Feb 2010 06:45 On Feb 19, 12:59 am, Rene_Surop <infodynamics...(a)yahoo.com> wrote: > Thanks for the reply Alistair/Jimmy. > > Jimmy; yes, I did it in ESQL and been doing the ESQL coding > "wizard" (Tools>OpenESQL Assistant) actually. If I click on the > 'Result' button under ESQL Assistant and execute it... it is showing > the contents. > > I will show you how the 'wizard' generated code; > > *>under working-storage section > 01 wQuantity pic ---,---,999. > 01 wSQLstat pic s9(9) comp-5. > 88 sqlSuccess values 0 1. > > *>program logic coding portion > *>05/items_info_head table > move wSQLrawdate to items-info-head-entered-date > items-info-head-edit-date > > EXEC SQL > DECLARE CSR6 CURSOR FOR SELECT > E.partno > ,E.status > ,E.borrowed_qty > ,E.entered_by > ,E.entered_date > FROM items_info_head E > WHERE > (LEFT(E.entered_date,10) = :items-info-head-entered- > date ) > END-EXEC > EXEC SQL OPEN CSR6 END-EXEC > PERFORM UNTIL (SQLCODE < 0) OR (SQLCODE = +100) > EXEC SQL > FETCH CSR6 INTO > :items-info-head-partno:items-info-head-partno-NULL > ,:items-info-head-status:items-info-head-status-NULL > ,:items-info-head-borrowed-qty:items-info-head-010- > NULL > ,:items-info-head-entered-by:items-info-head-012-NULL > ,:items-info-head-entered-date:items-info-head-013- > NULL > END-EXEC > > move SQLCODE to wSQLstat > if sqlSuccess > move items-info-head-borrowed-qty to wQuantity > > display "borrowed_qty : " wQuantity > display "entered_by : " items-info-head-entered-by > display "entered_date : " items-info-head-entered-date > end-if > END-PERFORM > EXEC SQL CLOSE CSR6 END-EXEC. > > The code is fairly easy as you can imagine. In fact is it > straightforward that JUST AFTER fetching it should be displayed as is. > BUT to my surprised... it is not, it is just like bypassing the value > of 'items-info-head-entered-by' field. > > All of the records that is supposed to FETCH is intact though... > except for the content of 'items-info-head-entered-by' field. Unfortunately the code you have supplied appears to be heavily edited. Using OpenESQL I know that the : variables are assigned sequentially and that in your FETCH you have two consecutive fields where the null fields are enumerated as -10- and -12-. Which is wrong (unless you edited out intervening fields). If the SQL is wrong, Microfocus/ OpenSQL won't tell you. You have to guess.
From: James J. Gavan on 19 Feb 2010 14:43 Alistair wrote: > On Feb 19, 12:59 am, Rene_Surop <infodynamics...(a)yahoo.com> wrote: > >>Thanks for the reply Alistair/Jimmy. >> >> >>All of the records that is supposed to FETCH is intact though... >>except for the content of 'items-info-head-entered-by' field. > > Unfortunately the code you have supplied appears to be heavily edited. > Using OpenESQL I know that the : variables are assigned sequentially > and that in your FETCH you have two consecutive fields where the null > fields are enumerated as -10- and -12-. Which is wrong (unless you > edited out intervening fields). If the SQL is wrong, Microfocus/ > OpenSQL won't tell you. You have to guess. Just a follow up on what Alistair wrote. Did you independently of COBOL do a test query within ESQL which outputs results to a report Table. If so and you get the wrong answers, theoretically you should be able to twiddle the test 'data' and re-run again with the same ESQL generated statement. Now in your case I think you are including input against the DB Table, but from memory, you can set some 'constant' values with equality checks, =, > and <, and combinations thereof. ?????? I tried looking for an example in my on code, not too much luck in comparison to what you are illustrating. It's ages since I did anything with SQL, but the following is a simplistic example using CURSOR and FETCH based on the input record type :- *>-------------------------------------------------------------- Method-id. "makeCollection". *>-------------------------------------------------------------- Local-storage section. copy "deslist.cpy" replacing ==(tag)== by ==ls==. Linkage section. 01 lnk-DBI object reference. 01 lnk-MethodName pic x(30). 01 lnk-rectype pic x(02). copy "\copylib\sqlResult.cpy" replacing ==(tag)== by ==01 lnk==. Procedure Division using lnk-DBI lnk-MethodName lnk-Rectype returning lnk-SqlResult. set ResultOK to true invoke os-SqlConnection "setCurrentConnection" using ws-dbID returning lnk-SqlResult if TableError EXIT METHOD End-if move lnk-rectype to A-DesID EXEC SQL DECLARE CSR80 CURSOR FOR SELECT DISTINCT `A`.`DesID` ,`A`.`DesCode` ,`A`.`DesName` ,`A`.`DesOther1` ,`A`.`DesOther2` FROM `Descriptions` A WHERE ( `A`.`DesID` = :A-DesID ) END-EXEC EXEC SQL OPEN CSR80 END-EXEC PERFORM UNTIL SQLSTATE <> "00000" EXEC SQL FETCH CSR80 INTO :A-DesID:A-DesID-NULL ,:A-DesCode:A-DesCode-NULL ,:A-DesName:A-DesName-NULL ,:A-DesOther1:A-DesOther1-NULL ,:A-DesOther2:A-DesOther2-NULL END-EXEC Evaluate true when SQLSTATE = "00000" initialize ls-DesListRecord move A-DesID to Des-ID move A-DesCode to Des-Code move A-DesName to Des-Name move A-DesOther1 to Des-Other1 move A-DesOther2 to Des-Other2 invoke lnk-DBI lnk-MethodName using ls-DesListRecord ***** see notes below about using the lnk-DBI object when SQLSTATE = "02000" *> no more rows set ResultOK to true EXIT PERFORM when other move A-DesID to ws-ErrorID(1:2) move A-DesCode to ws-ErrorID(3:4) invoke self "setErrorMessage" set TableError to true EXIT PERFORM End-evaluate END-PERFORM EXEC SQL CLOSE CSR80 END-EXEC End Method "makeCollection". *>-------------------------------------------------------------- Notes : 1. SQL ERRORCODE - one thing which might throw you looking at the code is I use SQLSTATE, based on a recommendation from Pete probably as much as ten years ago. The descriptions are more explicit than SQL ERRORCODE. However, once I did get one generalised SQLSTATE message which didn't tell me what was going n. So I animated through the SQLBlock and back to SQL ERRORCODE and the message was more definitive than the SQLSTATE one. C'est la vie; the important thing is to be able to establish what's gone wrong, which I did. 2. Use of lnk-DBI - dumb, dumb, dumb. You learn and progress. The purpose of the above particular method is to create a Sorted Collection to appear in a Listbox. Dialog is halved - Left a record format, Right the Listbox; select from the Listbox (for existing records) and they pop up in the Left record display - then change or delete. All changes made Add, Change and Delete are reflected in a refreshed version of the Listbox via the updated Sorted Collection. DBI = Database Interface, a term I picked up from Will Price in association with COBOL files; keep the, say, ISAM file clean, (vanilla flavoured) and use the DBI class to do any fancy selections. If you like, think of a mixture of DB features and OO features. It's the DBI that invokes/creates the initial SortedCollection; that collection is passed to this method as lnk-DBI and as you can see I return to the DBI to add to the collection. The more effective way is quite simple. I've passed the Collection reference to this method; I don't need to keep returning the result for each row selected. I can add the data within the above method. At the end of the game, (signalled by NoMoreRows), I do NOT need to return the info in lnk-DBI back to the DBI class - granted, using the Animator to take a look I will see different Hex value references to the Collection in the DBI Class and in this method; but they are both 'pointers' to the same object reference. Automatically within the DBI class 'it knows' it's own content based on my updates. An absurd example - I could have ten classes which update this active Collection, but regardless, the Collection reference in the DBI contains all those updates. Jimmy
From: Pete Dashwood on 19 Feb 2010 17:39 Rene_Surop wrote: > Hi, > > This is awkward. Tried so many time to read MySQL table, but this one > got me stucked. Ok, Ive got this MF Cobol code; > > 03 items-info-head-partno SQL TYPE IS CHAR(20). > 03 items-info-head-status SQL TYPE IS CHAR(2). > 03 items-info-head-borrowed-qty PIC S9(09) COMP-5. > 03 items-info-head-entered-by SQL TYPE IS CHAR(30). > 03 items-info-head-entered-date SQL TYPE IS TIMESTAMP. > > From the MYSQL Query browser, I could see that the contents are; > items-info-head-partno is "NAX00789" > items-info-head-status is "y" > items-info-head-borrowed-qty is "000000000+" > items-info-head-entered-by is "michael" > items-info-head-entered-date is "2010-02-17 10:09:31" > > But when I FETCH it from the table field contents are; > items-info-head-partno is "NAX00789" > items-info-head-status is "y" > items-info-head-borrowed-qty is "000000000+" > items-info-head-entered-by is " " > items-info-head-entered-date is "2010-02-17 10:09:31" > > Where is the "michael" in items-info-head-entered-by field?? > > Checked everything out and it works fine in all areas, except the > items-info-head-entered-by field. Even the next FETCHing of > records.... the items-info-head-entered-by seems to be spaces > eventhough I'm getting a value from my MySQL Query Browser. > > Is it because of the COMP-5 data type of items-info-head-borrowed- > qty?? But the such field is returning correct value. > Unlikely. As you say, it is returning the correct value. Have a look at the table schema and see how "items-info-head-entered-by" is defined. Is it a VARCHAR? If it is, it is possible that the fixed length CHAR 30 type is causing the length field to be returned, instead of the data. Youcould try amending the generated host variable from CHAR 30 to VARCHAR (30). (If it is defined on the schema as CHAR(30), then it really is a mystery...) The fact that it Browses correctly points to an incompatibility between the host variables in your program and the definition of the types for the table. Pete. -- "I used to write COBOL...now I can do anything."
From: Rene_Surop on 22 Feb 2010 20:09 Jimmy; code as listed below. This is the ESQL data declaration (wizard) on the above FETCH syntax; EXEC SQL DECLARE items_info_head TABLE ( ,partno varchar(20) NOT NULL DEFAULT ,status char(2) ,borrowed_qty integer(10) ,entered_by varchar(30) ,entered_date datetime(19) ) END-EXEC. ****************************************************************** * COBOL DECLARATION FOR TABLE items_info_head * ****************************************************************** 01 DCLitems-info-head. 03 items-info-head-partno SQL TYPE IS CHAR(20). 03 items-info-head-status SQL TYPE IS CHAR(2). 03 items-info-head-borrowed-qty PIC S9(09) COMP-5. 03 items-info-head-entered-by SQL TYPE IS CHAR(30). 03 items-info-head-entered-date SQL TYPE IS TIMESTAMP. ****************************************************************** * COBOL INDICATOR VARIABLES FOR TABLE * ****************************************************************** 01 DCLitems-info-head-NULL. 03 items-info-head-partno-NULL PIC S9(04) COMP-5. 03 items-info-head-status-NULL PIC S9(04) COMP-5. 03 items-info-head-010-NULL PIC S9(04) COMP-5. 03 items-info-head-012-NULL PIC S9(04) COMP-5. 03 items-info-head-013-NULL PIC S9(04) COMP-5. From the ESQL Assistant wizard code, it is plainly obvious that it should work. BUT IT IS NOT. Pete; the VARCHAR is actually defined in MySQL and I could view the content in my MySQL Query browser program. The MF Cobol code however coded (using IDE wizard) SQL TYPE IS CHAR(99). From the IDE wizard, we could assume that the data type conversion is alright. The SQLCODE is returning a successful read... well, it is. BUT the content value of 'items-info-head-entered-by' is a SPACE. If I look at my MySQL Query browser, it should have a value. Up to now I'm stuck :(
From: Rene_Surop on 22 Feb 2010 20:43
> Just a follow up on what Alistair wrote. Did you independently of COBOL > do a test query within ESQL which outputs results to a report Table. If > so and you get the wrong answers, theoretically you should be able to > twiddle the test 'data' and re-run again with the same ESQL generated > statement. Now in your case I think you are including input against the > DB Table, but from memory, you can set some 'constant' values with > equality checks, =, > and <, and combinations thereof. ?????? > A LESSON LEARNED. Never under estimate a bystander view :) Error code; *>05/items_info_head table move wSQLrawdate to items-info-head-entered-date items-info-head-edit-date EXEC SQL DECLARE CSR6 CURSOR FOR SELECT E.partno ,E.status ,E.borrowed_qty ,E.entered_by ,E.entered_date FROM items_info_head E WHERE (LEFT(E.entered_date,10) = :items-info-head-entered- date ) END-EXEC Change the code to; 01 wNewDate pic x(29). *> *>05/items_info_head table move wSQLrawdate to wNewDate EXEC SQL DECLARE CSR6 CURSOR FOR SELECT E.partno ,E.status ,E.borrowed_qty ,E.entered_by ,E.entered_date FROM items_info_head E WHERE (LEFT(E.entered_date,10) = :wNewDate ) END-EXEC Thanks guys. |