Skip to Content
author's profile photo Former Member
Former Member

Oracle timestamp field insert row

I created an oracle table with a timestamp data type field in it. When I test the query template that inserts the row, I set the date field with a parameter value of "19-SEP-2007 10:02:00" and it works fine.

When I set the Param value in my javascript code to the same value, I get a sql insert error telling me the column length is to long.

Does anyoen have any ideas?

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 20, 2007 at 03:25 AM

    Try TO_TIMESTAMP('[Param.1]') in your query template. That will convert the passed string to the timestamp format which includes fractional seconds too.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 20, 2007 at 01:04 PM

    BJ,

    You can also use [SD] or [ED] instead of [Param.1], and set the associated StartDate or EndDate query property in the same string format as the DateFormat (default is MM/dd/yyyy HH:mm:ss). This way the datetime string will be handled by the data server properties for DatePrefix, DateSuffix, and InternalDateFormat.

    SQLServer is simpler because it takes about any string that looks like a datetime wrapped with single quotes, but Oracle likes something such as TO_DATE('09/19/2007 10:02:00','MM/DD/YYYY HH24:MI:SS')

    By using one of the date tokens the data server itself will take care of the replacement so something like:

    INSERT INTO TABLE VALUES([SD])

    will actually go to the database as:

    INSERT INTO TABLE VALUES(TO_DATE('09/19/2007 10:02:00','MM/DD/YYYY HH24:MI:SS'))

    The only gotcha is that you need to make sure that the DateFormat setting for the query template is how you provide the real date value. In the query template editor, select your desired date from the calendar icon and then change the dateformat drop down (notice that the start date you selected changes format accordingly).

    Then if you are using html and Javascript on the client side to provide this date field to the query object it is as simple as document.APPLETNAME.getQueryObject().setStartDate(XXXX); or document.APPLETNAME.getQueryObject().setEndDate(XXXX);

    An additional trick is that if you use the [ED] token and provide no values for the time settings it will default to the current time on the webserver when the query is processed. You can see this happen in an editor test by clearing out the StartDate and EndDate properties and issuing your query as: INSERT INTO TABLE VALUES([ED])

    Regards,

    Jeremy

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.