Skip to main content
M
mega_L
Inspiring
October 16, 2014
Answered

Error Executing Database Query

  • October 16, 2014
  • 2 replies
  • 7222 views

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. 

This topic has been closed for replies.
Correct answer 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.

2 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
October 17, 2014

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.

M
mega_LAuthor
Inspiring
October 20, 2014

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

M
mega_LAuthorCorrect answer
Inspiring
October 20, 2014

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.

    WolfShade
    WolfShade
    Legend
    October 17, 2014

    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,

    ^_^

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices