Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    1

    Question Unanswered: TimeStampConversion

    hi,
    I have a login page after entering username and password iam entering logintime to below table

    CREATE TABLE VH_LOGIN_DTLS (
    USER_ID VARCHAR2(35) NOT NULL ,
    USER_STATUS NUMBER(1) ,
    LOGIN_TIME TIMESTAMP(6) ,
    LOGOUT_TIME TIMESTAMP(6)

    ) ;
    At this point of time iam passing logout time as null.

    After login i will enter into home page in this page logout link is there .when i click this link i want to update logout time .

    Iam facing two problems

    1) I can login from more than one system at a time,so when i click logout link against which row the value will be updated.
    2) I have uset following code
    String dt = "SELECT max(LOGIN_TIME) FROM " + TableList.VH_LOGIN_DTLS + " WHERE USER_ID='" + TEST+ "'";
    RowSet rs = DBUtils.fetchDetachedRowSet(dt);
    if (rs.next()) {
    String time = rs.getString("max(LOGIN_TIME)");
    System.out.println("Debug time....." + time);
    }
    When iam using getTimeStamp() it is showing problem

    For updating iam using below code
    String sql = "UPDATE " + TableList.VH_LOGIN_DTLS + " SET LOGOUT_TIME=" + "cast(systimestamp AS TIMESTAMP)" + " WHERE " + " USER_ID='" + TEST+ "' AND LOGIN_TIME='" + time + "' ";

    Problem is time is string type and LOGIN_TIME is timestamp.
    How can i solve this problem.

    Thanks in Advance..

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    1) If USER_ID is different in different systems, then use USER_ID with NULL LOGOUT_TIME and maximal LOGIN_TIME. Otherwise it is a nice philosophical question - how to uniquely distinguish among duplicates. I am afraid it has no solution except adding a new column uniquely identifying the session (system process id, Oracle session id, some other process identifier).

    2) "it is showing problem" is none Java/Oracle error I am aware of.
    Anyway, at least you should bind the variable(s) instead of hard coding them. I have never used Java, so I cannot describe it in details. These links may give you a rough idea how to use binding:
    http://java.sun.com/j2se/1.4.2/docs/...Statement.html
    http://commons.apache.org/dbutils/examples.html
    Or, just merge these two commands into one:
    Code:
    "UPDATE " <...>
    " AND LOGIN_TIME = (SELECT <...>)"

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Fyi

    FYI: SYSTIMESTAMP is already type TIMESTAMP, no need to 'CAST' it as such!

    Also, you may want to change this:
    Code:
    -- etc --
       ... LOGIN_TIME=TO_TIMESTAMP('" + time + "','{code here the actual format for time variable}'"
    -- etc --
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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