Image type support?

[markquezada]

Environment

• Elixir version (elixir -v): 1.13.4
• TDS version (mix deps): 2.3.0
• MSSQL Server version (SQL Server 2016 / 2019 / Azure SQL Edge): 2008
• Additional configuration parameter (ssl_opts, socket_options, ...): n/a
• Operating system: mac

Expected behavior

    field :picture_data, :string, source: :Picture

Actual behavior

I get a runtime error:

** (exit) an exception was raised:
    ** (FunctionClauseError) no function clause matching in anonymous fn/2 in Tds.Types.decode_info/1
        (tds 2.3.0) lib/tds/types.ex:368: anonymous fn(1, <<11, 0, 116, 0, 98, 0, 108, 0, 83, 0, 116, 0, 117, 0, 100, 0, 101, 0, 110, 0, 116, 0, 115, 0, 7, 80, 0, 105, 0, 99, 0, 116, 0, 117, 0, 114, 0, 101, 0, 0, 0, 0, 0, 9, 0, 167, 20, 0, 9, 4, ...>>) in Tds.Types.decode_info/1
        (elixir 1.13.4) lib/enum.ex:4136: Enum.reduce_range/5
        (tds 2.3.0) lib/tds/types.ex:365: Tds.Types.decode_info/1
        (tds 2.3.0) lib/tds/tokens.ex:514: Tds.Tokens.decode_column/1
        (tds 2.3.0) lib/tds/tokens.ex:509: Tds.Tokens.decode_columns/3
        (tds 2.3.0) lib/tds/tokens.ex:108: Tds.Tokens.decode_colmetadata/2
        (tds 2.3.0) lib/tds/tokens.ex:47: Tds.Tokens.decode_tokens/2
        (tds 2.3.0) lib/tds/messages.ex:102: Tds.Messages.parse/3
        (tds 2.3.0) lib/tds/protocol.ex:481: Tds.Protocol.decode/2
        (tds 2.3.0) lib/tds/protocol.ex:568: Tds.Protocol.send_prepare/3
        (db_connection 2.4.2) lib/db_connection/holder.ex:354: DBConnection.Holder.holder_apply/4
        (db_connection 2.4.2) lib/db_connection.ex:1349: DBConnection.prepare/4
        (db_connection 2.4.2) lib/db_connection.ex:1342: DBConnection.run_prepare/4
        (db_connection 2.4.2) lib/db_connection.ex:1354: DBConnection.run_prepare_execute/5
        (db_connection 2.4.2) lib/db_connection.ex:1459: DBConnection.run/6
        (db_connection 2.4.2) lib/db_connection.ex:595: DBConnection.parsed_prepare_execute/5
        (db_connection 2.4.2) lib/db_connection.ex:587: DBConnection.prepare_execute/4
        (ecto_sql 3.8.1) lib/ecto/adapters/sql.ex:855: Ecto.Adapters.SQL.execute!/5
        (ecto_sql 3.8.1) lib/ecto/adapters/sql.ex:847: Ecto.Adapters.SQL.execute/6
        (ecto 3.8.3) lib/ecto/repo/queryable.ex:221: Ecto.Repo.Queryable.execute/4

Looks like it's not parsing the binary format correctly. I'd be happy to send in a PR to fix this but so far can't find any documentation on the binary format for the Image column type

[markquezada]

@mjaric @mobileoverlord @moogle19 any thoughts on this one? If you can point me in the right direction I'd happily create a PR to fix this. Thank you!

[mjaric]

@markquezada I'm a bit rusty, haven't work on driver for some time. But you can try to work with binary (varbinary(max) to be precise).

What do you think your system would benefit from actual image type?

[markquezada]

@mjaric thanks for getting back to me. I'm working with a legacy system that is already using the mssql image type so I can't really change it.

All I need is a way to get the binary string out of the db so I can render it as an image from a controller endpoint. Is there a way to get the driver to just return the raw string from the db? Even if not using it from within an ecto schema?

[mjaric]

The simplest way is to cast(columName as varbinary) in select. Then it will be picked up as binary in elixir.

You can create ecto custom type using above to pack it nicely. For raw tds usage, I was planning 2-3 years ago to add pluggable serialization bit I think it is not there (probably never done)

[markquezada]

Unfortunately since using a custom ecto type seems to first require the raw data to be decoded via the TDS adapter, it still failed when I went down that route.

That said, I was able to access the data directly, using cast as you suggested:

    query =
       from s in Student,
         select: fragment("CAST(? AS varbinary(max))", s.picture_data),
         where: s.id == ^id,
         where: not is_nil(s.picture_data)

     case Repo.one(query) do
       nil -> {:error, :no_image}
       binary_data -> {:ok, binary_data}
     end

Thanks!

[fnicastri]

@mjaric facing something similar with geometry/geography type,
it would be nice to have a away to get the raw data from the db

[mjaric]

@markquezada, What if field in your model is :binary instead of :string? I'm referring to original issue at top. Somehow I didn't notice it earlier.

For geometry, I'll see if ecto custom type could help

[josevalim]

@mjaric I believe the issue happens before the Ecto type, because TDS does not know how to decode this. We either need to explicitly support them all OR we could return unknown types as TDS.Raw{binary: ...}. And now people should be able to create Ecto types that encode/decode to raw. :)

[mjaric]

@fnicastri, I created issue #147 for geometry support, it is not that simple and I don't want to block image support