Question Import\export Headache

[stokefc22]

We have a routine that allows for table records to be exported to a .d file and then Imported into tables in a different DB, all very simple I hear you say! Well it's never that straightforward is it...

We have a table called order and a table called orderh when orders reach a certain status they are copied to orderh and order deleted, they are supposed to identical but it turns out that orderh has an additional field.

My export of orderh excludes the additional field so in theory its the same as order. When we import into the new DB we put orderh records into order. This has happily chugged along for a while but I've now noticed a record where data is ending up in the wrong fields on the order table.

I have noticed that the order# and position values of the fields in the two tables do not match up for some of the fields does anyone know if this or anything else would cause this type of problem when exporting and importing data?

TIA

 

[oli]

If you are using the EXPORT statement or the "dump_d.p" (from the Data Administration) to generate your .d files, the "Position" value of your fields must match in both databases.

 

[GregTomkins]

Since it's only one record affected, just a suggestion: inspect every byte in the offending record, looking for non-printable characters like null, CR, LF, etc. My guess is, something unusual is lurking inside a field.

To do this you can use Linux hexdump, or HEdit in Windows, or even REPEAT: READKEY in Progress (though I'm always slighty suspicious of that one). Don't use a normal text editor, though.

 

[stokefc22]

Since it's only one record affected, just a suggestion: inspect every byte in the offending record, looking for non-printable characters like null, CR, LF, etc. My guess is, something unusual is lurking inside a field.

To do this you can use Linux hexdump, or HEdit in Windows, or even REPEAT: READKEY in Progress (though I'm always slighty suspicious of that one). Don't use a normal text editor, though.

Cheers Greg, I took your advice and downloaded HEdit but I can't see anything obvious... Below is the data, any other ideas?

1665598 "C6330" 08/10/15 50448 "516291/086783/A" 19/10/15 1530 ? "C6330" 57 57 57 "5" "EmailEdi" "" "Rose " "L92 Kingsway " "Team Valley" "Gat " "Tyne " "" "NE1" "UK" "LM" "" 500 "125L" "125T" "" "" "D" 920 1005 10486 10486 10751 9695 9940 355 "" "" 0 0 0 0 no "P" "" 18741 30052 0 "" no "50" "111577668" 1 0 12/10/15 75883 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 no 0 "" "DF" "" "KSQM" 10591 11010 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

 

[GregTomkins]

That's ASCII data, you want to look at the binary/raw version of the same data. Here is an example of a simple file in both formats, using Linux hexdump:

progress@rwsdev01 appserver$ cat /tmp/foo
data
world
progress@rwsdev01 appserver$ hexdump /tmp/foo
0000000 6164 6174 770a 726f 646c 000a
000000b

You can see the hard returns as '0a' in between the two words and also at the end. The '00' at the very end (due to NUXI, bytes are in reverse order) isn't actually there, it's just how hexdump shows it, the final character is actually the '0a'. You are looking for '00' especially, but any non-printable, somewhere other than at the very end of the record and file. HTH.

 

[stokefc22]

That's ASCII data, you want to look at the binary/raw version of the same data. Here is an example of a simple file in both formats, using Linux hexdump:

progress@rwsdev01 appserver$ cat /tmp/foo
data
world
progress@rwsdev01 appserver$ hexdump /tmp/foo
0000000 6164 6174 770a 726f 646c 000a
000000b
[ATTACH]1439[/ATTACH]

You can see the hard returns as '0a' in between the two words and also at the end. The '00' at the very end (due to NUXI, bytes are in reverse order) isn't actually there, it's just how hexdump shows it, the final character is actually the '0a'. You are looking for '00' especially, but any non-printable, somewhere other than at the very end of the record and file. HTH.

Ahh.. ok thanks Greg.

I've now managed to check the hex values and all seems well the only suspicious info is right on the end of the file (see attached) and it seems like you say to ignore that so I guess it's back to the drawing board

Is there anything in Oli's comments above do you think? I would of thought if that was the case it would fail every time?

 

[TheMadDBA]

Verify that the list fields are in the exact same order in both databases... depending on the data types and the differences in order you might not get errors when loading. But that should be happening on more than one record.

If you are 100% sure that it is only for one record then Greg has you on the right track.

 

[GregTomkins]

What happens if you take that same record and try to load it again? You can experiment with this with a temp-table defined LIKE the real table.

 

[stokefc22]

Verify that the list fields are in the exact same order in both databases... depending on the data types and the differences in order you might not get errors when loading. But that should be happening on more than one record.

If you are 100% sure that it is only for one record then Greg has you on the right track.

This is (to my knowledge) the second time this has happened the first time I thought it was the extra field that was in the orderh table so I excluded that from the dump and I've not seen it again until now..

I'm not 100% what you by list fields, the fields appear in the same order in the data dictionary but as I said above the order# and position numbers of the fields do not match in the two tables.

 

[stokefc22]

What happens if you take that same record and try to load it again? You can experiment with this with a temp-table defined LIKE the real table.

This works fine, I was initially thinking that this was because the temp-table doesn't carry any of the order# or position info but I'm not sure now.

I edited the fiel to have a different order number to get past the unique index and added to the actual DB without issue too...

 

[TheMadDBA]

The order of the fields alphabetically doesn't matter for the export/import functions... if they match by the order then all should be fine.

When in doubt specify the exact fields in the exact order for the export/import. For production code I almost never use the vanilla export tablename format.

My money is still on a special character or something else wrong with the file though.

 

[stokefc22]

The order of the fields alphabetically doesn't matter for the export/import functions... if they match by the order then all should be fine.

When in doubt specify the exact fields in the exact order for the export/import. For production code I almost never use the vanilla export tablename format.

My money is still on a special character or something else wrong with the file though.

Ok thanks Guys, I've taken your advice and specified each field in the import and export and will keep my eye on it hopefully this will be the end of it. I've wasted too much time chasing it around now, but I suspect it must be the order.