[MSSQL] '@head ' gets parsed to LIMIT in query instead of TOP

[ArchHem]

This causes ODBC errors when we try to collect, eg, say we have:

using ODBC
using Chain
import TidierDB as DB

...
conn_str = ...
db = DB.connect(DB.mssql(), conn_str)

local_quer = @chain DB.db_table(db, "NewTable") begin
    DB.@select(Id, height)
    DB.@head(2)
    DB.@show_query
end

"""
SELECT Id, height
        FROM NewTable 
        LIMIT 2
"""

Eg. when we try to collect:

@chain DB.db_table(db, "NewTable") begin
    DB.@select(Id, height)
    DB.@head(2)
    DB.@collect
end

ERROR: 42000: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near '2'.
Stacktrace:
 [1] error(s::String)
   @ Base ./error.jl:44
 [2] execdirect(stmt::ODBC.API.Handle, sql::String)
   @ ODBC.API ~/.julia/packages/ODBC/uhkdr/src/API.jl:123
 [3] execute(conn::ODBC.Connection, sql::String, params::Tuple{}; debug::Bool, kw::@Kwargs{})
   @ ODBC ~/.julia/packages/ODBC/uhkdr/src/dbinterface.jl:203
 [4] execute
   @ ~/.julia/packages/ODBC/uhkdr/src/dbinterface.jl:195 [inlined]
 [5] execute(conn::ODBC.Connection, sql::String)
   @ ODBC ~/.julia/packages/ODBC/uhkdr/src/dbinterface.jl:195
 [6] final_collect(sqlquery::TidierDB.SQLQuery, ::Type{TidierDB.mssql})
   @ ODBCExt ~/.julia/packages/TidierDB/nDkSP/ext/ODBCExt.jl:38
 [7] top-level scope

Seems to be just because of MySQL <-> MSSQL syntax clash.

Relevant Pkg.status() (Note that my TidierData is pinned to current main commit as the mssql fix is not released yet)

[be6f12e9] ODBC v1.2.1
[86993f9b] TidierDB v0.8.11 `https://github.com/TidierOrg/TidierDB.jl#1e17b9e`

[drizk1]

What would the correct SQL look like ?

[ArchHem]

SELECT TOP 2 Id, height
FROM NewTable

I think this is just purely syntax difference, eg. as per this SO post they should be equivalent.

https://stackoverflow.com/questions/5668540/difference-between-top-and-limit-keyword-in-sql

[drizk1]

Ok. Should be fixable. I'll update soon.

[drizk1]

try the mslimitfix branch when you have a moment please ( retry if it failed the first time. first version of branch didnt have main merged but now should be good)

[ArchHem]

Seems to work as of now, collect works and query seems OK.

@chain DB.db_table(db, "NewTable") begin
    DB.@select(Id, height)
    DB.@head(2)
    DB.@show_query
end
"""
SELECT TOP (2) Id, height
        FROM NewTable
"""

Relevant Pkg.status() (just got the branch directly, not pinned to commit):

[8be319e6] Chain v0.6.0
[be6f12e9] ODBC v1.2.1
[86993f9b] TidierDB v0.8.11 `https://github.com/TidierOrg/TidierDB.jl#mslimitfix`

Thank you for the fix^^^!

[drizk1]

Awesome. I'll release this week