cancel
Showing results for 
Search instead for 
Did you mean: 

Could anyone write an example of then -f option of hdbsql?

Former Member
0 Kudos

Hi

I need to execute batch process in hdbsql and I need a log file to check what was wrong

I've read about -f option: returns all SQL statements that are sent to the database instance

That's exactly what I want, but I don't know how to use this option.

Could you please help me?

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

Not quite sure that this feature will help you a lot, but hey, you asked for examples, so take these:

No option:

hdbsql -U s12dev "SELECT count(*) from fact"
COUNT(*)
10000000
1 row selected (overall time 574.115 msec; server time 268 usec)

-f option (printout the SQL commands😞

hdbsql -U s12dev -f "SELECT count(*) from fact"
SELECT count(*) from fact

COUNT(*)
10000000
1 row selected (overall time 1301.303 msec; server time 139.424 msec)

-fn option (printout the SQL commands with additional line numbering😞

hdbsql -U s12dev -fn "SELECT count(*) from fact"
1) SELECT count(*) from fact

COUNT(*)
10000000

1 row selected (overall time 606.342 msec; server time 178 usec)

-x option (run quietly (no messages, only query output)):

hdbsql -U s12dev -fn -x "SELECT count(*) from fact"
COUNT(*)
10000000

Former Member
0 Kudos

Thanks Lars, you're rigtht. It doesn't help me.

I'm working in a linux enviroment, I'm executing this code:

rm SQLFILE_TMP.sh

echo "hdbsql <<!EOF" > SQLFILE_TMP.sh              #enter into hdbsql 
echo "\I ${PASSDIR}/${HNUSER}" >> SQLFILE_TMP.sh   #connecting to hana 
echo "\mu" >> SQLFILE_TMP.sh                       #sql multiline 
cat ${SQLFILE}  >> SQLFILE_TMP.sh                  #I pass del sql file with linux enviroment variables 
echo "!EOF" >> SQLFILE_TMP.sh                      #end. Now SQLFILE_TMP.sh has all the sql + connection 
ksh SQLFILE_TMP.sh   > logfile 2>&1 

The logfile has data of execution, errors of execution but no sql. If we have a lot of querys is very dificult to know wich one was wrong. Do you understand me? can you help me?

lbreddemann
Active Contributor
0 Kudos

Alright, I don't care how you build up the script that you eventually call.

Show the final script and I look into why it fails.

Some general points, though:

  • passing in username/password as clear text (it will be in the final script) is a serious security bug. Do, what I've shown above and use hdbuserstore instead.
  • there is no need to redirect stdout. hdbsql provides the -O parameter to define an output file.
  • not sure why you want to specify the multi-line mode while in interactive mode. simply specifying the command line option -m would suffice.
  • same goes for the input file: use the -I parameter instead
  • the -fn options is only available as of HANA 2 SP 03. If your hdbsql client is on an older version (try hdbsql -v to display the version), then these options don't exist.

Anyhow, even on Linux the following produces an output file that includes the SQL statements:

hdbsql -U ddude -f -o /tmp/out.txt "SELECT count(*) from fact"

cat /tmp/out.txt
SELECT count(*) from fact

COUNT(*)
10000000

In case of an error, this is also recorded:

hdbsql -U ddude  -f -o /tmp/out.txt "SELECT count(*) from factX"
* 259: invalid table name:  Could not find table/view FACTX in schema DEVDUDE: line 1 col 22 (at pos 21) SQLSTATE: HY000

cat /tmp/out.txt
SELECT count(*) from factX

* 259: invalid table name:  Could not find table/view FACTX in schema DEVDUDE: line 1 col 22 (at pos 21) SQLSTATE: HY000