Error in dynamicCommand

[rkosafo]

Using the examples on the dynamic sql page

This works

type ExampleQueryResult =
    {   Id : int
        Name : string
        Email: string
    }

let exampleCommand (id : int) (nameSearch : string) =
    dynamicCommand<ExampleQueryResult>
        [|  sql "SELECT Id, Name, Email FROM USERS"
            sql " WHERE Id = "
            arg id
            sql " OR Name LIKE "
            arg ("%" + nameSearch + "%")
        |]

This fails

type MyMostlyStaticQuery = SQL<"""
    SELECT Id, Name, Email FROM USERS
    WHERE unsafe_inject_raw(@dynSql)
""">

let exampleCommand2 (id : int) (nameSearch : string) =
    let exampleSql =
        [|  sql "Id = "
            arg id
            sql " OR Name LIKE "
            arg ("%" + nameSearch + "%")
        |]
    MyMostlyStaticQuery.Command(dynSql = exampleSql)

Error is Incorrect syntax near '<'.

Calling the above 2 examples with args 1 and "Mojo", it appears the fragments for exampleCommand2 is invalid as below

Example 1 Fragments

[|CommandText "SELECT Id, Name, Email FROM USERS"; CommandText " WHERE Id = ";
  InlineParameter (Int32,1); CommandText " OR Name LIKE ";
  InlineParameter (String,"%Mojo%")|]

Example 2 Fragments

 [|CommandText "SELECT"; LineBreak; Indent; CommandText "[USERS].[Id]"; LineBreak; 
   CommandText ", [USERS].[Name]"; LineBreak; CommandText ", [USERS].[Email]"; 
   Outdent; LineBreak; CommandText "FROM "; Indent; CommandText "[USERS]"; 
   Outdent; LineBreak; CommandText "WHERE "; Indent; CommandText "(("; 
   Parameter 0; CommandText ")<>0)"; Outdent; CommandText ";"; LineBreak|]

Is there a way to log or capture the final sql statement that is executed? Is this error from the usage or a bug?

[rspeele]

This is happening because of a quirk in the translation to T-SQL.

T-SQL does not have a real boolean type. That is, there is no type for a variable @x that makes SELECT ... WHERE @x a valid query. Since RZSQL tries to pretend every SQL dialect has a boolean type, there is a hack in the translator for T-SQL that adds <> 0 around "booleans" that are actually references to bit values (0 or 1). The unsafe_inject_raw(...) is inferred as such a boolean.

See this snippet for an example of the kind of translation I'm talking about. Notice the bool column becomes a BIT NOT NULL column and the query gets the extra <>0 added to make it valid T-SQL syntax.

I think this can be fixed with a special case in the translator for it to detect the unsafe_inject_raw function, since it is reasonable to assume that the injected SQL will be an already-valid T-SQL predicate. But in the meantime you can work around it by adding CASE WHEN .... THEN 1 ELSE 0 END to your raw SQL parameter.