5 Replies Latest reply: Oct 20, 2014 9:54 AM by BKBK RSS

    Error Executing Database Query

    mega_L Community Member
    mega_L Oct 16, 2014 11:29 AM

      I have a web application written in CF8 with Oracle 11g as the back end. This application has been used very often for more than 6 years. Currently I am moving to CF10 and did only a little modification to the CF codes. When I ran the new application in my test server, everything seems to be working just fine except when it calls an Oracle Package. This action produces an error:  Error Executing Database Query The strange thing is everything work before and after calling the procedure, I tested using cfabort This Oracle Package is still working in production server (CF8)  but not when it is called by CF10. My question is: Is there any changes for CF10 when calling a procedures? or is there any hotfix that I'm not aware of? The code is as follow:   SELECT case trim(to_char(SYSDATE, 'DAY')) WHEN 'MONDAY' then '1' else '2' end AS TodaysDate                 from dual     

                  SELECT Count(other_id) AS NoRecFound     FROM gl_dup_ids_ssns     WHERE Trim(create_date) = 

                  SELECT Count(other_id) AS NoRecFound     FROM gl_dup_ids_ssns     WHERE Trim(create_date) =         CF codes to Stop the process and email admin                                                             

                    Error Executing Database Query shows up when it hit to run cfstoredproc. The codes are exactly the same as in CF8, this template was not modified. Exceptions 14:03:53.053 - Database Exception - in /home/space/users/www/GL/glproc.cfm : line 93     Error Executing Database Query. 

        • 1. Re: Error Executing Database Query
          WolfShade Community Member
          WolfShade Oct 17, 2014 5:52 AM (in response to mega_L)

          I have never used CFSTOREDPROC, so can't really comment on this.  But, I'd check the Adobe CF docs on CFSTOREDPROC and check if anything has changed between CF8 and CF10.

           

          V/r,

           

          ^_^

          • 2. Re: Error Executing Database Query
            BKBK Community Member
            BKBK Oct 17, 2014 8:25 AM (in response to mega_L)

            mega_L wrote:

             

            SELECT case trim(to_char(SYSDATE, 'DAY')) WHEN 'MONDAY' then '1' else '2' end AS TodaysDate                 from dual     

                        SELECT Count(other_id) AS NoRecFound     FROM gl_dup_ids_ssns     WHERE Trim(create_date) =

                        SELECT Count(other_id) AS NoRecFound     FROM gl_dup_ids_ssns     WHERE Trim(create_date) =         CF codes to Stop the process and email admin                                                            

                        

            Surely, that cannot be the actual SQL, as it is obviously invalid.

            • 3. Re: Error Executing Database Query
              mega_L Community Member
              mega_L Oct 20, 2014 8:03 AM (in response to BKBK)

              I looked at the CF administrator application log and see a better error message: Error Executing Database Query. Executing stored procedures is not allowed Is there a setting in the administrator where I can enable and disable ColdFusion to use CFSTOREPROC? if there is, I can't find it. For BKBK:

              That query is actually working because I can see it got run in the debug screen

              • 4. Re: Error Executing Database Query
                mega_L Community Member
                mega_L Oct 20, 2014 8:26 AM (in response to mega_L)

                I found the answer! In case someone out there also facing the same issue. In the administrator, Datasource Advance setting, go down and find: Allowed SQL where there are checkboxes for Select, Update,Delete,Insert and one of them is store procedure checkbox. My checkbox was not checked that's why ColdFusion can't call a store procedure. I Checked and saved it and I'm good to go.

                • 5. Re: Error Executing Database Query
                  BKBK Community Member
                  BKBK Oct 20, 2014 9:54 AM (in response to mega_L)

                  Curious solution! Thanks, Mega_L, for sharing it with us.