FAQ
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 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!!!

Thank you,

Curt Crandall

Search Discussions

  • Sterin, Ilya at Jul 3, 2001 at 4:23 pm
    Not sure about the attribute, but why not just use $DBI::errstr along with
    the return value. If an error is return you can display the $DBI::errstr if
    not, but no rows are returned/updated you can display the "No rows found"
    message yourself.

    Ilya

    -----Original Message-----
    From: Curt Russell Crandall
    To: dbi-users@perl.org
    Sent: 07/03/2001 10:14 AM
    Subject: getting return messages from non-selects in Informix

    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 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!!!

    Thank you,

    Curt Crandall
  • Curt Russell Crandall at Jul 3, 2001 at 8:09 pm
    That's kind of like the workaround I have in now, but it's not the
    solution I really want... I would like to display verbatim the return
    message from Informix since there will be some commands issued where
    looking at the return value for the number of rows updated will be
    insufficient.

    Thanks,
    Curt
    On Tue, 3 Jul 2001, Sterin, Ilya wrote:

    Not sure about the attribute, but why not just use $DBI::errstr along with
    the return value. If an error is return you can display the $DBI::errstr if
    not, but no rows are returned/updated you can display the "No rows found"
    message yourself.

    Ilya
  • Wilson, Doug at Jul 3, 2001 at 4:26 pm
    Look at the perldoc for DBD::Informix, you have access to the sqlda
    structure.

    Or the do() and execute() methods return the number of rows affected.

    -----Original Message-----
    From: Curt Russell Crandall
    Sent: Tuesday, July 03, 2001 9:15 AM
    To: dbi-users@perl.org
    Subject: getting return messages from non-selects in Informix


    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 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!!!

    Thank you,

    Curt Crandall
  • Curt Russell Crandall at Jul 3, 2001 at 8:11 pm
    The perldoc I have does not refer to a sqlda structure, however it briefly
    talks about a sqlca structure... but I am unable to find the specific
    information I need. I'll have to track down the Informix manuals and look
    at this structure in there.

    Thanks,
    Curt
    On Tue, 3 Jul 2001, Wilson, Doug wrote:

    Look at the perldoc for DBD::Informix, you have access to the sqlda
    structure.

    Or the do() and execute() methods return the number of rows affected.
  • Jonathan Leffler at Jul 12, 2001 at 5:44 am
    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 (jleffler@earthlink.net, jleffler@informix.com)
    Guardian of DBD::Informix 1.00.PC1 -- see http://www.cpan.org/
    #include <disclaimer.h>

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedJul 3, '01 at 4:15p
activeJul 12, '01 at 5:44a
posts6
users4
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase