4 Replies Latest reply on Sep 10, 2014 10:55 AM by jkcrosby

    How do I add/update/delete data using ColdFusion Builder 3?

    jkcrosby Level 1

      I feel like this is a really simple and stupid question and should be easy to figure out, but I can't.

       

      I am relearning ColdFusion after 12 years out of the IT field.  I am using the ColdFusion 9 Getting Started:Volume 1 book.  In Chapter 7 it states that I need to edit the Application.cfc file so that I can edit the database rather than just selecting from the database.  I can't seem to find anything related to that file in any search.  In my ColdFusion 11 Administrator I have set this mySQL database to be able to be edited, but ColdFusion Builder 3 doesn't seem to allow any editing of the tables.  I have not had much luck searching for help regarding this on the internet either.  My biggest problem so far is getting ColdFusion Builder to talk with my mySQL database properly so I can actually get on with relearning ColdFusion and writing code.  Frustrating!!

        • 1. Re: How do I add/update/delete data using ColdFusion Builder 3?
          Carl Von Stetten Adobe Community Professional & MVP

          When you say that you have "set this mySQL database to be able to be edited", do you mean you enabled the INSERT, UPDATE, DELETE checkboxes for the datasource?

           

          Also, ColdFusion Builder is only an IDE for writing ColdFusion code - it doesn't actually run the code (you need a ColdFusion Server set up somewhere to run the code, although CF Builder does come with an optional built-in copy of ColdFusion 11).  So what do you mean that "ColdFusion Builder 3 doesn't seem to allow any editing of the tables"?

           

          Can you provide some code samples to show exactly what you are trying to do and what the actual result (or error) is?

           

          -Carl V.

          • 2. Re: How do I add/update/delete data using ColdFusion Builder 3?
            jkcrosby Level 1

            I do have ColdFusion 11 Administrator installed.  When I go to Data and Services there and open the datasource (OWS) that I want to connect to and query off of, and go to Advanced Settings, I show that the following are selected:

            SELECT CREATE GRANT
            INSERT DROP REVOKE
            UPDATE ALTER Stored Procedures
            DELETE

             

            If, in ColdFusion Builder 3, I open the RDS Query viewer and write the following basic update query (Server: default:local; Datasource: OWS):

            insert into directors(FirstName, LastName)

            Values ('Benjamin', 'Forta');

             

            I get the following RDS error message:

            java.sql.SQLException: Can not issue data manipulation statements with executeQuery().

             

            According to the book and some information I have seen online, the SQL Query Tool, for security's sake, by default allows execution of SELECT statements, but no other SQL statements.  According to the book, to change this behavior I need to edit the Application.cfc file in the ows/sql directory.  I have to change the THIS.select_only flag from the default of yes to no and save the file.  I cannot find this file anywhere.  I have seen an application.cfm, but I think this is different that what they want because I can't find anything in that file that says THIS.select.

            • 3. Re: How do I add/update/delete data using ColdFusion Builder 3?
              Carl Von Stetten Adobe Community Professional & MVP

              jkcrosby,

               

              OK, so you have the datasource configured properly in CF Administrator.  Did you set up the ColdFusion server instance properly inside CF Builder (or are you using the built-in "defaultLocal" instance)?  And did you enable RDS during the ColdFusion server installation?

               

              FWIW, I just tried inserting a new record into a scratch database from RDS.  I get a different error:

              java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]No ResultSet set was produced.

              but the record is created if I refresh the table view contents.  Also, if end the insert statement with a semicolon and put a select statement after it, everything runs without error.

               

              -Carl V.

              • 4. Re: How do I add/update/delete data using ColdFusion Builder 3?
                jkcrosby Level 1

                Answering the questions backwards: Yes, RDS is enabled in CF Administrator.  Everything that I have installed is locally as a localhost, from my mySQL to my CFAdministraor.  CFAdministrator connects successfully with mySQL, and CF Builder 3 connects successfully to CF Administrator and thus the mySQL database. 

                 

                One thing I find in the book regarding Application.cfc (pg 394, ch 18 if anyone is interested) is that it must be created and it is automatically executed just before each of the pages that make up the application.  It is supposed to be in the same folder as the other templates, somewhere within the Web server's document root.  I've created the file and placed it in the same place that I save my other pages, so I think it technically should be OK.  I put the following in it:

                 

                <cfcomponent output="false">

                <cfset this.name="OrangeWhipSite">

                <cfset this.clientManagement=true>

                <cfset this.datasource = "ows">

                <cfset THIS.select = "no">

                </cfcomponent>

                I think it should be OK, but it still doesn't work.  I'm about to give up on this and if I need to test out some SQL or change something I'll just use mySQL.  I'm assuming that if I create a CF page that edits a row in the database that it will work, it is just that I can't use the SQL editor in the CF Builder 3 to change the data.  Any confirmation or thoughts?