Blog

In ColdFusion, it is easy to get the row count from a SQL select statement, just use recordcount. But what about when you do an insert, update or delete? In these cases recordcount is always 0, and a cfdump or using getMetaData doesn’t help either. On CF-Talk Qasim Rasheed posted a way to get the amount of affected rows using Java and the JDBC drivers. I’m going to split this in two, because the first part is database specific. For Oracle, use this://connection urlconnURL = "jdbc:macromedia:oracle://..........";jclass = createobject('java','java.lang.Class');jclass.forName('macromedia.jdbc.oracle.OracleDriver');For Microsoft SQL Server://connection urlconnURL = "jdbc:macromedia:sqlserver://:1433";jclass = createobject('java','java.lang.Class');jclass.forName('macromedia.jdbc.sqlserver.SQLServerDriver');The last part of the code is the same for any database:driverManager = CreateObject('java', 'java.sql.DriverManager');//user name and passwordconn = driverManager.getConnection( connURL, 'USER', 'PASSWORD' );sql = "update budgets.dbo.budgetids set CC = ? where budgetid like ?";ps = conn.prepareStatement(sql);ps.setString(1,'400');ps.setString(2,'B007%');n = ps.executeUpdate();Records Affected: #n#You can see that we are using prepared statements here. Any place that you pass in a value, use a question mark. Then use the setString function to populate those values with your data.