How to get parameter value of sql from shell script

Question

I use a shell script to run sqlite3 command, the code is as below:

idd=0;
/usr/bin/sqlite3 ~/Library/Application\ Support/NotificationCenter/*.db <<SQL_END
select app_id from app_info where bundleid='com.myapp.main';
select last_known_path from app_loc where app_id=29;
SQL_END

i want to get the last_konwn_path of the sql, and i want to transfer a paramter instead of using '29', any one can help me with this, thanks.

Answer 1

String interpolation, the mechanism replacing variables in doubly quoted strings, happens in here-documents. You can thus wrap your call in a function as follows:

# notificationdb_last_known_path APP_ID
notificationdb_last_known_path()
{
  local APP_ID
  APP_ID="$1"
  /usr/bin/sqlite3 ~/Library/Application\ Support/NotificationCenter/*.db <<SQL_END
  select app_id from app_info where bundleid='com.myapp.main';
  select last_known_path from app_loc where app_id=${APP_ID};
  SQL_END
}

You can then call this function like this to store answer in a variable:

my_app_id=$(notificationdb_last_known_path 29)

Factorize the sqlite invocation If you have several similar database accesses, it is worthy to factorize the sqlite invocation as follows:

# notificationdb_session()
{
  /usr/bin/sqlite3 ~/Library/Application\ Support/NotificationCenter/*.db
}

# notificationdb_last_known_path_query APP_ID
notification_last_known_path_query()
{
  local APP_ID
  APP_ID="$1"
  cat <<SQL_END
  select app_id from app_info where bundleid='com.myapp.main';
  select last_known_path from app_loc where app_id=${APP_ID};
  SQL_END
}

# notificationdb_last_known_path_query APP_ID
notificationdb_last_known_path()
{
  notificationdb_last_known_path_query "$1" | notificationdb_session
}

Do not store variables In shell programming it is much easier to pass structured data around as data flows between processes as to pass it through variables. Programs sort, cut, paste, join and awk are especially useful when working on structured data streams.

Disable text interpolation in here-documents If you need to disable text interpolation in a here-document, you can achieve this by writing the here-document delimiter between single-quotes as in

cat <<'SQL_END'
SQL_STATEMENT
SQL_END