Solved

datetime via jdbc

Posted on 2004-04-14
8
829 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

726 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