0

I am using JDBC PreparedStatement to query a Teradata database from a web service. My table has a PHONE_NUMBER column, stored as VARCHAR(10). I have always used PreparedStatement setString() to supply the parameter for this column, like below:

String myPhoneNumber = "5551234567";    
String sql = "SELECT * FROM MYTABLE " +
             "WHERE PHONE_NUMBER = ? ";
PreparedStatement p_stmt = db.getPreparedStatement(sql);
p_stmt.setString(1, myPhoneNumber);
ResultSet rs = db.executeQuery(p_stmt);

It returns correct results, but I noticed the CPU Teradata is using for this query is quite high. According to the EXPLAIN plan, it appears that Teradata is interpreting the myPhoneNumber parameter as a FLOAT, instead of VARCHAR, and so it has to do a data conversion to compare it to the VARCHAR column PHONE_NUMBER. Here is an excerpt of the EXPLAIN plan:

...
MYDATABASE.MYTABLE.PHONE_NUMBER (FLOAT, FORMAT
'-9.99999999999999E-999'))= 5.55123456700000E 009)

So, I came up with the below, which showed a great improvement in CPU usage (99.86% improvement): 

String myPhoneNumber = "5551234567";    
String sql = "SELECT * FROM MYTABLE " +
             "WHERE PHONE_NUMBER = ''||?||'' ";
PreparedStatement p_stmt = db.getPreparedStatement(sql);
p_stmt.setString(1, myPhoneNumber);
ResultSet rs = db.executeQuery(p_stmt);

So my question is why is this necessary? Shouldn't setString tell JDBC to tell Teradata to expect a String/VARCHAR parameter?

Thanks!

ktmq
  • 43
  • 1
  • 8
  • 1
    It certainly seems odd. – Kayaman Nov 30 '16 at 19:45
  • SetString doesn't add quotes around your parameters. Think about it from a concatentation perspective. If you concat `select * from ... where phone_number = ` and myPhoneNumber, you end up with `where phone_number = 5551234567`. It's doing exactly what you asked it. Instead of failing, Teradata is silently doing a (very expensive) implicit cast. – Andrew Nov 30 '16 at 19:45
  • 2
    @Andrew It shouldn't *have* to. Whatever the online binary format is, should be constructed according to the set parameter type. Quotes are for human readability, the database doesn't need them. – Kayaman Nov 30 '16 at 19:48
  • The db doesn't "need" the quotes, but if you don't include them, it won't treat the parameter as a char data type. Thus the cast you see in your explain. – Andrew Nov 30 '16 at 19:49
  • 4
    @Andrew It's not my explain. I don't know of any other database that would do a ridiculous cast after using `setString()`. If this is considered to be normal behaviour for Teradata, then the driver is really poorly written. – Kayaman Nov 30 '16 at 19:52
  • 3
    @Andrew Neither JDBC nor the database creates an SQL string by concatenation in this circumstance, with or without quotes. The SQL string with ? placeholders and the parameters are transmitted directly to the database, which parses the SQL and applies the parameters and executes the query. The problem here appears to be the database itself, not JDBC. – user207421 Nov 30 '16 at 21:12
  • Is the getPreparedStatement method part of some framework? Or it is custom code? Another question: is it pure Java standalone code, or it is running in some application server? – Insac Dec 01 '16 at 20:51
  • @Insac - Yes, `getPreparedStatement` is a wrapper I created for `java.sql.Connection prepareStatement(String sql)` in my custom database class. The code is running on a JBoss web server. – ktmq Dec 02 '16 at 00:21
  • Are you able to replicate the same behavior on a standalone Java application? Just to understand if it is the driver or some wrapper from the Application server? – Insac Dec 02 '16 at 06:39

1 Answers1

0

Have you tried String myPhoneNumber = "'5551234567'";

Note -- The inclusion of the single quotes to wrap the value.

If you look at the example in the Teradata manuals here, you will see that a Query Band being set the same way as the OP's first example arrives as expected without single quotes wrapping it. It would seem to me this behavior in the first example of the OP is expected.

EDIT The sample code provided by Teradata for their JDBC driver is using java.sql.PreparedStatment. With this their example program uses setString without any tricks to provide a string value for an INSERT statement. Sample Code If you are not able to replicate that behavior, I would open an incident with the Teradata GSC.

Rob Paller
  • 7,736
  • 29
  • 26