Record not found when attempting to do an UPDATE

I'm using the ACUXDBC driver to update a single field in an UPDATE statement. My statement is thus:

UPDATE Orders SET RECORD_DATE = '2015-02-27' WHERE TRANSACTION_NUMBER = 906944200

When I run the above statement I get the following error.

ERROR [HY000] [TOD][ODBC][GENESIS]VISION: Cannot update 'Orders',Record not found

The only problem is the record *does* exist because when I do a SELECT as in:

SELECT RECORD_DATE FROM Orders WHERE TRANSACTION_NUMBER = 906944200

It finds and returns the correct value for the row in question.

So I know the record exists, and I know it can be found, but for some reason the XDBC driver is telling me that it cannot be when I try to do an UPDATE. I have tried updating a different field in the same row and it works fine. This statement runs without any problems.

UPDATE Orders SET mkting_unit = 1 WHERE TRANSACTION_NUMBER = 906944200

Any thoughts?

Appreciate it!

Tags:

  • I'm not sure if it matters, but is RECORD-DATE part of a key in your FD? Also, how is RECORD-DATE defined, and is it mapped to a SQL Date or DateTime (i.e. do you have a $XFD DATE directive in your FD)?

    Just wondering if there's an issue converting a DateTime back to a PIC 9(8) on the update operation.

  • Hmmm, maybe I should start by saying I have no experience with ACUCobol whatsoever. All of my experiences are in other languages and database engines. Also, unfortunately, none of the people working here with me have any experience with it either. We have an application that uses ACUXDBC but it was built somewhere else. We're just trying to update the database which is something we have done in the past in other tables and fields.

    I am sure RECORD_DATE is a Date/Time field of some type because I get an error like this one "Illegal data: '2015-02/27'. Expected format: 'Default Datetime Mask'" if I don't have the field formatted like it is in my example.

    I'm not sure what FD is unless it means Field Definition? Is there a config file somewhere we can look at to look at this information?

  • An FD is a File Description and is part of the original COBOL source code. It defines the record layout(s) of the data file. It sounds like this is something you don't have access to. Do you have any files with a .xfd extension? These are eXtended FDs, and are generated by the ACUCOBOL compiler from the original source. They are then used to build the AcuXDBC System Catalog. You may not have these, but if you do, please provide the orders.xfd file and we might be able to figure out what's going on.

  • Here are the details of the art1db.xfd file. The table in question is not called Orders, but art1db although I used a more generic sounding name for my question.

    # ../xfd/art1db.xfd - generated by ACUCOBOL-GT v7.2.0 ECN-3532

    # Generated Wed Sep 19 14:02:14 2007

    # [Identification Section]

    XFD,05,ART1-FILE,ART1DB,12

    0000002076,0000002076,002

    # [Key Section]

    01,0,027,0000000000

    07

    DIST-ID

    PV-DATE

    COUNTRY-CODE

    RECORD-DATE

    TRANSACTION-NUMBER

    TRANSACTION-TYPE

    TRANSACTION-SEQ-NO

    01,1,008,0000000019

    03

    TRANSACTION-NUMBER

    TRANSACTION-TYPE

    TRANSACTION-SEQ-NO

    # [Condition Section]

    001

    001,8,CUSTOM-DATA,"custom-data "

    # [Field Section]

    0077,00086,00112,00133

    0000000000,0000002076,16,0000002076, 00,000,999,01,REC

    0000000000,0000000027,16,0000000027, 00,000,999,02,DIST-ID-KEY

    0000000000,0000000005,12,0000000013, 00,000,000,03,DIST-ID

    0000000005,0000000006,01,0000000006, 00,000,000,03,PV-DATE

    0000000005,0000000006,16,0000000006, 00,000,999,03,PV-DATE-R

    0000000005,0000000004,01,0000000004, 00,000,999,04,PV-YEAR

    0000000009,0000000002,01,0000000002, 00,000,999,04,PV-PERIOD

    0000000011,0000000004,16,0000000004, 00,000,000,03,COUNTRY-CODE

    0000000015,0000000004,12,0000000010, 00,001,000,03,RECORD-DATE                   ,HHYYYYMMDD

    0000000019,0000000008,16,0000000008, 00,000,999,03,TRANSACTION-KEY

    0000000019,0000000004,12,0000000010, 00,000,000,04,TRANSACTION-NUMBER

    0000000023,0000000002,16,0000000002, 00,000,000,04,TRANSACTION-TYPE

    0000000025,0000000002,12,0000000005, 00,000,000,04,TRANSACTION-SEQ-NO

    0000000027,0000000100,16,0000000100, 00,000,000,02,DESCRIPTION

    0000000127,0000000002,01,0000000002, 00,000,000,02,MKTING-UNIT

    0000000129,0000000004,12,0000000010, 00,001,000,02,BANK-DATE                     ,HHYYYYMMDD

    0000000133,0000000004,12,0000000010, 00,001,000,02,ENTRY-DATE                    ,HHYYYYMMDD

    0000000137,0000000006,16,0000000006, 00,000,000,02,ENTRY-INIT

    0000000143,0000000006,16,0000000006, 00,000,000,02,POST-INIT

    0000000149,0000000004,12,0000000010, 00,001,000,02,POST-DATE                     ,HHYYYYMMDD

    0000000153,0000000004,12,0000000010, 00,000,000,02,POST-BCH

    0000000157,0000000006,11,0000000015,-02,000,000,02,TRANS-AMOUNT

    0000000163,0000000054,16,0000000054, 00,000,999,02,PRICES

    0000000163,0000000006,11,0000000015,-02,000,000,03,PRICE-1

    0000000163,0000000006,11,0000000015,-02,000,999,03,WHSLE-AMOUNT

    0000000169,0000000006,11,0000000015,-02,000,000,03,PRICE-2

    0000000169,0000000006,11,0000000015,-02,000,999,03,PV-AMOUNT

    0000000175,0000000006,11,0000000015,-02,000,000,03,PRICE-3

    0000000175,0000000006,11,0000000015,-02,000,999,03,RETAIL-AMOUNT

    0000000181,0000000006,11,0000000015,-02,000,000,03,PRICE-4

    0000000181,0000000006,11,0000000015,-02,000,999,03,CV-AMOUNT

    0000000187,0000000006,11,0000000015,-02,000,000,03,PRICE-5

    0000000193,0000000006,11,0000000015,-02,000,000,03,PRICE-6

    0000000199,0000000006,11,0000000015,-02,000,000,03,PRICE-7

    0000000205,0000000006,11,0000000015,-02,000,000,03,PRICE-8

    0000000211,0000000006,11,0000000015,-02,000,000,03,PRICE-9

    0000000163,0000000054,16,0000000054, 00,000,999,02,PRICE-R

    90001,00009,00006,START-OCCURS

    0000000163,0000000006,11,0000000015,-02,000,999,03,PRICE

    90002,END-OCCURS

    0000000217,0000000030,16,0000000030, 00,000,999,02,MISC-CHARGES

    0000000217,0000000006,11,0000000015,-02,000,000,03,MISC-CHARGE-1

    0000000223,0000000006,11,0000000015,-02,000,000,03,MISC-CHARGE-2

    0000000229,0000000006,11,0000000015,-02,000,000,03,MISC-CHARGE-3

    0000000235,0000000006,11,0000000015,-02,000,000,03,MISC-CHARGE-4

    0000000241,0000000006,11,0000000015,-02,000,000,03,MISC-CHARGE-5

    0000000217,0000000006,16,0000000006, 00,000,999,02,MISC-CHARGES-R

    0000000217,0000000006,11,0000000015,-02,000,999,03,MISC-CHARGE

    0000000247,0000000025,16,0000000025, 00,000,999,02,MISC-AMOUNTS

    0000000247,0000000005,11,0000000013,-02,000,000,03,AMOUNT-1

    0000000252,0000000005,11,0000000013,-02,000,000,03,AMOUNT-2

    0000000257,0000000005,11,0000000013,-02,000,000,03,AMOUNT-3

    0000000262,0000000005,11,0000000013,-02,000,000,03,AMOUNT-4

    0000000267,0000000005,11,0000000013,-02,000,000,03,AMOUNT-5

    0000000247,0000000020,16,0000000020, 00,000,999,02,MISC-AMOUNTS-R

    90001,00005,00004,START-OCCURS

    0000000247,0000000004,12,0000000010, 00,000,999,03,MISC-AMOUNT

    90002,END-OCCURS

    0000000272,0000000010,16,0000000010, 00,000,999,02,FLAGS-DUM

    90001,00010,00001,START-OCCURS

    0000000272,0000000001,16,0000000001, 00,000,000,03,FLAG

    90002,END-OCCURS

    0000000272,0000000010,16,0000000010, 00,000,999,02,FLAGS

    0000000272,0000000001,16,0000000001, 00,000,999,03,FLAG-1

    0000000273,0000000001,16,0000000001, 00,000,999,03,FLAG-2

    0000000274,0000000001,16,0000000001, 00,000,999,03,FLAG-3

    0000000275,0000000001,16,0000000001, 00,000,999,03,FLAG-4

    0000000276,0000000001,16,0000000001, 00,000,999,03,FLAG-5

    0000000277,0000000001,16,0000000001, 00,000,999,03,FLAG-6

    0000000278,0000000001,16,0000000001, 00,000,999,03,FLAG-7

    0000000279,0000000001,16,0000000001, 00,000,999,03,FLAG-8

    0000000280,0000000001,16,0000000001, 00,000,999,03,FLAG-9

    0000000281,0000000001,16,0000000001, 00,000,999,03,FLAG-10

    0000000282,0000000004,12,0000000010,-04,000,000,02,CONVERSION-FACTOR

    0000000286,0000000004,11,0000000010,-05,000,000,02,DISCOUNT-PERCENT

    0000000290,0000000006,11,0000000015,-02,000,000,02,DISCOUNT-AMOUNT

    0000000296,0000000006,11,0000000015,-02,000,000,02,DISCOUNT-ON

    0000000302,0000000006,16,0000000006, 00,000,000,02,VOID-INIT

    0000000308,0000000004,12,0000000010, 00,001,000,02,VOID-DATE                     ,HHYYYYMMDD

    0000000312,0000000004,12,0000000010, 00,001,000,02,COMM-VOID-DATE                ,HHYYYYMMDD

    0000000316,0000000012,16,0000000012, 00,000,000,02,PROGRAM

    0000000328,0000000001,16,0000000001, 00,000,000,02,NET-TRAX-SB

    0000000329,0000000004,12,0000000010, 00,000,000,02,BONUS-BATCH-NUMBER

    0000000333,0000000001,16,0000000001, 00,000,000,02,ORDER-TYPE

    0000000334,0000000003,01,0000000003, 00,000,000,02,ORDER-SOURCE

    0000000337,0000000001,16,0000000001, 00,000,000,02,EXTRACT-FLAG

    0000000338,0000000001,16,0000000001, 00,000,000,02,JOURNAL-TYPE

    0000000339,0000000004,11,0000000010, 00,000,000,02,ADC-EXTEND

    0000000343,0000000001,16,0000000001, 00,000,000,02,ADC-EXTEND-FLAG

    0000000344,0000000005,16,0000000005, 00,000,999,02,ARCHIVE-DATA

    0000000344,0000000001,16,0000000001, 00,000,000,03,VIEW-FILE

    0000000345,0000000004,12,0000000010, 00,001,000,03,ARCH-DATE                     ,HHYYYYMMDD

    0000000349,0000000001,16,0000000001, 00,000,000,02,SOURCE

    0000000350,0000000001,16,0000000001, 00,000,000,02,PRE-ORDER-F

    0000000351,0000000005,12,0000000013, 00,000,000,02,PICKUP-CENTER

    0000000356,0000000004,01,0000000004, 00,000,000,02,BEG-BUS-CTR

    0000000360,0000000004,01,0000000004, 00,000,000,02,END-BUS-CTR

    0000000364,0000000009,01,0000000009, 00,000,000,02,DOCUMENT-ID

    0000000373,0000000001,16,0000000001, 00,000,000,02,DIST-STATUS

    0000000374,0000000002,01,0000000002, 00,000,000,02,RANK

    0000000376,0000000002,01,0000000002, 00,000,000,02,MEM-CAT

    0000000378,0000000006,16,0000000006, 00,000,000,02,RELEASED-INIT

    0000000384,0000000004,12,0000000010, 00,001,000,02,RELEASED-DATE                 ,HHYYYYMMDD

    0000000388,0000000004,12,0000000010, 00,000,000,02,RELEASED-TIME

    0000000392,0000000060,16,0000000060, 00,000,999,02,MISC-IDS

    0000000392,0000000012,01,0000000012, 00,000,000,03,MISC-ID-1

    0000000404,0000000012,01,0000000012, 00,000,000,03,MISC-ID-2

    0000000416,0000000012,01,0000000012, 00,000,000,03,MISC-ID-3

    0000000428,0000000012,01,0000000012, 00,000,000,03,MISC-ID-4

    0000000440,0000000012,01,0000000012, 00,000,000,03,MISC-ID-5

    0000000452,0000000001,16,0000000001, 00,000,000,02,INITIAL-ORDER-FLG-F

    0000000576,0000001500,16,0000001500, 00,000,000,02,CUSTOM-DATA

    0000000576,0000000130,16,0000000130, 00,000,999,02,XGO-CUSTOM-DATA

    0000000576,0000000001,16,0000000001, 00,000,001,03,INITIAL-ORDER-FLG-MANUAL-F

    0000000577,0000000100,16,0000000100, 00,000,001,03,INITIAL-ORDER-FLG-REASON

    0000000677,0000000025,16,0000000025, 00,000,001,03,INITIAL-ORDER-FLG-USER

    0000000702,0000000004,12,0000000010, 00,001,001,03,INITIAL-ORDER-FLG-DATE        ,HHYYYYMMDD

  • Verified Answer

    We can see under "Key Section" that RECORD_DATE is a key field so it may not be updated.

  • OK, so that field is not updatable at all? Is it possible to change the definition of the table temporarily, then change it back?

    If not I guess I can always delete the rows I want to change and do an INSERT instead.

  • The table definition may not be changed.  Deleting the row then inserting a new row with the desired values is the correct way to manage it.