Solved

datetime via jdbc

Posted on 2004-04-14
8
827 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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
 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 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