2

I'm having trouble creating this stored procedure on IBM Informix Dynamic Server Version 10.00.FC9 (see Jonathan Leffler's answer to this post here) using the 'isql' command from Informix SQL.

I get an error on the ( char for each of his two examples near RETURNING CHAR(8)

ex. 1:

CREATE PROCEDURE ampm_time(tm SMALLINT) RETURNING CHAR(8);
    DEFINE hh SMALLINT;
    DEFINE mm SMALLINT;
    DEFINE am SMALLINT;
    DEFINE m3 CHAR(3);
    DEFINE a3 CHAR(3);
    LET hh = MOD(tm / 100 + 11, 12) + 1;
    LET mm = MOD(tm, 100) + 100;
    LET am = MOD(tm / 1200, 2);
    LET m3 = mm;
    IF am = 0
    THEN LET a3 = ' am';
    ELSE LET a3 = ' pm';
    END IF;
    RETURN (hh || ':' || m3[2,3] || a3);
END PROCEDURE;

ex. 2:

CREATE PROCEDURE ampm_time(tm SMALLINT) RETURNING CHAR(8);
    DEFINE i2 SMALLINT;
    DEFINE hh SMALLINT;
    DEFINE mm SMALLINT;
    DEFINE am SMALLINT;
    DEFINE m3 CHAR(3);
    DEFINE a3 CHAR(3);
    LET i2 = tm / 100;
    LET hh = MOD(i2 + 11, 12) + 1;
    LET mm = MOD(tm, 100) + 100;
    LET i2 = tm / 1200;
    LET am = MOD(i2, 2);
    LET m3 = mm;
    IF am = 0
    THEN LET a3 = ' am';
    ELSE LET a3 = ' pm';
    END IF;
    RETURN (hh || ':' || m3[2,3] || a3);
END PROCEDURE;

They were designed by Jonathan Leffler (Informix Magician extraordinaire) in an effort to resolve the question I asked in the linked post. The intended return is to convert an INT datatype holding military time into a 2:30 pm type format.

Community
  • 1
  • 1
CheeseConQueso
  • 5,831
  • 29
  • 93
  • 126
  • Given that you're using IDS 10.00, you may well be able to use the version of the SP without the i2 variable. Note that the version of ISQL might well be 7.32 (possibly 7.31 or 7.30, even); the current version is 7.50, and 7.32 is not badly out of date at all. – Jonathan Leffler Oct 20 '09 at 18:16
  • maybe thats why i thought informix was v7.3 all this time... i have been using this system for about a year now, and i don't care about versions so long as things just work – CheeseConQueso Oct 20 '09 at 18:19
  • It could be why you were confused. It is also why when someone (like me) asks for versions, supplying the version number information helps to get things into perspective. FWIW: upgrading to ISQL 7.50 would not fix the 'isql does not like stored procedures' problem. Also, remember that IDS 10.00 is now the oldest supported version of IDS. You're on one of the latest versions in that, but an upgrade of IDS should be in the plans for the next year or so. – Jonathan Leffler Oct 20 '09 at 18:30
  • i work at a community college that uses jenzabar CX and jenzabar usually handle these upgrades & hotfixes with or without the help of one of our other programmers. their system lays on top of everything and creates an interesting mix of layers upon layers of processing environments – CheeseConQueso Oct 20 '09 at 18:40

1 Answers1

2

This problem is DB-Access vs ISQL.

ISQL has a warped sense of humour and thinks that the syntax of SQL still matches what was current with Informix OnLine 4.10 (or, in those days, INFORMIX-OnLine 4.10). Specifically, it doesn't know that stored procedures are made up of multiple statements separated by semi-colons and mis-assumes that SQL statements end at the first semi-colon not in a string or comment.

Workarounds:

  • Use DB-Access instead of ISQL to create stored procedures.
  • Obtain SQLCMD from the IIUG Software Archive and use that instead.
  • Use 'mkproc' from the SQLCMD software to create stored procedures.

Of these, the easiest is to use DB-Access (aka dbaccess - found in $INFORMIXDIR/bin where the server software lives).

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278