As it happens, I have experience calling Informix SPs from shell scripts. Here's an example:
Given this SP:
-- This function returns a datetime year to second from a date and a time
-- string. No error checking.
-- the time string is of the type: 12:21:16
-- Author: Nails
CREATE PROCEDURE ret_datetime(date1 DATE, time1 INTERVAL HOUR TO SECOND)
RETURNING DATETIME YEAR TO SECOND;
-- RETURNING CHAR(15);
DEFINE xchar CHAR(30);
DEFINE dt_ret DATETIME YEAR TO SECOND;
create temp table b (
xdate1 date,
xtime1 interval hour to second
) with no log;
insert into b values (date1, time1);
LET dt_ret = (SELECT EXTEND(xdate1, YEAR TO SECOND) + time1 FROM b);
drop table b;
RETURN dt_ret;
END PROCEDURE;
-- End SP
Here's a shell script, that sends arguments to the SP and even captures output. You either have to invoke the dbaccess or isql utility. I like to parameterize as much as I can:
!/bin/ksh
# database name
DBNAME="testdb"; export DBNAME
# use dbaccess or isql
INTERFACE_CMD="dbaccess"; export INTERFACE_CMD
# send to standard output
DBCOMMAND="$INTERFACE_CMD -e $DBNAME"; export DBCOMMAND
# send errors and output to files and standard output
DBCOMMAND_OTF="eval $INTERFACE_CMD -e $DBNAME 2>> err.txt |tee /dev/tty >> output.txt";
export DBCOMMAND_OTF
engine_up=$(onstat - | grep "Up" | wc -l)
if [ $engine_up -eq 0 ]
then
echo "Informix Engine must be Up to execute SPs."
exit
fi
dt1="02/06/2006"
time1="10:12:16"
# get the informix datetime using SP.
intermed=$(dbaccess -e ificsdc <<MSG
EXECUTE PROCEDURE ret_datetime("$dt1", "$time1")
MSG)
# the last two fields of the expression returned from
# executing ret_datetime procedure are the date time
dt2=$(echo $intermed| awk ' { printf("%s %s", $(NF-1), $NF) } ')
echo "datetime is $dt2"
I'll answer your date questions in another post.