Hi there!
When digging for the error in my previous message:
"Possible to set timeout on the connection ?"
I came over a very strange bug somewhere from SQLMap, the Oracle
driver classes12.jar and to the database Oracle 9.2.
I do a plain SQL without binding variables (I do $complexWhere$ ->
myBean.geComplexWhere) in the
xml file) the query looks like this from the debug info:
DEBUG 2005-09-07 11:55:50,003
com.ibatis.common.jdbc.logging.PreparedStatementLogProxy (Line: 48) -
{pstm-100007}
PreparedStatement: select C.COMPANYID, C.DMID, .......... where ...
and (( C.ORGANIZATIONNO like '%Galleri
Henrik%'
or lower(C.BUSINESSNAME) like
lower('%Galleri Henrik%')
or lower(C.MARKETNAME) like
lower('%Galleri Henrik%') ......) ) )
order by .....
DEBUG 2005-09-07 11:55:50,023
com.ibatis.common.jdbc.logging.PreparedStatementLogProxy (Line: 49) -
{pstm-100007}Parameters: []
DEBUG 2005-09-07 11:55:50,033
com.ibatis.common.jdbc.logging.PreparedStatementLogProxy (Line: 50) -
{pstm-100007} Types: []
(mine lineshifts in the query, and mine replacments of sql with .....)
---
The query string is of 1983 length (if you count the spaces before the "select"
statment),
or 1973 without the spaces, no tail whitespaces counted. When I search e.g. for
'Galleri Henrik',
the query string get exactly total 1983 of length. But lets say it's 1983 then.
SO WHEN the query string is of exactly 1983 of length the SQLMap connection
hangs and I get some kind
of lock in the database (CURSOR BIND). Believe it or not !!! I still can't
believe it :-)
One Oracle guru told me that the case could be that Oracle are not able to
return the result
back again after doing this query and goes into somekind of wait modus. Waiting
for "something"
for the connection ?? I don't have a clue.
It's nothing wrong with the data or the SQL. I can copy&paste this query string
into a SQL
tool, using the same Oracle JDBC driver as SQLMap does (I use DbVisualizer),
and run the
query with or without the whitespaces in front, and the query runs just fine
under 1 second.
This make me think, can this has something with SQLMap to do ? Do SQLMap
somekind of string
manipulating that could result in some kind of "modus" when the query string is
of 1983 in length ?
Oracle can handle more then that of course and should be discovered for a long
time agao if that
was the case ? Same with the driver, but what about SQLMap ??? A bug ?
It's a actually unbelievably this case, when the query string is less or larger
than 1983, no problem
at all, makes it even more strange. As my previous e-mail, when size is 1982 or
1984 no problem,
And it's not related to Galleri Henrik .. I can typ in '1234567 123456' -> same
error, it's the length !!
--
Have you heard of this before ? :-) Any wild ideas ?? Something I should test ?
--
The quick solution is to remove many whitespaces in the sql string = become
less that 1983
of length with normal searches. Not that quick is to make binding variables.
Tested both, works just fine.
We use the SQLMap version available in january 2005.
Clues ?
Cheers,
Erlend
