# base crashes if like query has blank

I'm getting a crash whenever the parameter to a query has a space in it. A simplified version of the query is:

SELECT * FROM "My_Table" WHERE ( UPPER ( "Title" ) LIKE UPPER ( '%' || ? || '%' ) )

This works fine if the parameter has no spaces but with spaces there's about a 45 second hang followed by a crash. I looked for debug log options but couldn't find any. Also couldn't see the crash report when I chose to send one. Also couldn't append any info to the crash report so doubt that it's useful to developers without knowing what triggered the crash. The Windows application log just showed that soffice.bin crashed.

The table is very simple, one key integer ID field with autovalues and 6 varchar text fields. There are currently only 540 records in the table. The "Title" field used in the simplified query has no null entries and no non ascii characters that I know of.

There is of course a simple work around. When I enter the query and replace spaces with _ it works. Incidentially, the documentation I found still showed :param instead of ?. That gave me a lot of fun. Another aside, I had planned on improving my comparison by using Difference(). I had it all worked out when I found that Firebird doesn't support the Difference function.

This is a trivial application of course. I'm a retired software engineer playing around to fight boredom.

Thank you,

Wayne

edit retag close merge delete

Sort by » oldest newest most voted

Hello,

There is a bug in Base using Firebird when converted from HSQLDB. This bug concerns sub forms but also involves using :param vs ?.

To fix (one-time only needed per .odb) can be found in this post -> How to unzip ODB file and edit the content.xml file to fix Firebird bug?. After the fix, :param works with the exception of running in direct mode. In that case you do need to use ?.

As for the problem with the space it is a bug and should be reported here -> Bugzilla if not done by someone else already.

Another workaround, similar to yours, is to surround the data with apostrophes when entering - 'search for me'

As for Difference function, I only find this in a few DB's such as SQL Server and Azure. Not certain it is a standard.

more

Thank you. I followed the post and fixed the :param bug but of course I had already replaced :param with ? in my queries. I didn't find anything like my problem on Bugzilla with blanks in the parameter so I posted it. I'm surprised this hasn't surfaced before if it is reproducible.

( 2019-09-03 00:26:39 +0200 )edit

Just a note. It is helpful to others if the actual link to the reported bug is given.

Will post here - tdf#127299

( 2019-09-03 00:34:20 +0200 )edit