IMPORT data from Script in BTEQ

[DFW1999]

Hi,

I have one test data file named "Datain.dat" which has only one entry:
100

I have another file named "JobScript.bteq" with the following code:
.logon demotdat/dbc,dbc ;

.IMPORT DATA FILE = DATAIN.DAT
.QUITE ON
.REPEAT *
USING SYS_ID ( INTEGER)
DELETE FROM Test.BaseTab
WHERE U_ID=:SYS_ID;
.QUIT

I have three records in the BaseTab table with U_id=100. I want to delete these three records by giving input "100" from the datain.dat file in this script.The datatype for U_id is Integer.

When I run this bteq script I get this error:

BTEQ 08.02.00.00 Tue Jun 15 19:04:05 2004

+---------+---------+---------+---------+---------+---------+---------+----
.logon demotdat/dbc,

*** Logon successfully completed.
*** Transaction Semantics are BTET.
*** Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----
.IMPORT DATA FILE = DATAIN.dat
+---------+---------+---------+---------+---------+---------+---------+----
USING SYS_ID ( INTEGER)
.QUITE ON
.REPEAT *
DELETE FROM Test.BaseTab
WHERE U_id=:SYS_ID;
*** Growing Buffer to 12337
*** Error: Import data size does not agree with byte length.
The cause may be:
1) IMPORT DATA vs. IMPORT REPORT
2) incorrect incoming data
3) import file has reached end-of-file.
*** Warning: Out of data.
+---------+---------+---------+---------+---------+---------+---------+----
.QUIT
*** You are now logged off from the DBC.
*** Exiting BTEQ...
*** RC (return code) = 0

Any recommendations?

thanks

 

[dnoeth]

Your syntax/command order is wrong:
.QUIET ON /** not QUITE ***/
.REPEAT *
USING SYS_ID ( INTEGER)

instead of
USING SYS_ID ( INTEGER)
.QUITE ON
.REPEAT *

I can't test right now if this will cause an error.


"I have one test data file named "Datain.dat" which has only one entry:
100"

Is that file readable, i.e. it's '100' as a string?
Then you defined the wrong data format in your script:
.IMPORT DATA FILE = DATAIN.DAT

DATA expects internal format, i.e. 100 as a 4-byte integer.

Try
.IMPORT VARTEXT FILE = DATAIN.DAT
.QUIET ON
.REPEAT *
USING SYS_ID ( VARCHAR(11))

Dieter

 

[DFW1999]

Thanks dnoeth ,

I changed my script according to your advice and now it works fine.