Mengambil ParameterMetaData melalui useFmtOnly
Driver Microsoft JDBC untuk SQL Server menyertakan cara alternatif untuk mengkueri Metadata Parameter dari server, gunakanFmtOnly. Fitur ini pertama kali diperkenalkan dalam driver versi 7.4, dan diperlukan sebagai solusi untuk masalah yang diketahui di sp_describe_undeclared_parameters.
Driver terutama menggunakan prosedur sp_describe_undeclared_parameters tersimpan untuk mengkueri Metadata Parameter. Prosedur ini adalah pendekatan yang direkomendasikan untuk pengambilan Metadata Parameter dalam sebagian besar keadaan. Namun, menjalankan prosedur tersimpan saat ini gagal di bawah kasus penggunaan berikut:
- Terhadap kolom Always Encrypted
- Terhadap tabel sementara dan variabel tabel
- Terhadap tampilan
Solusi yang diusulkan untuk kasus penggunaan ini adalah mengurai kueri SQL pengguna untuk parameter dan target tabel, lalu menjalankan SELECT kueri dengan FMTONLY diaktifkan. Cuplikan berikut akan membantu memvisualisasikan fitur.
--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;
Mengaktifkan/menonaktifkan fitur
Fitur useFmtOnly nonaktif secara default. Pengguna dapat mengaktifkan fitur ini melalui string koneksi dengan menentukan useFmtOnly=true. Misalnya: jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=<databaseName>;user=<user>;password=<password>;useFmtOnly=true;.
Fitur ini juga tersedia melalui 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
}
Fitur ini juga tersedia pada tingkat Pernyataan. Pengguna dapat mengaktifkan/menonaktifkan fitur melalui PreparedStatement.setUseFmtOnly(boolean).
Catatan
Driver akan memprioritaskan properti tingkat Pernyataan melalui properti Tingkat koneksi.
Menggunakan fitur
Setelah diaktifkan, driver akan mulai menggunakan fitur baru secara internal alih-alih saat mengkueri sp_describe_undeclared_parameters Metadata Parameter. Tidak ada tindakan lebih lanjut yang diperlukan dari pengguna akhir.
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'
}
}
Catatan
Fitur ini hanya mendukung SELECT/INSERT/UPDATE/DELETE kueri. Kueri harus dimulai dengan salah satu dari 4 kata kunci yang didukung atau Ekspresi Tabel Umum diikuti oleh salah satu kueri yang didukung. Parameter dalam Ekspresi Tabel Umum tidak didukung.
Masalah umum
Saat ini ada beberapa masalah dengan fitur , yang disebabkan oleh kesenjangan dalam logika penguraian SQL. Masalah ini dapat diatasi dalam pembaruan fitur di masa mendatang, dan didokumenkan di bawah ini bersama dengan saran solusi.
J. Menggunakan alias 'teruskan dideklarasikan'
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. Nama Kolom Ambigu saat tabel memiliki nama kolom bersama
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 dari subkueri dengan parameter
CREATE TABLE Foo(c1 int)
SELECT * FROM (SELECT * FROM Foo WHERE c1 = ?) WHERE c1 = ?; --Incorrect syntax near '?'
--Workaround: N/A
D. Subkueri dalam klausa 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 = ?;