Supporting skip/take in MS SQL server

[tpetricek]

I was trying to use the SQL service to expose data stored in Azure-hosted MS SQL database. This seems to work, though I'm having troubles getting skip and take to work. When using SQL Lite, the q.take and q.skip calls generate:

SELECT ... LIMIT 10 OFFSET 1`

I managed to connect to MS SQL by adding jdbc-mssql-azure and activerecord-jdbcmssql-adapter gems (and messing around with CLASSPATH a bit) and using:

ActiveRecord::Base.establish_connection(
  driver: 'com.microsoft.sqlserver.jdbc.SQLServerDriver',
  adapter: "sqlserver",
  host: "thegamma-sql-data.database.windows.net",
  database: "thegamma-sql-data",
  username: "my-user",
  password: "my-password"
)

Many things still work, but it now calling q.take and q.skip does not do anything - I guess this might be limitation of the MSSQL provider because decent paging support is relatively new in MSSQL, though it should be doable with just:

SELECT ... OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY

Is there a way to somehow patch what q.take and q.skip do?

[jazzido]

Never worked with SQL Server and ActiveRecord, but I'll try to find a solution! What SQL is it being generated now when you use take or skip?

[jazzido]

Additionally, if you can share privately the credentials and the setup instructions, I'd be happy to take a look.

[tpetricek]

I pushed my changes to an azuresql branch in the-gamma organization. I'll send you user/password for the Azure SQL database via Twitter DM.

The diff shows the changes and I also added NOTES.md documenting what I was doing:

• I was running this locally inside Docker, so I mapped my local directory into Docker so that I can edit code outside Docker but run it inside (I think getting JRuby to work decently on my Windows machine would be harder than just using it inside Docker, but it might be strange workflow...).

• I could not quite figure out how to tell JRuby to find the sqljdbc4.jar driver, as a workaround, I tried copying the file to the root of the project, which did not help, but extracting it did help. (There must be a better way, but setting CLASSPATH did not seem to work for me...).

EDIT: There is one table in the database, so to add it to a visualization, I used:

g.providers.pivot("http://127.0.0.1:8087/land-registry-monthly-price-paid-data")

Also, the SQL command that gets printed is just SELECT * FROM ... with no LIMIT or anything else. To see if it works otherwise, I used string replace before running q.to_sql replacing "SELECT" => "SELECT TOP 10". That does not work in general, but it was enough to get a preview :-)

Thanks very much for helping with this!