What is the correct way to execute a stored procedure where you want to
pass in a date parameter for MySQL?
I am using the DB.run, and per MySQL to execute a stored procedure the
command is "CALL".
*With MySQL I tried the following each with different errors:*
For all: @date_time = DateTime.now
1. DB.run('CALL storedProc(?)', @datetime_variable)
2. DB.run('CALL storedProc(?)', @datetime_variable)
3. DB.run('CALL storedProc(:start_date)', start_date: @datetime_variable)
4. DB.run('CALL storedProc(:start_date)', start_date:
@datetime_variable.to_s)
5. DB.run('CALL storedProc(:start_date)', {start_date:
@datetime_variable})
6. DB.run('CALL storedProc(:start_date)', {start_date:
@datetime_variable.to_s})
7. DB.run("CALL storedProc(#{@datetime_variable})")
8. DB.run("CALL storedProc(#{@datetime_variable.to_s})")
*Stored Proc beginning:*
CREATE PROCEDURE `storedProc`(
IN v_called_date TIMESTAMP
)
storedProc:BEGIN
...
...
END storedProc
*1. Error for 1:*
ERROR: Unknown - undefined method `[]' for #<DateTime:0x007fe55d285970>
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/shared/mysql_prepared_statements.rb:31:in
`execute': undefined method `[]' for #<DateTime:0x007fe55d285970>
(NoMethodError)
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/mysql.rb:157:in
`execute_dui'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/database/query.rb:45:in
`execute_ddl'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/database/query.rb:78:in
`run'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/etl-0.1.0/lib/etl/load_accounts.rb:191:in
`run'
from ./load_accounts:61:in `<main>'
*2. Error for 2:*
ERROR: Unknown - no implicit conversion of Symbol into Integer
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/shared/mysql_prepared_statements.rb:31:in
`[]': no implicit conversion of Symbol into Integer (TypeError)
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/shared/mysql_prepared_statements.rb:31:in`execute'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/mysql.rb:157:in
`execute_dui'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/database/query.rb:45:in
`execute_ddl'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/database/query.rb:78:in
`run'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/etl-0.1.0/lib/etl/load_accounts.rb:191:in
`run'
from ./load_accounts:61:in `<main>'
*3. Error for 3:*
E, [2016-03-17T22:20:57.105512 #14461] ERROR -- : Mysql::Error: 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 ':start_date)' at line 1:
CALL processStagedTransactions(:start_date)
Sequel Error: Mysql::Error: 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 ':start_date)' at line 1
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/mysql.rb:177:in
`query': Mysql::Error: 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 ':start_date)' at line
1 (Sequel::DatabaseError)
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/mysql.rb:177:in
`block in _execute'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/database/logging.rb:39:in
`log_yield'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/mysql.rb:177:in
`_execute'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/shared/mysql_prepared_statements.rb:36:in`block
in execute'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/database/connecting.rb:251:in
`block in synchronize'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/connection_pool/threaded.rb:105:in
`hold'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/database/connecting.rb:251:in
`synchronize'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/shared/mysql_prepared_statements.rb:36:in`execute'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/mysql.rb:157:in
`execute_dui'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/database/query.rb:45:in
`execute_ddl'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/database/query.rb:78:in
`run'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/etl-0.1.0/lib/etl/load_accounts.rb:191:in
`run'
from ./load_accounts:61:in `<main>'
*4. Error for 4:*
Same as #3
*5. Error for 5:*
Same as #3
*6. Error for 6:*
Same as #3
*7. Error for 7:*
E, [2016-03-17T22:12:02.953578 #14103] ERROR -- : Mysql::Error: 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 ':12:02+00:00)' at line 1:
CALL storedProc(2016-03-17T22:12:02+00:00)
Sequel Error: Mysql::Error: 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 ':12:02+00:00)' at line 1
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/mysql.rb:177:in
`query': Mysql::Error: 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 ':12:02+00:00)' at line 1 (Sequel::DatabaseError)
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/mysql.rb:177:in
`block in _execute'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/database/logging.rb:39:in
`log_yield'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/mysql.rb:177:in
`_execute'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/shared/mysql_prepared_statements.rb:36:in`block
in execute'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/database/connecting.rb:251:in
`block in synchronize'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/connection_pool/threaded.rb:105:in
`hold'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/database/connecting.rb:251:in
`synchronize'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/shared/mysql_prepared_statements.rb:36:in`execute'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/adapters/mysql.rb:157:in
`execute_dui'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/database/query.rb:45:in
`execute_ddl'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/sequel-4.32.0/lib/sequel/database/query.rb:78:in
`run'
from
/opt/rbenv/versions/2.2.2/lib/ruby/gems/2.2.0/gems/etl-0.1.0/lib/etl/load_accounts.rb:192:in
`run'
from ./load_accounts:61:in `<main>'
*8. Error for 8:*
This is the same as the error for 7
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
