The stored prodedure doesn't return rows, it just inserts data into the
tables. Which was why i was using the run, because without the variables
that was the only way that the data would be inserted into the correct
tables.
When i tried:
DB.fetch("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).all
i got the error
C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/adapters/ado.rb:146:in
`method_missing': WIN32OLERuntimeError: (in OLE method `eof': )
(Sequel::DatabaseError)
OLE error code:800A0E78 in ADODB.Recordset
Operation is not allowed when the object is closed.
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:146:in
`block in fetch_rows'
from
C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/adapters/ado.rb:94: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/dataset/actions.rb:778:in
`execute'
from
C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/adapters/ado.rb:133:in
`fetch_rows'
from
C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/dataset/actions.rb:154:in
`each'
from
C:/Ruby192/lib/ruby/gems/1.9.1/gems/sequel-3.42.0/lib/sequel/dataset/actions.rb:55:in
`all'
from C:/RM_Filter_tests/rm_filter_test.rb:39:in `<top (required)>'
from -e:1:in `load'
from -e:1:in `<main>'
On Thu, Jan 10, 2013 at 12:41 AM, Jeremy Evans <[email protected]>wrote:
> 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.
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
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.
