On Wednesday, January 9, 2013 6:50:09 PM UTC-8, Hillary wrote:
> I'm trying to execute a mssql store_procedure that inserts date into the
> database using DB.run. For some of the parameters i want to pass in values
> stored in variables. I'd done a similar thing with a DB.fetch, but i get an
> error when trying to use the same concept in DB.run
>
> The working DB.fetch method
> def get_rec_types(catalog_id)
> array = Array.new
> $DB.fetch("exec Get_Recommendation_Types @catalog_id = :catalog",
> :catalog => catalog_id) do |n|
> array << n[:name]
> end
> array
> end
>
> But when i run
>
> DB.run("exec Clickstream_Insert_Data @Application_ID =
> 'resonancerecords01',
> @Tracking_ID = :tracking_id,
> @Session_ID = :session_id,
> @Page_ID = :page_id,
> @Page_URL = '',
> @Referrer_URL = '',
> @Event = 'Product',
> @Event_Items = '<ROOT><ei i=''B000000993''
> /><ei i=''B00000099Y'' /><ei i=''B00005B36H'' /><ei i=''B000002GJS''
> /></ROOT>',
> @Customer_ID = '360741930054024',
> @Transaction_ID = NULL,
> @Timestamp = '2013-01-08 10:05:57.293'",
> :tracking_id => @tracking_id, :session_id => @session_id, :page_id =>
> @page_id)
>
> I get the error
>
> C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/adapters/ado.rb:93:in
>
> `method_missing': WIN32OLERuntimeError: (in OLE method `Execute': )
> (Sequel::DatabaseError)
> OLE error code:80040E14 in Microsoft OLE DB Provider for ODBC Drivers
> [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
> ':'.
> HRESULT error code:0x80020009
> Exception occurred.
> from
> C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/adapters/ado.rb:93:in
>
> `block (2 levels) in execute'
> from
> C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/database/logging.rb:33:in
>
> `log_yield'
> from
> C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/adapters/ado.rb:93:in
>
> `block in execute'
> from
> C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/database/connecting.rb:236:in
>
> `block in synchronize'
> from
> C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/connection_pool/threaded.rb:104:in
>
> `hold'
> from
> C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/database/connecting.rb:236:in
>
> `synchronize'
> from
> C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/adapters/ado.rb:91:in
>
> `execute'
> from
> C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/adapters/ado.rb:66:in
>
> `execute_ddl'
> from
> C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/database/query.rb:135:in
>
> `run'
> from C:/RM_Filter_tests/rm_filter_test.rb:41:in `get_rec_types'
> from C:/RM_Filter_tests/rm_filter_test.rb:47:in `<top (required)>'
> from -e:1:in `load'
> from -e:1:in `<main>'
>
>
> So how do i pass in the variables?
>
Database.run does not support variables, it's designed for executing DDL
statements or configuration statements, not for stored procedures. Based
on your stored procedure name, you may want to use DB.fetch(sql,
params).all instead of DB.run if the query is going to return rows.
The technical reason your code doesn't work is that parameter interpolation
is a dataset-level feature, and DB.run does not use a dataset internally.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/W1fJ4kP5ar8J.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
