PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Michael Peppler <mpeppler at MBAY dot NET>
Subject: Error when checking "if exists"
Date: May 26 1998 6:30PM

Rashmi Anand writes:
 > Hello, 
 > I am trying to check for the existance of a row and if it exists, send
 > an update otherwise, send an Insert statement:
 > 
 > if exists (select * from ... where ...)
 >    begin 
 >      Update ... where ...
 >    end
 > else
 >    begin 
 >      Insert ...
 >    end
 > 
 > and I get the following error:
 > 
 > DB-Library error: Attempt to initiate a new SQL Server operation with
 > results pending.

This needs to go into the FAQ.

You should *always* call dbresults() in a loop, until it returns
NO_MORE_RESULTS. Your query returns at least two sets of results, even 
if it doesn't return any *rows*.

As an aside you should probably code the above like this:

update ... where
if @@rowcount = 0
  insert ....

This avoids an extra select on the table (the update fails silently if 
the where clause does not match any rows).

Michael
-- 
Michael Peppler         -||-  Data Migrations Inc.
mpeppler@datamig.com    -||-  http://www.mbay.net/~mpeppler
Int. Sybase User Group  -||-  http://www.isug.com