[OpenBD] Problem with specific types of SQL UNIONs and read-only datasources

[Jari Ketola]

Hello,

We have both read-write and read-only datasources configured for OpenBD. I 
just ran into problems running specific types of UNION queries against the 
read-only DS. The read-only datasource has the following configuration:

<datasource name="read_only">
  <displayname>read_only</displayname>
  <password></password>
  <connectionretries>0</connectionretries>
  <sqldelete>false</sqldelete>
  <sqlupdate>false</sqlupdate>
  <username>user</username>
  <drivername>com.mysql.jdbc.Driver</drivername>
  <sqlstoredprocedures>false</sqlstoredprocedures>
  <perrequestconnections>false</perrequestconnections>
  <sqlinsert>false</sqlinsert>
  <sqlselect>true</sqlselect>
  <connectiontimeout>120</connectiontimeout>
  <port>3306</port>
  <server>dbserver</server>
  <databasename>databasename</databasename>
  <description></description>
  
<connectstring>cacheServerConfiguration=true&amp;useDynamicCharsetInfo=false&amp;useJvmCharsetConverters=false&amp;useUnicode=true&amp;characterEncoding=Cp1252&amp;useOldUTF8Behavior=true&amp;allowMultiQueries=true</connectstrin
g>
  <initstring></initstring>
  <logintimeout>120</logintimeout>
  
<hoststring>jdbc:mysql://dbserver:3306/databasename?cacheResultSetMetadata=false&amp;autoReconnect=true&amp;cacheServerConfiguration=true&amp;useDynamicCharsetInfo=false&amp;useJvmCharsetConverters=false&amp;useUnicode=true&amp;cha
racterEncoding=Cp1252&amp;useOldUTF8Behavior=true&amp;allowMultiQueries=true</hoststring>
  <maxconnections>24</maxconnections>
  <name>read_only</name>
</datasource>

So only SELECTs are allowed - INSERT, UPDATE, DELETE, and Stored Procedures 
are not.

Now running the following query 

<cfquery datasource="read_only" name="test_query">
(select 1 as test)
union
(select 2 as test)
</cfquery>

throws *General SQL Error; SQL operation disabled via admin console *database 
exception from OpenBD.

I did some testing and found out that enabling Stored Procedures for the 
datasource fixes the problem.

It seems OpenBD interprets this type of notation as a stored procedure, 
even though it's a valid UNION (see 
http://dev.mysql.com/doc/refman/5.5/en/union.html ).

Am I missing something or should I just enable stored procedures for 
read-only datasources? And maybe file a bug report on this?

 Jari

-- 
online documentation: http://openbd.org/manual/
 http://groups.google.com/group/openbd?hl=en