i've got some code that is triggering a syntax error because of some misplaced semicolons. if this was running on the command line, i'd solve this with a delimiter. unfortunately, the jdbc4 driver doesn't seem to recognize delimiters. anyway to get this to run?
delimiter |
CREATE TRIGGER obs_update BEFORE UPDATE ON obs
FOR EACH ROW
BEGIN
IF OLD.voided = 0 AND NEW.voided = 1 THEN
DELETE FROM clinic_obs WHERE id = OLD.obs_id;
ELSE
UPDATE clinic_obs SET clinic_obs.revision_token = NOW()
WHERE NEW.obs_id = clinic_obs.id;
END IF;
END;
|
delimiter ;
解决方案
Delimiter is a command for SQL client. There is no need to use delimiter in JDBC.
Example below shows it:
import java.sql.*;
public class TriggerExample {
public static void main(String args[]) {
String connectionURL = "jdbc:mysql://localhost:3306/test";
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(connectionURL, "login",
"password");
Statement stmt = con.createStatement();
stmt.execute("CREATE TRIGGER obs_update BEFORE UPDATE ON obs "
+ "FOR EACH ROW "
+ "BEGIN "
+ "IF OLD.voided = 0 AND NEW.voided = 1 THEN "
+ " DELETE FROM clinic_obs WHERE id = OLD.obs_id; "
+ "ELSE "
+ " UPDATE clinic_obs SET clinic_obs.revision_token = NOW() "
+ " WHERE NEW.id = clinic_obs.id; "
+ "END IF; "
+ "END;");
con.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
934
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
