Obtaining Affected rows from SQL Query
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:
<cfscript>
//connection url
connURL = "jdbc:macromedia:oracle://..........";
jclass = createobject('java','java.lang.Class');
jclass.forName('macromedia.jdbc.oracle.OracleDriver');
//connection url
connURL = "jdbc:macromedia:oracle://..........";
jclass = createobject('java','java.lang.Class');
jclass.forName('macromedia.jdbc.oracle.OracleDriver');
For Microsoft SQL Server:
<cfscript>
//connection url
connURL = "jdbc:macromedia:sqlserver://<server name>:1433";
jclass = createobject('java','java.lang.Class');
jclass.forName('macromedia.jdbc.sqlserver.SQLServerDriver');
//connection url
connURL = "jdbc:macromedia:sqlserver://<server name>: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 password
conn = 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();
</cfscript>
<cfoutput>
Records Affected: #n#
</cfoutput>
//user name and password
conn = 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();
</cfscript>
<cfoutput>
Records Affected: #n#
</cfoutput>
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.
Jake Munson
35 Yrs old
<cfquery name="updateSomething" datasource="someMSSQLSERVERdsn">
SET NOCOUNT ON
UPDATE authors
SET au_lname = 'Jones'
WHERE au_id = '999-888-7777'
SELECT rowsEffected=@@ROWCOUNT
SET NOCOUNT OFF
</cfquery>
<cfoutput>
rows updated:=#updateSomething.rowsEffected#
</cfoutput>
there's really nothng wrong w/multiple SQL statements, you can not write useful sp w/out then. and if your code is susceptible to sql injection you're screwed no matter what.
as far as sql server dropping it, kind of doubtful.
SQL injection is the problem, and it's only a problem when you are running queries from an outside source. SPs are run locally by a developer, so Oracle and DB2 allow that. But if you are making a connection from another source (like from a web server), Oracle and DB2 silently ignore any extra queries you send them (they probably do allow you to execute an SP with multiple queries, however). We have DB2 and Oracle servers here at work, I have tried to run multiple queries from my SQL Servers (using OpenQuery), and they won't allow it.
dss = createObject("Java", "coldfusion.server.ServiceFactory").getDataSourceService()
conn = ds.getDatasource("dbname").getConnection();
ps = conn.prepareStatement("...");
and on from there.
One thing you must note - do call conn.close() when you are done with your connection.
I've noticed with some DB's and CF, they may not neccessarily release the connection, even if you aren't using it anymore, particularly for external JDBC drivers like mySQL 4.x+.
Of course.. all this stuff is non supported etc etc etc
Enjoy.
<cfscript>
dss = createObject("Java", "coldfusion.server.ServiceFactory").getDataSourceService()
conn = dss.getDatasource(arguments.datasource).getConnection();
sql = "update mytable
set mycolumn = #cfmyownvariable#
where col2 = #othervariable#;
ps = conn.prepareStatement(sql);
affectedRows = ps.executeUpdate();
</cfscript>
Very simple, very helpfull.
So, if you are not a java developer you may decide to keep away from this solution or not, but I would say that if you have some java knowlege I'm pretty sure you can just jump ahead and you will have a way to fix it if Adobe decides to make a change there.
There is always a little risk when mixing languages, but all depends on the pros and cons to see if the benefit worth it and is always up to each developer.
And always remember that there is a big comunity willing to answer and helping each other.
;)
Nestor
http://www.java2s.com/Code/Oracle/PL-SQL/Implicitc...
Jeff