MySQL VARCHAR missing value content
in [Cobol]
|
From: Rene_Surop on 22 Feb 2010 20:46 >Alistair wrote; > 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. Yes, I did edit them not to include 'other' variables to shorten the code posting. Excellent observation :)
From: Pete Dashwood on 22 Feb 2010 21:29 Rene_Surop wrote: > 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 :( Yes, it is baffling. Some things to check... (please don't be offended; I know some of them are basic, and I have great respect for your ability :-)) First, establish the playing field is even between the MySQL Browser (which is showing the expected results) and COBOL (which isn't...)... 1. Are you sure that connection for COBOL and connection for the MySQL Browser are to the same DB? (there isn't a different version lying around..?) 2. Are you sure that both COBOL and MySQL are looking at the same key (PartNo?) in the table? OK, they are on the same page... So the problem MUST be in the transferral and conversion to the COBOL data types generated by the Wizard. (OR, there is something veryfundamental being overlooked - like the field really does have spaces in it... :-)) If it were mine, I'd run DECLGEN, get a COBOL declaration for these fields, then access them into the Declared Host Variables (but then, I wouldn't be using the Wizard in the first place...:-)). Nevertheless, we have to trust the Wizard and it converts a VARCHAR partno to CHAR(20) without problem, so it obviously SHOULD get the "entered by" stuff in the same way. What happens if you change the order the fields are defined in, in the table? Does it still fail? If it does, we are drawing closer to the conclusion that whatever it is accessing DOES have spaces in it. It then comes down to confirming what is being accessed by COBOL and ensuring it is the same as what is being accessed by MySQL. I would normally take your table definition and run it through some of the tools I have here, but I really can't at the moment. (I have allocated myself 30 minutes off to check CLC and I already used 10 of them :-) Good Luck! Pete. -- "I used to write COBOL...now I can do anything."
From: Alistair on 23 Feb 2010 11:28 On Feb 23, 1:43 am, Rene_Surop <infodynamics...(a)yahoo.com> wrote: > > 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. Does that mean that the problem is solved?
From: Rene_Surop on 1 Mar 2010 02:59 > > Does that mean that the problem is solved? Yes. Tried using a (separate) local variable.... instead of using the ":items-info-head-entered- date" which is utilized in SQL table structure.
From: Alistair on 1 Mar 2010 08:38
On Mar 1, 7:59 am, Rene_Surop <infodynamics...(a)yahoo.com> wrote: > > Does that mean that the problem is solved? > > Yes. Tried using a (separate) local variable.... instead of using the > ":items-info-head-entered- > date" which is utilized in SQL table structure. Glad to hear that it is resolved. |