SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-19-2008, 09:17 PM
| ||||
| ||||
 Datetime data type problem with dbimport. 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|2 7/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      |
|
04-19-2008, 09:17 PM
| |||
| |||
| Re: Datetime data type problem with dbimport. 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. |
|
04-19-2008, 09:17 PM
| |||
| |||
| Re: Datetime data type problem with dbimport. 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. |
|
04-19-2008, 09:17 PM
| |||
| |||
| Re: Datetime data type problem with dbimport. Can you specify the exact GL_DATETIME you set? I set this on 9.40FC3W2 on HP-UX 11.11: informix@hamvpar2[/home/system/informix]export GL_DATETIME="%d/%m/%Y %H:%M:%S" informix@hamvpar2[/home/system/informix] dbaccess system << ! > 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; > ! Database selected. Temporary table created. 1 row(s) inserted. 1 row(s) unloaded. 1262: Non-numeric character in datetime or interval. 847: Error in load file line 1. Error in line 1 Near character position 38 Database closed. "Doug Lawry" <lawry@nildram.co.uk> wrote in message news:4096318d$0$95318$65c69314@mercury.nildram.net ... > 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. > > |
|
04-19-2008, 09:17 PM
| |||
| |||
| Re: Datetime data type problem with dbimport. Neil Truby wrote: > Can you specify the exact GL_DATETIME you set? > > I set this on 9.40FC3W2 on HP-UX 11.11: > > informix@hamvpar2[/home/system/informix]export GL_DATETIME="%d/%m/%Y > %H:%M:%S" > informix@hamvpar2[/home/system/informix] dbaccess system << ! > >> 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; >>! > > > Database selected. > > > Temporary table created. > > > 1 row(s) inserted. > > > 1 row(s) unloaded. > > > 1262: Non-numeric character in datetime or interval. > > 847: Error in load file line 1. > Error in line 1 > Near character position 38 > > > Database closed. Well, that looks pretty comprehensively like a bug somewhere - you should be able to load that which you just unloaded. You're probably going to be horrified to hear that it isn't obvious where the bug is. The start of the problem is that neither LOAD nor UNLOAD is built into the server. Most likely, the data sent by the server to dbaccess for the unload is a binary data representation of the datetime value. DB-Access then formats it using client-side formatting. The inverse operation should apply to LOAD - the client-side code should convert the string to the binary format. Howeer, there's a decent chance that the string is being sent to the client unconverted (I don't have a system with Informix on it in front of me, so I can't verify). Assuming that my inference/guess is correct, questions arising include: should LOAD need to do that conversion, and should the server be able to do that conversion, or both? I'm not sure that SQLCMD is immune from this problem, either. It uses the dtcvasc() function (see convertdata() in readload.c), and that does not provide any option to specify the data format; likewise, on output, it uses dttoasc(). It has more sophisticated controls over DATE values. So, if you've gotten exotic with your unload format, part of the problem could be that dtcvasc() cannot process that which dttoasc() just produced. > "Doug Lawry" <lawry@nildram.co.uk> wrote in message > news:4096318d$0$95318$65c69314@mercury.nildram.net ... > >>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 -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ |
|
04-19-2008, 09:18 PM
| ||||
| ||||
| Re: Datetime data type problem with dbimport. scottishpoet wrote: > I think this is related to GL_DATETIME unloading in the specified > format but not loading in from the specified format. > > Try unloading and reloading the data with GL_DATETIME unset in both > scenarios. That's the obvious workaround - I should have mentioned it. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ |
Linear Mode
