INSERT with zero SELECT (fwd)

> > Hi all, > > Consider, if you will, the following scenario. There are two tables, > TABLEA and TABLEB, with the same column definitions. The following SQL > statement is used to copy any rows from TABLEA to TABLEB (however, > TABLEA is empty). > > INSERT INTO TABLEB SELECT ... FROM TABLEA> > Under INFORMIX 4 (Turbo) the return value from sqlcode is 0, with a > qualifier, "No rows processed". > > Under INGRES 6.2 (Ultrix/SQL 1.0) the return value from sqlcode = 100 > (defined as SQL_NOTFOUND). > > It appears that Informix returns an informational OK, whereas Ingres > returns an informational error/warning. > > Which (if either) is "more correct"? I have an application which works > with both database systems, and I need to have a standard response to a > query regardless of which platform I'm using at the time. > > For any non-SELECT statement, could I map any SQL_NOTFOUND code (value > 100) back to a zero value (meaning everything's hunkydory)? > > Chris > -- > VISIONWARE LTD, 57 Cardigan Lane, LEEDS LS4 2LE, England > Tel +44 532 788858. Fax +44 532 304676. Email chris@visionware.co.uk > -------------- "VisionWare: The home of DOS/UNIX/X integration" ------------- > Chris - I think one could be deemed more correct than the other if it were closer to compliance with the ANSI SQL standard than the other. Off hand, I'm not sure whether the standard specifies behavior of SQL_NOTFOUND, but I think it's a safe guess that this has been left to the vendor's imagination, as have many aspects of SQL, and that these two vendor's differ slightly on this point. If your code has to run only on these two platforms, and this behavior is documented by the vendors (so that you have a reasonable expectation that it will not change in the next release) then you could probably hard code the solution you describe for this particular case only (100 != 0 for all cases!). A more general solution that would take in to account addition of a 3rd (or nth) database platform in the future, and would allow for changes in behavior in future releases, and would allow for other dbms specific behavior, would be to make your application aware of the identity of the database platform at runtime (possibly via environment variable), and provide platform specific code as required. Such platform specific code could be easily modified, since you would make it easy to find. - Greg ------------ DHL WORLDWIDE EXPRESS ------------------------------------------- Greg Bryan gbryan@ssf-sys.dhl.com DHL Systems Data Administrator uunet!ssf-sys.dhl.com!gbryan San Francisco -------------------------------------------------------------------------------