Datetime data type problem with dbimport.

Posted in 2004

Topics: Security, Permissions & Auditing, Data Types & Schema Design, Migration, Import/Export & Data Conversion

Willie Snyman — Mon, 3 May 2004 10:56:17 +0200 — source: Usenet: comp.databases.informix

This is a multi-part message in MIME format. ------_=_NextPart_001_01C430EC.7EF9694A Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi there. =20 When I do a dbexport of a database with tables that include datetime data types it works fine. But when I try to use dbimport it stops at the tables with this error message :=20 =20 Non-numeric character in datetime or interval. =20 I've tried to manually load the rows but with no success. =20 In INSERT statement number 1 of raw data file sec_user.unl. Row number 1 is bad. E1141|MARTHIA|M||BRAND|126|6705300011085|27/08/2003|5|1|Y|27/08/2003 14:20:10|||Willie@test| =20 Non-numeric character in datetime or interval. =20 This is the table layout: =20 create table "informix".sec_user ( userid char(8) not null , name varchar(20) not null , initials char(3) not null , nickname varchar(20), surname varchar(20) not null , raf_id_num integer, id_num char(20) not null , date_create date not null , user_stat_cde char(5) not null , user_type_cde char(5) not null , confidential_sign char(1) not null , date_active datetime year to second, date_inactive datetime year to second, user_basket integer, e_mail varchar(20,1) not null , primary key (userid) constraint "informix".pk_sec_user1 ); revoke all on "informix".sec_user from "public";=20 =20 My environment variables is set to: GL_DATETIME=3D%d/%m/%Y %H:%M:%S DBDATE=3DDMY4/=20 I'me using Informix Dynamic Server Version 9.30.UC2 =20 Any suggestions are welcome. =20 Thanks. =20 =20 ------_=_NextPart_001_01C430EC.7EF9694A Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD><TITLE>Message</TITLE> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Dus-ascii"> <META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR></HEAD> <BODY> <DIV><FONT face=3DArial size=3D2><SPAN class=3D128024308-03052004>Hi=20 there.</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D128024308-03052004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D128024308-03052004>When I = do a dbexport=20 of a database with tables that include datetime data types it works=20 fine.</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D128024308-03052004>But = when I try=20 to use dbimport it stops at the tables with this error=20 message : </SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D128024308-03052004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D128024308-03052004><SPAN=20 class=3D128024308-03052004>Non-numeric character in datetime or=20 interval.</SPAN></SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D128024308-03052004><SPAN=20 class=3D128024308-03052004></SPAN></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D128024308-03052004>I've = tried to=20 manually load the rows but with no success.</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D128024308-03052004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D128024308-03052004>In = INSERT statement=20 number 1 of raw data file sec_user.unl.<BR>Row number 1 is=20 bad.<BR>E1141|MARTHIA|M||BRAND|126|6705300011085|27/08/2003|5|1|Y|27/08/2= 003=20 14:20:10|||Willie@test|</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN = class=3D128024308-03052004>Non-numeric=20 character in datetime or interval.</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D128024308-03052004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D128024308-03052004>This = is the table=20 layout:</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D128024308-03052004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D128024308-03052004>create = table=20 "informix".sec_user<BR>  (<BR>    userid char(8) not = null=20 ,<BR>    name varchar(20) not null = ,<BR>   =20 initials char(3) not null ,<BR>    nickname=20 varchar(20),<BR>    surname varchar(20) not null=20 ,<BR>    raf_id_num integer,<BR>    id_num = char(20) not null ,<BR>    date_create date not null=20 ,<BR>    user_stat_cde char(5) not null = ,<BR>   =20 user_type_cde char(5) not null ,<BR>    confidential_sign = char(1)=20 not null ,<BR>    date_active datetime year to=20 second,<BR>    date_inactive datetime year to=20 second,<BR>    user_basket integer,<BR>    = e_mail=20 varchar(20,1) not null ,<BR>    primary key = (userid) =20 constraint "informix".pk_sec_user1<BR>  );<BR>revoke all on=20 "informix".sec_user from "public";</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D128024308-03052004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D128024308-03052004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D128024308-03052004>My = environment=20 variables is set to:</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN = class=3D128024308-03052004>GL_DATETIME=3D%d/%m/%Y=20 %H:%M:%S</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D128024308-03052004>DBDATE=3DDMY4/</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D128024308-03052004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D128024308-03052004>I'me = using Informix=20 Dynamic Server Version 9.30.UC2</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D128024308-03052004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN class=3D128024308-03052004>Any = suggestions are=20 welcome.</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D128024308-03052004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D128024308-03052004>Thanks.</SPAN></FONT></DIV> <DIV><FONT face=3DArial size=3D2><SPAN=20 class=3D128024308-03052004></SPAN></FONT> </DIV> <DIV><FONT face=3DArial size=3D2><SPAN = class=3D128024308-03052004> </DIV> <DIV><BR></DIV> <DIV><BR></DIV></SPAN></FONT></BODY></HTML> =00 ------_=_NextPart_001_01C430EC.7EF9694A-- sending to informix-list

↪ replying to Willie Snyman

Doug Lawry — Mon, 3 May 2004 12:21:05 +0100 — source: Usenet: comp.databases.informix

Hi Willie. Try to post messages in plain text, or some members get cross! See Options/Send in Outlook. Can you confirm that you are running dbexport and dbimport with GL_DATETIME set the same way for both? If so, this is surely a bug. I would recommend unsetting the variable completely to use the default format %Y-%m-%d %H:%M:%S or at least try d-%m-%Y %H:%M:%S in case it particularly doesn't like the slashes. Hope the weather is better in SA that here: it always rains on a public holiday in UK :-( Regards, Doug Lawry www.douglawry.webhop.org "Willie Snyman" <willie@raf.co.za> wrote: > Hi there. > > When I do a dbexport of a database with tables that include datetime > data types it works fine. > > But when I try to use dbimport it stops at the tables with this error > message: > > Non-numeric character in datetime or interval. > > I've tried to manually load the rows but with no success. > > In INSERT statement number 1 of raw data file sec_user.unl. > Row number 1 is bad. > E1141|MARTHIA|M||BRAND|126|6705300011085|27/08/2003|5|1|Y|27/08/2003 > 14:20:10|||Willie@test| > > Non-numeric character in datetime or interval. > > This is the table layout: > > create table "informix".sec_user > ( > userid char(8) not null , > name varchar(20) not null , > initials char(3) not null , > nickname varchar(20), > surname varchar(20) not null , > raf_id_num integer, > id_num char(20) not null , > date_create date not null , > user_stat_cde char(5) not null , > user_type_cde char(5) not null , > confidential_sign char(1) not null , > date_active datetime year to second, > date_inactive datetime year to second, > user_basket integer, > e_mail varchar(20,1) not null , > primary key (userid) constraint "informix".pk_sec_user1 > ); > revoke all on "informix".sec_user from "public";> > My environment variables is set to: > > GL_DATETIME=%d/%m/%Y %H:%M:%S > DBDATE=DMY4/> > I'me using Informix Dynamic Server Version 9.30.UC2 > > Any suggestions are welcome. > > Thanks.

↪ replying to Doug Lawry

Doug Lawry — Mon, 3 May 2004 12:48:30 +0100 — source: Usenet: comp.databases.informix

I have just done my own tests running the following SQL with GL_DATETIME set to anything except the default: create temp table test (timestamp datetime year to second); insert into test values (current); unload to "test.unl" select * from test; load from "test.unl" insert into test; Result from 9.30.UC3 on AIX 4.3: 1263: A field in a datetime or interval value is incorrect or an illegal operation specified on datetime field. Result from 9.30.UC5 on Suse 2.4 1262: Non-numeric character in datetime or interval. I don't have access to a working 9.4 instance from here. Could someone try it? "Doug Lawry" <lawry@nildram.co.uk> wrote: > Hi Willie. > > Try to post messages in plain text, or some members get cross! See > Options/Send in Outlook. > > Can you confirm that you are running dbexport and dbimport with GL_DATETIME > set the same way for both? If so, this is surely a bug. I would recommend > unsetting the variable completely to use the default format > > %Y-%m-%d %H:%M:%S > > or at least try > > d-%m-%Y %H:%M:%S > > in case it particularly doesn't like the slashes. > > Hope the weather is better in SA that here: it always rains on a public > holiday in UK :-( > > Regards, > Doug Lawry > www.douglawry.webhop.org > > > "Willie Snyman" <willie@raf.co.za> wrote: > > > Hi there. > > > > When I do a dbexport of a database with tables that include datetime > > data types it works fine. > > > > But when I try to use dbimport it stops at the tables with this error > > message: > > > > Non-numeric character in datetime or interval. > > > > I've tried to manually load the rows but with no success. > > > > In INSERT statement number 1 of raw data file sec_user.unl. > > Row number 1 is bad. > > E1141|MARTHIA|M||BRAND|126|6705300011085|27/08/2003|5|1|Y|27/08/2003 > > 14:20:10|||Willie@test| > > > > Non-numeric character in datetime or interval. > > > > This is the table layout: > > > > create table "informix".sec_user > > ( > > userid char(8) not null , > > name varchar(20) not null , > > initials char(3) not null , > > nickname varchar(20), > > surname varchar(20) not null , > > raf_id_num integer, > > id_num char(20) not null , > > date_create date not null , > > user_stat_cde char(5) not null , > > user_type_cde char(5) not null , > > confidential_sign char(1) not null , > > date_active datetime year to second, > > date_inactive datetime year to second, > > user_basket integer, > > e_mail varchar(20,1) not null , > > primary key (userid) constraint "informix".pk_sec_user1 > > ); > > revoke all on "informix".sec_user from "public";> > > > My environment variables is set to: > > > > GL_DATETIME=%d/%m/%Y %H:%M:%S > > DBDATE=DMY4/> > > > I'me using Informix Dynamic Server Version 9.30.UC2 > > > > Any suggestions are welcome. > > > > Thanks.

Glossary

dbexport / dbimport