Intersystems Cache commands when written manually in a file and executed is successful but not when built through a script

Question

It would be great if this is taken up by Intersystems Cache DB experts:

I created a script by manually editing a file from VI editor on RHEL linux and ran that on CACHE DB, and all commands ran successfully. The batch script was:

#! /bin/ksh
. /apps/ins/.profile
cache <<-!END
do \$system.Security.Login("_SYSTEM","SYS")
do \$system.SQL.Shell()
select count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator= '278/PLMN/000100'
select count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator= '208/PLMN/002000'
select count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator= '208/PLMN/000100'
select count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator= '206/PLMN/001000'
exit
h
!END

SUCCESSFUL OUTPUT:

Node: sur-eir2, Instance: CACHEHA

INS>

INS>
SQL Command Line Shell
----------------------------------------------------
Enter q to quit, ? for help.
INS>>
1. select count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator= '278/PLMN/000100'
Aggregate_1
0

1 Rows(s) Affected
---------------------------------------------------------------------------
INS>>
2. select count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator= '208/PLMN/002000'
Aggregate_1
0

1 Rows(s) Affected
---------------------------------------------------------------------------
INS>>
3. select count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator= '208/PLMN/000100'
Aggregate_1
0

1 Rows(s) Affected
---------------------------------------------------------------------------
INS>>
4. select count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator= '206/PLMN/001000'
Aggregate_1
0

1 Rows(s) Affected
---------------------------------------------------------------------------
INS>>

INS>

Now, while I'm trying to create such a script via another main script (echo commands) and run it, the same commands fail and show syntactical error. My main script is:

==============From Here=========================
#! /bin/ksh 
while read line
do
 echo "select count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator = '$line'"
done < plmn_list >> command_list ;

echo "#! /bin/ksh" >> get_count
echo ". /apps/ins/.profile" >> get_count
echo "cache <<-!END" >> get_count
echo "do \$system.SQL.Shell()" >> get_count
cat command_list >> get_count
echo "exit" >> get_count
echo "h" >> get_count
echo "!END" >> get_count
==================till here the get_count script is generated fine========
chmod u+rwx get_count
ksh ./get_count > query_result <-- query_result shows syntax errors
sed -n '/Aggregate_1/,+1p' query_result | grep -v "Aggregate_1" > count
paste plmn_list count > report
rm -f command_list get_count

The get_count content:

#! /bin/ksh
. /apps/ins/.profile
cache <<-!END
do $system.SQL.Shell()
select count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator = '278/PLMN/000100'
select count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator = '208/PLMN/002000'
select count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator = '208/PLMN/000100'
exit
h
!END 

So everything looks fine there. However the query_result shows:

Node: sur-eir2, Instance: CACHEHA

INS>

DO .SQL.Shell()
^
<SYNTAX>
INS>

SELECT count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator = '278/PLM
^
N/000100'
<SYNTAX>
INS>

SELECT count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator = '208/PLM
^
N/002000'
<SYNTAX>
INS>

SELECT count(*) from cdat_tb_ceir_blacklist_7 where cdat_c_originator = '208/PLM
^
N/000100'
<SYNTAX>
INS>

EXIT
^
<SYNTAX>
INS>

Each command from get_count when ran individually also works just fine. So why the syntactical error ?

Answer 1

I got the answer.

The command do $system.SQL.Shell() in the get_count file should actually be:

do \$system.SQL.Shell()

This is the command that takes you to the SQL shell in Intersystems Cache-DB from where you run normal SQL query. Since this was wrong, all my following SQL commands were failing as well.

So the changes in my main script would be:

The line:

echo "do \$system.SQL.Shell()" >> get_count

Changing to:

echo "do \\\$system.SQL.Shell()" >> get_count

Everything else remaining same.