Re: Date format error after upgrade IDS 7.30UC10 -> 7.31UC4

From: Richard Spitz <Richard.Spitz@ana.med.uni-muenchen.de> > >after upgrading our Server from IDS 7.30UC10 to 7.31UC4 over the >weekend, I'm now experiencing a strange error involving date formats. > >I have an application written in Visual Basic 6 that connects to the >database server via the Informix ODBC driver 3.30.00.10139. With >"Setnet32", DBDATE is set to "Y4MD-" on the PC client. > >On the database side, there is an insert trigger defined which >calls a stored procedure which in turn executes an ESQL/C program >(running on the database server itself) with some arguments, including >two dates. This program now produces error -1218 in the "rstrdate" >function. > >Nothing has changed on the client side, the error occurs no matter >what client I run the VB6 program on. Neither has the ESQL/C program >changed. > >I believe I traced the cause for the error down to the format of the >date arguments produced by the stored procedure. As I already said, >DBDATE is set to "Y4MD-" by the client, this is also shown in a trace >of the environment the ESQL/C program is called with. However, the >stored procedure formats the date arguments as "DD.MM.YYYY". I have >no idea why this happens. Unfortunately, I don't have a test environ- >ment with IDS 7.30UC10 available where I could test what the old >version did. > >Here is the trigger: > >create trigger "verteil".ins_urlaub insert on "verteil".urlaub > referencing new as post_ins > for each row > ( > execute procedure "verteil".upd_genommen(post_ins.persnr , > post_ins.jahr ), > execute procedure "verteil".url_verteil_chk(post_ins.persnr, > post_ins.von ,post_ins.bis )); > >And this is the SP where the error happens. The line where it happens >is marked with ">>>>> <<<<<" > >CREATE DBA PROCEDURE "verteil".url_verteil_chk(p_persnr INTEGER, > p_von DATE, p_bis DATE) > DEFINE sql_err, isam_err INT; > DEFINE errstring CHAR(30); > DEFINE sysstring CHAR(69); > > ON EXCEPTION > SET sql_err, isam_err > > IF isam_err = -90 THEN > RAISE EXCEPTION -746,0,"Falsche Anzahl Argumente beim Aufruf!"; > ELIF isam_err = -91 THEN > RAISE EXCEPTION -746,0,"Fehler bei Datumsumwandlung!"; > ELIF isam_err = -92 THEN > RAISE EXCEPTION -746,0,"In diesem Zeitraum bereits Eintrag >im Verteiler!"; > ELIF isam_err = -98 THEN > RAISE EXCEPTION -746,0,"SQL-Fehler!, Errorlog konnte nicht > geoeffnet werden!"; > ELIF isam_err = -99 THEN > RAISE EXCEPTION -746,0,"SQL-Fehler! Details siehe Errorlog"; > ELSE > LET errstring = "Sonstiger Fehler " || isam_err; > RAISE EXCEPTION -746,0,errstring; > END IF > END EXCEPTION > > BEGIN >-- SET DEBUG FILE TO "/home/verteil/url_verteil_chk.trace"; >-- TRACE ON; > > >>>>> LET sysstring = "/home/verteil/url_verteil_chk " || p_persnr || > " " || p_von || " " || p_bis; <<<<<<<<<<<<<<<< > SYSTEM sysstring; > LET sysstring = "/home/verteil/url_verteil_ins " || p_persnr || > " " || p_von || " " || p_bis; > SYSTEM sysstring; > END >END PROCEDURE >DOCUMENT "Prozedur prueft, ob bei Urlaubseintrag schon Verteiler-Eintrag >da.", > "Falls nicht, wird der Urlaub sofort im Verteiler eingetragen." >WITH LISTING IN "/tmp/vert_chk.list" >; > > >Why are the dates in "sysstring" formatted as "DD.MM.YYYY", when they >are supposed to be formatted as "YYYY-MM-DD"? Any chance that the server was started with DBDATE=dmy4. ? ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com