thr3ads.net - Rails - Rails and MSSQLserver: Anyone had success with Microsoft Sqlserver ? [Aug 2005]

If this information is useful, please help other people find it:
Share via:

Al Dev

2005-Aug-19 14:40 UTC

Rails and MSSQLserver: Anyone had success with Microsoft Sqlserver ?

Does anyone had successful application using Rails and 
Microsoft SQLserver? I am having trouble using MS SQLserver 
with rails. I tried using ODBC and ADO but both fail. I 
am able to do listing but when I click on Edit and save,
 it fails.

But the same application code works just fine with MySQL 
(I created identical tables in MySQL and modified 
database.yml to point to MySQL).

With MS SQLserver when I click on Edit and save I get this error:

37000 (156) [Microsoft][ODBC SQL Server Driver][SQL Server]
Incorrect syntax near the keyword ''WHERE''.: UPDATE 
citrix.sdb_metrics SET  WHERE id_rails = 3

/app/controllers/sdb_metrics_controller.rb:37:in `update''
script/server:49

Request Parameters: {"id_rails"=>"3",
"commit"=>"Edit",
"sdb_metrics"=>{"MAXMETRICVALUE"=>"7366.9482421875",
"id_rails"=>"3",
"METRICDATACOUNT"=>"240",
"STDDEVMETRICVALUE"=>"975.008666992188",
"METRICSAMPLEPERIOD"=>"3600",
"FK_SERVERID"=>"61",
"MINMETRICVALUE"=>"76.5201110839844",
"SERVERUTCBIAS"=>"360",
"FK_SERVERINFID"=>"3",
"FK_METRICID"=>"1",
"MEANMETRICVALUE"=>"355.020080566406", 
"METRICUPDATETIME"=>"2005-01-03 04:05:05.0"}}

The sdb_metrics_controller.rb has this - failing at line 37
35  def update
36    @sdb_metrics = SdbMetricsModel.find(params[:id_rails])
37    if @sdb_metrics.update_attributes(params[:sdb_metrics])
38      flash[:notice] = ''SdbMetrics was successfully
updated.''
39      redirect_to :action => ''show'', :id_rails =>
@sdb_metrics
40    else
41      render :action => ''edit''
42    end
43  end

The Show-Framework-trace has this:
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
active_record/connection_adapters/abstract_adapter.rb:462:in `log''
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
active_record/connection_adapters/sqlserver_adapter.rb:242:in `execute''
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
active_record/connection_adapters/sqlserver_adapter.rb:252:in `update''
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
active_record/base.rb:1145:in `update_without_lock''
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
active_record/locking.rb:45:in `update_without_callbacks''
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
active_record/callbacks.rb:274:in `update_without_timestamps''
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
active_record/timestamp.rb:39:in `update''
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
active_record/base.rb:1139:in `create_or_update_without_callbacks''
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
active_record/callbacks.rb:249:in `create_or_update''
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
active_record/base.rb:965:in `save_without_validation''

Ken Barker

2005-Aug-20 17:58 UTC

head link

Re: Rails and MSSQLserver: Anyone had success with Microsoft Sqlserver ?

I have successfully implemented the Depot application from the Agile Web 
Development with Rails with SQLServer using the sqlserver adapter which 
requires an installation of the ADO module of DBI (iirc).

Your naming of the id column may be an issue. My suggestion is to try to get 
a simple app working first to isolate the db connection issues you are 
having.

HTH,

Ken

On 8/19/05, Al Dev <al.dev-GGtUyPwMHHo@public.gmane.org>
wrote:> 
> Does anyone had successful application using Rails and
> Microsoft SQLserver? I am having trouble using MS SQLserver
> with rails. I tried using ODBC and ADO but both fail. I
> am able to do listing but when I click on Edit and save,
> it fails.
> 
> But the same application code works just fine with MySQL
> (I created identical tables in MySQL and modified
> database.yml to point to MySQL).
> 
> With MS SQLserver when I click on Edit and save I get this error:
> 
> 37000 (156) [Microsoft][ODBC SQL Server Driver][SQL Server]
> Incorrect syntax near the keyword ''WHERE''.: UPDATE
> citrix.sdb_metrics SET WHERE id_rails = 3
> 
> /app/controllers/sdb_metrics_controller.rb:37:in `update''
> script/server:49
> 
> Request Parameters: {"id_rails"=>"3",
"commit"=>"Edit",
>
"sdb_metrics"=>{"MAXMETRICVALUE"=>"7366.9482421875",
"id_rails"=>"3",
> "METRICDATACOUNT"=>"240",
"STDDEVMETRICVALUE"=>"975.008666992188",
> "METRICSAMPLEPERIOD"=>"3600",
"FK_SERVERID"=>"61",
> "MINMETRICVALUE"=>"76.5201110839844",
"SERVERUTCBIAS"=>"360",
> "FK_SERVERINFID"=>"3",
"FK_METRICID"=>"1",
> "MEANMETRICVALUE"=>"355.020080566406",
> "METRICUPDATETIME"=>"2005-01-03 04:05:05.0"}}
> 
> The sdb_metrics_controller.rb has this - failing at line 37
> 35 def update
> 36 @sdb_metrics = SdbMetricsModel.find(params[:id_rails])
> 37 if @sdb_metrics.update_attributes(params[:sdb_metrics])
> 38 flash[:notice] = ''SdbMetrics was successfully
updated.''
> 39 redirect_to :action => ''show'', :id_rails =>
@sdb_metrics
> 40 else
> 41 render :action => ''edit''
> 42 end
> 43 end
> 
> The Show-Framework-trace has this:
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/connection_adapters/abstract_adapter.rb:462:in `log''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/connection_adapters/sqlserver_adapter.rb:242:in
`execute''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/connection_adapters/sqlserver_adapter.rb:252:in
`update''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/base.rb:1145:in `update_without_lock''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/locking.rb:45:in `update_without_callbacks''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/callbacks.rb:274:in `update_without_timestamps''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/timestamp.rb:39:in `update''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/base.rb:1139:in `create_or_update_without_callbacks''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/callbacks.rb:249:in `create_or_update''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/base.rb:965:in `save_without_validation''
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Corey Lawson

2005-Aug-20 21:12 UTC

head link

Re: Rails and MSSQLserver: Anyone had success with Microsoft Sqlserver ?

Yes, but I''m doing all my development on Windows. Obviously, MSSQL has
to run on Windows (MSSQL2000 will run on Win2K Pro...). But I just
don''t want to deal with ODBC on non-Windows, but the new adapters
support that as well.

Can you link to your MSSQL database in Access? If not, then reinstall
your MDAC components and check your MSSQL install and database.

You have to add users in two places in MSSQL security: One at the
server level (the Security folder in Sqlserver Enterprise Mgr is where
you add SQL Server users, set up their pwd and default database, and
then add that user to the database and probably give it db_datareader
and db_datawriter roles there too...).
>From looking at your initial error, yes, the SQL passed in won''t
work.Here''s what MSSQL is trying to run:

UPDATE citrix.sdb_metrics 
SET  
WHERE id_rails = 3

It doesn''t know what field is being updated, nor what value to update
it to.

Here''s what my 1.12/1.13.1 scaffold-generated ProductController.rb
update method looks like:

 def update
    @product = Product.find(@params[:id])
    if @product.update_attributes(@params[:product])
      flash[''notice''] = ''Product was successfully
updated.''
      redirect_to :action => ''show'', :id => @product
    else
      render_action ''edit''
    end
  end

I''m guessing from yours that either :id_rails or :sdb_metrics getting
passed into @params[] isn''t really the right value to use, most
likely, :id_rails. try just using :id.

On 8/19/05, Al Dev <al.dev-GGtUyPwMHHo@public.gmane.org>
wrote:> Does anyone had successful application using Rails and
> Microsoft SQLserver? I am having trouble using MS SQLserver
> with rails. I tried using ODBC and ADO but both fail. I
> am able to do listing but when I click on Edit and save,
>  it fails.
> 
> But the same application code works just fine with MySQL
> (I created identical tables in MySQL and modified
> database.yml to point to MySQL).
> 
> With MS SQLserver when I click on Edit and save I get this error:
> 
> 37000 (156) [Microsoft][ODBC SQL Server Driver][SQL Server]
> Incorrect syntax near the keyword ''WHERE''.: UPDATE
> citrix.sdb_metrics SET  WHERE id_rails = 3
> 
> /app/controllers/sdb_metrics_controller.rb:37:in `update''
> script/server:49
> 
> Request Parameters: {"id_rails"=>"3",
"commit"=>"Edit",
>
"sdb_metrics"=>{"MAXMETRICVALUE"=>"7366.9482421875",
"id_rails"=>"3",
> "METRICDATACOUNT"=>"240",
"STDDEVMETRICVALUE"=>"975.008666992188",
> "METRICSAMPLEPERIOD"=>"3600",
"FK_SERVERID"=>"61",
> "MINMETRICVALUE"=>"76.5201110839844",
"SERVERUTCBIAS"=>"360",
> "FK_SERVERINFID"=>"3",
"FK_METRICID"=>"1",
> "MEANMETRICVALUE"=>"355.020080566406",
> "METRICUPDATETIME"=>"2005-01-03 04:05:05.0"}}
> 
> The sdb_metrics_controller.rb has this - failing at line 37
> 35  def update
> 36    @sdb_metrics = SdbMetricsModel.find(params[:id_rails])
> 37    if @sdb_metrics.update_attributes(params[:sdb_metrics])
> 38      flash[:notice] = ''SdbMetrics was successfully
updated.''
> 39      redirect_to :action => ''show'', :id_rails =>
@sdb_metrics
> 40    else
> 41      render :action => ''edit''
> 42    end
> 43  end
> 
> The Show-Framework-trace has this:
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/connection_adapters/abstract_adapter.rb:462:in `log''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/connection_adapters/sqlserver_adapter.rb:242:in
`execute''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/connection_adapters/sqlserver_adapter.rb:252:in
`update''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/base.rb:1145:in `update_without_lock''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/locking.rb:45:in `update_without_callbacks''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/callbacks.rb:274:in `update_without_timestamps''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/timestamp.rb:39:in `update''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/base.rb:1139:in `create_or_update_without_callbacks''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/callbacks.rb:249:in `create_or_update''
> c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.11.1/lib/
> active_record/base.rb:965:in `save_without_validation''
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Rails - Aug 2005 - Rails and MSSQLserver: Anyone had success with Microsoft Sqlserver ?

Rails and MSSQLserver: Anyone had success with Microsoft Sqlserver ?

Re: Rails and MSSQLserver: Anyone had success with Microsoft Sqlserver ?

Re: Rails and MSSQLserver: Anyone had success with Microsoft Sqlserver ?