Solved

datetime via jdbc

Posted on 2004-04-14
8
809 Views
Last Modified: 2011-09-20
Grettings,
  I have a table with a datetime column(CHECK_IN_TIME), among other columns.  I would like to be able to go to
it via JDBC and do some searches using the CHECK_IN_TIME column as the qualifier. My query statement(see below) seems
to work fine via the interactive mode (mysql) but throws an exception when I used it via JDBC.  Any help is
greatly appreicated.

select count(*) from MY_TABLE  where CHECK_IN_TIME > '2004-04-14 14:04:29'
0
Comment
Question by:johnchan2000
  • 4
  • 3
8 Comments
 
LVL 3

Expert Comment

by:eicheled
ID: 10828636
Rather than writing the code, can we see yours?
0
 
LVL 1

Author Comment

by:johnchan2000
ID: 10828970
Not sure what's your response is about.  I am not asking about writing codes.
The question is pertaining as to why the embedded sql(select count(*) from MY_TABLE  where CHECK_IN_TIME > '2004-04-14 14:04:29') works in interactive mode but not as an embedded statement in a Java application via JDBC.  FYI, this is the exception being thrown:

java.sql.SQLException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '> '2004-04-14 14:04:29'' at line 1"
0
 
LVL 3

Expert Comment

by:eicheled
ID: 10829604
I am asking HOW you are putting this sql statement into JDBC, it makes all the difference. Is it a string, EXACTLY as you have shown? Is it a prepared statement? Etc etc.

If we see the code, we can more easily determine what might be wrong, rather than trying to guess.
0
 
LVL 1

Author Comment

by:johnchan2000
ID: 10829808
OK.  I get it.  Some background info.  I have written this java-based tool to 'replicate' data between different databases.  Originally I wrote this to run against Sybase(ASE 12.x) and it worked perfectly.  Recently, we migrated it to mysql and are encountering some problems.  Basically, what the program does is allows the user to connect to two databases (a target database and a source database).  When the user click on the 'replicate' button, the program probe both databases (tables) to determine when it was last updated (CHEC_IN_TIME).  If the source database has any records that has the CHECK_IN_TIME > max(CHECK_IN_TIME) of the target database then that means there are records from the source database that I need to pull.

Here's the method that helps create the select statement:
/*
   * Create sql statement for retrieving records from
   * remote database
   */
  private String createSelectStmt(String lastReplicate) {
    String remoteName = sourceDbManager.getTableName();
    String localName = targetDbManager.getTableName();

    String retVal = new String();
    if (lastReplicate == null) {
      retVal = "select * from " + remoteName;
    }
    else {
      retVal = "select * from " + remoteName +
          "where CHECK_IN_TIME > '" + lastReplicate + "'";
    }
    return retVal;
  }

This method helps insert the 'pulled' data into the target database:

private synchronized void insertRecord(ResultSet sourceDbResultSet)
{
    ResultSetMetaData rmd;
    try {
      ResultSet targetResultSet=
          targetDbManager.executeQuery("select * from " +
                                       targetDbManager.getTableName() +
                                       " where CHECK_IN_TIME = -0.09");
      int numberOfColumns =
          sourceDbResultSet.getMetaData().getColumnCount();
      targetResultSet.first();
      while (sourceDbResultSet.next())
     {
        targetResultSet.moveToInsertRow();
        for (int i = 1; i <= numberOfColumns; i++)
       {
          targetResultSet.updateObject(i, sourceDbResultSet.getObject(i));
        }
        targetResultSet.insertRow();
        targetResultSet.moveToCurrentRow();
      }
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }

...and then there's the code that called these two
public void replicate()
{
        this.sqlStmt = createSelectStmt(lastReplicate);
        rset = sourceDbManager.executeQuery(this.sqlStmt);
        if (rset != null)
        {
           insertRecord(rset);
           rset.close();
        }
      }

I hope this clarify things a bit.  Any help is appreciated.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Accepted Solution

by:
eicheled earned 100 total points
ID: 10830337
I understand what you are doing, but I don't see an immediate problem. I wrote the sample test case below and it works fine.
I have CHECK_IN_TIME as a DATETIME column. This is on mysql v3.23 on Windows XP.

I assume that the statement that is throwing the exception is :
>> rset = sourceDbManager.executeQuery(this.sqlStmt);

Maybe print out your retval string before you return it to make sure it looks exactly like you expect?

Sorry I don't have anything better to offer.

*******************************


import java.sql.*;

public class MyTest {
static    String driver="com.mysql.jdbc.Driver";
static    String url="jdbc:mysql://localhost/xxx";
static    String userid = "xxx";
static    String password = "xxx";

    public static void main (String args[] ) {
      try {
      // Load the driver
      Class.forName(driver);
      // Obtain a connection to the database
      Connection conn = DriverManager.getConnection(url, userid, password);
      // Prepare the query statement
      Statement stmt = conn.createStatement();
      // Execute the SELECT statement
      String checkTime= "2003-03-12 12:00:07";
      String sql = "select * from MY_TABLE where CHECK_IN_TIME < '" + checkTime + "'";
      ResultSet rs = stmt.executeQuery( sql);
      
      while (rs.next()) {
          System.out.println ("Got a row");
      }
      } catch (Exception e) {
          e.printStackTrace();
      }
    }
}
0
 
LVL 1

Author Comment

by:johnchan2000
ID: 10834256
I got it fixed.  Based on your last reply, basically you confirmed that there's nothing wrong with the embedded sql (actually I was concerned about the datetime).  Went back and print out the sqlStmt and show that it was not properly put together.  Look at the createSelectStmt(), I left out a SPACE before the where clause and thus the sql generated is:
select * from MY_TABLEwhere...
 instead of
select * from MY_TABLE where ....

Thanks for taking the time looking into it.
0
 
LVL 3

Expert Comment

by:eicheled
ID: 10834450
Glad you found it!
0
 

Expert Comment

by:Crystalxxx
ID: 29951751
In this case, if i want to search by date only on the datestamp. How can I do that?
I checked mysql database in JDBC  in netbeans. however, instead of showing "2010-03-01 12:00:00", it indicates 2010-03-01 12:00:00.000, im  just wondering where are the last three digits from. Can anyone help please?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now