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