Inserting Datetime Y to M with Java
A third-party Java app inserts timestamps as java.sql.Timestamp/strings with fractional seconds (e.g. "2016-03-29 13:50:17.0000"), which fails with error -1264 ("Extra characters at the end of a datetime") against columns defined as DATETIME YEAR TO SECOND or YEAR TO MINUTE. Advice given: pass a string whose precision matches the column, since Informix casts implicitly; where the literal is more precise, cast it explicitly in stages, e.g. '...'::DATETIME YEAR TO FRACTION(5)::DATETIME YEAR TO SECOND, or ::DATETIME YEAR TO SECOND::DATETIME YEAR TO MINUTE. The poster couldn't change the app's generated string, and no confirmation of a final fix is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Is there something other than java.sql.Datetime to insert a datetime? The current scenario we have is a 3rd party product that doesn't officially support Informix(boooo). When they insert a datetime they use java.sql.Datetime which uses datetime(year to fraction). We can wrap a function around the datetimes in the select but it's all or nothing and we have datetime(year to minute) and datetime(year to second) fields so that's not really an option. Any help would be appreciated.
You can always insert an appropriately precise string and the engine will automatically convert it. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 29, 2016 at 1:54 PM, NATE HICKS <nathaniel.hicks@trnswrks.com> wrote: > Is there something other than java.sql.Datetime to insert a datetime? > > The current scenario we have is a 3rd party product that doesn't officially > support Informix(boooo). When they insert a datetime they use > java.sql.Datetime which uses datetime(year to fraction). We can wrap a > function around the datetimes in the select but it's all or nothing and we > have datetime(year to minute) and datetime(year to second) fields so that's > not really an option. > > Any help would be appreciated. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bd76beae84e46052f3438c0
Art, thank you for the quick response.
When I try that it errors in dbaccess and the java stuff.
{
create table test (
ser serial,
dt datetime year to second);
}
insert into test(dt) values (CURRENT);
insert into test(dt) values ("2016-03-29 13:50:17.0000");# ^
# 1264: Extra characters at the end of a datetime or interval.
#
The second insert is what the app is using for year to minute or year to
second.
Try
insert into test(dt) values ("2016-03-29 13:50:17");
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of NATE
HICKS
Sent: Tuesday, March 29, 2016 1:34 PM
To: ids@iiug.org
Subject: Re: Inserting Datetime Y to M with Java [36858]
Art, thank you for the quick response.
When I try that it errors in dbaccess and the java stuff.
{
create table test (
ser serial,
dt datetime year to second);
}
insert into test(dt) values (CURRENT);
insert into test(dt) values ("2016-03-29 13:50:17.0000");# ^
# 1264: Extra characters at the end of a datetime or interval.
#
The second insert is what the app is using for year to minute or year to
second.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
That works Paul but the problem is the string(time) is generated from the 3rd party app that we can't touch. So we can bandaid it but if we bandaid it to use "2016-03-29 13:50:17" it works for year to second but not year to minute.
Try knocking off the extra zeroes. You didn't define the datetime as year to
fraction:
insert into test(dt) values ("2016-03-29 13:50:17");
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> NATE HICKS
> Sent: Tuesday, March 29, 2016 13:34 PM
> To: ids@iiug.org
> Subject: Re: Inserting Datetime Y to M with Java [36858]
>
> Art, thank you for the quick response.
>
> When I try that it errors in dbaccess and the java stuff.
>
> {
> create table test (
> ser serial,
> dt datetime year to second)> ;
> }
>
> insert into test(dt) values (CURRENT);
> insert into test(dt) values ("2016-03-29 13:50:17.0000"); # ^ # 1264:> Extra characters at the end of a datetime or interval.
> #
>
> The second insert is what the app is using for year to minute or year
> to second.
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
Nate:
The error tells the tale and you missed the critical part of my answer. You
have to provide a string "of the appropriate precision". Your column is
YEAR TO SECOND but the string is YEAR TO FRACTION(5). Not appropriate
precision. Instead do either:
insert into test(dt) values ("2016-03-29 13:50:17");
insert into test(dt) values ("2016-03-29 13:50:17.0000"::DATETIME YEAR TOFRACTION(5)::DATETIME YEAR TO SECOND);
Notice that I had to cast your original string to DATETIME YEAR TO
FRACTION(5) then cast that result to DATETIME YEAR TO SECOND. However, the
first string works with no explicit case because the cast is implicit
because the column dt matches the precision of the assumed type of the
string.
Art
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Tue, Mar 29, 2016 at 2:33 PM, NATE HICKS <nathaniel.hicks@trnswrks.com>
wrote:
> Art, thank you for the quick response.
>
> When I try that it errors in dbaccess and the java stuff.
>
> {
> create table test (
> ser serial,
> dt datetime year to second)> ;
> }
>
> insert into test(dt) values (CURRENT);
> insert into test(dt) values ("2016-03-29 13:50:17.0000");> # ^
> # 1264: Extra characters at the end of a datetime or interval.
> #
>
> The second insert is what the app is using for year to minute or year to
> second.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a113ee868a44f96052f34fe2e
"2016-03-29 13:50:17"::DATETIME YEAR TO SECOND::DATETIME YEAR TO MINUTE Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 29, 2016 at 3:12 PM, NATE HICKS <nathaniel.hicks@trnswrks.com> wrote: > That works Paul but the problem is the string(time) is generated from the > 3rd > party app that we can't touch. So we can bandaid it but if we bandaid it to > use "2016-03-29 13:50:17" it works for year to second but not year to > minute. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0139fddc6e6769052f350cb3