com.microsoft.sqlserver.jdbc.SQLServerParameterMetaData Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of mssql-jdbc Show documentation
Show all versions of mssql-jdbc Show documentation
Microsoft JDBC Driver for SQL Server.
/*
* Microsoft JDBC Driver for SQL Server Copyright(c) Microsoft Corporation All rights reserved. This program is made
* available under the terms of the MIT License. See the LICENSE file in the project root for more information.
*/
package com.microsoft.sqlserver.jdbc;
import java.sql.ParameterMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* Provides meta data for prepared statement parameters.
*
* The API javadoc for JDBC API methods that this class implements are not repeated here. Please see Sun's JDBC API
* interfaces javadoc for those details.
*
* For SQL Servers versioned below SQL Server 2012, prepared statements are executed with SET FMT ONLY to retrieve
* column meta data Callable statements : sp_sp_sproc_columns is called to retrieve names and meta data for the
* procedures params.
*
* For SQL Server 2012 and above, sp_describe_undeclared_parameter is used to retrieve parameter metadata.
*/
public final class SQLServerParameterMetaData implements ParameterMetaData {
private final static int SQL_SERVER_2012_VERSION = 11;
private static final String DATA_TYPE = "DATA_TYPE";
private static final String COLUMN_TYPE = "COLUMN_TYPE";
private static final String TYPE_NAME = "TYPE_NAME";
private static final String PRECISION = "PRECISION";
private static final String SCALE = "SCALE";
private static final String NULLABLE = "NULLABLE";
private static final String SS_TYPE_SCHEMA_NAME = "SS_TYPE_SCHEMA_NAME";
private final SQLServerPreparedStatement stmtParent;
private SQLServerConnection con;
private List> procMetadata;
boolean procedureIsFound = false;
static final private java.util.logging.Logger logger = java.util.logging.Logger
.getLogger("com.microsoft.sqlserver.jdbc.internals.SQLServerParameterMetaData");
static private final AtomicInteger baseID = new AtomicInteger(0); // Unique id generator for each instance (used for
// logging).
final private String traceID = " SQLServerParameterMetaData:" + nextInstanceID();
boolean isTVP = false;
// Returns unique id for each instance.
private static int nextInstanceID() {
return baseID.incrementAndGet();
}
/**
* Provides a helper function to provide an ID string suitable for tracing.
*
* @return traceID string
*/
@Override
final public String toString() {
return traceID;
}
/* Used for prepared statement meta data */
class QueryMeta {
String parameterClassName = null;
int parameterType = 0;
String parameterTypeName = null;
int precision = 0;
int scale = 0;
int isNullable = ParameterMetaData.parameterNullableUnknown;
boolean isSigned = false;
}
Map queryMetaMap = null;
/*
* Parses query metadata.
*/
private void parseQueryMeta(ResultSet rsQueryMeta) throws SQLServerException {
Pattern datatypePattern = Pattern.compile("(.*)\\((.*)(\\)|,(.*)\\))");
try {
if (null != rsQueryMeta) {
while (rsQueryMeta.next()) {
QueryMeta qm = new QueryMeta();
SSType ssType = null;
int paramOrdinal = rsQueryMeta.getInt("parameter_ordinal");
String typename = rsQueryMeta.getString("suggested_system_type_name");
if (null == typename) {
typename = rsQueryMeta.getString("suggested_user_type_name");
try (SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) con.prepareStatement(
"select max_length, precision, scale, is_nullable from sys.assembly_types where name = ?")) {
pstmt.setNString(1, typename);
try (ResultSet assemblyRs = pstmt.executeQuery()) {
if (assemblyRs.next()) {
qm.parameterTypeName = typename;
qm.precision = assemblyRs.getInt("max_length");
qm.scale = assemblyRs.getInt("scale");
ssType = SSType.UDT;
}
}
}
} else {
qm.precision = rsQueryMeta.getInt("suggested_precision");
qm.scale = rsQueryMeta.getInt("suggested_scale");
Matcher matcher = datatypePattern.matcher(typename);
if (matcher.matches()) {
// the datatype has some precision/scale defined explicitly.
ssType = SSType.of(matcher.group(1));
if ("varchar(max)".equalsIgnoreCase(typename)
|| "varbinary(max)".equalsIgnoreCase(typename)) {
qm.precision = SQLServerDatabaseMetaData.MAXLOBSIZE;
} else if ("nvarchar(max)".equalsIgnoreCase(typename)) {
qm.precision = SQLServerDatabaseMetaData.MAXLOBSIZE / 2;
} else if (SSType.Category.CHARACTER == ssType.category
|| SSType.Category.BINARY == ssType.category
|| SSType.Category.NCHARACTER == ssType.category) {
try {
// For character/binary data types "suggested_precision" is 0. So get the precision
// from
// the type itself.
qm.precision = Integer.parseInt(matcher.group(2));
} catch (NumberFormatException e) {
MessageFormat form = new MessageFormat(
SQLServerException.getErrString("R_metaDataErrorForParameter"));
Object[] msgArgs = {paramOrdinal};
SQLServerException.makeFromDriverError(con, stmtParent,
form.format(msgArgs) + " " + e.getMessage(), null, false);
}
}
} else
ssType = SSType.of(typename);
// For float and real types suggested_precision returns the number of bits, not digits.
if (SSType.FLOAT == ssType) {
// https://msdn.microsoft.com/en-CA/library/ms173773.aspx
// real is float(24) and is 7 digits. Float is 15 digits.
qm.precision = 15;
} else if (SSType.REAL == ssType) {
qm.precision = 7;
} else if (SSType.TEXT == ssType) {
qm.precision = SQLServerDatabaseMetaData.MAXLOBSIZE;
} else if (SSType.NTEXT == ssType) {
qm.precision = SQLServerDatabaseMetaData.MAXLOBSIZE / 2;
} else if (SSType.IMAGE == ssType) {
qm.precision = SQLServerDatabaseMetaData.MAXLOBSIZE;
} else if (SSType.GUID == ssType) {
qm.precision = SQLServerDatabaseMetaData.UNIQUEIDENTIFIER_SIZE;
} else if (SSType.TIMESTAMP == ssType) {
qm.precision = 8;
} else if (SSType.XML == ssType) {
qm.precision = SQLServerDatabaseMetaData.MAXLOBSIZE / 2;
}
qm.parameterTypeName = ssType.toString();
}
// Check if ssType is null. Was caught by static analysis.
if (null == ssType) {
throw new SQLServerException(SQLServerException.getErrString("R_metaDataErrorForParameter"),
null);
}
JDBCType jdbcType = ssType.getJDBCType();
qm.parameterClassName = jdbcType.className();
qm.parameterType = jdbcType.getIntValue();
// The parameter can be signed if it is a NUMERIC type (except bit or tinyint).
qm.isSigned = ((SSType.Category.NUMERIC == ssType.category) && (SSType.BIT != ssType)
&& (SSType.TINYINT != ssType));
queryMetaMap.put(paramOrdinal, qm);
}
}
} catch (SQLException e) {
throw new SQLServerException(SQLServerException.getErrString("R_metaDataErrorForParameter"), e);
}
}
private void parseFMTQueryMeta(ResultSetMetaData md, SQLServerFMTQuery f) throws SQLServerException {
try {
// Gets the list of parsed column names/targets
List columns = f.getColumns();
// Gets VALUES(?,?,?...) list. The internal list corresponds to the parameters in the bracket after VALUES.
List> params = f.getValuesList();
int valueListOffset = 0;
int mdIndex = 1;
int mapIndex = 1;
for (int i = 0; i < columns.size(); i++) {
/**
* For INSERT table VALUES(?,?,?...) scenarios where the column names are not specifically defined after
* the table name, the parser adds a '*' followed by '?'s equal to the number of parameters in the
* values bracket. The '*' will retrieve all values from the table and we'll use the '?'s to match their
* position here
*/
if ("*".equals(columns.get(i))) {
for (int j = 0; j < params.get(valueListOffset).size(); j++) {
if ("?".equals(params.get(valueListOffset).get(j)) && (!md.isAutoIncrement(mdIndex + j))) {
QueryMeta qm = getQueryMetaFromResultSetMetaData(md, mdIndex + j);
queryMetaMap.put(mapIndex++, qm);
i++;
}
}
mdIndex += params.get(valueListOffset).size();
valueListOffset++;
} else {
/*
* If this is not a INSERT table VALUES(...) situation, just add the entry.
*/
QueryMeta qm = getQueryMetaFromResultSetMetaData(md, mdIndex);
queryMetaMap.put(mapIndex++, qm);
mdIndex++;
}
}
} catch (SQLException e) {
throw new SQLServerException(SQLServerException.getErrString("R_metaDataErrorForParameter"), e);
}
}
private QueryMeta getQueryMetaFromResultSetMetaData(ResultSetMetaData md, int index) throws SQLException {
QueryMeta qm = new QueryMeta();
qm.parameterClassName = md.getColumnClassName(index);
qm.parameterType = md.getColumnType(index);
qm.parameterTypeName = md.getColumnTypeName(index);
qm.precision = md.getPrecision(index);
qm.scale = md.getScale(index);
qm.isNullable = md.isNullable(index);
qm.isSigned = md.isSigned(index);
return qm;
}
String parseProcIdentifier(String procIdentifier) throws SQLServerException {
ThreePartName threePartName = ThreePartName.parse(procIdentifier);
StringBuilder sb = new StringBuilder();
if (threePartName.getDatabasePart() != null) {
sb.append("@procedure_qualifier=");
sb.append(threePartName.getDatabasePart());
sb.append(", ");
}
if (threePartName.getOwnerPart() != null) {
sb.append("@procedure_owner=");
sb.append(threePartName.getOwnerPart());
sb.append(", ");
}
if (threePartName.getProcedurePart() != null) {
sb.append("@procedure_name=");
sb.append(threePartName.getProcedurePart());
} else {
SQLServerException.makeFromDriverError(con, stmtParent, SQLServerException.getErrString("R_noMetadata"),
null, false);
}
return sb.toString();
}
private void checkClosed() throws SQLServerException {
// stmtParent does not seem to be re-used, should just verify connection is not closed.
// stmtParent.checkClosed();
con.checkClosed();
}
/**
* Construct a SQLServerParameterMetaData parameter meta data.
*
* @param st
* the prepared statement
* @param sProcString
* the procedure name
* @throws SQLServerException
*/
@SuppressWarnings("serial")
SQLServerParameterMetaData(SQLServerPreparedStatement st, String sProcString) throws SQLServerException {
assert null != st;
stmtParent = st;
con = st.connection;
if (logger.isLoggable(java.util.logging.Level.FINE)) {
logger.fine(toString() + " created by (" + st.toString() + ")");
}
try {
// If the CallableStatement/PreparedStatement is a stored procedure call
// then we can extract metadata using sp_sproc_columns
if (null != st.procedureName) {
String sProc = parseProcIdentifier(st.procedureName);
try (SQLServerStatement s = (SQLServerStatement) con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
SQLServerResultSet rsProcedureMeta = s
.executeQueryInternal(con.isKatmaiOrLater()
? "exec sp_sproc_columns_100 " + sProc
+ ", @ODBCVer=3, @fUsePattern=0"
: "exec sp_sproc_columns " + sProc
+ ", @ODBCVer=3, @fUsePattern=0")) {
// if rsProcedureMeta has next row, it means the stored procedure is found
if (rsProcedureMeta.next()) {
procedureIsFound = true;
} else {
procedureIsFound = false;
}
rsProcedureMeta.beforeFirst();
// Sixth is DATA_TYPE
rsProcedureMeta.getColumn(6).setFilter(new DataTypeFilter());
if (con.isKatmaiOrLater()) {
rsProcedureMeta.getColumn(8).setFilter(new ZeroFixupFilter());
rsProcedureMeta.getColumn(9).setFilter(new ZeroFixupFilter());
rsProcedureMeta.getColumn(17).setFilter(new ZeroFixupFilter());
}
procMetadata = new ArrayList<>();
// Process ResultSet Procedure Metadata for API usage
while (rsProcedureMeta.next()) {
HashMap map = new HashMap<>();
map.put(DATA_TYPE, rsProcedureMeta.getShort(DATA_TYPE));
map.put(COLUMN_TYPE, rsProcedureMeta.getInt(COLUMN_TYPE));
map.put(TYPE_NAME, rsProcedureMeta.getString(TYPE_NAME));
map.put(PRECISION, rsProcedureMeta.getInt(PRECISION));
map.put(SCALE, rsProcedureMeta.getInt(SCALE));
map.put(NULLABLE, rsProcedureMeta.getInt(NULLABLE));
map.put(SS_TYPE_SCHEMA_NAME, rsProcedureMeta.getString(SS_TYPE_SCHEMA_NAME));
procMetadata.add(map);
}
}
}
// Otherwise we just have a parameterized statement.
// if SQL server version is 2012 and above use stored
// procedure "sp_describe_undeclared_parameters" to retrieve parameter meta data
// if SQL server version is 2008, then use FMTONLY
else {
queryMetaMap = new HashMap<>();
if (con.getServerMajorVersion() >= SQL_SERVER_2012_VERSION && !st.getUseFmtOnly()) {
String preparedSQL = con.replaceParameterMarkers(stmtParent.userSQL,
stmtParent.userSQLParamPositions, stmtParent.inOutParam, stmtParent.bReturnValueSyntax);
try (SQLServerCallableStatement cstmt = (SQLServerCallableStatement) con
.prepareCall("exec sp_describe_undeclared_parameters ?")) {
cstmt.setNString(1, preparedSQL);
parseQueryMeta(cstmt.executeQueryInternal());
}
} else {
SQLServerFMTQuery f = new SQLServerFMTQuery(sProcString);
try (SQLServerStatement stmt = (SQLServerStatement) con.createStatement();
ResultSet rs = stmt.executeQuery(f.getFMTQuery())) {
parseFMTQueryMeta(rs.getMetaData(), f);
}
}
}
}
// Do not need to wrapper SQLServerException again
catch (SQLServerException e) {
throw e;
} catch (SQLException | StringIndexOutOfBoundsException e) {
SQLServerException.makeFromDriverError(con, stmtParent, e.getMessage(), null, false);
}
}
@Override
public boolean isWrapperFor(Class iface) throws SQLException {
return iface.isInstance(this);
}
@Override
public T unwrap(Class iface) throws SQLException {
T t;
try {
t = iface.cast(this);
} catch (ClassCastException e) {
throw new SQLServerException(e.getMessage(), e);
}
return t;
}
private Map getParameterInfo(int param) {
if (stmtParent.bReturnValueSyntax && isTVP) {
return procMetadata.get(param - 1);
} else {
// Note row 1 is the 'return value' meta data
return procMetadata.get(param);
}
}
private boolean isValidParamProc(int n) {
// Note row 1 is the 'return value' meta data
return (n >= 1 // JDBC index is 1 based
&& ((stmtParent.bReturnValueSyntax && isTVP && procMetadata.size() >= n) || procMetadata.size() > n));
}
private boolean isValidParamQuery(int n) {
return (null != queryMetaMap && queryMetaMap.containsKey(n));
}
/**
* Checks if the @param passed is valid for either procedure metadata or query metadata.
*
* @param param
* @throws SQLServerException
*/
private void checkParam(int param) throws SQLServerException {
// Check if Procedure Metadata is not available
if (null == procMetadata) {
// Check if Query Metadata is also not available
if (!isValidParamQuery(param)) {
SQLServerException.makeFromDriverError(con, stmtParent, SQLServerException.getErrString("R_noMetadata"),
null, false);
}
} else if (!isValidParamProc(param)) {
// Throw exception if @param index not found
MessageFormat form = new MessageFormat(SQLServerException.getErrString("R_invalidParameterNumber"));
Object[] msgArgs = {param};
SQLServerException.makeFromDriverError(con, stmtParent, form.format(msgArgs), null, false);
}
}
@Override
public String getParameterClassName(int param) throws SQLServerException {
checkClosed();
checkParam(param);
try {
if (null == procMetadata) {
return queryMetaMap.get(param).parameterClassName;
} else {
return JDBCType.of((short) getParameterInfo(param).get(DATA_TYPE)).className();
}
} catch (SQLServerException e) {
SQLServerException.makeFromDriverError(con, stmtParent, e.getMessage(), null, false);
return null;
}
}
@Override
public int getParameterCount() throws SQLServerException {
checkClosed();
if (null == procMetadata) {
return queryMetaMap.size();
} else {
// Row 1 is Return Type metadata
return (procMetadata.isEmpty() ? 0 : procMetadata.size() - 1);
}
}
@Override
public int getParameterMode(int param) throws SQLServerException {
checkClosed();
checkParam(param);
if (null == procMetadata) {
// if it is not a stored procedure, the @param can only be input.
return parameterModeIn;
} else {
int n = (int) getParameterInfo(param).get(COLUMN_TYPE);
if (n == 1)
return parameterModeIn;
else if (n == 2)
return parameterModeOut;
else
return parameterModeUnknown;
}
}
@Override
public int getParameterType(int param) throws SQLServerException {
checkClosed();
checkParam(param);
int parameterType = 0;
if (null == procMetadata) {
parameterType = queryMetaMap.get(param).parameterType;
} else {
parameterType = (short) getParameterInfo(param).get(DATA_TYPE);
}
if (0 != parameterType) {
switch (parameterType) {
case microsoft.sql.Types.DATETIME:
case microsoft.sql.Types.SMALLDATETIME:
parameterType = SSType.DATETIME2.getJDBCType().asJavaSqlType();
break;
case microsoft.sql.Types.MONEY:
case microsoft.sql.Types.SMALLMONEY:
parameterType = SSType.DECIMAL.getJDBCType().asJavaSqlType();
break;
case microsoft.sql.Types.GUID:
parameterType = SSType.CHAR.getJDBCType().asJavaSqlType();
break;
default:
break;
}
}
return parameterType;
}
@Override
public String getParameterTypeName(int param) throws SQLServerException {
checkClosed();
checkParam(param);
if (null == procMetadata) {
return queryMetaMap.get(param).parameterTypeName;
} else {
return getParameterInfo(param).get(TYPE_NAME).toString();
}
}
@Override
public int getPrecision(int param) throws SQLServerException {
checkClosed();
checkParam(param);
if (null == procMetadata) {
return queryMetaMap.get(param).precision;
} else {
return (int) getParameterInfo(param).get(PRECISION);
}
}
@Override
public int getScale(int param) throws SQLServerException {
checkClosed();
checkParam(param);
if (null == procMetadata) {
return queryMetaMap.get(param).scale;
} else {
return (int) getParameterInfo(param).get(SCALE);
}
}
@Override
public int isNullable(int param) throws SQLServerException {
checkClosed();
checkParam(param);
if (procMetadata == null) {
return queryMetaMap.get(param).isNullable;
} else {
return (int) getParameterInfo(param).get(NULLABLE);
}
}
/**
* Returns if a supplied parameter index is valid.
*
* @param param
* the @param index
* @throws SQLServerException
* when an error occurs
* @return boolean
*/
@Override
public boolean isSigned(int param) throws SQLServerException {
checkClosed();
checkParam(param);
try {
if (null == procMetadata) {
return queryMetaMap.get(param).isSigned;
} else {
return JDBCType.of((short) getParameterInfo(param).get(DATA_TYPE)).isSigned();
}
} catch (SQLException e) {
SQLServerException.makeFromDriverError(con, stmtParent, e.getMessage(), null, false);
return false;
}
}
String getTVPSchemaFromStoredProcedure(int param) throws SQLServerException {
checkClosed();
checkParam(param);
return (String) getParameterInfo(param).get(SS_TYPE_SCHEMA_NAME);
}
}