Last week a reader sent me an interesting problem. When deleting a record from a database using a standard <cfquery> tag you don’t get any indication back that let’s you know whether or not the row or rows in question actually existed, nor do you get back any indication as to how many rows might have been affected. He wanted to be able run a single query that deleted a record AND let him know whether or not the record existed. He thought (correctly) that there was a way to do this in Java and wondered if I could write a CFX tag to do it for him. I could, but it would be much easier to do it in <cfscript>. In Java, you would write this:
// replace the package/class name of your db driver
Class.forName("org.gjt.mm.mysql.Driver");
// replace w/ your server, database name, username & password
String conurl = "jdbc:mysql://server/db?user=u&password=p";
Connection con = DriverManager.getConnection(conurl);
PreparedStatement ps;
ps = con.prepareStatement("DELETE from table_name where id = 3045");
int result = ps.executeUpdate();
con.close();
In short, you load the appropriate database driver (I’m using MySQL in the above example), obtain a connection to the database using the DriverManager class, create a PreparedStatement, and then call the executeUpdate() method. This method returns an int: “… either (1) the row count for INSERT, UPDATE, or DELETE statements or (2) 0 for SQL statements that return nothing.”
Using the above sample it’s pretty easy to do the same thing in ColdFusion using <cfscript>:
<cfscript>
clazz = CreateObject("java", "java.lang.Class");
// replace the package/class name of your db driver
clazz.forName("org.gjt.mm.mysql.Driver");
driverManager = CreateObject("java", "java.sql.DriverManager");
// replace w/ your server, database name, username & password
conurl = "jdbc:mysql://server/db?user=u&password=p";
connection = driverManager.getConnection(conurl);
query = "DELETE from category where id = 3045";
preparedStatement = connection.prepareStatement(query);
result = preparedStatement.executeUpdate();
WriteOutput("result = " & result);
connection.close();
</cfscript>
Further, you can test the value of the result variable so that you can show a custom message to a end user or perform some other type of business logic:
if (result == "0") {
WriteOutput("No records were deleted.");
} else if (result == "1") {
WriteOutput("One record was deleted.");
} else {
WriteOutput(result & " records were deleted.");
}
Aaron, interesting info. Unfortunately, when I try it in CFMX, I get “The selected method getConnection was not found” on the “connection = driverManager.getConnection(url);”. When I dump the the driverManager variable (as in your code) after the CFOBJECT, it doesn’t show having a getConnection method. Any thoughts? I feel like I mus thave something wrong on my end, but I do have a MySQL database configured in CFMX, so that part would seem to rule out obvious issues.
hey Charlie,
bug in my code… I typically write the code, test it and then make it pretty for the blog. In this case, the error is here:
url = “jdbc:mysql://server/db?user=u&password=p”;
connection = driverManager.getConnection(url);
Can you spot it? 🙂 ‘url’ is a reserved word in ColdFusion, the error message that getConnection(url) doesn’t exist is because getConnection(coldfusion.filter.UrlScope) doesn’t exist, getConnection(java.lang.String) does exist. All fixed now. Sorry! 🙂
AJ
Doh! Good catch. 🙂
Aaron,
Thanks for this very useful information. I actually only need to be able to create the connection object, and this worked great once I figured out the driver class name for the macromedia built in drivers and the connection string to use (thanks to the datadirect documentation).
This puts me one step closer to implementing JasperReports within CFMX =)
– Rick
What is the driver for Native Oracle?
Pat
hi Patrick,
I don’t have Oracle, but *I think* that you’d use:
clazz.forName(“macromedia.jdbc.oracle.OracleDriver”);
Let me know if that works for you.
AJ
Hi
In your example, what does the line:
clazz.forName(“org.gjt.mm.mysql.Driver”)
actually do?
I don’t see you referencing clazz later in your example or using any result from that statement.
Another question:
Is it possible to use CFs connection pooling when using JDBC objects directly like this somehow?
Thanks in advance
– Per
this can be made without the execution of the CreateObject function or Tag CFOBJECT
DriverManager = “”;
DriverManager = DriverManager.getClass();
DriverManager = DriverManager.forName(“coldfusion.sql.JdbcImpl”);
DriverManager = DriverManager.newInstance();
DriverManager.setDriver(“com.mysql.jdbc.Driver”);
DriverManager.setUrl(“jdbc:mysql://host:port/database”);
DriverManager.setPassword(“root”);
DriverManager.setUsername(“root”);
Connection = DriverManager.getConnection();
Connection.setUseUnicode(true);
Connection.setCharacterEncoding(“utf8”);
sql = “delete from tabela where campo = 1;”;
Statement = Connection.prepareStatement(sql);
result = Statement.executeUpdate();
if (result == “0”) {
WriteOutput(“No records were deleted.”);
} else if (result == “1”) {
WriteOutput(“One record was deleted.”);
} else {
WriteOutput(result & ” records were deleted.”);
}
Connection.close();
A classloaded JDBC driver would look like this
You need to register the Driver object
//A look see thought the drivers
hai,
i have a doubt about the driver connection.
query:
“how to connect the JdbcOdbc driver in the applet window?”
If you use the approach above you will create a new connection outside of the CF connection pool. You may desire to use a connection from CF instead which will be managed by the pool.
In this situation, instead of creating a new connection from URL you would retrieve the existing connection from CF:
connection = ServiceFactory.getDataSourceService().getDatasource(“GoDirect”).getConnection();
Thanks Aaron – your connection string is the only one that worked for me out of dozens. You saved me much time today 🙂 Thanks,