Solved

datetime via jdbc

Posted on 2004-04-14
8
812 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
password_verify with prepared statements 10 35
SQL Insert Query Help, part2 6 56
Get error when search query run in loop 3 43
MySQL Init Waits 25 77
A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
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…
A short film showing how OnPage and Connectwise integration works.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

943 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

15 Experts available now in Live!

Get 1:1 Help Now