if exists in php with UDB Linux

[Bob Stearns]

I need to see if some rows exist, but do not need to read them. The best
I could come up with is:

$sql = "SELECT 1 FROM $schema.auction_owners
WHERE auction_id=$ai
AND item_id=$item_id
FETCH FIRST 1 ROW ONLY
FOR READ ONLY
";
dosql($sql, "S AO4", -1, "", $n2, $res2);
if(odbc_fetch_array($res)) {
....
}

[jefftyzzer]

On Feb 27, 10:16 am, Bob Stearns <[e-mail]:

I need to see if some rows exist, but do not need to read them. The best
I could come up with is:

$sql = "SELECT 1 FROM $schema.auction_owners
WHERE auction_id=$ai
AND item_id=$item_id
FETCH FIRST 1 ROW ONLY
FOR READ ONLY
";
dosql($sql, "S AO4", -1, "", $n2, $res2);
if(odbc_fetch_array($res)) {
...

}- Hide quoted text -

- Show quoted text -

Your method certainly works. If you want to do something SQL-related
after you find if the rows exist or not, you may want to consider
using BEGIN ATOMIC:

BEGIN ATOMIC
IF ((SELECT 1 FROM X WHERE ... FETCH FIRST 1 ROW ONLY) = 1) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;

You could also combine your method with BEGIN ATOMIC, i.e.,

BEGIN ATOMIC
IF (NOT EXISTS(SELECT 1 FROM X WHERE ...)) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;
--Jeff

[Bob Stearns]

jefftyzzer wrote:

On Feb 27, 10:16 am, Bob Stearns <[e-mail]:

>>I need to see if some rows exist, but do not need to read them. The best
I could come up with is:

$sql = "SELECT 1 FROM $schema.auction_owners
WHERE auction_id=$ai
AND item_id=$item_id
FETCH FIRST 1 ROW ONLY
FOR READ ONLY
";
dosql($sql, "S AO4", -1, "", $n2, $res2);
if(odbc_fetch_array($res)) {
...

}- Hide quoted text -

- Show quoted text -

Your method certainly works. If you want to do something SQL-related
after you find if the rows exist or not, you may want to consider
using BEGIN ATOMIC:

BEGIN ATOMIC
IF ((SELECT 1 FROM X WHERE ... FETCH FIRST 1 ROW ONLY) = 1) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;

You could also combine your method with BEGIN ATOMIC, i.e.,

BEGIN ATOMIC
IF (NOT EXISTS(SELECT 1 FROM X WHERE ...)) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;
--Jeff

Thanks. I'm doing different things at the PHP level depending on the result.

[Ray]

On Feb 27, 12:48 pm, Bob Stearns <[e-mail]:

jefftyzzer wrote:

On Feb 27, 10:16 am, Bob Stearns <[e-mail]:

>I need to see if some rows exist, but do not need to read them. The best
I could come up with is:

>$sql = "SELECT 1 FROM $schema.auction_owners
WHERE auction_id=$ai
AND item_id=$item_id
FETCH FIRST 1 ROW ONLY
FOR READ ONLY
";
dosql($sql, "S AO4", -1, "", $n2, $res2);
if(odbc_fetch_array($res)) {
...

>}- Hide quoted text -

>- Show quoted text -

Your method certainly works. If you want to do something SQL-related
after you find if the rows exist or not, you may want to consider
using BEGIN ATOMIC:

BEGIN ATOMIC
IF ((SELECT 1 FROM X WHERE ... FETCH FIRST 1 ROW ONLY) = 1) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;

You could also combine your method with BEGIN ATOMIC, i.e.,

BEGIN ATOMIC
IF (NOT EXISTS(SELECT 1 FROM X WHERE ...)) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;

--Jeff

Thanks. I'm doing different things at the PHP level depending on the result.

Why not use SELECT COUNT (*) ?

[jefftyzzer]

On Feb 28, 7:23 am, "Ray" <[e-mail]:

On Feb 27, 12:48 pm, Bob Stearns <[e-mail]:

jefftyzzer wrote:

On Feb 27, 10:16 am, Bob Stearns <[e-mail]:

>>I need to see if some rows exist, but do not need to read them. The best
>>I could come up with is:

>>$sql = "SELECT 1 FROM $schema.auction_owners
> WHERE auction_id=$ai
> AND item_id=$item_id
> FETCH FIRST 1 ROW ONLY
> FOR READ ONLY
> ";
>>dosql($sql, "S AO4", -1, "", $n2, $res2);
>>if(odbc_fetch_array($res)) {
>>...

>>}- Hide quoted text -

>>- Show quoted text -

Your method certainly works. If you want to do something SQL-related
after you find if the rows exist or not, you may want to consider
using BEGIN ATOMIC:

BEGIN ATOMIC
IF ((SELECT 1 FROM X WHERE ... FETCH FIRST 1 ROW ONLY) = 1) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;

You could also combine your method with BEGIN ATOMIC, i.e.,

BEGIN ATOMIC
IF (NOT EXISTS(SELECT 1 FROM X WHERE ...)) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
END IF;--
END;

--Jeff

Thanks. I'm doing different things at the PHP level depending on the result.

Why not use SELECT COUNT (*) ?- Hide quoted text -

- Show quoted text -

Please see the section titled "Tuning existential predicates" in the
following TinyURL link for an opinion on COUNT(*): http://tinyurl.com/2t3gac

--Jeff

[Ray]

On Feb 28, 12:40 pm, "jefftyzzer" <[e-mail]:

Please see the section titled "Tuning existential predicates" in the
following TinyURL link for an opinion on COUNT(*):http://tinyurl.com/2t3gac

I agree that irrelevant existential tests are a waste of time but I
don't see where that's the case when the whole reason for the query is
simply to establish existence to control program flow at a later time.
That said, I'm sure I'm overlooking something obvious to the more
experienced so what am I missing?

[jefftyzzer]

On Mar 1, 7:23 am, "Ray" <[e-mail]:

On Feb 28, 12:40 pm, "jefftyzzer" <[e-mail]:

Please see the section titled "Tuning existential predicates" in the
following TinyURL link for an opinion on COUNT(*):http://tinyurl.com/2t3gac

I agree that irrelevant existential tests are a waste of time but I
don't see where that's the case when the whole reason for the query is
simply to establish existence to control program flow at a later time.
That said, I'm sure I'm overlooking something obvious to the more
experienced so what am I missing?

Hi, Ray.

I'm not saying you're missing anything. I think Serge's argument (the
author of the opinion I included) is that there's no sense counting up
a bunch of things only to throw this number away.

The better thing about EXISTS is that the minute a single row tests
true, the search stops, so there's the potential for a much faster
test.

Anyway, I think we're splitting hairs here. As in all things,
individual results will vary. Do what works :-)

Regards,

--Jeff

[Ray]

On Mar 1, 12:58 pm, "jefftyzzer" <[e-mail]:

I'm not saying you're missing anything. I think Serge's argument (the
author of the opinion I included) is that there's no sense counting up
a bunch of things only to throw this number away.

Thanks, Jeff. In fact I WAS missing a couple of things. First was the
explicit optimization of the select, which I now understand.

The better thing about EXISTS is that the minute a single row tests
true, the search stops, so there's the potential for a much faster
test.

Does that mean that DB2 will actually short circuit the select in the
infocenter example for exists?

EXISTS (SELECT * FROM TEMPL WHERE SALARY < 10000)

[Brian Tkatch]

On 1 Mar 2007 12:52:04 -0800, "Ray" <ra**************@yahoo.com>
wrote:

>On Mar 1, 12:58 pm, "jefftyzzer" <[e-mail]:

>I'm not saying you're missing anything. I think Serge's argument (the
author of the opinion I included) is that there's no sense counting up
a bunch of things only to throw this number away.

Thanks, Jeff. In fact I WAS missing a couple of things. First was the
explicit optimization of the select, which I now understand.

>The better thing about EXISTS is that the minute a single row tests
true, the search stops, so there's the potential for a much faster
test.

Does that mean that DB2 will actually short circuit the select in the
infocenter example for exists?

EXISTS (SELECT * FROM TEMPL WHERE SALARY < 10000)

I think it should. EXISTS (SELECT * is the classic example of
short-circuiting.

B.