Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!asuvax!ncar!husc6!lange!bochner
From: bochner@lange.harvard.EDU (Harry Bochner)
Newsgroups: comp.databases
Subject: Re: Informix-ESQL/C errors
Message-ID: <4132@husc6.harvard.edu>
Date: 10 Sep 90 18:48:27 GMT
References: <1990Sep8.192210.26950@investor.pgh.pa.us>
Sender: news@husc6.harvard.edu
Reply-To: bochner@lange.harvard.EDU (Harry Bochner)
Organization: Aiken Computation Laboratory, Harvard University
Lines: 38

In article <1990Sep8.192210.26950@investor.pgh.pa.us>,
rbp@investor.pgh.pa.us (Bob Peirce #305) writes:
 > when I do the following, which I know fails, I get a zero in sqlcode
 > and in the ISAM error code, sqlerrd[1].  Anybody know why?
 > $include sqlca;
 > $	database reports
 > $	SELECT	c_name, c_no
 > 	FROM client
 > 	where c_no = 12345	<== 12345 substituted for $account
 > 	into temp c;
 > fprintf(stderr,
 > 	"Unable to find client record for %d.  Sqlcode = %ld, sqlerrd = %ld.\n",
 > 	account, sqlca.sqlcode, sqlca.sqlerrd[1]);

I just tried it out with INFORMIX-4gl (I don't have ESQL, but I imagine it'll
be the same). What seems to be happening is that finding zero rows, and thus
creating a temp table c with zero rows, doesn't count as an error condition.
Not too strange, actually.

According to my experiments, the following behaves more like what you're
looking
for (assuming ESQL has this 4GL construction): sqlca.sqlcode gets set to 100
(NOTFOUND).
	select c_name, c_no into namevar, numvar ...
                            ^^^^
Likewise if you set up a cursor for the select: no error when you open
the cursor,
but error 100 when you do a fetch.

But your best bet might be to do a select count(*) into cnt, and then
just check
whether cnt=0. I use this method frequently. Assuming that you really want to
fetch into a temp table, as in your example, you can keep the select as
you wrote
it, but follow it with "select count(*) into cnt from c".

Harry Bochner
bochner@das.harvard.edu