useFmtOnly를 통해 ParameterMetaData 검색
Microsoft JDBC Driver for SQL Server에는 서버에서 매개 변수 메타데이터를 쿼리하는 다른 방법인 useFmtOnly가 포함되어 있습니다. 이 기능은 드라이버 버전 7.4에서 처음 도입되었으며 sp_describe_undeclared_parameters의 알려진 문제에 대한 해결 방법으로 필요합니다.
드라이버는 주로 sp_describe_undeclared_parameters 저장 프로시저를 사용하여 매개 변수 메타데이터를 쿼리합니다. 이 프로시저는 대부분의 경우 매개 변수 메타데이터 검색에 권장되는 방법입니다. 그러나 저장 프로시저 실행은 현재 다음과 같은 사용 사례에 따라 실패합니다.
- Always Encrypted 열에 대해
- 임시 테이블 및 테이블 변수에 대해
- 보기에 대해
이러한 사용 사례에 제안된 솔루션은 매개 변수 및 테이블 대상에 대한 사용자의 SQL 쿼리를 구문 분석한 다음 FMTONLY 사용이 설정된 SELECT 쿼리를 실행하는 것입니다. 다음 코드 조각은 기능을 시각화하는 데 도움이 됩니다.
--create a normal table 'Foo' and a temporary table 'Bar'
CREATE TABLE Foo(c1 int);
CREATE TABLE #Bar(c1 int);
EXEC sp_describe_undeclared_parameters N'SELECT * FROM Foo WHERE c1 = @p0' --works fine
EXEC sp_describe_undeclared_parameters N'SELECT * FROM #Bar WHERE c1 = @p0' --fails with "Invalid object name '#Bar'"
SET FMTONLY ON;
SELECT c1 FROM Foo; --works
SET FMTONLY OFF;
SET FMTONLY ON;
SELECT c1 FROM #Bar; --works
SET FMTONLY OFF;
기능 켜기/끄기
useFmtOnly 기능은 기본적으로 해제되어 있습니다. 사용자는 useFmtOnly=true를 지정하여 연결 문자열을 통해 이 기능을 사용하도록 설정할 수 있습니다. 예: jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=<databaseName>;user=<user>;password=<password>;useFmtOnly=true;
이 기능은 SQLServerDataSource를 통해서도 사용할 수 있습니다.
SQLServerDataSource ds = new SQLServerDataSource();
ds.setServerName(<server>);
ds.setPortNumber(<port>);
ds.setDatabaseName("<databaseName>");
ds.setUser("<user>");
ds.setPassword("<password>");
ds.setUseFmtOnly(true);
try (Connection c = ds.getConnection()) {
// do work with connection
}
이 기능은 문 수준에서도 사용할 수 있습니다. 사용자는 PreparedStatement.setUseFmtOnly(boolean)을 통해 기능을 켜거나 끌 수 있습니다.
참고 항목
드라이버는 연결 수준 속성보다 문 수준 속성의 순위를 우선적으로 지정합니다.
기능 디자이너 사용
사용하도록 설정하면 드라이버는 매개 변수 메타데이터를 쿼리하는 sp_describe_undeclared_parameters 대신 새 기능을 내부적으로 사용하기 시작합니다. 최종 사용자의 추가 작업은 필요하지 않습니다.
final String sql = "INSERT INTO #Bar VALUES (?)";
try (Connection c = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
try (Statement s = c.createStatement()) {
s.execute("CREATE TABLE #Bar(c1 int)");
}
try (PreparedStatement p1 = c.prepareStatement(sql); PreparedStatement p2 = c.prepareStatement(sql)) {
((SQLServerPreparedStatement) p1).setUseFmtOnly(true);
ParameterMetaData pmd1 = p1.getParameterMetaData();
System.out.println(pmd1.getParameterTypeName(1)); // prints int
ParameterMetaData pmd2 = p2.getParameterMetaData(); // throws exception, Invalid object name '#Bar'
}
}
참고 항목
이 기능은 SELECT/INSERT/UPDATE/DELETE 쿼리만 지원합니다. 쿼리는 지원되는 4개의 키워드 중 하나 또는 공용 테이블 식에 뒤따르는 지원 쿼리 중 하나로 시작되어야 합니다. 공용 테이블 식 내의 매개 변수는 지원되지 않습니다.
알려진 문제
현재 이 기능에는 SQL 구문 분석 논리의 공백으로 인해 발생하는 몇 가지 문제가 있습니다. 이러한 문제는 향후 기능 업데이트에서 해결될 수 있으며, 해결 방법 제안과 함께 아래에 설명되어 있습니다.
A. 'forward declared' 별칭 사용
CREATE TABLE Foo(c1 int)
DELETE fooAlias FROM Foo fooAlias WHERE c1 > ?; --Invalid object name 'fooAlias'
--Workaround #1: Specify AS keyword
DELETE fooAlias FROM Foo AS fooAlias WHERE c1 > ?;
--Workaround #2: Use the table name
DELETE Foo FROM Foo fooAlias WHERE c1 > ?;
B. 테이블에 공유 열 이름이 있는 경우 모호한 열 이름
CREATE TABLE Foo(c1 int, c2 int, c3 int)
CREATE TABLE Bar(c1 int, c2 int, c3 int)
SELECT c1,c2 FROM Foo WHERE c3 IN (SELECT c3 FROM Bar WHERE c1 > ? and c2 < ? and c3 = ?); --Ambiguous Column Name
--Workaround: Use aliases
SELECT c1,c2 FROM Foo WHERE c3 IN (SELECT c3 FROM Bar b WHERE b.c1 = ? and b.c2 = ? and b.c3 = ?);
C. 매개 변수를 사용하여 하위 쿼리에서 선택(SELECT)
CREATE TABLE Foo(c1 int)
SELECT * FROM (SELECT * FROM Foo WHERE c1 = ?) WHERE c1 = ?; --Incorrect syntax near '?'
--Workaround: N/A
D. SET 절의 하위 쿼리
CREATE TABLE Foo(c1 int)
UPDATE Foo SET c1 = (SELECT c1 FROM Foo) WHERE c1 = ?; --Incorrect syntax near ')'
--Workaround: Add a 'delimiting' condition
UPDATE Foo SET c1 = (SELECT c1 FROM Foo HAVING (HASH JOIN)) WHERE c1 = ?;