Query that never returns

Post Reply
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Query that never returns

Post by barry.marcus »

I have the following query in my code:

<sql "select project_id searchingProject, search_field searchingField, filter_id searchingFilter, documentType searchingDocumentType from backgroundSearchFlag where isSearching = '1'"></sql>

The definition of backgroundSearchFlag is:

NAME TYPE SIZE
------------+------------+------------+
id counter 1
filter_id varchar 25
search_field varchar 25
project_id varchar 25
pid varchar 25
documentType varchar 4
category varchar 4
pEquivs varchar 4
cEquivs varchar 4
isSearching varchar 1


The problem is that if the table has zero rows, then the query hangs. The code never progresses beyond the <sql> statement and the texis process continues to grind on and on, as if in an infinite loop. I don't don't understand why it does this. What should I do differently?

The output of Texis -version is:

Texis Web Script (Vortex) Copyright (c) 1996-2013 Thunderstone - EPI, Inc.
Commercial Version 7.00.1376340754 20130812 (i686-intel-winnt-64-32)

However, I notice this same behavior on our version 6.x installations as well.

Thanks
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

Query that never returns

Post by mark »

Odd. Any index on backgroundSearchFlag?
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Query that never returns

Post by barry.marcus »

Indeed.

Well, there weren't any indexes on the table because there are rarely more than a dozen rows in it, and I never saw the need. But I created an index on isSearching (the values of which are either '1', '0' or NULL) and also an index on id. Neither of these had any affect... Same thing.

What's even odder is that we see the same behavior on another table as well. Both tables have a column defined as varchar(1) for which the criteria "column='1'" is used in the query. In the case of that other query we found that it hung whenever the table contained zero rows WITH A NON-NULL value in that column. This was also a mystery. But we discovered a rather kludgey workaround in that case. Just prior to the execution of the select query with the "column='1'" criteria, the code inserts into the table a bogus row with a non-null value in that varchar(1) column. With that row in there, the query executes just fine. Then, immediately after the select query returns, the bogus row is deleted. Without that bogus row, the select query hangs forever. Like I said, it's a kludge, but it works in that case. We have yet to find a workaround in this case, though. Why the workaround was/is necessary in the other case, I have no idea. Like I said, it's a mystery.

Any ideas what I can do to trace and/or reveal what might be going on internally when the code hangs like it does?
User avatar
Kai
Site Admin
Posts: 1271
Joined: Tue Apr 25, 2000 1:27 pm

Query that never returns

Post by Kai »

Open a tech support ticket and send us a zipped copy of the table.
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

Query that never returns

Post by mark »

Yeah, maybe there's something wrong with the table? Try kdbfchk on it. I'm not able to reproduce the hang with the code below. Maybe you can try it or come up with a complete tiny example that does reproduce the hang.

<script language=vortex>
<a name=main>
<sqlcp tracesql on>
<sql "drop table backgroundSearchFlag"></sql>
<sql "create table backgroundSearchFlag(
id counter,
filter_id varchar(25),
search_field varchar(25),
project_id varchar(25),
pid varchar(25),
documentType varchar(4),
category varchar(4),
pEquivs varchar(4),
cEquivs varchar(4),
isSearching varchar(1)
)"></sql>
<sql "insert into backgroundSearchFlag values(counter,'foo','bar','baz','666','text','all','none','none','')"></sql>
<sql "insert into backgroundSearchFlag values(counter,'foo','bar','baz','666','text','all','none','none','2')"></sql>
<!--
<sql "insert into backgroundSearchFlag values(counter,'foo','bar','baz','666','text','all','none','none','1')"></sql>
-->
<sql "select project_id searchingProject, search_field searchingField, filter_id searchingFilter, documentType searchingDocumentType from backgroundSearchFlag where isSearching = '1'">
$loop:$Number,$Flag
</sql>
$loop matched
</a>
</script>
Post Reply