Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

datetime via jdbc

Posted on 2004-04-14
8
Medium Priority
?
836 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month20 days, 23 hours left to enroll

810 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