Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am pulling data from remote sqlserver to local mysql server using jdbc .

In remote sqlserver server I have 2 columns 

Name  Count

In local mysql I have 3 columns made 

Name  Count  Time_stamp

time_stamp as datetime datatype

I am pulling data using below insert code segment,

while (rs.next()) {
                    String zoneName = rs.getString("Name");
                   int zonecount = rs.getInt("Count");



                    String insertSql = "insert into occupancy.occupancy_phase_2(Name,Count)values('"+zoneName+"',"+zonecount+")";                 
                   pstmt = con2.prepareStatement(insertSql);
                    pstmt.executeUpdate();

I want to add timestamp to the insert query at every run .

How can i do this.

share|improve this question

2 Answers

up vote 0 down vote accepted

You can use NOW() function of mysql to get the currentdate and insert it (Here I am assuming the database server to be in same timezone as application server)

You can use following query string

String insertSql = 
"insert into occupancy.occupancy_phase_2(Name,Count,Time_stamp)values('"+zoneName+"',"+zonecount+", Now())";

Also you directly give values instead of specifying the column

String insertSql = 
    "insert into occupancy.occupancy_phase_2 values('"+zoneName+"',"+zonecount+", Now())";

NOTE:

While using prepared statement use the PreparedStatement instance to set the query parameters, like below

String insertSql = 
        "insert into occupancy.occupancy_phase_2 values(?,?, Now())";
PreparedStatement ps = connection.prepareStatement(insertSql);
ps.setString(1, zoneName);
ps.setInt(2, zonecount);
share|improve this answer
 
yes its working ...thanks, –  user1896796 Jul 11 at 14:27
up vote 1 down vote

Another option is to use the type "timestamp" in the mysql table and it will be automatically filled in by mysql.

for example,

create table if not exists blammo
(
  Name varchar(250),
  Count int,
  last_updated timestamp
);

then just insert values into Name and Count.

for example,

insert into blammo
(Name, Count) values ('kapowzie', 17);

The timestamp field will update every time the row changes unless you set it to it's current value. There is a way to define the column to disable the automatic updating, but I can't remember the syntax for that.

For details, check the MySql Reference

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.