using hdbsql in shell script and query database

[qwertie]

Hello,

i want to use hdbsql in a shell script, to run a query for checking database backups. Unfortunately i am only able to login with hdbuserstorekey in my script after that the select statement does not work (select "command not found")

SAPSTRING=$(find /usr/sap/ccms -maxdepth 2 -type d -name sapccmsr | head -n 1 | cut -c 15-17)
HDBSTRING=$(/usr/bin/cat /usr/sap/"$SAPSTRING"/SYS/profile/DEFAULT.PFL | grep dbs/hdb/dbname | awk {' print$3'})
echo "HDB System ID ----------> " $HDBSTRING
declare -l HDBSIDADM
HDBSIDADM=$HDBSTRING'adm'

HDBBACKUPTEST=$(sudo su - "$HDBSIDADM" -c 'hdbsql -c ";" "\c -U HDBUSERSTOREKEY ";" select * from "SYS"."M_BACKUP_CATALOG" " ' )

this results in

-bash: select * from SYS.M_BACKUP_CATALOG : command not found

seems like that after the successful connect with hdbuserstorekey, it closes the connection and so the select command is not known.

any ideas?

thanks in advance

[Dan_Cummins]

Hello,

[Dan_Cummins · Accepted solution]

Hello,
I was able to reproduce the issue on a local test system.
I got around it by simplifying the hdbsql command as follows:


HDBBACKUPTEST=$(sudo su - "$HDBSIDADM" -c 'hdbsql -U HDBUSERSTOREKEY " select * from "SYS"."M_BACKUP_CATALOG" " ' )

In the example above I took out the ";" which I assumed was causing the command to get broken.  I see that the original command was trying to use \c and ; for multiline mode, but I'm not sure that you need that.

If you need to embed a more complex hdbsql command, I'd suggest embedding the command into its own environment variable using a heredoc, similar to this:

HDBSQL_CMD=$(cat <<'EOSQL'
hdbsql -U HDBUSERSTOREKEY "select \* from \"SYS\".\"M_BACKUP_CATALOG\""
EOSQL
)

That may help you avoid issues with the quoting later on.

I hope this helps,
Kind regards,
Dan

[qwertie · Accepted solution]

Hello Dan,

thank you very much for clarification. It did the trick and script is working now 🙂

Best regards

qwertie

[CharlesFeng]

Hi @qwertie ,

In the past, I encountered similar errors. I resolved them by placing the hdbsql commands in a file and then calling it from the shell script.
I suggest you try this approach.

Best regards,
Charles