Uploaded image for project: 'Railo'
  1. Railo
  2. RAILO-1957

Oracle thin driver (ojdbc6.jar) throwing errors when using cfquery result attribute and insert into... select

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 3.3.1.000
    • Fix Version/s: None
    • Labels:
    • Environment:

      Linux (CentOS), Java 1.6.0_29 (Sun Microsystems Inc.) 64bit (running on Tomcat), Oracle 11g XE R2 database server (running on Windows 2008), latest JDBC thin driver (ojdbc6.jar) from Oracle

      Description

      Query performed within above-described environment
      cfquery tag has the "result" attribute defined
      This specific type of SQL is used:
      INSERT INTO myTable (id, name) SELECT 1 as id, 'Foobar' as name FROM
      dual

      When all of these things are true, I get back this error:
      ORA-00933: SQL command not properly ended

      Testing has revealed that the same query will execute just fine if I
      omit the "result" attribute from cfquery.

      Also, this query works (and I can leave the "result" attribute on
      cfquery for this version):
      INSERT INTO myTable (id, name) VALUES (1, 'Foobar')

      As luck would have it, I have the perfect means of demonstrating this
      bug. The site that I'm using Railo to run is used for the sole
      purpose of testing queries against various database backends and then
      sharing the results with others. So, I can easily demonstrate this
      specific bug within that context:

      http://sqlfiddle.com/#!4/ee7da/23 – this version shows the error
      with the INSERT INTO ... SELECT
      http://sqlfiddle.com/#!4/ee7da/1 – this version works fine with
      INSERT INTO VALUES

      Also, my CF code for the site is available on github here, if that's
      helpful:
      https://github.com/jakefeasel/sqlfiddle

      Googling around about this problem has revealed that Adobe has
      apparently had a very similar issue with Oracle thin drivers, and they
      addressed it in a hotfix for version 8: http://kb2.adobe.com/cps/403/kb403070.html
      (see ID Number 70819). This blogger discusses his initial trouble
      with Adobe CF 8 and Oracle thin+INSERT INTO SELECT:
      http://zacster.blogspot.com/2007/11/did-adobe-cripple-oracle-thin-driver-in.html
      . His follow up is also useful: http://zacster.blogspot.com/2008/01/my-cf-oracle-problem-ora-00933-was.html

      So it appears that the Railo folks may need to do something similar to
      whatever Adobe did for that hotfix.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                micstriit Michael Offner
                Reporter:
                jfeasel Jake Feasel
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated: