Re: [Info-Ingres] [Openroad-users] Copying to flat filecausing errorthroughIngresGateway.

#1

Paul White wrote:

Quote:

I've just run a quick test on EA/MSSQL R2.6 and found the same problem
as yourself.
Then I found some of my notes from 2004 which says character copy is
not supported through the gateway.

I can confirm this, see the EA DPA guide (Developing Portable
Applications) p 3-28

Quote:

However binary copy is supported. I used binary copy to unload ingres
data from Solaris then reload to MSSQL 2000.

Yup, you can even copy/paste an entire table in Visual DBA from Oracle
to Ingres, and vice-versa.

Quote:

the only gotcha was in the mapping of money fields. We had to multiply
all money values by *.01 after the import.
I cant remember if Oracle has the same gotcha.

For EA 2.1 this is the case, for EA 2.6 it depends on the setting you
have for money mapping (defined at iigwcatmss / iigwcatora time), you
can check this with:

select * from iidbcapabilities where CAP_CAPABILITY = 'GW_MONEY_MAPPING'

If it is set to float you will need to "fix" the values. If you are
using money (with mssql) then there is no need to do this, for Oracle
which you mention, then the decimal setting avoids the need to alter values.

Quote:

If you are doing this from OpenROAD I suspect you'll have to do it the
hard way, cursor string object, line by line. etc.

Just throwing this out there as a raw idea; you could probably modify
DAOGen http://community.ingres.com/wiki/DAOGen_Project and automate a
lot of this.

Chris

Quote:

-----Original Message-----
*From:* info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com]*On Behalf Of
*Graham Jakins
*Sent:* Thursday, 8 January 2009 8:19 AM
*To:* Ingres and related product discussion forum
*Subject:* Re: [Info-Ingres] Copying to flat file causing
errorthroughIngresGateway.

Hi Joan/Marty,

Was wondering if the invalid target expression comes because the
file is specified as ‘C:/ ral_new_staff.txt'.

I am sure that this should be a “\” on Windows not a “/” –
i.e.’C:\ ral_new_staff.txt’

Regards

**Graham Jakins**

**Team Leader Database Engineering**

tel: 08 9340 5234

fax: 08 9340 5111

mbl: 0408 956 864

------------------------------------------------------------------------

*From:* info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] *On Behalf
Of *Martin Bowes
*Sent:* Thursday, 8 January 2009 5:51 AM
*To:* Ingres and related product discussion forum
*Subject:* Re: [Info-Ingres] Copying to flat file causing error
throughIngresGateway.

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

*******************_______________________________ _______________________________

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [Info-Ingres] [Openroad-users] Copying to flat filecausing errorthroughIngresGateway.

comp.databases.ingres comp.databases.ingres

Re: [Info-Ingres] [Openroad-users] Copying to flat filecausing errorthroughIngresGateway. - 01-07-2009 , 05:41 PM