Mysql – How to call a thesql stored procedure, with arguments, from command line

callcommand lineMySQLprocedure

How can I call a stored procedure from command line?

I have a procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `insertEvent`(IN `dateTimeIN` DATETIME)
    NO SQL
BEGIN
    SET @eventIDOut = NULL;

    IF  EXISTS(SELECT * FROM `events` WHERE `eventDate` = dateTimeIN) THEN
        SELECT `eID` INTO @eventIDOut FROM `events` WHERE `eventDate` = dateTimeIN LIMIT 1;
        ELSE
        INSERT INTO `events` (`eventDate`) VALUES(dateTimeIN);
        SET @eventIDOut = last_insert_id();
    END IF;

    SELECT CONCAT(@eventIDOut);
END

I tried this: mysql> CALL insertEvent(2012.01.01 12:12:12);

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '.01 12:12:12)' at line 1
And this: mysql> CALL insertEvent

-> 2012.01.01 12:12:12;

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2012.01.01 12:12:12' at line 2

Best Answer

With quotes around the date:

mysql> CALL insertEvent('2012.01.01 12:12:12');

Bash Space-Separated (e.g., `--option argument`)

cat >/tmp/demo-space-separated.sh <<'EOF'
#!/bin/bash

POSITIONAL=()
while [[ $# -gt 0 ]]; do
  key="$1"

  case $key in
    -e|--extension)
      EXTENSION="$2"
      shift # past argument
      shift # past value
      ;;
    -s|--searchpath)
      SEARCHPATH="$2"
      shift # past argument
      shift # past value
      ;;
    -l|--lib)
      LIBPATH="$2"
      shift # past argument
      shift # past value
      ;;
    --default)
      DEFAULT=YES
      shift # past argument
      ;;
    *)    # unknown option
      POSITIONAL+=("$1") # save it in an array for later
      shift # past argument
      ;;
  esac
done

set -- "${POSITIONAL[@]}" # restore positional parameters

echo "FILE EXTENSION  = ${EXTENSION}"
echo "SEARCH PATH     = ${SEARCHPATH}"
echo "LIBRARY PATH    = ${LIBPATH}"
echo "DEFAULT         = ${DEFAULT}"
echo "Number files in SEARCH PATH with EXTENSION:" $(ls -1 "${SEARCHPATH}"/*."${EXTENSION}" | wc -l)
if [[ -n $1 ]]; then
    echo "Last line of file specified as non-opt/last argument:"
    tail -1 "$1"
fi
EOF

chmod +x /tmp/demo-space-separated.sh

/tmp/demo-space-separated.sh -e conf -s /etc -l /usr/lib /etc/hosts

Output from copy-pasting the block above

FILE EXTENSION  = conf
SEARCH PATH     = /etc
LIBRARY PATH    = /usr/lib
DEFAULT         =
Number files in SEARCH PATH with EXTENSION: 14
Last line of file specified as non-opt/last argument:
#93.184.216.34    example.com

Usage

demo-space-separated.sh -e conf -s /etc -l /usr/lib /etc/hosts

Bash Equals-Separated (e.g., `--option=argument`)

cat >/tmp/demo-equals-separated.sh <<'EOF'
#!/bin/bash

for i in "$@"; do
  case $i in
    -e=*|--extension=*)
      EXTENSION="${i#*=}"
      shift # past argument=value
      ;;
    -s=*|--searchpath=*)
      SEARCHPATH="${i#*=}"
      shift # past argument=value
      ;;
    -l=*|--lib=*)
      LIBPATH="${i#*=}"
      shift # past argument=value
      ;;
    --default)
      DEFAULT=YES
      shift # past argument with no value
      ;;
    *)
      # unknown option
      ;;
  esac
done
echo "FILE EXTENSION  = ${EXTENSION}"
echo "SEARCH PATH     = ${SEARCHPATH}"
echo "LIBRARY PATH    = ${LIBPATH}"
echo "DEFAULT         = ${DEFAULT}"
echo "Number files in SEARCH PATH with EXTENSION:" $(ls -1 "${SEARCHPATH}"/*."${EXTENSION}" | wc -l)
if [[ -n $1 ]]; then
    echo "Last line of file specified as non-opt/last argument:"
    tail -1 $1
fi
EOF

chmod +x /tmp/demo-equals-separated.sh

/tmp/demo-equals-separated.sh -e=conf -s=/etc -l=/usr/lib /etc/hosts

Output from copy-pasting the block above

FILE EXTENSION  = conf
SEARCH PATH     = /etc
LIBRARY PATH    = /usr/lib
DEFAULT         =
Number files in SEARCH PATH with EXTENSION: 14
Last line of file specified as non-opt/last argument:
#93.184.216.34    example.com

Usage

demo-equals-separated.sh -e=conf -s=/etc -l=/usr/lib /etc/hosts

To better understand ${i#*=} search for "Substring Removal" in this guide. It is functionally equivalent to `sed 's/[^=]*=//' <<< "$i"` which calls a needless subprocess or `echo "$i" | sed 's/[^=]*=//'` which calls two needless subprocesses.

Using bash with getopt[s]

getopt(1) limitations (older, relatively-recent getopt versions):

can't handle arguments that are empty strings
can't handle arguments with embedded whitespace

More recent getopt versions don't have these limitations. For more information, see these docs.

POSIX getopts

Additionally, the POSIX shell and others offer getopts which doen't have these limitations. I've included a simplistic getopts example.

cat >/tmp/demo-getopts.sh <<'EOF'
#!/bin/sh

# A POSIX variable
OPTIND=1         # Reset in case getopts has been used previously in the shell.

# Initialize our own variables:
output_file=""
verbose=0

while getopts "h?vf:" opt; do
  case "$opt" in
    h|\?)
      show_help
      exit 0
      ;;
    v)  verbose=1
      ;;
    f)  output_file=$OPTARG
      ;;
  esac
done

shift $((OPTIND-1))

[ "${1:-}" = "--" ] && shift

echo "verbose=$verbose, output_file='$output_file', Leftovers: $@"
EOF

chmod +x /tmp/demo-getopts.sh

/tmp/demo-getopts.sh -vf /etc/hosts foo bar

Output from copy-pasting the block above

verbose=1, output_file='/etc/hosts', Leftovers: foo bar

Usage

demo-getopts.sh -vf /etc/hosts foo bar

The advantages of getopts are:

It's more portable, and will work in other shells like dash.
It can handle multiple single options like -vf filename in the typical Unix way, automatically.

The disadvantage of getopts is that it can only handle short options (-h, not --help) without additional code.

There is a getopts tutorial which explains what all of the syntax and variables mean. In bash, there is also help getopts, which might be informative.

Ruby – How to pass command line arguments to a rake task

You can specify formal arguments in rake by adding symbol arguments to the task call. For example:

require 'rake'

task :my_task, [:arg1, :arg2] do |t, args|
  puts "Args were: #{args} of class #{args.class}"
  puts "arg1 was: '#{args[:arg1]}' of class #{args[:arg1].class}"
  puts "arg2 was: '#{args[:arg2]}' of class #{args[:arg2].class}"
end

task :invoke_my_task do
  Rake.application.invoke_task("my_task[1, 2]")
end

# or if you prefer this syntax...
task :invoke_my_task_2 do
  Rake::Task[:my_task].invoke(3, 4)
end

# a task with prerequisites passes its 
# arguments to it prerequisites
task :with_prerequisite, [:arg1, :arg2] => :my_task #<- name of prerequisite task

# to specify default values, 
# we take advantage of args being a Rake::TaskArguments object
task :with_defaults, :arg1, :arg2 do |t, args|
  args.with_defaults(:arg1 => :default_1, :arg2 => :default_2)
  puts "Args with defaults were: #{args}"
end

Then, from the command line:

> rake my_task[1,false]
Args were: {:arg1=>"1", :arg2=>"false"} of class Rake::TaskArguments
arg1 was: '1' of class String
arg2 was: 'false' of class String

> rake "my_task[1, 2]"
Args were: {:arg1=>"1", :arg2=>"2"}

> rake invoke_my_task
Args were: {:arg1=>"1", :arg2=>"2"}

> rake invoke_my_task_2
Args were: {:arg1=>3, :arg2=>4}

> rake with_prerequisite[5,6]
Args were: {:arg1=>"5", :arg2=>"6"}

> rake with_defaults
Args with defaults were: {:arg1=>:default_1, :arg2=>:default_2}

> rake with_defaults['x','y']
Args with defaults were: {:arg1=>"x", :arg2=>"y"}

As demonstrated in the second example, if you want to use spaces, the quotes around the target name are necessary to keep the shell from splitting up the arguments at the space.

Looking at the code in rake.rb, it appears that rake does not parse task strings to extract arguments for prerequisites, so you can't do task :t1 => "dep[1,2]". The only way to specify different arguments for a prerequisite would be to invoke it explicitly within the dependent task action, as in :invoke_my_task and :invoke_my_task_2.

Note that some shells (like zsh) require you to escape the brackets: rake my_task\['arg1'\]

Best Answer

Related Solutions

Bash – How to parse command line arguments in Bash

Bash Space-Separated (e.g., --option argument)

Output from copy-pasting the block above

Usage

Bash Equals-Separated (e.g., --option=argument)

Output from copy-pasting the block above

Usage

Using bash with getopt[s]

POSIX getopts

Output from copy-pasting the block above

Usage

Ruby – How to pass command line arguments to a rake task

Related Topic

Bash Space-Separated (e.g., `--option argument`)

Bash Equals-Separated (e.g., `--option=argument`)