Hi Lukas: Thanks. Please find the sql, sample jdbc and expected result.
Regards, Venkat On Thursday, 6 February 2014 02:47:49 UTC-5, Lukas Eder wrote: > > Hi Venkat, > > Thanks for pointing out these things. I think that the Query > implementations get these things right, but it's quite possible that there > is some issue with the Routine implementations. Just to be sure, are you > using SQL Server's own sqljdbc_4.0 JDBC driver, or the Open Source jTDS? > > Could you provide me with a minimal T-SQL example procedure to be sure > we're going to be fixing things as expected? > > In the mean time, I have created an issue for this: > https://github.com/jOOQ/jOOQ/issues/3011 > > This has a high chance of still making it into jOOQ 3.3.0 and 3.2.4 > > Thanks > Lukas > > > 2014-02-05 Venkat Sadasivam <[email protected] <javascript:>>: > >> When executing stored procedure against SQL Server 2012 we get different >> behavior between execute and executeUpdate methods. >> >> When raising error from stored procedure execute method doesn't through >> SQLException whereas executeUpdate method throws as SQLException. >> >> As per the Microsoft guidelines when using execute Method is recommended >> with getMoreResults method invocation to capture complete result. >> >> http://blogs.msdn.com/b/jdbcteam/archive/2008/08/01/use-execute-and-getmoreresults-methods-for-those-pesky-complex-sql-queries.aspx >> >> Can you provide following option with jOOQ API? >> 1) Ability to configure jOOQ to either use execute or executeUpdate for >> stored procedure execution >> 2) For all the execute method calls getMoreResults method to capture >> complete data in Java end. >> >> Thanks. >> Venkat >> >> -- >> You received this message because you are subscribed to the Google Groups >> "jOOQ User Group" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> For more options, visit https://groups.google.com/groups/opt_out. >> > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
CREATE TABLE EMPLOYEE(
EMPLOYEERSN int PRIMARY KEY,
EMPLOYEENAME varchar (100) NOT NULL
)
GO
INSERT INTO EMPLOYEE VALUES('1', 'SANTOSH')
GO
CREATE TRIGGER Employee_Upd_1 ON EMPLOYEE INSTEAD OF UPDATE
AS
BEGIN
UPDATE EMPLOYEE SET EMPLOYEENAME = 'SANTOSH KUMAR' FROM EMPLOYEE, deleted Where EMPLOYEE.EMPLOYEERSN = deleted.EMPLOYEERSN
END
GO
CREATE TRIGGER Employee_Upd_2 ON EMPLOYEE FOR UPDATE
AS
BEGIN
Raiserror('Employee_Upd_2 Trigger called...',16,-1)
Raiserror('Employee_Upd_2 Trigger called...1',16,-1)
Raiserror('Employee_Upd_2 Trigger called...2',16,-1)
Raiserror('Employee_Upd_2 Trigger called...3',16,-1)
Raiserror('Employee_Upd_2 Trigger called...4',16,-1)
Raiserror('Employee_Upd_2 Trigger called...5',16,-1)
END
GOpackage com.test.mytest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TriggerIssueTest {
public static void main(String[] args) {
Connection conn;
PreparedStatement statement = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url = "jdbc:sqlserver://;serverName=XXX.XXX.XXX.XXX\\XXXXXXXXX; databaseName=XXXXXXXXXXXXXXX";
conn = DriverManager.getConnection(url, "XXXXXX", "XXXXXXXXXXXXXXXX");
String query = "Update Employee set EmployeeName ='ABC'";
statement = conn.prepareStatement(query);
boolean rs = statement.execute();
System.out.println("Execution is " + rs);
if (!rs) {
System.out
.println("Update Count " + statement.getUpdateCount());
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
// int loopCount = 0;
//
// for (int i = 0; i < 10; i++) {
// System.out.println("I=" + i);
// try {
// while (statement.getMoreResults()
// || statement.getUpdateCount() > -1) {
// loopCount++;
// System.out.println("After Execution...." + i + ":" + loopCount);
// if (loopCount > 100) { // after 100 iteration, come out
// // of the loop
// throw new Exception(
// "WARNING: There is something wrong with the JDBC driver or the Stored Procedure");
// }
// }
//
// } catch (SQLException ex) {
// ex.printStackTrace();
// } catch (Exception ex) {
// ex.printStackTrace();
// }
// }
}
}
ExpectedResult.log
Description: Binary data
