无法使用ISQL命令创建Informix存储过程? [英] Can't create Informix stored procedure using ISQL command?

问题描述

我在IBM Informix Dynamic Server版本10.00.FC9上创建此存储过程时遇到麻烦(请参阅Jonathan Leffler对本文的回答此处).

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.

对于RETURNING CHAR(8)

例如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;

例如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;

它们是由Informix Magician非凡的Jonathan Leffler设计的，旨在解决我在链接文章中提出的问题.预期的回报是将保存军事时间的INT数据类型转换为2:30 pm的类型格式.

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.

推荐答案

此问题是DB-Access与ISQL.

This problem is DB-Access vs ISQL.

ISQL具有一种扭曲的幽默感，并认为SQL的语法仍然与Informix OnLine 4.10(或当时的INFORMIX-OnLine 4.10)保持一致.具体来说，它不知道存储过程是由用分号分隔的多个语句组成的，并且错误地假定SQL语句在第一个分号结尾而不是在字符串或注释中.

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.

解决方法:

• 使用DB-Access代替ISQL创建存储过程.
• 从 IIUG软件档案库中获取SQLCMD，并使用它.
• 使用SQLCMD软件中的'mkproc'创建存储过程.

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

其中最简单的方法是使用DB-Access(又名dbaccess-在服务器软件所处的$ INFORMIXDIR/bin中找到).

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

这篇关于无法使用ISQL命令创建Informix存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！