Databases
StackOverflow2013
Postsdbo
POLIMIT clause in MSSQL RoR ActiveRecord Query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLIMIT clause in MSSQL RoR ActiveRecord Query
    primarykey
    data
    text
    <p>We are having an issue with a Ruby on Rails 3.0.3 application. It seems that for some reason, ActiveRecord is using a query formatted for MySQL when we do a find() on an MSSQL model. The rails app is using MySQL for its models; however, also has reference models that are backed within an MSSQL database. As shown in the below message, we are using an ODBC connection. When I call Club.find(137) I get the following error:</p> <p><strong>ODBC::Error: 37000 (102) [unixODBC][FreeTDS][SQL Server]Incorrect syntax near 'LIMIT'.: SELECT [club].* FROM [club] WHERE ([club].[ClubID] = N'137') LIMIT 1</strong></p> <p><em>Note that Club.find(:all) works fine so connectivity is working, the problem lies in the generation of the SQL query format. OtherModel.find(1) also works (the model that is connected to the MySQL database).</em></p> <p>Is what I'm doing impossible? I'm assuming its something with the caching of queries... Does anyone know how to resolve the above? Thanks so much for all the help in advance.</p> <p>Here is the setup:</p> <p><strong>Gems:</strong> gem 'mysql2' gem 'rails-dbi' gem 'ruby-odbc' gem 'activerecord-sqlserver-adapter'</p> <p><strong>Models (examples):</strong></p> <pre><code>class Club &lt; External ... end class External &lt; ActiveRecord:Base establish_connection('mssql_' + Rails.env) ... end class OtherModel &lt; ActiveRecord:Base ... end </code></pre> <p><strong>database.yml Example:</strong></p> <pre><code>environment: adapter: mysql2 encoding: utf8 host: localhost pool: 5 username: password: database: my_app_development mssql_environment: adapter: sqlserver mode: odbc pool: 5 dsn: MYDSN username: password: </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USejlevin1
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COLIMIT is not available in MSSQL, thus making pagination quite difficult.
      singulars
      1. POLIMIT clause in MSSQL RoR ActiveRecord Query
      1. UScfEngineers
    2. CO@cfEngineers - LIMIT is not available in MSSQL, but the sqlserver activerecord gem should take care of the LIMIT -> TOP translation.
      singulars
      1. POLIMIT clause in MSSQL RoR ActiveRecord Query
      1. USBrandon
    3. COBrandon that is why I was confused--I thought it would make the translation appropriately. Additionally, I was running this in JRuby on my mac (different environment then the above) and would only see this type of error sporadically-which leads me to believe it is something with caching?
      singulars
      1. POLIMIT clause in MSSQL RoR ActiveRecord Query
      1. USejlevin1
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload