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

[Alan Williamson]

aaah can you file that as a bug please, we can fix that fairly sharpish

On 01/06/2012 03:45, Jari Ketola wrote:

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</connectstring>
<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;characterEncoding=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

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