Oninit cdi / Archive
Talk to an expert →

DBLOAD and dates

Posted in 2000

Topics: General Discussion

↪ replying to lachlan Dunlop
mars1972@my-deja.com — — source: Informix-list mailing list archive (1991-1998)

Can you give us the schema of the table? In article <39A75031.F9C03C@lach.net>, lachlan Dunlop <lach@lach.net> wrote: > I'm having a hell of a time trying to load some data from mysql to > informix. > > It has to do with dates. I have DBDATE set to "MDY4/". When I try to > run dbload I get invalid month? > > Here is a data example: > > In INSERT statement number 1 of raw data file loan.txt. > Row number 1 is bad. > 1|Mansion on the Hill|Hill 1, lot 24, Ralph's > Addition|1000000.0000|1000000.0000 > |400000.0000|12/01/1915|8.5000|08/22/2000|1|1|09/01/2000|500.0000|0|N|N| 0|N|N|4| > > 2|1|1|1|2|3|1|3|3|3|2|0|N|1| > > Invalid month in date > > Thanks > > Lach > > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

↪ replying to lachlan Dunlop
Jonathan Leffler — — source: Informix-list mailing list archive (1991-1998)

lachlan Dunlop wrote: > I'm having a hell of a time trying to load some data from mysql to > informix. > > It has to do with dates. I have DBDATE set to "MDY4/". When I try to > run dbload I get invalid month? > > Here is a data example: > > In INSERT statement number 1 of raw data file loan.txt. > Row number 1 is bad. > 1|Mansion on the Hill|Hill 1, lot 24, Ralph's > Addition|1000000.0000|1000000.0000 > |400000.0000|12/01/1915|8.5000|08/22/2000|1|1|09/01/2000|500.0000|0|N|N|0|N|N|4| > > 2|1|1|1|2|3|1|3|3|3|2|0|N|1| > > Invalid month in date There are a couple of possibilities. Since you've set DBDATE explicitly, the most likely one (it seems to me) is that you miscounted the columns somehow and are inserting something other than 12/01/1915, 08/22/2000 or 09/01/2000 into a date column. Since you appear to have 34 columns in your data, is there any chance that your data definition is misaligned with your data? If that's not the trouble, are you using a sufficiently antique shell that you can set DBDATE but not export it? You don't mention which version of the software you are using, but that should not matter. Assuming you have a sufficiently recent version of the servers, you might try using a violations table to catch the problems. Failing that, you could try SQLCMD from the IIUG web site; it is better at diagnosing which field has errors in it (though there is room for improvement, no doubt). Of course, it has to be compiled with ESQL/C (or Client SDK) which makes this a more time-consuming option than the others -- especially as you also have to modify the DBLOAD command file into something that sqlreload (one of the synonyms for sqlcmd) will understand. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

lachlan Dunlop — — source: Informix-list mailing list archive (1991-1998)

I'm having a hell of a time trying to load some data from mysql to informix. It has to do with dates. I have DBDATE set to "MDY4/". When I try to run dbload I get invalid month? Here is a data example: In INSERT statement number 1 of raw data file loan.txt. Row number 1 is bad. 1|Mansion on the Hill|Hill 1, lot 24, Ralph's Addition|1000000.0000|1000000.0000 |400000.0000|12/01/1915|8.5000|08/22/2000|1|1|09/01/2000|500.0000|0|N|N|0|N|N|4| 2|1|1|1|2|3|1|3|3|3|2|0|N|1| Invalid month in date Thanks Lach

↪ replying to mars1972@my-deja.com
lachlan Dunlop — — source: Informix-list mailing list archive (1991-1998)

Here is the create statement: CREATE TABLE loan( loan_id serial NOT NULL CONSTRAINT PK_loan1 PRIMARY KEY, collateral_short_desc CHAR(50) NOT NULL, collateral_long_desc CHAR(255), collateral_sale_value DECIMAL NOT NULL, construction_costs DECIMAL NOT NULL, legal_desc CHAR(255), maturity_date DATE NOT NULL, interest_rate DECIMAL NOT NULL, closing_date DATE NOT NULL, extended_maturity_date DATE, last_payment_date DATE, loan_code_id INTEGER NOT NULL, collateral_code_id INTEGER NOT NULL, profit_center_id INTEGER NOT NULL, loan_rating_id INTEGER NOT NULL, office_code_id INTEGER NOT NULL, late_charge_id INTEGER NOT NULL, loan_type_id INTEGER NOT NULL, responsibility_code_id INTEGER NOT NULL, interest_code_id INTEGER NOT NULL, payment_code_id INTEGER NOT NULL, active DECIMAL(1) DEFAULT 1 NOT NULL, payment_day INTEGER DEFAULT 1 NOT NULL, payment_amount DECIMAL,above_prime DECIMAL(1) DEFAULT 1 NOT NULL); CREATE INDEX IDX_loan_1 ON loan (construction_costs); CREATE INDEX IDX_loan_2 ON loan (maturity_date); CREATE INDEX IDX_loan_3 ON loan (loan_type_id); CREATE INDEX IDX_loan_4 ON loan (active); Lach mars1972@my-deja.com wrote: > Can you give us the schema of the table? > > In article <39A75031.F9C03C@lach.net>, > lachlan Dunlop <lach@lach.net> wrote: > > I'm having a hell of a time trying to load some data from mysql to > > informix. > > > > It has to do with dates. I have DBDATE set to "MDY4/". When I try to > > run dbload I get invalid month? > > > > Here is a data example: > > > > In INSERT statement number 1 of raw data file loan.txt. > > Row number 1 is bad. > > 1|Mansion on the Hill|Hill 1, lot 24, Ralph's > > Addition|1000000.0000|1000000.0000 > > > |400000.0000|12/01/1915|8.5000|08/22/2000|1|1|09/01/2000|500.0000|0|N|N| > 0|N|N|4| > > > > 2|1|1|1|2|3|1|3|3|3|2|0|N|1| > > > > Invalid month in date > > > > Thanks > > > > Lach > > > > > > -- > # unrm / > ksh: unrm: not found > # man cpio > > Sent via Deja.com http://www.deja.com/ > Before you buy.

↪ replying to lachlan Dunlop
Art S. Kagel — — source: Informix-list mailing list archive (1991-1998)

Then your loadfile is off a bit. You have in the example record dates in columns 7, 9, & 12 but in the schema the 7th, 9th, 10th & 11th columns are dates. The 10th and 11th filed in the load record hold the numeric constant '1' which is indeed not a valid date. Art S. Kagel lachlan Dunlop wrote: > > Here is the create statement: > > CREATE TABLE loan( > loan_id serial NOT NULL CONSTRAINT PK_loan1 PRIMARY KEY, > collateral_short_desc CHAR(50) NOT NULL, > collateral_long_desc CHAR(255), > collateral_sale_value DECIMAL NOT NULL, > construction_costs DECIMAL NOT NULL, > legal_desc CHAR(255), > maturity_date DATE NOT NULL, > interest_rate DECIMAL NOT NULL, > closing_date DATE NOT NULL, > extended_maturity_date DATE, > last_payment_date DATE, > loan_code_id INTEGER NOT NULL, > collateral_code_id INTEGER NOT NULL, > profit_center_id INTEGER NOT NULL, > loan_rating_id INTEGER NOT NULL, > office_code_id INTEGER NOT NULL, > late_charge_id INTEGER NOT NULL, > loan_type_id INTEGER NOT NULL, > responsibility_code_id INTEGER NOT NULL, > interest_code_id INTEGER NOT NULL, > payment_code_id INTEGER NOT NULL, > active DECIMAL(1) DEFAULT 1 NOT NULL, > payment_day INTEGER DEFAULT 1 NOT NULL, > payment_amount DECIMAL,> above_prime DECIMAL(1) DEFAULT 1 NOT NULL); > > CREATE INDEX IDX_loan_1 ON loan (construction_costs); > CREATE INDEX IDX_loan_2 ON loan (maturity_date); > CREATE INDEX IDX_loan_3 ON loan (loan_type_id); > CREATE INDEX IDX_loan_4 ON loan (active);> > Lach > > mars1972@my-deja.com wrote: > > > Can you give us the schema of the table? > > > > In article <39A75031.F9C03C@lach.net>, > > lachlan Dunlop <lach@lach.net> wrote: > > > I'm having a hell of a time trying to load some data from mysql to > > > informix. > > > > > > It has to do with dates. I have DBDATE set to "MDY4/". When I try to > > > run dbload I get invalid month? > > > > > > Here is a data example: > > > > > > In INSERT statement number 1 of raw data file loan.txt. > > > Row number 1 is bad. > > > 1|Mansion on the Hill|Hill 1, lot 24, Ralph's > > > Addition|1000000.0000|1000000.0000 > > > > > |400000.0000|12/01/1915|8.5000|08/22/2000|1|1|09/01/2000|500.0000|0|N|N| > > 0|N|N|4| > > > > > > 2|1|1|1|2|3|1|3|3|3|2|0|N|1| > > > > > > Invalid month in date > > > > > > Thanks > > > > > > Lach > > > > > > > > > > -- > > # unrm / > > ksh: unrm: not found > > # man cpio > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy.