Fetch Next wont fetch next

[ThomasY]

Hello

On JDE 7331 sp 7.1. Finding out that "out-of-blue" my NER BSFN
in production wont work anymore, neither will new stuff I build - Here goes:

Have a fetch next inside of a Do-While loop. If table i/o is
performed within this loop, either on the same table or different table, the fetch next will not fetch a second record or beyond.
If No table i/o involved, seems to work fine. JDE says SP14.2
will fix the problem. I have told 14.2 creates more problems then
it solves.
Anybody else have this problem?

Tom

 

[vbojan]

Hi Tom,

Did you try to use handle instead of table for that “Select, FetchNext” operation? Handle should make an independent view on table and shouldn’t interfere with other table I/O operation (at least on B733.2, NT4.0, SQL7).

Hope this will help.

Bojan.

 

[Larry_Jones]

Tom,

surely "something" changed.
Was an update package recently built/installed?
Did you rebuild the BSFN?
If you are SQL Server (your signature is missing) has a new MDAC package been installed or has something only possibly changed the ODBC DSN or Driver Settings?

Just some things to try.


Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
SandBox: OneWorld XE

 

[velasj]

Tom,

I think we could get a better idea on how you're using the Fetch Next if you
could just write part of the code for the people in the list to help.
Anyway, have you tried using the Select - Fetch.Next functionality?

Jorge


>From: ThomasY <[email protected]>
>Reply-To: [email protected]
>To: [email protected]
>Subject: Fetch Next wont fetch next
>Date: Wed, 14 Mar 2001 10:26:49 -0800 (PST)
>
>Hello
>
>On JDE 7331 sp 7.1. Finding out that "out-of-blue" my NER BSFN
>in production wont work anymore, neither will new stuff I build - Here
>goes:
>
>Have a fetch next inside of a Do-While loop. If table i/o is
>performed within this loop, either on the same table or different table,
>the fetch next will not fetch a second record or beyond.
>If No table i/o involved, seems to work fine. JDE says SP14.2
>will fix the problem. I have told 14.2 creates more problems then
>it solves.
>Anybody else have this problem?
>
>Tom
>
>
>
>--------------------------
>Visit the forum to view this thread at:
>http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OWDEV&Number=7314
>+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
>This is the JDEList One World / XE Developers Mailing List.
>Archives and information on how to SUBSCRIBE, and
>UNSUBSCRIBE can be found at http://www.JDELIST.com
>+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
>

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com

 

[ThomasY]

Bojan,

As a rather new developer, I have not been trained/exposed to handles.
Also, I am not a C++ program by history, but a RPG/COBOL guy. I have
found out some more interesting stuff which I will include in the
next reply to the second response I got. Please read it.

Thanks

 

[ThomasY]

Jorge,


Yes I am using Select/Fetch Next. This is what I sent to JDE: (They
did not have aproblem with it, just said to go to SP 14.2)


Listing of ER for Named ER: N570020Q


=======================================================================
NAMED ER: N570020QDeleteF570030
=======================================================================
evt_returnedqtypicked
evt_returnedqtypacked
evt_returnedsalesorder
evt_returnedpsn
evt_returnedcontainer
0001 F570030.Select
BF szContainerID = TK Container I.D.
BF szIdentifier2ndItem = TK 2nd Item Number
BF szCostCenter = TK Business Unit
0002 F570030.Fetch Next
VA evt_returnedcontainer <- TK Container I.D.
VA evt_returnedsalesorder <- TK Document (Order No, Invoice, etc.)
VA evt_returnedpsn <- TK Pick Slip Number
VA evt_returnedqtypicked <- TK Units - Per Container
VA evt_returnedqtypacked <- TK Number of Containers
0003 While SV File_IO_Status is equal to CO SUCCESS
0004
0005 If VA evt_returnedqtypacked is greater than or equal to VA evt_returnedqtypicked
0006 F570030.Delete
BF szContainerID = TK Container I.D.
VA evt_returnedsalesorder = TK Document (Order No, Invoice, etc.)
VA evt_returnedpsn = TK Pick Slip Number
BF szCostCenter = TK Business Unit
0007 End If
0008 F570030.Fetch Next
VA evt_returnedsalesorder <- TK Document (Order No, Invoice, etc.)
VA evt_returnedpsn <- TK Pick Slip Number
VA evt_returnedqtypicked <- TK Units - Per Container
VA evt_returnedqtypacked <- TK Number of Containers
0009 End While

Friday February 16, 2001 08:00

Note: do not let "Container I.D." lead you to think that it makes a unique record. It is used for I.D'ing. the person who created the record.


I now also got a new BSFN with a fetch single. The fetch single returns the value of a a field STA. On one dev machine it works
fine. On another dev machine, same BSFN,DS,Environment,Table,Record.
It will return anything BUT the STA field-AARRGGHHHH!!


Tom

 

[velasj]

Tom,

You're program looks fine, but I wonder if you have made sure that the table
specs are checked out on the PCs that your program is not working? At the
same time, have you built the NER, that you're using, to the PCs that this
program is not working?
The strange thing is that if it works on one computer and not on other,
maybe is not the SP, but the configuration of the workstation. Have you
tried reinstalling that computer?

Jorge


>From: ThomasY <[email protected]>
>Reply-To: [email protected]
>To: [email protected]
>Subject: Re: Fetch Next wont fetch next
>Date: Thu, 15 Mar 2001 05:03:02 -0800 (PST)
>
>Jorge,
>
>
>Yes I am using Select/Fetch Next. This is what I sent to JDE: (They
>did not have aproblem with it, just said to go to SP 14.2)
>
>
>Listing of ER for Named ER: N570020Q
>
>
>=======================================================================
> NAMED ER: N570020QDeleteF570030
>=======================================================================
> evt_returnedqtypicked
> evt_returnedqtypacked
> evt_returnedsalesorder
> evt_returnedpsn
> evt_returnedcontainer
>0001 F570030.Select
> BF szContainerID = TK Container I.D.
> BF szIdentifier2ndItem = TK 2nd Item Number
> BF szCostCenter = TK Business Unit
>0002 F570030.Fetch Next
> VA evt_returnedcontainer <- TK Container I.D.
> VA evt_returnedsalesorder <- TK Document (Order No, Invoice,
>etc.)
> VA evt_returnedpsn <- TK Pick Slip Number
> VA evt_returnedqtypicked <- TK Units - Per Container
> VA evt_returnedqtypacked <- TK Number of Containers
>0003 While SV File_IO_Status is equal to CO SUCCESS
>0004
>0005 If VA evt_returnedqtypacked is greater than or equal to VA
>evt_returnedqtypicked
>0006 F570030.Delete
> BF szContainerID = TK Container I.D.
> VA evt_returnedsalesorder = TK Document (Order No, Invoice,
>etc.)
> VA evt_returnedpsn = TK Pick Slip Number
> BF szCostCenter = TK Business Unit
>0007 End If
>0008 F570030.Fetch Next
> VA evt_returnedsalesorder <- TK Document (Order No, Invoice,
>etc.)
> VA evt_returnedpsn <- TK Pick Slip Number
> VA evt_returnedqtypicked <- TK Units - Per Container
> VA evt_returnedqtypacked <- TK Number of Containers
>0009 End While
>
>Friday February 16, 2001 08:00
>
>Note: do not let "Container I.D." lead you to think that it makes a unique
>record. It is used for I.D'ing. the person who created the record.
>
>
>I now also got a new BSFN with a fetch single. The fetch single returns
>the value of a a field STA. On one dev machine it works
>fine. On another dev machine, same BSFN,DS,Environment,Table,Record.
>It will return anything BUT the STA field-AARRGGHHHH!!
>
>
>Tom
>
>
>--------------------------
>Visit the forum to view this thread at:
>http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OWDEV&Number=7359
>+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
>This is the JDEList One World / XE Developers Mailing List.
>Archives and information on how to SUBSCRIBE, and
>UNSUBSCRIBE can be found at http://www.JDELIST.com
>+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
>

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com

 

[ThomasY]

Jorge

For the Fetch/next Delete issue, the PC that it does not work on is the same PC that deveopled all the object that go with the BSFN. I am
in development phase. Should not be a synch problem. The Other
issue of two PC's different results is such: I did check in the
BSFN,DS, then checked out and compiled on the second machine. The table part has been around,used, and unchanged for quite some time-it's specs should be Ok. This 2nd machine I believe needs a reload. It also JITI'd a UBE I called when I had just checked it out to the machine. It also has check out/in problems at times too. So I think
I am going to reload.

BTY, Here is JDE response to the first issue:

Tom,

In regards to your issue with table i/o in which development suggested that
you go to SP 14.2, you wanted to know
if there is a known issue and why you haven't seen it on the Knowledge
Garden.

Development's response is:
> Currently, we required the user to upgrade to SP14.2. There have been
> several fixes for the JDEUSER.DLL as well as the JDEKRNL.DLL. If the
> issue exist in SP14.2, we can put the fix into the next release; but we
> cannot put the fixes into the back release such as SP7.1.
> It is not feasible for us to track an issue in SP7.1 that might have been
> resolve in other service pack.
>
>
Please let me know if you have any other questions.








Thanks

Tom

 

[Larry_Jones]

Tom,

interesting response from JDE ... I'm not sure I would jump from 7.1 to 14.2 though for such an ambivalent answer. If anything you should upgrade to 11.3 which is a very stable release for B733.1 or 2.

Your code looks OK, however can you please tell us what the index definitions are on your table as well as which indices are unique/not unique. Also please tell us what index the SELECT statement is based on.

The reason I'm asking this stuff is a guess, based on experience with other platform's ISAM/VSAM type file I-O. If your database was ORACLE or SQL Server I know the below couldn't happen, but I don't know the AS/400 so I'll throw this out anyway. Here's my guess:

1. The index specified in the SELECT statement allows DUPLICATES.
2. Because the index used allows dupes, performing a DELETE causes the current position in the result set to be lost, causing the next FETCH to be unsuccessful.

Whether or not my above hypothesis is true, using a file handle to perform the DELETE or SELECT/FETCH statements should at least give you a work-around.

To create a table "handle" you will need to do the following:

1) in the Data Dictionary create a new DD ITEM as follows:
Data Item: HandleF570030
Alias: 57HF570030
System Code: 57
Description: Table Handle for F570030
Data Type: 7
Size: 11
Class: HANDLE
Control Type: 4
Edit Rule - Handle: F570030 (the table name associated with the handle)

2) In the DIALOG IS INITIALIZED event of your form:
a) Create a FORM Level variable based on DD item 57HF570030.
Call it HandleF570030.
b) Add a Table I-O statement in this event to OPEN the handle
VA frm_HandleF570030 = F570030.Open Handle

3) In the Form's END DIALOG event add a Table I-O statement
to CLOSE the handle:
F570030(VA frm_HandleF570030).Close

4) Replace your existing F570030.DELETE statement with one that uses the Table Handle to perform the delete.

Note: Handles are found on the Handles Tab in the Data Source form when defining Table I-O statements. Choose the handles tab instead of the default Tables tab.

Sorry for the long winded response folks. Hope this helps Tom.



Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
SandBox: OneWorld XE

 

[ThomasY]

Larry,

I had since used another method to accomplish my goals, this code was
replaced. So I do not know what index I was using. I still wanted to resolve this issue in case I am confronted with it again. It probably was not unique for the select. My only index that is unique is the primary index.- this is a Oracle database. If your hypothesis is correct, is this "supposed" to happen - thus "bad" codeing - or is it a bona fide bug? Anyway, I will save your instructions and try them out when I have the opportunity.

Thanks

Tom

 

[Larry_Jones]

Tom,

For ORACLE or any other RDBMS your code should have worked.

So how did you resolve the problem?

Regards

Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
SandBox: OneWorld XE

 

[ThomasY]

Well,

This file has three buckets. One shows qty that was picked, the
second was of the qty picked how many had also been packed. The third bucket showed the remainder, if any, that were left to be packed. So
a typical set would be 5 picked - 3 packed = 2 left to pack.
What I was doing was deleting the record if picked(5) = packed(5). Instead, I just did a delete where "left to packed" = 0. (Actually more efficient, should have done it in the first place) So, I did not
need the fetch next/delete routine anymore -thank goodness there was another way to do it.

Tom