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/