[Geoserver-users] Setting timestamp value using sql now() function on commit

g_f · March 18, 2009, 1:45pm

Hello all,
I have a postgres db table that looks like this:
CREATE TABLE user_location
(
user_name character varying(50) NOT NULL,
user_bbox geometry NOT NULL,
user_enter_ts timestamp without time zone DEFAULT now(),
user_location_id serial NOT NULL,
CONSTRAINT user_location_id PRIMARY KEY (user_location_id)
)

Notice the user_enter_ts timestamp with the DEFAULT now() declaration.

What I am trying to accomplish is that upon feature commit(), the bbox and the user_name attributes are created the insert happens and the database takes care of the timestamp generation using the now function.
Unfortunately this is not happening.
If I run the insert in a sql pane: Insert into table (user_name,user_bbox) values (‘somename’,‘somebbox’)

I get a record with the users name, users_bbox and a postgres generated timestamp.

//This code below works well although I am relying on javascript to generate the timestamp(not what I want). The user_name, user_bbox and user_enter_ts all get inserted into the db
Here is the code for the attribute creation:
var d = new Date();
feature.attributes = {‘user_name’:USER,
‘user_enter_ts’:d.toISO8601String(6)
};

//I want to do this which would allow postgres to take care of the timestamp, however all that gets inserted into the db is user_name and user_bbox(no timestamp).
feature.attributes = {‘user_name’:USER };

//I also tried this(which doesnt work either):
feature.attributes = {‘user_name’:USER,
‘user_enter_ts’:now())
};

Any ideas?
Thanks in advance.!

Justin_Deoliveira · March 18, 2009, 3:17pm

Hi,

It would be helpful if we had the exact request being sent to geoserver in the cases where you are not specifying the date in Javascript. Any chance you have firebug available on your machine?

-Justin

g f wrote:

Hello all,
I have a postgres db table that looks like this:
CREATE TABLE user_location
(
  user_name character varying(50) NOT NULL,
  user_bbox geometry NOT NULL,
  user_enter_ts timestamp without time zone DEFAULT now(),
  user_location_id serial NOT NULL,
  CONSTRAINT user_location_id PRIMARY KEY (user_location_id)
)

Notice the user_enter_ts timestamp with the DEFAULT now() declaration.

What I am trying to accomplish is that upon feature commit(), the bbox and the user_name attributes are created the insert happens and the database takes care of the timestamp generation using the now function.
Unfortunately this is not happening.
If I run the insert in a sql pane: Insert into table (user_name,user_bbox) values ('somename','somebbox')

I get a record with the users name, users_bbox and a postgres generated timestamp.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

//This code below works well although I am relying on javascript to generate the timestamp(not what I want). The user_name, user_bbox and user_enter_ts all get inserted into the db
Here is the code for the attribute creation:
var d = new Date();
        feature.attributes = {'user_name':USER,
                              'user_enter_ts':d.toISO8601String(6)
        };

//I want to do this which would allow postgres to take care of the timestamp, however all that gets inserted into the db is user_name and user_bbox(no timestamp).
        feature.attributes = {'user_name':USER };

//I also tried this(which doesnt work either):
        feature.attributes = {'user_name':USER,
                              'user_enter_ts':now())
        };

Any ideas?
Thanks in advance.!

------------------------------------------------------------------------

------------------------------------------------------------------------------
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse(TM)based development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com

------------------------------------------------------------------------

_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

--
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

g_f · March 18, 2009, 3:37pm

Thanks for the reply Justin

This is the POST which works(Using javascript to insert the timestamp)
<wfs:Transaction xmlns:wfs=“http://www.opengis.net/wfs” version=“1.0.0” service=“WFS”>wfs:Insert
<feature:user_location xmlns:feature=“https://www.djc2.org/schemas”>feature:user_bbox
<gml:MultiPolygon xmlns:gml=“http://www.opengis.net/gml”>gml:polygonMember gml:Polygon gml:outerBoundaryIs gml:LinearRing
<gml:coordinates decimal=“.” cs=“,” ts=" ">-159.43359375,-17.05078125 42.36328125,-17.05078125 42.36328125
,50.44921875 -159.43359375,50.44921875 -159.43359375,-17.05078125</gml:coordinates></gml:LinearRing>
</gml:outerBoundaryIs></gml:Polygon></gml:polygonMember></gml:MultiPolygon>
</feature:user_bbox>feature:user_nametest01</feature:user_name>
feature:user_enter_ts2009-03-18T15:32:47.024Z</feature:user_enter_ts></feature:user_location></wfs:Insert></wfs:Transaction>

This doesnt work as I dont generate the timestamp using javascript but rather am relying on postgres to populate it with the DEFAULT VALUE of now().
<wfs:Transaction xmlns:wfs=“http://www.opengis.net/wfs” version=“1.0.0” service=“WFS”>wfs:Insert
<feature:user_location xmlns:feature=“https://www.djc2.org/schemas”>
feature:user_bbox<gml:MultiPolygon xmlns:gml=“http://www.opengis.net/gml”>
gml:polygonMember gml:Polygon gml:outerBoundaryIs
gml:LinearRing<gml:coordinates decimal=“.” cs=“,” ts=" ">-273.1640625,-50.9765625 130.4296875,-50.9765625 130.4296875
,84.0234375 -273.1640625,84.0234375 -273.1640625,-50.9765625</gml:coordinates>
</gml:LinearRing>
</gml:outerBoundaryIs></gml:Polygon></gml:polygonMember></gml:MultiPolygon>
</feature:user_bbox>feature:user_nametest01</feature:user_name></feature:user_location></wfs:Insert></wfs:Transaction>

Isnt there a way to pass the now string like so:
feature.attributes = {‘user_name’:USER,
‘user_enter_ts’:now()
};

Thanks alot!

On Wed, Mar 18, 2009 at 10:17 AM, Justin Deoliveira <jdeolive@anonymised.com> wrote:

Hi,

It would be helpful if we had the exact request being sent to geoserver in the cases where you are not specifying the date in Javascript. Any chance you have firebug available on your machine?

-Justin

g f wrote:

Hello all,
I have a postgres db table that looks like this:
CREATE TABLE user_location
(
user_name character varying(50) NOT NULL,
user_bbox geometry NOT NULL,
user_enter_ts timestamp without time zone DEFAULT now(),
user_location_id serial NOT NULL,
CONSTRAINT user_location_id PRIMARY KEY (user_location_id)
)

Notice the user_enter_ts timestamp with the DEFAULT now() declaration.

What I am trying to accomplish is that upon feature commit(), the bbox and the user_name attributes are created the insert happens and the database takes care of the timestamp generation using the now function.
Unfortunately this is not happening.
If I run the insert in a sql pane: Insert into table (user_name,user_bbox) values (‘somename’,‘somebbox’)

I get a record with the users name, users_bbox and a postgres generated timestamp.

//This code below works well although I am relying on javascript to generate the timestamp(not what I want). The user_name, user_bbox and user_enter_ts all get inserted into the db
Here is the code for the attribute creation:
var d = new Date();
feature.attributes = {‘user_name’:USER,
‘user_enter_ts’:d.toISO8601String(6)
};

//I want to do this which would allow postgres to take care of the timestamp, however all that gets inserted into the db is user_name and user_bbox(no timestamp).
feature.attributes = {‘user_name’:USER };

//I also tried this(which doesnt work either):
feature.attributes = {‘user_name’:USER,
‘user_enter_ts’:now())
};

Any ideas?
Thanks in advance.!

Apps built with the Adobe(R) Flex(R) framework and Flex Builder™ are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse™based development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

–
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

Justin_Deoliveira · March 19, 2009, 4:24am

Hi,

So i looked into the problem and was able to reproduce the issue. The problem lies in the query being generated by the postgis datastore:

INSERT INTO "public"."user_location"
("user_name","user_bbox","user_enter_ts")
VALUES
('test01',setSRID('...'::geometry,4326),null)

As you can see null is explicitly being inserted, which is bogus.

Can you open a bug report on jira.codehaus.org? Thanks.

-Justin

g f wrote:

Thanks for the reply Justin

This is the POST which works(Using javascript to insert the timestamp)
<wfs:Transaction xmlns:wfs="http://www.opengis.net/wfs" version="1.0.0" service="WFS"><wfs:Insert>
<feature:user_location xmlns:feature="https://www.djc2.org/schemas"><feature:user\_bbox>
<gml:MultiPolygon xmlns:gml="http://www.opengis.net/gml"><gml:polygonMember><gml:Polygon><gml:outerBoundaryIs><gml:LinearRing>
<gml:coordinates decimal="." cs="," ts=" ">-159.43359375,-17.05078125 42.36328125,-17.05078125 42.36328125
,50.44921875 -159.43359375,50.44921875 -159.43359375,-17.05078125</gml:coordinates></gml:LinearRing>
</gml:outerBoundaryIs></gml:Polygon></gml:polygonMember></gml:MultiPolygon>
</feature:user_bbox><feature:user_name>test01</feature:user_name>
<feature:user_enter_ts>2009-03-18T15:32:47.024Z</feature:user_enter_ts></feature:user_location></wfs:Insert></wfs:Transaction>

This doesnt work as I dont generate the timestamp using javascript but rather am relying on postgres to populate it with the DEFAULT VALUE of now().
<wfs:Transaction xmlns:wfs="http://www.opengis.net/wfs" version="1.0.0" service="WFS"><wfs:Insert>
<feature:user_location xmlns:feature="https://www.djc2.org/schemas">
<feature:user_bbox><gml:MultiPolygon xmlns:gml="http://www.opengis.net/gml">
<gml:polygonMember><gml:Polygon><gml:outerBoundaryIs>
<gml:LinearRing><gml:coordinates decimal="." cs="," ts=" ">-273.1640625,-50.9765625 130.4296875,-50.9765625 130.4296875
,84.0234375 -273.1640625,84.0234375 -273.1640625,-50.9765625</gml:coordinates>
</gml:LinearRing>
</gml:outerBoundaryIs></gml:Polygon></gml:polygonMember></gml:MultiPolygon>
</feature:user_bbox><feature:user_name>test01</feature:user_name></feature:user_location></wfs:Insert></wfs:Transaction>

Isnt there a way to pass the now string like so:
feature.attributes = {'user_name':USER,
                              'user_enter_ts':now()
        };

Thanks alot!

On Wed, Mar 18, 2009 at 10:17 AM, Justin Deoliveira <jdeolive@anonymised.com <mailto:jdeolive@anonymised.com>> wrote:

    Hi,

    It would be helpful if we had the exact request being sent to
    geoserver in the cases where you are not specifying the date in
    Javascript. Any chance you have firebug available on your machine?

    -Justin

    g f wrote:

        Hello all,
        I have a postgres db table that looks like this:
        CREATE TABLE user_location
        (
         user_name character varying(50) NOT NULL,
         user_bbox geometry NOT NULL,
         user_enter_ts timestamp without time zone DEFAULT now(),
         user_location_id serial NOT NULL,
         CONSTRAINT user_location_id PRIMARY KEY (user_location_id)
        )

        Notice the user_enter_ts timestamp with the DEFAULT now()
        declaration.

        What I am trying to accomplish is that upon feature commit(),
        the bbox and the user_name attributes are created the insert
        happens and the database takes care of the timestamp generation
        using the now function.
        Unfortunately this is not happening.
        If I run the insert in a sql pane: Insert into table
        (user_name,user_bbox) values ('somename','somebbox')

        I get a record with the users name, users_bbox and a postgres
        generated timestamp.

        ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

        //This code below works well although I am relying on javascript
        to generate the timestamp(not what I want). The user_name,
        user_bbox and user_enter_ts all get inserted into the db
        Here is the code for the attribute creation:
        var d = new Date();
               feature.attributes = {'user_name':USER,
                                     'user_enter_ts':d.toISO8601String(6)
               };

        //I want to do this which would allow postgres to take care of
        the timestamp, however all that gets inserted into the db is
        user_name and user_bbox(no timestamp).
               feature.attributes = {'user_name':USER };

        //I also tried this(which doesnt work either):
               feature.attributes = {'user_name':USER,
                                     'user_enter_ts':now())
               };

        Any ideas?
        Thanks in advance.!

        ------------------------------------------------------------------------

        ------------------------------------------------------------------------------
        Apps built with the Adobe(R) Flex(R) framework and Flex
        Builder(TM) are
        powering Web 2.0 with engaging, cross-platform capabilities.
        Quickly and
        easily build your RIAs with Flex Builder, the Eclipse(TM)based
        development
        software that enables intelligent coding and step-through debugging.
        Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com

        ------------------------------------------------------------------------

        _______________________________________________
        Geoserver-users mailing list
        Geoserver-users@lists.sourceforge.net
        <mailto:Geoserver-users@lists.sourceforge.net>
        https://lists.sourceforge.net/lists/listinfo/geoserver-users

    -- Justin Deoliveira
    OpenGeo - http://opengeo.org
    Enterprise support for open source geospatial.

--
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.