Custom SQL cannot be other than a SELECT

[telenieko]

Hi there,

1st, awesome Sling. simple, fast, does what I need! Thx!!

Now, I have one scenario in which I dropped quite a large SQL query into an SQL file, that query (mssql) uses WITH.... SELECT. Sling won't work.

--- database.go:824 StreamRowsContext ---
mssql: Sintaxis incorrecta cerca de ')'.

I think it is due to this:

https://github.com/slingdata-io/sling-cli/blame/1b969524621d8ca5668d6655acc9396aa7d1a950/core/dbio/database/database.go#L1407-L1410

    limitSQL := g.R(
        conn.GetTemplateValue("core.limit"),
        "sql", table.SQL,
        "limit", "1",

Sling is wrapping the whole query with select top 1 * from ( but as my custom SQL starts with WITH, which cannot be inside another SELECT... you see the problem.

I see no way to override how the limitSQL thing is created, so... it appears customs SQL statements must be SELECT queries without any strange things like variable declarations or WITH statements?

[flarco]

Hi, thanks for the kind words.

Interesting, sling does this to get the column/types for the custom SQL. So it wraps the custom SQL into a subquery, and limits the result to 1 so get the column types/names. Other dbs such as Postgres/MySQL allow an embedded WITH, but I guess not SQL Server.

• Maybe you could create a VIEW in SQL Server and use that as the source stream?
• Another, would be to use the old subquery style without WITH: select from ( select from ( select.. ) ) (which I hate haha).
• The other way I'm thinking is harder, where sling has to parse any custom sql starting with with and add a TOP 1 to the last SELECT. A bit gnarly, but would work. I'll think a bit more about this.