Oninit cdi / Archive
Talk to an expert →

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

Posted in 2000

Topics: Installation, Setup & Upgrades, Stored Procedures & SPL, Connectivity: ODBC / JDBC / .NET, Connectivity: ESQL/C, 4GL & Embedded SQL, Server Administration, Triggers, Constraints & Referential Integrity, Versions, Editions & End-of-Life

Richard Spitz — — source: Informix-list mailing list archive (1991-1998)

Hi Informixers, 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"? Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. med Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | | Klinikum Grosshadern | FAX : +49-89-7095-6420 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+

↪ replying to Richard Spitz
Kevin Zou — — source: Informix-list mailing list archive (1991-1998)

I had been in same situation. Do following see if fixes: 1.bring down database 2.export DBDATE=Y4MD- 3.startup database in the same session Regards Kevin Zou Richard Spitz <Richard.Spitz@ana.med.uni-muenchen.de> wrote in message news:3905C822.430EB7AF@ana.med.uni-muenchen.de... > Hi Informixers, > > 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"? > > Regards, Richard > -- > +--------------------------+------------------------------------------+ > | Dr. med Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | > | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | > | Klinikum Grosshadern | FAX : +49-89-7095-6420 | > | 81366 Munich, Germany | GSM : +49-172-8933578 | > +--------------------------+------------------------------------------+

↪ replying to Richard Spitz
David Williams — — source: Informix-list mailing list archive (1991-1998)

In article <3905C822.430EB7AF@ana.med.uni-muenchen.de>, Richard Spitz <Richard.Spitz@ana.med.uni-muenchen.de> writes >Hi Informixers, > >after upgrading our Server from IDS 7.30UC10 to 7.31UC4 over the >weekend, I'm now experiencing a strange error involving date formats. > ..See below.. > >>>>>> LET sysstring = "/home/verteil/url_verteil_chk " || p_persnr || > " " || p_von || " " || p_bis; <<<<<<<<<<<<<<<< Try adding a USING clause to this line USING "YYYY-MM-DD" > 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"? What is the environment when online is started?? > >Regards, Richard -- David Williams

↪ replying to David Williams
Richard Spitz — — source: Informix-list mailing list archive (1991-1998)

David Williams wrote: > >>>>>> LET sysstring = "/home/verteil/url_verteil_chk " || p_persnr || > > " " || p_von || " " || p_bis; <<<<<<<<<<<<<<<< > > Try adding a USING clause to this line USING "YYYY-MM-DD" I tried this, but that gave me a syntax error (-201) on the "USING" statement. However, you pointed me in the right direction! I finally succeeded with TO_CHAR(p_von, '%Y-%m-%d') etc., and now everything is working again. > What is the environment when online is started?? This is what I checked first when the problem started, and DBDATE is not set anywhere. I couldn't bounce the engine due to heavy user activity (we are running a 24x7 shop), so it isn't entirely impossible that DBDATE was set in root's environment (it normally isn't) when I brought the engine up after the upgrade. Is there a way to check the environment for a running engine? Thanks for your help, and thanks to all the others who replied, mostly pointing towards the DBDATE variable in the engine's environment. Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. med Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | | Klinikum Grosshadern | FAX : +49-89-7095-6420 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+

↪ replying to Richard Spitz
David Williams — — source: Informix-list mailing list archive (1991-1998)

In article <3906A3FE.38B76E68@ana.med.uni-muenchen.de>, Richard Spitz <Richard.Spitz@ana.med.uni-muenchen.de> writes >David Williams wrote: > >> >>>>>> LET sysstring = "/home/verteil/url_verteil_chk " || p_persnr || >> > " " || p_von || " " || p_bis; <<<<<<<<<<<<<<<< >> >> Try adding a USING clause to this line USING "YYYY-MM-DD" > >I tried this, but that gave me a syntax error (-201) on the "USING" >statement. > >However, you pointed me in the right direction! I finally succeeded >with TO_CHAR(p_von, '%Y-%m-%d') etc., and now everything is working >again. > >> What is the environment when online is started?? > >This is what I checked first when the problem started, and DBDATE >is not set anywhere. I couldn't bounce the engine due to heavy user >activity (we are running a 24x7 shop), so it isn't entirely impossible >that DBDATE was set in root's environment (it normally isn't) when >I brought the engine up after the upgrade. Is there a way to check >the environment for a running engine? > Which operating system are you on? >Thanks for your help, and thanks to all the others who replied, mostly >pointing towards the DBDATE variable in the engine's environment. > Also I have found that it may be using DBDATE format which is active when you create the stored procedure!! Something else to check. >Regards, Richard -- David Williams

Related threads

Glossary