 Re: [Info-Ingres] Copying to flat file causing error throughIngresGateway. -
01-08-2009
, 03:51 AM
Hi Martin,
It appears the EA 2.6 only supports the Binary copy, so back to square
one and sorting the original error message. i.e. E_CO0024 COPY:
Unexpected END OF FILE while processing row 1.
E_CO002B COPY: 1 rows could not be processed.
E_CO002A COPY: Copy has been aborted.
And because of this 'end of file' error I went down the C0NL route.
The Cap value = FLOAT(126) and because I'm using Oracle this should be
ok, but I'm unsure L before the 'end of file error' I also get bad data
value for 3 of the columns, structural and column type differences are
playing a part from the in put into the flat file from oracle and the
input into Ingres.
Joan.
From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 08 January 2009 09:13
To: Berry, J (Joan)
Cc: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: RE: [Info-Ingres] Copying to flat file causing error through
IngresGateway.
Hi Joan,
The band column is nullable, yet the copy out and copy in commands do
not allow for nullable data. That's not a problem as long as there are
no nulls in the data.
Do you have any null data in the band column...or any of the others
which are nullable?
If so the copy commands will need to be altered along the lines of:
band=c0tab with null(']^NULL^[')
Also why does the ingres table use floats for fields which the oracle
table has as integer4's ?
Marty
From: Berry, J (Joan) [mailto:joan.berry (AT) stfc (DOT) ac.uk]
Sent: 07 January 2009 22:10
To: Martin Bowes
Cc: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: RE: [Info-Ingres] Copying to flat file causing error through
IngresGateway.
Hi Martin,
Thanks for the suggestion, much appreciated. I've already tried c0tab
and c0comma, however I haven't checked the 4 character years, but I do
have floats as you suggest. This all started when the 'band' column
changed (not my idea !!) from numeric to alpha. A perfectly bulk copy
was copy across the gateway no problem.
This is the Oracle tables structure;-
PID integer 4 no
DLPID integer 4 yes
PWAID varchar 8 yes
TITLE varchar 30 yes
INITIALS varchar 15 yes
FORENAME varchar 30 yes
SURNAME varchar 100 yes
DATE_OF_BIRTH date yes
SEX varchar 1 yes
LABORATORY varchar 1 yes
ORG_UNIT varchar 4 yes
STRUCTURE varchar 18 yes
BAND varchar 15 yes
PAY_NO varchar 15 yes
BU_ID integer 4 yes
BU_NAME varchar 255 yes
START_DATE date yes
END_DATE date yes
FEDERAL_ID varchar 10 yes
STATUS varchar 50 yes
TIME_IMPORTED date yes
ADVERTISED_EMAIL_ADDRESS varchar 80 yes
And this is the Ingres Table structure:-
pid float 8 no
dlpid float 8 yes
pwaid varchar 8 yes
title varchar 30 yes
initials varchar 15 yes
forename varchar 30 yes
surname varchar 100 yes
date_of_birth date yes
sex varchar 1 yes
laboratory varchar 1 yes
org_unit varchar 4 yes
structure varchar 18 yes
band varchar 15 yes
pay_no varchar 15 yes
bu_id float 8 yes
bu_name varchar 255 yes
start_date date yes
end_date date yes
federal_id varchar 10 yes
status varchar 50 yes
time_imported date yes
advertised_email_address varchar 80 yes
Best Regards
Joan.
From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 07 January 2009 20:51
To: Ingres and related product discussion forum
Cc: Berry, J (Joan)
Subject: RE: [Info-Ingres] Copying to flat file causing error through
IngresGateway.
Hi Joan,
Yes this is exactly the right newsgroup to post Ingres questions to.
Try replacing all but the last 'c0NL' with 'c0tab'. The last 'c0NL'
should be 'c0nl'.
Note that this produces an ascii file. You need to be careful of dates
and floats with such files. Looking at the column names I'd guess you
have many dates in this data and probably no floats. Check the data in
the file to ensure you have 4 character years.
The command you've given to load the data uses a binary file not an
ascii file...ie they are incompatible.
If the download works without error then alter the copy for the upload
to read:
copy table ral_new_staff(
pid=c0tab,
dlpid=c0tab,
...etc...
) from 'C:ral_new_staff.txt';
Martin Bowes
________________________________
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com on behalf of Berry,
J (Joan)
Sent: Wed 07/01/2009 16:52
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] Copying to flat file causing error through
IngresGateway.
Dear All,
I wonder if anyone could shine some light on my problem. I have a table
in Oracle (10g) that I am copying out to a flat file. I have tried a
bulk copy which runs successfully through the gateway (EA 2.6/0605
(int.w32/00)) on windows 2003 server, however when I run the same
command with the following syntax :-
COPY table ral_new_staff (
pid = c0NL,
dlpid = c0NL,
pwaid = c0NL,
title = c0NL,
initials = c0NL,
forename = c0NL,
surname = c0NL,
date_of_birth = c0NL,
sex = c0NL,
laboratory = c0NL,
org_unit = c0NL,
structure = c0NL,
band = c0NL,
pay_no = c0NL,
bu_id = c0NL,
bu_name = c0NL,
start_date = c0NL,
end_date = c0NL,
federal_id = c0NL,
status = c0NL,
time_imported = c0NL,
advertised_email_address = c0NL
) into'c:/ral_new_staff.txt';
I get the following error.
E_GX0204 Syntax Error: Found a keyword or token other than that which
was
expected
COPY: Invalid target expressions
(Wed Jan 07 16:29:45 2009)
If I run the copy into command on the Ingres database (II 2.6/0604
(int.w32/0000) on windows server 2003 I get the following message
1> copy table ral_new_staff()
from 'C:\schedules\FinanceProjectOwners\data\ral_new_st aff.txt'
E_CO0024 COPY: Unexpected END OF FILE while processing row 1.
E_CO002A COPY: Copy has been aborted.
Can anyone help? Am I one the right mailing list?
Joan.
__________________________________________________ ____________
Joan Berry - Administrative Database Team Leader
STFC - Science & Technology Facilities Council,
Corporate Information and Communications Technology,
Business Technology Unit, S10, Daresbury Laboratory,
Daresbury, Warrington, Cheshire, WA4 4AD.
Telephone : +44 (0) 1925 60(3412) , Mobile: 07920 703440
E-Mail : joan.berry (AT) stfc (DOT) ac.uk dmthelpdesk (AT) rl (DOT) ac.uk
Internal ADT Web: http://database7a.dl.ac.uk/ADTWeb/index.html
-------------------__________________________________________________ ___
_________
 |
Linear Mode
