inserting values in database after calculation

[Divya Gehlot]

Hi,
I have one table having coulmns id,Startdate,enddate,duration.
I am taking above three values id,StartDate,endDate from other table and at the same time I have to insert values in column duration according to id and duration will be number of days between startdate and endate for that I have already one function in java but I dont know how to use that java function in the query which is returning number of days.
Can any one help me as I am new to JDBC.

[Paul Campbell]

Originally posted by Divya Gehlot:
Hi,
I have one table having coulmns id,Startdate,enddate,duration.
I am taking above three values id,StartDate,endDate from other table and at the same time I have to insert values in column duration according to id and duration will be number of days between startdate and endate for that I have already one function in java but I dont know how to use that java function in the query which is returning number of days.
Can any one help me as I am new to JDBC.

Insert into mytable (id, StartDate, enddate, duration)
VALUES (id, StartDate, endDate, (to_date(endDate,'date format of endDate') - to_date(StartDate,'date format of StartDate)))

[Divya Gehlot]

Hi Paul,
Thanks for your help.
But I dont want it in this way for calculation of number of days I have one java function( customised function to calculate number of days) want to use that.
I think you didnt read my question properly.
Please do read my question once again and help me out.
Thanks in advance.

[Paul Sturrock]

Originally posted by Paul Campbell:


Insert into mytable (id, StartDate, enddate, duration)
VALUES (id, StartDate, endDate, (to_date(endDate,'date format of endDate') - to_date(StartDate,'date format of StartDate)))

This will work, but a much better route would be to do the calculation in Java and use a prepared statement. It makes you code portable and avoids the need to change things if the date format is changed.

Originally posted by Divya Gehlot:

But I dont want it in this way for calculation of number of days I have one java function( customised function to calculate number of days) want to use that.

In which case, assuming your database supports Java, you will most probably need to implement this query as a Java stored procedure in the database. I don't know if databases that support Java also support compilation on the fly from adhoc queries - you may want to check your database documentation. For what its worth, my personal preference would always be to do calculations in the application not the database.

[Paul Campbell]

Originally posted by Paul Sturrock:


This will work, but a much better route would be to do the calculation in Java and use a prepared statement. It makes you code portable and avoids the need to change things if the date format is changed.

I'm sure the java route is better (I'm just not there yet), but your second statement doesn't make sense to me... if the data base date format changes, it doesn't matter... the date format in the to_date format only describes the format of your date you're passing to the DBMS... and if the date format is changing in your code... you're already making changes there anyway.

Am I misunderstanding your POV?
[ October 26, 2007: Message edited by: Paul Campbell ]

[Divya Gehlot]

Hi Paul,

This will work, but a much better route would be to do the calculation in Java and use a prepared statement. It makes you code portable and avoids the need to change things if the date format is changed.

I need my answer in this manner only I am getting mad as I dont know how to use that java function for each update(insert statement for each row) as id in my table also very random. Please help me out

[Paul Campbell]

Originally posted by Divya Gehlot:
Hi Paul,

I need my answer in this manner only I am getting mad as I dont know how to use that java function for each update(insert statement for each row) as id in my table also very random. Please help me out

Could you post what you have?

[Paul Sturrock]

Originally posted by Paul Campbell:


I'm sure the java route is better (I'm just not there yet), but your second statement doesn't make sense to me... if the data base date format changes, it doesn't matter... the date format in the to_date format only describes the format of your date you're passing to the DBMS... and if the date format is changing in your code... you're already making changes there anyway.

Am I misunderstanding your POV?

[ October 26, 2007: Message edited by: Paul Campbell ]

My point of view is from the Java application. Unless the application treats Dates exclusively as Strings at some point or other you will have to convert it to the String format your SQL expects (a simple toString() will not do - for example if you change the default locale of your JVM this will change the format of this String). You can do this in a safe way using a DateFormat, however that generates more code than is necessary, given you can forget about this if you wrap it up in a by using a PreparedStatement.