Skip to content

Parameter binding change seems to have moved from nchar to ntext #39

Open

Description

@robx

Not quite sure who/what's wrong here, but thought I'd log this one anyway since others might run into similar issues.

I tried updating hasura/graphql-engine to build against odbc-0.2.5 (currently it builds against 7c0cea45d0b779419eb16177407c4ee9e7ba4c6f which is between 0.2.3 and 0.2.4), and I see test failures with

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The data types nvarchar and ntext are incompatible in the equal to operator.

Reading through the recent changes to this package, I believe this is likely to be related to how commit 555182e changes text values to be passed via withBindParameter as a TextParam t, where it was previously encoded directly via renderValue.

Now it may well be that the tests are just wrong, but if so, it seems like the change in behaviour might be worth mentioning in the changelog?

Activity

  1. spencerjanssen commented on Sep 14, 2021

    @spencerjanssen
    Contributor

    I also encountered this #34 (comment). We're still using the approach described in the comment of casting to nvarchar(max), which is working well for us. At the time I think I looked into whether it was possible to avoid ntext for at least short strings, but it didn't work out for reasons that escape me at the moment.

  2. chrisdone commented on Sep 15, 2021

    @chrisdone
    Contributor

    Thanks chaps. I don't have time right now to look at this in detail, but I understand that explicit binding vs the more implicit text rendering causes comparison issues.

  3. chrisdone commented on Jan 11, 2022

    @chrisdone
    Contributor

    @spencerjanssen Do you have the commit for your patch? I'm curious what approach you took.

  4. spencerjanssen commented on Jan 11, 2022

    @spencerjanssen
    Contributor

    Sorry, the workaround isn't in any public repository.

    In our codebase we use a ToQuery class which for most types is the same as Database.ODBC.SQLServer.ToSql. For Text we expand to cast(? as nvarchar(max)).

    class ToQuery a where
        toQuery :: a -> Query
        default toQuery :: ToSql a => a -> Query
        toQuery = toSql
    
    -- odbc treats Text as ntext, which has all sorts of problems including equality comparison
    -- and use in AT TIME ZONE
    instance ToQuery Text where
        toQuery x = "cast(" <> toSql x <> " as nvarchar(max))"
  5. chrisdone commented on Jan 12, 2022

    @chrisdone
    Contributor

    Ah, thanks. That wouldn't cover the parametrized case that we're discussing (https://github.com/hasura/graphql-engine-mono/issues/2913#issuecomment-1010162201), but good to know. 👌

  6. chrisdone commented on Jan 12, 2022

    @chrisdone
    Contributor

    I could wrap up the ? with a cast to nvarchar(max), though. So that tells me something helpful.

  7. chrisdone commented on Jan 12, 2022

    @chrisdone
    Contributor

    Related attempt: #44

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions