CODEWITHSUNDEEP
sqlstored-proceduresinformix
CheeseConQueso
CheeseConQueso

Reputation: 6041

Can't create Informix stored procedure using ISQL command?

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.

Upvotes: 2

Views: 2134

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753695

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).

Upvotes: 2

Related Questions