Thanks for including Informix in the subject! I've seen answers from Ilya
Sterin and Doug Wilson too, but I think I'm adding useful new information,
albeit somewhat belatedly.
Curt Russell Crandall wrote:
> I have a FCGI script that lets you enter an sql statement in a textbox and
> on submit the statement is submitted to the database. If it is a select
> statement, a table with the results is displayed. If it is not a select
> statement, the program simply prints an error that fetch* didn't work.
>
> I'd like to make this program more intelligent by displaying the message
> returned by the database by successful update, delete, insert,
> etc. statements.
I'm not clear what message you think is returned by the database. If the
UPDATE (or DELETE or INSERT) is successful, the SQLCODE (and sqlca.sqlcode)
value is zero, indicating success, and the sqlca.sqlerrd[1] value indicates
how many rows were updated, deleted or inserted. [Check the index; I'm
working from memory; I think it is 1 or 2, though!] You can read about
accessing the sqlca structure in 'perldoc DBD::Informix'.
> I'm using Informix and I'm unable to find an attribute
> or function that allows me to do this. I believe in Sybase you can just
> do a fetch and grab a particular piece of the result set that would
> contain this data (don't have the perldoc for DBD::Sybase in front of me,
> so I don't know offhand what the syntax would be to do this). Is there's
> something equivalent I can use under Informix to get this information?
>
> Example
>
> update informix.vp_ppreq
> set ts = '2001-07-04 12:00:00'
> where prt_date = '2001-03-01'
>
> No rows found. -----> this is what I want to get!!!
The database never says that directly. Indeed, unless use a MODE ANSI
database, you won't even get an SQLCODE of 100 (NOT FOUND) for an UPDATE that
updates zero rows -- that's an Informix feature dating back to 1985!
If you want to know about whether the statement is a cursory statement (from
which data can be fetched) or not, then you prepare the statement ($sth =
$dbh->prepare($stmt)) and examine the $sth->{NUM_OF_FIELDS} value which tells
you how many columns there are in each returned row of data. If this is zero,
then you don't want to do any fetching; if it is non-zero, you do need to
collect the results.
If you decide you want to produce 'No rows found', then you need to do the
producing. That's what Db-Access does, only it would say 'No rows updated'
for the statement quoted.
Finally, you should consider whether it is remotely secure to let people enter
arbitrary SQL. Succinctly, it isn't. What if the user types a SELECT command
followed by a semi-colon and a DELETE statement? That could hurt. A lot!
--
Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED])
Guardian of DBD::Informix 1.00.PC1 -- see http://www.cpan.org/
#include <disclaimer.h>
