#355 Error parsing CREATE FUNCTION statement

[John Didion]

Executing this statement:

create function dbo.dsc_CalculateMatchWeight (
@weight int,
@exact bit,
@scalefactor float
)
RETURNS int AS
BEGIN
RETURN cast(cast(@weight as float) * (1 +(cast(@exact
as float) * .25)) * @scalefactor as int)
END

With this code:

PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(_sql);
stmt.executeUpdate();
} finally {
if (stmt != null) try { stmt.close(); } catch
(Exception ex) {}
}

Produces this exception:

java.sql.SQLException: Incorrect syntax near the
keyword 'function'.
at
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364)
at
net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2708)
at
net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2150)
at
net.sourceforge.jtds.jdbc.TdsCore.clearResponseQueue(TdsCore.java:687)
at
net.sourceforge.jtds.jdbc.TdsCore.submitSQL(TdsCore.java:849)
at
net.sourceforge.jtds.jdbc.TdsCore.microsoftPrepare(TdsCore.java:1062)
at
net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareSQL(ConnectionJDBC2.java:520)
at
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:396)
at
com.loudeye.tools.ant.tasks.sql.SQLStatement.exec(SQLStatement.java:137)
at
com.loudeye.tools.ant.tasks.UpdateSchemaTask.execute(UpdateSchemaTask.java:116)
at
org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
at org.apache.tools.ant.Task.perform(Task.java:364)
at
org.apache.tools.ant.Target.execute(Target.java:341)
at
org.apache.tools.ant.Target.performTasks(Target.java:369)
at
org.apache.tools.ant.Project.executeTarget(Project.java:1214)
at
org.apache.tools.ant.Project.executeTargets(Project.java:1062)
at
org.apache.tools.ant.Main.runBuild(Main.java:673)
at
org.apache.tools.ant.Main.startAnt(Main.java:188)
at
org.apache.tools.ant.launch.Launcher.run(Launcher.java:196)
at
org.apache.tools.ant.launch.Launcher.main(Launcher.java:55)

[Alin Sinpalean]

Logged In: YES
user_id=564978

John,

The error is caused by jTDS trying to create a temporary
stored procedure that creates a function (temporary stored
procedures are jTDS' default way of preparing a statement).
SQL Server/Transact SQL does not seem to support CREATE
FUNCTION/PROCEDURE statements inside other functions or
procedures.

At first I thought this may just work if executed with
sp_execute ("prepareSql=1" in the connection properties) but
after some testing it turns out that doesn't work either
(probably because internally it's very similar to creating a
temporary stored procedure). The only working configuration
is "prepareSql=0", in which jTDS doesn't do any preparation
at all, just inserts the values (if any) into the query and
executes the query as a plain statement. This would mean
reduced performance with all your prepared statements.

Which brings me to this: why would you want to prepare a
CREATE FUNCTION? How many times are you going to reuse that
statement? Does it have parameters? If not, why aren't you
using a plain statement?

Alin.

[John Didion]

Logged In: YES
user_id=295135

Fantastic...thanks for the quick response, Alin.

I guess I was just using a prepared statement out of
laziness (it's executing within a loop that executes many
types of statements, so I used a prepared statement to avoid
having to decern between those statements that would benefit
from preparation and those that wouldn't). I switched to
using a plain Statement and it works fine.

This kind of support is why I'm pushing my team to switch to
jTDS (we're using JSQLConnect right now). I was able to
debug my problem using the source and get an answer within
20 minutes. If it was an actual bug, I'm guessing the fix
would've been in the next release, whereas there are known
bugs in JSQLConnect that have been there for months or
years. Great work guys.

[Alin Sinpalean]

Logged In: YES
user_id=564978

Actually during the last 3 months the average turnaround
time for bugs has been of around 2 or 3 days. Of course, if
you were willing to go the whole way and build jTDS
yourself. :o)

(There's a reporting page to prove this, but it's only
available to project members.)