PreparedStatement is executed on prepareStatement on SQL Server

Question

We have a Java code similar to the following:

PreparedStatement stmt = connection.prepareStatement("drop login tmp");
int result = stmt.executeUpdate();

When running through a debugger, it seems that our SQL statement is executed after the first line is executed, and even before the second line is executed. When the second line is executed, the drop SQL is executed again, and is causing an error, as the username tmp no longer exist.

This only happens against SQL Server, but not against Oracle and Postgres with similar drop SQL queries.

Is this a known problem? Is there a common workaround, other than moving to Statement instead of PreparedStatement?

Answer 1

I think your best bet is to run SQL Server Profiler against a test database and see what's really hitting the server when this code runs. With C# prepared statements, you see something like

declare @p1 int
set @p1=-1
exec sp_prepexec @p1 output, N'@param, varchar(100)', ...
select @p1

Java or your SQL client library might use a different technique.

Having said that, prepared statements are only designed to cache and parameterize repeated, similar statements that are parameterizable. The idea is to save recompiling the SQL statement. If you aren't issuing many repeated, similar statements then prepared statements aren't working in your favor, and caching the SQL isn't useful. Personally I can't imagine using 'drop login' so much that caching would be helpful.

On top of that, I don't think the DROP LOGIN statement can take a parameter in T-SQL.

Answer 2

When you create a PreparedStatement, the query is sent to the server to precompile it (Source).

At a guess, SQLServer sees that there are no placeholders and just executes the query instead.

Judging from the comments, you already know that the fix is to create a Statement instead.