FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: THF [mailto:thf AT cs.vabo.cz]
> Sent: November 12, 2002 3:57 AM
>
> I try this (ex.):
> 
> //connect to DB
> mssql_query("BEGIN TRAN",$db);
> $res=mssql_query("select USER_ID from USERS where USER_ID > 
> 10 and USER_ID
> <15);
> while ($rows=mssql_fetch_array($res)){
>      mssql_query("delete from USERS where USER_ID = $rows[USER_ID]);
> }
> mssql_query("COMMIT",$db);
> mssql_close();
> 
> but It don't work.

If you treat it like a data problem (instead of an application problem), you
can often save yourself work and make the application faster.  Try to move
the logic into a stored procedure with a query along these lines:

        delete USERS
        where USER_ID in (
                select USER_ID
                from USERS
                where USER_ID > 10 
                and USER_ID <15
        )

Of course the inner "where" clause could be more interesting.  :)  

Note this approach does not require a transaction.  It will also be an order
of magnitude faster.  

HTH.

--jkl


The information contained in this transmission may contain privileged and
confidential information and is intended only for the use of the person(s)
named above.  If you are not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient, any
review, dissemination, distribution or duplication of this communication is
strictly prohibited. If you are not the intended recipient, please contact
the sender immediately by reply e-mail and destroy all copies of the
original message. Please note that we do not accept account orders and/or
instructions by e-mail, and therefore will not be responsible for carrying
out such orders and/or instructions.