Get the metadata for prepared statement in java with MySql DB

Question

I want to fetch parameter name and parameter type of given prepared statement. I am using MySQL Database. But when I run my program it is throwing an error:

Exception in thread "main" java.sql.SQLException: Parameter metadata not available for the   given statement

at this line

String paramTypeName = paramMetaData.getParameterTypeName(param);

I don't know why this is happening. Please anybody help me if possible.

Here's my code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.Statement;

public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    Statement st = conn.createStatement();

    String query = "select * from survey where id > ? and name = ?";
    PreparedStatement pstmt = conn.prepareStatement(query);
    ParameterMetaData paramMetaData = pstmt.getParameterMetaData();
    if (paramMetaData == null) {
      System.out.println("db vendor does NOT support ParameterMetaData");
    } else {
      System.out.println("db vendor supports ParameterMetaData");
      // find out the number of dynamic parameters
      int paramCount = paramMetaData.getParameterCount();
      System.out.println("paramCount=" + paramCount);
      System.out.println("-------------------");
      for (int param = 1; param <= paramCount; param++) {
        System.out.println("param number=" + param);
        String paramTypeName = paramMetaData.getParameterTypeName(param);
        System.out.println("param SQL type name=" + paramTypeName);
      }
    }
    pstmt.close();
    conn.close();
  }

public static Connection getMySqlConnection() throws Exception {
    String driver = "com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://localhost:3306/mydb";
    String username = "root";
    String password = "";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }

}

Answer 1

According to this

Should the driver generate simplified parameter metadata for PreparedStatements when no metadata is available either because the server couldn't support preparing the statement, or server-side prepared statements are disabled?

You have to set generateSimpleParameterMetadata to true

use a connection string similar to this

jdbc:mysql://localhost:3306/mydb?generateSimpleParameterMetadata=true

Answer 2

MySQL JDBC driver currently does not support it. I am solving the similar issue and came up with the following workaround:

1. include H2 database in your project (it can also run in embedded mode or in-memory)
2. translate your MySQL create database script to H2 syntax (or write it in ANSI so it is compatible with both)
3. compile prepared statements on H2 database first and get metadata from them - H2 database supports this function and SQL query syntax is similar in most cases - then save the obtained meta information for later use
4. there might be differences in data types, etc, but in general this should give you about 80% match with MySQL without too much hassle

I know it has much caveats, but it might work in some use cases.

Also consider upgrading MySQL database to 5.7, there are some enhancements related to prepared statements which may help, but I am not very deeply knowledgable about those:

• http://dev.mysql.com/doc/refman/5.7/en/prepared-statements-instances-table.html
• http://dev.mysql.com/doc/refman/5.7/en/prepare.html

Answer 3

You have not set the parameter to the prepared statements, without which you cannot get parameter metadata. so first set the parameter

pstmt.setInt(val)
pstmt.setString(val)

After adding the parameters you can get the meta data about the parameter.

Hope this helps.