{"id":595,"date":"2004-05-10T10:25:59","date_gmt":"2004-05-10T14:25:59","guid":{"rendered":"http:\/\/wordpress.cephas.net\/?p=595"},"modified":"2004-05-10T10:25:59","modified_gmt":"2004-05-10T14:25:59","slug":"coldfusion-mx-and-javasql","status":"publish","type":"post","link":"https:\/\/cephas.net\/blog\/2004\/05\/10\/coldfusion-mx-and-javasql\/","title":{"rendered":"ColdFusion MX and java.sql"},"content":{"rendered":"

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:
\n
\n\/\/ replace the package\/class name of your db driver
\nClass.forName(\"org.gjt.mm.mysql.Driver\");
\n\/\/ replace w\/ your server, database name, username & password
\nString conurl = \"jdbc:mysql:\/\/server\/db?user=u&password=p\";
\nConnection con = DriverManager.getConnection(conurl);
\nPreparedStatement ps;
\nps = con.prepareStatement(\"DELETE from table_name where id = 3045\");
\nint result = ps.executeUpdate();
\ncon.close();
\n<\/code>
\nIn short, you load the appropriate database driver (I’m using MySQL in the above example), obtain a connection to the database using the DriverManager<\/a> class, create a PreparedStatement<\/a>, and then call the executeUpdate()<\/a> 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.”<\/p>\n

Using the above sample it’s pretty easy to do the same thing in ColdFusion using <cfscript>:
\n
\n<cfscript>
\nclazz = CreateObject(\"java\", \"java.lang.Class\");
\n\/\/ replace the package\/class name of your db driver
\nclazz.forName(\"org.gjt.mm.mysql.Driver\");
\ndriverManager = CreateObject(\"java\", \"java.sql.DriverManager\");
\n\/\/ replace w\/ your server, database name, username & password
\nconurl = \"jdbc:mysql:\/\/server\/db?user=u&password=p\";
\nconnection = driverManager.getConnection(conurl);
\nquery = \"DELETE from category where id = 3045\";
\npreparedStatement = connection.prepareStatement(query);
\nresult = preparedStatement.executeUpdate();
\nWriteOutput(\"result = \" & result);
\nconnection.close();
\n<\/cfscript>
\n<\/code>
\nFurther, 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:
\n
\nif (result == \"0\") {
\n  WriteOutput(\"No records were deleted.\");
\n} else if (result == \"1\") {
\n  WriteOutput(\"One record was deleted.\");
\n} else {
\n  WriteOutput(result & \" records were deleted.\");
\n}
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

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 … Continue reading ColdFusion MX and java.sql<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,3],"tags":[],"_links":{"self":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/posts\/595"}],"collection":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/comments?post=595"}],"version-history":[{"count":0,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/posts\/595\/revisions"}],"wp:attachment":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/media?parent=595"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/categories?post=595"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/tags?post=595"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}