0

I'm converting an application that runs currently under MYSQL to use SQL Server. The upshot is, that I have an odd situation! The existing query works in MySQL via JDBC, and works inside MySQL as a command line query -- yet, the same query is not working via the SQL Server JDBC driver, despite working inside the SQL Server studio from the command line as a query.

Under MYSQL JDBC I get a set of results, under SQL Server JDBC I get 0 results.

I've tried the query this way (which works in SQL Server from the command line) 

SELECT DISTINCT
    x,
    y
FROM
    table1,
    table2
WHERE
    x = y
AND start_date <= '2015-05-15'
AND end_date >= '2015-05-15'
AND direction LIKE 'Direction';

which inside JDBC looks like this:

"select DISTINCT x, y from table1, table2" +
                        "WHERE x=y and start_date <= ? and end_date >= ? and direction like ?"
wax_lyrical
  • 922
  • 1
  • 10
  • 22
  • Perhaps there is no matching data with respect to the joining conditions ? – Abhik Chakraborty May 14 '15 at 11:08
  • I've checked the data in MYSQL and in SQL Server, both are identical, both in structure and content. The MYSQL version returns 2 rows, the SQL Server returns 0 rows. – wax_lyrical May 14 '15 at 11:09
  • you're connecting to the same database as the same user? Are any errors logged? – DaveH May 14 '15 at 11:17
  • @DaveHowes That's the thing, Dave. No errors are logged: it's a local test database. All I have, are incorrect results. I've been though, and checked the databases are connecting via logs. I've noticed the last OR can be removed, and from the command line, and the SQL client returns the correct results set. However, JDBC wise, I'm still getting nothing back, just an empty set. This is part of a larger application, which I can mostly get through via connection to SQL Server: therefore, it's very unlikely to be a users/mixed up connections issue. – wax_lyrical May 14 '15 at 11:33
  • Can you post the code that sits around the select statement so we can see it in context? – DaveH May 14 '15 at 11:52
  • Hi Dave, I've added the JDBC routine concerned, and the two resulting Log4J logs. – wax_lyrical May 14 '15 at 12:13
  • Not sure about mysql, but in SQLServer and others, you sometimes need to cast the datetime to a date in order to do the comparison correctly. Do the dates your passing contain a time as well? Have you tried putting a time into your query on the command line and see what it returns? – Mike May 14 '15 at 13:46

1 Answers1

0

The answer was that SQL Server needed the 'like' delimiting by % %. As in this example:

Using "like" wildcard in prepared statement

Our code worked fine with MySQL driver, but the drivers are obviously different in this respect. I've simplified the post in case anyone else gets this problem, and finds this post.

Community
  • 1
  • 1
wax_lyrical
  • 922
  • 1
  • 10
  • 22