MySQL VARCHAR missing value content
in [Cobol]
|
From: Rene_Surop on 18 Feb 2010 02:51 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. Im using MF NetExpress v3.1 devtool. Any experience on this one??
From: Alistair on 18 Feb 2010 11:18 On Feb 18, 7:51 am, Rene_Surop <infodynamics...(a)yahoo.com> 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. > > Im using MF NetExpress v3.1 devtool. Any experience on this one?? I would check the SELECT statement that you are using for the FETCH and make sure that you have included the field items-info-head-entered- by and the receiving data item it goes to.
From: James J. Gavan on 18 Feb 2010 15:21 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. > > Im using MF NetExpress v3.1 devtool. Any experience on this one?? Rene, No experience on what you are writing about. I use the ESQL Tool. Have you tried it, or were you reluctant to give it a shot ? If you haven't used it, just for this one query, I'm quite happy to take you step-by-step through the ESQL to test the above and you don't have to run within COBOL, just from within the IDE - it allows you to set a Table up and then test the query code as a separate exercise. Meanwhile I just wrote to you within the last few days. Did you get it ? On both above - private e-mails please. There's absolutely no point including the ESQL 'How to' here, as it is M/F specific. Jimmy, Calgary AB
From: Rene_Surop on 18 Feb 2010 19:59 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.
From: James J. Gavan on 19 Feb 2010 00:01
Rene_Surop 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. OK, without further info on this topic from me by e-mail, just the other one mentioned. I'm looking at that :- 01 wQuantity pic ---,---,999. Might possibly be a problem, or perhaps I've picked up on a red herring. Suggestion :- post the three-part copyfile that ESQL generates (1) SQL-formats, (2) COBOL Record format, and (3) the NULL definitions. I wouldn't mind betting one of the long-time SQL users spot it before I have a chance to put my two cents in. Jimmy |