DIO oddities

[Brice Hamon <no...@gmail.com>]

Hi All,

I ran across a strange problem using the DIO interface over a Mysql
database.

When I run this sql command:

set sql "select * from records where userID=6"
set res [DIO exec $sql]

I get the expected result.

When I execute:
set sql "(select * from records where userID=6)"

I get nothing back.

Parenthesis are needed in some complex sql query I use so I need to have
them in my sql string.

I tried with: set sql "\(select * from records where userID=6\)"

with no luck.

Any ideas?

Thank you
B.

Re: DIO oddities

[Brice Hamon <no...@gmail.com>]

Hi Massimo.

Your fix is working. DIO returns the correct data now. Thank you.

So we might want to consider porting DIO to tdbc for TCL >= 8.6?

Thanks again.
B.




On Fri, Sep 13, 2013 at 12:05 PM, Massimo Manghi <mx...@apache.org>wrote:

> I have just committed a new dio_Mysql where queries are checked against
> the following regular expression to detect whether they are SELECTs or not
>
> if {[regexp -nocase {^\(*\s*select\s+} $q]} { set cmd mysqlsel }
>
> q is the trimmed query. Not tested yet. Brice, would you please check it
> out?
>
>  -- Massimo
>
>
>
>
> On 09/12/2013 07:45 PM, Damon Courtney wrote:
>
>> All that was done a LONG time ago.  The MySQLTcl needs the two
>> separate calls for select and other statements, so I went with the
>> simple case to detect which to use.  You can use some sort of pattern
>> match or regex just to get over the hump, but I think we would want
>> to rewrite DIO to use TDBC in the long run.
>>
>> Either that or just mark it as deprecated and encourage direct use of
>> TDBC.  Personally, I wrote a new library that does much of what DIO
>> did in terms of queries and argument handling, but it doesn't use
>> objects for anything.  All in all, the whole object basis of DIO is
>> mostly unnecessary and doesn't actually jive with the way some of the
>> DB libraries work.
>>
>> But, it solved a need at the time. :)
>>
>> D
>>
>>
>> On Sep 12, 2013, at 12:04 PM, Massimo Manghi
>> <ma...@unipr.it> wrote:
>>
>>  Hi Brice,
>>>
>>> I took a quick tour of the exec method in dio_Mysql.tcl (class
>>> ::DIO::Mysql)
>>>
>>> It seems to me you hit hard a problem with mysqltcl and DIO
>>>
>>> mysqltcl uses 2 calls for executing queries (actually there are 3
>>> of them, mysql::query is meant for multiple concurrent queries when
>>> one doesn't want to draw on the JOIN syntax)
>>>
>>> One call is for SELECT statements and the other for non-SELECT
>>> queries
>>>
>>> DIO discriminates between SELECT and non-SELECT statements in a
>>> rather simplicistic way: when the first 6 characters of the query
>>> match the "SELECT" string ::mysql::sel is called otherwise
>>> ::mysql::exec is executed. Your query is sent to ::mysql::exec and
>>> the call fails. I tried to send a trivial SELECT to ::mysql::exec,
>>> I got no errors but mysql from that point on returns the error
>>>
>>> "mysql::sel/db server: Commands out of sync; you can't run this
>>> command..."
>>>
>>> I must confess I can't recover from this error and I have to drop
>>> the handle and reopen a new connection.
>>>
>>> So I think we may try something smarter although computationally
>>> more expensive like using some pattern matching using a regular
>>> exp. The problem is how general a regular expression must be in
>>> order to prove to be robust enough for a general SQL statement
>>> shipping a SELECT?
>>>
>>> We can start from this simple case and see, please be patient and I
>>> will provide a simple workaround while we think of something
>>> smarter.
>>>
>>> I wonder if rewriting DIO in terms of tdbc would fix this sort of
>>> bugs. It would at least concentrate on single component and a
>>> single maintainer the cause of future problems
>>>
>>> -- Massimo
>>>
>>>
>>> On 01-09-2013 3:00, Brice Hamon wrote:
>>>
>>>> Hi Massimo,
>>>>
>>>> I did run a test directly using the mysqltcl package, and I did
>>>> get the correct data.
>>>>
>>>> So the same sql statement returns data directly, and nothing via
>>>> DIO, so I am guessing the problem lies somewhere in DIO.
>>>>
>>>> Any sql statement with parenthesis inside makes DIO not
>>>> returning data, for example (select * from .. where ..) union
>>>> (select * from .. where ....)
>>>>
>>>> That is what I used for my test.
>>>>
>>>> Please let me know if you need me to conduct more tests.
>>>>
>>>> Thank you B.
>>>>
>>>> On Sat, Aug 31, 2013 at 7:19 PM, Brice Hamon
>>>> <normandviking@gmail.com [2]> wrote:
>>>>
>>>>  OK I will do the test and let you know.
>>>>>
>>>>> Thanks, B.
>>>>>
>>>>>
>>>
>>> ------------------------------**------------------------------**
>>> ---------
>>>
>>>
>>>  To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.**apache.org<ri...@tcl.apache.org>
>
>> For additional commands, e-mail: rivet-dev-help@tcl.apache.org
>>>
>>>
>>
>> ------------------------------**------------------------------**---------
>>
>>
>>  To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.**apache.org<ri...@tcl.apache.org>
>
>> For additional commands, e-mail: rivet-dev-help@tcl.apache.org
>>
>>
>>

Re: DIO oddities

[Massimo Manghi <mx...@apache.org>]

I have just committed a new dio_Mysql where queries are checked against 
the following regular expression to detect whether they are SELECTs or not

if {[regexp -nocase {^\(*\s*select\s+} $q]} { set cmd mysqlsel }

q is the trimmed query. Not tested yet. Brice, would you please check it 
out?

  -- Massimo



On 09/12/2013 07:45 PM, Damon Courtney wrote:
> All that was done a LONG time ago.  The MySQLTcl needs the two
> separate calls for select and other statements, so I went with the
> simple case to detect which to use.  You can use some sort of pattern
> match or regex just to get over the hump, but I think we would want
> to rewrite DIO to use TDBC in the long run.
>
> Either that or just mark it as deprecated and encourage direct use of
> TDBC.  Personally, I wrote a new library that does much of what DIO
> did in terms of queries and argument handling, but it doesn't use
> objects for anything.  All in all, the whole object basis of DIO is
> mostly unnecessary and doesn't actually jive with the way some of the
> DB libraries work.
>
> But, it solved a need at the time. :)
>
> D
>
>
> On Sep 12, 2013, at 12:04 PM, Massimo Manghi
> <ma...@unipr.it> wrote:
>
>> Hi Brice,
>>
>> I took a quick tour of the exec method in dio_Mysql.tcl (class
>> ::DIO::Mysql)
>>
>> It seems to me you hit hard a problem with mysqltcl and DIO
>>
>> mysqltcl uses 2 calls for executing queries (actually there are 3
>> of them, mysql::query is meant for multiple concurrent queries when
>> one doesn't want to draw on the JOIN syntax)
>>
>> One call is for SELECT statements and the other for non-SELECT
>> queries
>>
>> DIO discriminates between SELECT and non-SELECT statements in a
>> rather simplicistic way: when the first 6 characters of the query
>> match the "SELECT" string ::mysql::sel is called otherwise
>> ::mysql::exec is executed. Your query is sent to ::mysql::exec and
>> the call fails. I tried to send a trivial SELECT to ::mysql::exec,
>> I got no errors but mysql from that point on returns the error
>>
>> "mysql::sel/db server: Commands out of sync; you can't run this
>> command..."
>>
>> I must confess I can't recover from this error and I have to drop
>> the handle and reopen a new connection.
>>
>> So I think we may try something smarter although computationally
>> more expensive like using some pattern matching using a regular
>> exp. The problem is how general a regular expression must be in
>> order to prove to be robust enough for a general SQL statement
>> shipping a SELECT?
>>
>> We can start from this simple case and see, please be patient and I
>> will provide a simple workaround while we think of something
>> smarter.
>>
>> I wonder if rewriting DIO in terms of tdbc would fix this sort of
>> bugs. It would at least concentrate on single component and a
>> single maintainer the cause of future problems
>>
>> -- Massimo
>>
>>
>> On 01-09-2013 3:00, Brice Hamon wrote:
>>> Hi Massimo,
>>>
>>> I did run a test directly using the mysqltcl package, and I did
>>> get the correct data.
>>>
>>> So the same sql statement returns data directly, and nothing via
>>> DIO, so I am guessing the problem lies somewhere in DIO.
>>>
>>> Any sql statement with parenthesis inside makes DIO not
>>> returning data, for example (select * from .. where ..) union
>>> (select * from .. where ....)
>>>
>>> That is what I used for my test.
>>>
>>> Please let me know if you need me to conduct more tests.
>>>
>>> Thank you B.
>>>
>>> On Sat, Aug 31, 2013 at 7:19 PM, Brice Hamon
>>> <normandviking@gmail.com [2]> wrote:
>>>
>>>> OK I will do the test and let you know.
>>>>
>>>> Thanks, B.
>>>>
>>
>>
>> ---------------------------------------------------------------------
>>
>>
To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
>> For additional commands, e-mail: rivet-dev-help@tcl.apache.org
>>
>
>
> ---------------------------------------------------------------------
>
>
To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
> For additional commands, e-mail: rivet-dev-help@tcl.apache.org
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
For additional commands, e-mail: rivet-dev-help@tcl.apache.org

Re: DIO oddities

[Damon Courtney <da...@tclhome.com>]

All that was done a LONG time ago.  The MySQLTcl needs the two separate calls for select and other statements, so I went with the simple case to detect which to use.  You can use some sort of pattern match or regex just to get over the hump, but I think we would want to rewrite DIO to use TDBC in the long run.

Either that or just mark it as deprecated and encourage direct use of TDBC.  Personally, I wrote a new library that does much of what DIO did in terms of queries and argument handling, but it doesn't use objects for anything.  All in all, the whole object basis of DIO is mostly unnecessary and doesn't actually jive with the way some of the DB libraries work.

But, it solved a need at the time. :)

D


On Sep 12, 2013, at 12:04 PM, Massimo Manghi <ma...@unipr.it> wrote:

> Hi Brice,
> 
> I took a quick tour of the exec method in dio_Mysql.tcl (class ::DIO::Mysql)
> 
> It seems to me you hit hard a problem with mysqltcl and DIO
> 
> mysqltcl uses 2 calls for executing queries (actually there are 3 of them, mysql::query is meant for multiple concurrent queries when one doesn't want to draw on the JOIN syntax)
> 
> One call is for SELECT statements and the other for non-SELECT queries
> 
> DIO discriminates between SELECT and non-SELECT statements in a rather simplicistic way: when the first 6 characters of the query match the "SELECT" string ::mysql::sel is called otherwise ::mysql::exec is executed. Your query is sent to ::mysql::exec and the call fails. I tried to send a trivial SELECT to ::mysql::exec, I got no errors but mysql from that point on returns the error
> 
> "mysql::sel/db server: Commands out of sync; you can't run this command..."
> 
> I must confess I can't recover from this error and I have to drop the handle and reopen a new connection.
> 
> So I think we may try something smarter although computationally more expensive like using some pattern matching using a regular exp. The problem is how general a regular expression must be in order to prove to be robust enough for a general SQL statement shipping a SELECT?
> 
> We can start from this simple case and see, please be patient and I will provide a simple workaround while we think of something smarter.
> 
> I wonder if rewriting DIO in terms of tdbc would fix this sort of bugs. It would at least concentrate on single component and a single maintainer the cause of future problems
> 
> -- Massimo
> 
> 
> On 01-09-2013 3:00, Brice Hamon wrote:
>> Hi Massimo,
>> 
>> I did run a test directly using the mysqltcl package, and I did get
>> the correct data.
>> 
>> So the same sql statement returns data directly, and nothing via DIO,
>> so I am guessing the problem lies somewhere in DIO.
>> 
>> Any sql statement with parenthesis inside makes DIO not returning
>> data, for example (select * from .. where ..) union (select * from ..
>> where ....)
>> 
>> That is what I used for my test.
>> 
>> Please let me know if you need me to conduct more tests.
>> 
>> Thank you
>> B.
>> 
>> On Sat, Aug 31, 2013 at 7:19 PM, Brice Hamon <normandviking@gmail.com
>> [2]> wrote:
>> 
>>> OK I will do the test and let you know.
>>> 
>>> Thanks,
>>> B.
>>> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
> For additional commands, e-mail: rivet-dev-help@tcl.apache.org
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
For additional commands, e-mail: rivet-dev-help@tcl.apache.org

Re: DIO oddities

[Massimo Manghi <ma...@unipr.it>]

Hi Brice,

I took a quick tour of the exec method in dio_Mysql.tcl (class 
::DIO::Mysql)

It seems to me you hit hard a problem with mysqltcl and DIO

mysqltcl uses 2 calls for executing queries (actually there are 3 of 
them, mysql::query is meant for multiple concurrent queries when one 
doesn't want to draw on the JOIN syntax)

One call is for SELECT statements and the other for non-SELECT queries

DIO discriminates between SELECT and non-SELECT statements in a rather 
simplicistic way: when the first 6 characters of the query match the 
"SELECT" string ::mysql::sel is called otherwise ::mysql::exec is 
executed. Your query is sent to ::mysql::exec and the call fails. I 
tried to send a trivial SELECT to ::mysql::exec, I got no errors but 
mysql from that point on returns the error

"mysql::sel/db server: Commands out of sync; you can't run this 
command..."

I must confess I can't recover from this error and I have to drop the 
handle and reopen a new connection.

So I think we may try something smarter although computationally more 
expensive like using some pattern matching using a regular exp. The 
problem is how general a regular expression must be in order to prove to 
be robust enough for a general SQL statement shipping a SELECT?

  We can start from this simple case and see, please be patient and I 
will provide a simple workaround while we think of something smarter.

  I wonder if rewriting DIO in terms of tdbc would fix this sort of 
bugs. It would at least concentrate on single component and a single 
maintainer the cause of future problems

  -- Massimo


On 01-09-2013 3:00, Brice Hamon wrote:
> Hi Massimo,
>
> I did run a test directly using the mysqltcl package, and I did get
> the correct data.
>
> So the same sql statement returns data directly, and nothing via DIO,
> so I am guessing the problem lies somewhere in DIO.
>
> Any sql statement with parenthesis inside makes DIO not returning
> data, for example (select * from .. where ..) union (select * from ..
> where ....)
>
> That is what I used for my test.
>
> Please let me know if you need me to conduct more tests.
>
> Thank you
> B.
>
> On Sat, Aug 31, 2013 at 7:19 PM, Brice Hamon <normandviking@gmail.com
> [2]> wrote:
>
>> OK I will do the test and let you know.
>>
>> Thanks,
>> B.
>>


---------------------------------------------------------------------
To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
For additional commands, e-mail: rivet-dev-help@tcl.apache.org

Re: DIO oddities

[Massimo Manghi <ma...@unipr.it>]

On 01-09-2013 3:00, Brice Hamon wrote:
> Hi Massimo,
>
> I did run a test directly using the mysqltcl package, and I did get
> the correct data.
>
> So the same sql statement returns data directly, and nothing via DIO,
> so I am guessing the problem lies somewhere in DIO.
>
> Any sql statement with parenthesis inside makes DIO not returning
> data, for example (select * from .. where ..) union (select * from ..
> where ....)
>
> That is what I used for my test.
>
> Please let me know if you need me to conduct more tests.
>


No thanks, I don't need more tests...still I need more time to do
the tests myself with DIO, you have to be patient for a day or two.

thanks

  -- Massimo


---------------------------------------------------------------------
To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
For additional commands, e-mail: rivet-dev-help@tcl.apache.org

Re: DIO oddities

[Brice Hamon <no...@gmail.com>]

Hi Massimo,

I did run a test directly using the mysqltcl package, and I did get the
correct data.

So the same sql statement returns data directly, and nothing via DIO, so I
am guessing the problem lies somewhere in DIO.

Any sql statement with parenthesis inside makes DIO not returning data, for
example (select * from .. where ..) union (select * from .. where ....)

That is what I used for my test.

Please let me know if you need me to conduct more tests.

Thank you
B.



On Sat, Aug 31, 2013 at 7:19 PM, Brice Hamon <no...@gmail.com>wrote:

> OK I will do the test and let you know.
>
> Thanks,
> B.
>
>
> On Sat, Aug 31, 2013 at 7:17 PM, Massimo Manghi <ma...@unipr.it>wrote:
>
>>
>> yes, strange,
>>
>> I haven't looked at the code for exec yet but I don't recall this method
>> mangling with the statement passed by the caller. I will check it out but
>> in the meanwhile you may put up a test script loading libmysqltcl and
>> passing your SQL statement directly to that...
>>
>>  I see only TDBC as alternative for a different (abstraction
>> layer,database connectivity) pair.
>>
>>  -- Massimo
>>
>> On 01-09-2013 1:11, Brice Hamon wrote:
>>
>>> Hi Massimo,
>>>
>>> I havent tried to call directly the TCL mysql module.
>>>
>>>
>>> I am using mysqltcl-3.052 with a native 5.5.28-3.14.1 mysql
>>> installation.
>>>
>>> I was doing (select ...) union (select ...) when I got that problem.
>>> The query runs fine in MySql WorkBench.
>>>
>>> Strange isnt it.
>>>
>>> B.
>>>
>>>
>>
>

Re: DIO oddities

[Brice Hamon <no...@gmail.com>]

OK I will do the test and let you know.

Thanks,
B.


On Sat, Aug 31, 2013 at 7:17 PM, Massimo Manghi <ma...@unipr.it>wrote:

>
> yes, strange,
>
> I haven't looked at the code for exec yet but I don't recall this method
> mangling with the statement passed by the caller. I will check it out but
> in the meanwhile you may put up a test script loading libmysqltcl and
> passing your SQL statement directly to that...
>
>  I see only TDBC as alternative for a different (abstraction
> layer,database connectivity) pair.
>
>  -- Massimo
>
> On 01-09-2013 1:11, Brice Hamon wrote:
>
>> Hi Massimo,
>>
>> I havent tried to call directly the TCL mysql module.
>>
>>
>> I am using mysqltcl-3.052 with a native 5.5.28-3.14.1 mysql
>> installation.
>>
>> I was doing (select ...) union (select ...) when I got that problem.
>> The query runs fine in MySql WorkBench.
>>
>> Strange isnt it.
>>
>> B.
>>
>>
>

Re: DIO oddities

[Massimo Manghi <ma...@unipr.it>]

yes, strange,

I haven't looked at the code for exec yet but I don't recall this 
method mangling with the statement passed by the caller. I will check it 
out but in the meanwhile you may put up a test script loading 
libmysqltcl and passing your SQL statement directly to that...

  I see only TDBC as alternative for a different (abstraction 
layer,database connectivity) pair.

  -- Massimo

On 01-09-2013 1:11, Brice Hamon wrote:
> Hi Massimo,
>
> I havent tried to call directly the TCL mysql module.
>
> I am using mysqltcl-3.052 with a native 5.5.28-3.14.1 mysql
> installation.
>
> I was doing (select ...) union (select ...) when I got that problem.
> The query runs fine in MySql WorkBench.
>
> Strange isnt it.
>
> B.
>


---------------------------------------------------------------------
To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
For additional commands, e-mail: rivet-dev-help@tcl.apache.org

Re: DIO oddities

[Brice Hamon <no...@gmail.com>]

Hi Massimo,

I haven't tried to call directly the TCL mysql module.

I am using mysqltcl-3.052 with a native 5.5.28-3.14.1 mysql installation.

I was doing (select ...) union (select ...) when I got that problem. The
query runs fine in MySql WorkBench.

Strange isn't it.

B.



On Sat, Aug 31, 2013 at 7:05 PM, Massimo Manghi <ma...@unipr.it>wrote:

> first of all I would try to rule out the possibility that the DB specific
> driver is misinterpreting your SQL code. What DBMS are you using? If you
> put together your complex SQL statement and try to call directly the Tcl
> connector for the DB what happens?
>
>  -- Massimo
>
>
> On 01-09-2013 0:19, Brice Hamon wrote:
>
>> Hi All,
>>
>> I ran across a strange problem using the DIO interface over a Mysql
>> database.
>>
>> When I run this sql command:
>>
>> set sql "select * from records where userID=6"
>>  set res [DIO exec $sql]
>>
>> I get the expected result.
>>
>> When I execute:
>> set sql "(select * from records where userID=6)"
>>
>> I get nothing back.
>>
>> Parenthesis are needed in some complex sql query I use so I need to
>> have them in my sql string.
>>
>> I tried with: set sql "(select * from records where userID=6)"
>>
>>
>> with no luck.
>>
>> Any ideas?
>>
>> Thank you
>>
>> B.
>>
>
>

Re: DIO oddities

[Massimo Manghi <ma...@unipr.it>]

first of all I would try to rule out the possibility that the DB 
specific driver is misinterpreting your SQL code. What DBMS are you 
using? If you put together your complex SQL statement and try to call 
directly the Tcl connector for the DB what happens?

  -- Massimo

On 01-09-2013 0:19, Brice Hamon wrote:
> Hi All,
>
> I ran across a strange problem using the DIO interface over a Mysql
> database.
>
> When I run this sql command:
>
> set sql "select * from records where userID=6"
>  set res [DIO exec $sql]
>
> I get the expected result.
>
> When I execute:
> set sql "(select * from records where userID=6)"
>
> I get nothing back.
>
> Parenthesis are needed in some complex sql query I use so I need to
> have them in my sql string.
>
> I tried with: set sql "(select * from records where userID=6)"
>
> with no luck.
>
> Any ideas?
>
> Thank you
>
> B.


---------------------------------------------------------------------
To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
For additional commands, e-mail: rivet-dev-help@tcl.apache.org