Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Unanswered: Lost minutes and seconds from timestamp parameters...

    Hi
    First – sorry for my bad English )

    I have a stored procedure
    Code:
    CREATE OR REPLACE FUNCTION "public"."fun_status_save" (
       _doc_id integer, _doc_status_type_id integer, _doc_status_msg varchar, 
       _doc_status_datetime timestamp, _is_control boolean, _user_id_maker integer, 
       _control_datetime timestamp, _control_datetime_begin timestamp) RETURNS integer AS
    $body$
    DECLARE
    
      _doc_status_id        int4;
      _doc_status_msg_id    int4;
      _user_id              int4;
       sq                   text;
    BEGIN
    ...
       INSERT INTO public.doc_status_list 
       ( doc_id, user_id, doc_status_msg_id, doc_status_type_id, doc_status_datetime)
       VALUES
       (_doc_id,_user_id,_doc_status_msg_id,_doc_status_type_id,_doc_status_datetime);
    ...
    END;
    When I'm execute this procedure from db client (Delphi+ADO+ODBC), a parameters (with timestamp datatype) recive only date, and lost the minutes and seconds.
    In the client I'm set - “10.08.2009 16:00”. After commit, in the base I see 10.08.2009 00:00

    I try to find reason..
    Delphi client code:

    Code:
    procedure my_doc_status_item.SaveSP(conn: TADOConnection);
    var
      ds: TADODataSet   ;
      sp: TADOStoredProc;
    begin
       conn.BeginTrans;
       try
         try
         _doc_status_datetime := StrToDatetime('10.08.2009 16:00');
         sp := TADOStoredProc.Create(nil);
         sp.Connection    := conn;
         sp.ProcedureName := 'public.fun_status_save';
         sp.Parameters.CreateParameter('_doc_id'                , ftInteger , pdInput,  10, doc_id             );
         sp.Parameters.CreateParameter('_doc_status_type_id'    , ftInteger , pdInput,  10, doc_status_type_id );
         sp.Parameters.CreateParameter('_doc_status_msg'        , ftString  , pdInput, 255, doc_status_msg     );
         sp.Parameters.CreateParameter('_doc_status_datetime'   , ftDateTime, pdInput,   8, doc_status_datetime);
         sp.Parameters.CreateParameter('_is_control'            , ftBoolean , pdInput,  10,(is_control or (user_id_maker > 0)));
         sp.Parameters.CreateParameter('_user_id_maker'         , ftInteger , pdInput,  10, user_id_maker      );
         sp.Parameters.CreateParameter('_control_datetime'      , ftDateTime, pdInput,   8, control_datetime   );
         sp.Parameters.CreateParameter('_control_datetime_begin', ftDateTime, pdInput,   8, control_datetime_begin);
         ShowMessage(Sp.Parameters.ParamByName('_doc_status_datetime').Value);
         sp.Open;
         Self.doc_status_id := sp.FieldByName('fun_status_save').AsInteger;
         sp.Close;
         conn.CommitTrans;
         except
            if conn.InTransaction then conn.RollbackTrans;
         end;
       finally
         sp.Free;
       end;
    end;
    ShowMessage(Sp.Parameters.ParamByName('_doc_status _datetime').Value);
    This command show message (10.08.2009 16:00)
    It is means that parameter _doc_status_datetime have value 10.08.2009 16:00
    Ok...

    Next... i'm append to the stored procedure some code:

    Code:
    RAISE LOG 'status_datetime = %',_doc_status_datetime;
    RAISE LOG 'control_datetime = %',_control_datetime;
    RAISE LOG 'control_datetime_begin = %',_control_datetime_begin;

    View log...
    Code:
    2009-08-23 13:37:14 MSDLOG:  status_datetime = 2009-08-10 00:00:00
    2009-08-23 13:37:14 MSDSTATEMENT:  BEGIN;SELECT * FROM public.fun_status_save(464, 80, E'', '2009-08-10'::date, '0', 11, '2009-08-10'::date, '2009-08-10'::date) 
    2009-08-23 13:37:14 MSDLOG:  control_datetime = 2009-08-10 00:00:00
    2009-08-23 13:37:14 MSDSTATEMENT:  BEGIN;SELECT * FROM public.fun_status_save(464, 80, E'', '2009-08-10'::date, '0', 11, '2009-08-10'::date, '2009-08-10'::date) 
    2009-08-23 13:37:14 MSDLOG:  control_datetime_begin = 2009-08-10 00:00:00
    2009-08-23 13:37:14 MSDSTATEMENT:  BEGIN;SELECT * FROM public.fun_status_save(464, 80, E'', '2009-08-10'::date, '0', 11, '2009-08-10'::date, '2009-08-10'::date)
    MSDLOG: control_datetime = 2009-08-10 00:00:00

    I think that the loss occurs in the ODBC driver. it seems that somewhere along the path of drunk … :-D

    When I use ado dataset for inserting a data -

    Code:
            ds.CommandText
               := 'SELECT * FROM control_list WHERE doc_status_id='+IntToStr(doc_status_id);
            ds.Open;
            is_new := (ds.RecordCount=0);
            if is_new then
               ds.Append
            else
               ds.Edit;
    
            ds.FieldByName('doc_status_id'         ).AsInteger  := doc_status_id          ;
            ds.FieldByName('user_id_maker'         ).AsInteger :=  user_id_maker          ;
            ds.FieldByName('control_datetime'      ).AsDateTime:=  control_datetime       ;
            ds.FieldByName('control_datetime_begin').AsDateTime:=  control_datetime_begin ;
            ds.FieldByName('control_datetime_done' ).AsDateTime:=  control_datetime_end   ;
            ds.FieldByName('is_done'               ).AsBoolean :=  is_done                ;
            ds.Post;
    ...then I have no such problems

    why such a situation?
    how to solve this problem?

    Windows version - Vista x64, ODBC 8.04,
    PostgreSQL 8.4.0, compiled by Visual C++ build 1400
    Last edited by Dimasm; 08-25-09 at 23:41.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Just so we're sure... what is the data type of those date fields in the database?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Aug 2009
    Posts
    3
    Quote Originally Posted by loquin
    what is the data type of those date fields in the database?
    the tables that stored proc alter

    Code:
    CREATE TABLE doc_status_list
    (
      doc_status_id integer NOT NULL DEFAULT nextval('doc_status_id_seq'::regclass),
      doc_id integer NOT NULL,
      user_id integer NOT NULL,
      doc_status_msg_id integer,
      doc_status_type_id integer NOT NULL,
      doc_status_datetime timestamp without time zone DEFAULT now(),
      is_last_status boolean DEFAULT false,
      is_vaild_in_group boolean DEFAULT false,
      CONSTRAINT pk_doc_status_list_actual PRIMARY KEY (doc_status_id),
      ...
    )
    CREATE TABLE control_list
    (
      id serial NOT NULL DEFAULT nextval('control_list_id_seq'::regclass),
      doc_status_id integer,
      doc_status_id_arch integer,
      user_id_maker integer,
      control_datetime_begin timestamp without time zone,
      control_datetime timestamp without time zone,
      control_datetime_done timestamp(0) without time zone,
      is_done boolean DEFAULT false,
      CONSTRAINT pk_control_list PRIMARY KEY (id),
      ...
    )

  4. #4
    Join Date
    Aug 2009
    Posts
    3
    I make some screenshorts

    The rows of doc_status_table for doc_id=473 before execute the procedure
    http://zernograd.org/files/screen1.gif

    The delphi debug code and variable watch windows
    http://zernograd.org/files/screen3.gif

    result of ShowMessage(Sp.Parameters.ParamByName('_doc_status _datetime').Value);
    http://zernograd.org/files/screen4.gif

    The rows of doc_status_table for doc_id=473 after execute the procedure
    http://zernograd.org/files/screen5.gif

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •