Ummmm will give it a try, thanks. Givens me another direction to go
down.
Cheers.
Joan.
From: openroad-users-bounces (AT) peerlessit (DOT) com
[mailto
penroad-users-bounces (AT) peerlessit (DOT) com] On Behalf Of Paul White
Sent: 08 January 2009 14:52
To: International OpenROAD Users; Martin Bowes
Cc: info-ingres (AT) kettleriverconsulting (DOT) com; Chris M. Clark
Subject: Re: [Openroad-users] [Info-Ingres] Copying to
flatfilecausingerrorthrough IngresGateway.
Hi Joan,
Can you set up a temporary holding table which is a closer match to the
ingres table?
Use copydb to create a copy.in script from the Ingres DB.
Change the copy.in script table name (take out indexes and unsupported
storage modifiers
Run the modified copy.in script into EA/Oracle.
Then try importing the data into the holding table.
Run some sql to transfer the data to the real table.
does this make sense?
Paul
-----Original Message-----
From: openroad-users-bounces (AT) peerlessit (DOT) com
[mailto
penroad-users-bounces (AT) peerlessit (DOT) com]On Behalf Of Berry, J
(Joan)
Sent: Friday, 9 January 2009 12:42 AM
To: International OpenROAD Users; Martin Bowes
Cc: info-ingres (AT) kettleriverconsulting (DOT) com; Chris M. Clark
Subject: Re: [Openroad-users] [Info-Ingres] Copying to flat
filecausingerrorthrough IngresGateway.
Hi Durwin,
I connected via an isql session to the ORACLE machine via the
gateway and ran the following :
1> copy table ral_new_staff() into 'c:\ral_new_staff.ingres'
(8 rows)
2> commit
3> copy table ral_new_staff() from 'c:\ral_new_staff.ingres'
(8 rows)
End of Request
And they both run ok..... ummm
Tried using the copy into command on the Ingres Database on the
production server and this failed with the end of file encountered
error.
So it's looking like it's the ORACLE NUMBER MAPPING or the
actually transfer of the binary object from Oracle to Ingres.
Not sure what to do now, I've pretty much run out of ideas L
Joan.
From: openroad-users-bounces (AT) peerlessit (DOT) com
[mailto
penroad-users-bounces (AT) peerlessit (DOT) com] On Behalf Of Durwin
Wright
Sent: 08 January 2009 11:45
To: International OpenROAD Users; Martin Bowes
Cc: info-ingres (AT) kettleriverconsulting (DOT) com; Chris M. Clark
Subject: Re: [Openroad-users] [Info-Ingres] Copying to flat
filecausingerror through IngresGateway.
I do not know if the following is your problem but if you do the
following you will get the E_CO002B in boty ingres and Enterprise Access
1. Connect to a database
2. Issue the command: copy table mytable() into
'mytable.dat'
3. FTP mytable.dat as an ASCII file instead of a BINARY
file to another machine
4. Connect to a database
5. Create a new table called newtable that is identical to
the old mytable.
6. Issue the following command: copy table newtable() from
'mytable.dat'
You can also run into this ifi the architectures of the original
client platform and new platform are different. If this is the case
then you can connect via Ingres Net to the database on the new platform
and do the 'copy table into' and 'copy talbe from' on the same machine.
That might be a simple test that you can do (if anything that I
said is applicable). Try doing the COPY TABLE INTO and COPY TABLE FROM
on the same client and see if you get the same error. The only reason
BINARY COPY exists in the Enterprise Access product is to support Ingres
Star. This is how Ingres Star works.
There could also be a discrepancy between the ORACLE NUMBER
MAPPING on the source and target systems.
Thes are just a few ideas.
Durwin Wright | Sr. Architect | Durwin.Wright (AT) ingres (DOT) com
<mailto
urwin.Wright (AT) ingres (DOT) com> | Ingres | 500 Arguello Street |
Suite 200 | Redwood City | CA | 94063 | USA
<http://maps.google.com/maps?q=500+ar...ll=37.487297,-
122.233200&spn=0.004602,0.012771&t=k&hl=en> +1 650-587-5523 | fax: +1
650-587-5550 | "Wag the Dog"
________________________________
From: openroad-users-bounces (AT) peerlessit (DOT) com
[mailto
penroad-users-bounces (AT) peerlessit (DOT) com] On Behalf Of Berry, J
(Joan)
Sent: Thursday, January 08, 2009 1:51 AM
To: Martin Bowes
Cc: info-ingres (AT) kettleriverconsulting (DOT) com; Chris M. Clark;
International OpenROAD Users
Subject: Re: [Openroad-users] [Info-Ingres] Copying to flat file
causingerror through IngresGateway.
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
-------------------__________________________________________________ ___
_________
Re: [Info-Ingres] [Openroad-users] Copying toflatfilecausingerrorthrough IngresGateway. -
01-08-2009
, 08:58 AM
Linear Mode
