Could not find prepared statement with handle 0.

[Hermanverschooten]

I am processing a list of articles and searching the db for related records. After a while the process will fail with following error:

08:35:54.169 [error] Tds.Protocol (#PID<0.200.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.2041.0> exited: an exception was raised:
    ** (Tds.Error) 1 (8179): Could not find prepared statement with handle 0.
        (ecto) lib/ecto/adapters/sql.ex:440: Ecto.Adapters.SQL.execute_or_reset/7
        (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
        (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4
        (ecto) lib/ecto/repo/queryable.ex:70: Ecto.Repo.Queryable.one/4
        (ex_winkel) lib/artikel_processor.ex:86: ExWinkel.ArtikelProcessor.find_start_stock/1
        (ex_winkel) lib/artikel_processor.ex:71: ExWinkel.ArtikelProcessor.proc/1
        (elixir) lib/task/supervised.ex:85: Task.Supervised.do_apply/2
        (elixir) lib/task/supervised.ex:36: Task.Supervised.reply/5

[Hermanverschooten]

I added some log statements and notice that it crashes just under the 10 minute mark. start: 08:55:03, crash: 09:04:07 start: 09:25:35, crash: 09:34:16 Don't know if that helps.

[mjaric]

Hi, Sorry for delayed response. I have trouble to reproduce this. Do you have any ecto extensions? Could you please try explain what ArtikelProcessor do, or if it is confidential, could you try to create case with similar circumstances so we can debug it?

Thanks!

[Hermanverschooten]

No problem. I cannot give you access to the repo, but I can tell/show you what that does. First some info. Elixir 1.5.2 Erlang 20.1 tds_ecto 2.0.0-alpha3

I can however show you the ArtikelProcessor code. This isn't exactly the version that produced the above error, but the error was still there.

Hope this helps.

artikel_processor.txt

[mjaric]

Sorry for delayed response. I tried to create similar query but without luck. Could you please update tds_ecto to latest version? It is available in hexpm https://hex.pm/packages/tds_ecto

[Hermanverschooten]

I just did that, but now I run into a completely different problem:

iex(22)> last_art = 0
0
iex(23)> q = from a in Artikels, join: h in subquery(iv_arts), on: a.artcode == h.artcode, where: a.voorraadbeheer == true, where: a.artcode > ^last_art, order_by: [desc: a.artcode], select: a.artcode
#Ecto.Query<from a0 in ExWinkel.Artikels,
 join: a1 in subquery(from a in ExWinkel.ArtHist,
  where: a.rectype == "IV",
  where: a.datum < ^~N[2008-01-01 00:00:00],
  where: a.aantal > 0,
  distinct: true,
  select: a.artcode),
 on: a0.artcode == a1.artcode, where: a0.voorraadbeheer == true,
 where: a0.artcode > ^0, order_by: [desc: a0.artcode], select: a0.artcode>
iex(24)> q |> Repo.all
** (Tds.Error) Tds is unable to conver struct into supported MSSQL types
    (tds_ecto) lib/tds_ecto/connection.ex:112: Tds.Ecto.Connection.prepare_param/1
    (tds_ecto) lib/tds_ecto/connection.ex:31: anonymous fn/2 in Tds.Ecto.Connection.prepare_execute/5
    (elixir) lib/enum.ex:1372: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (elixir) lib/enum.ex:1372: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (tds_ecto) lib/tds_ecto/connection.ex:30: Tds.Ecto.Connection.prepare_execute/5
    (ecto) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6
    (ecto) lib/ecto/adapters/sql.ex:426: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
iex(24)> q2 = from a in Artikels, where: a.artcode > ^last_art
#Ecto.Query<from a in ExWinkel.Artikels, where: a.artcode > ^0>
iex(25)> q2 |> Repo.all
** (Tds.Error) Tds is unable to conver struct into supported MSSQL types
    (tds_ecto) lib/tds_ecto/connection.ex:112: Tds.Ecto.Connection.prepare_param/1
    (tds_ecto) lib/tds_ecto/connection.ex:31: anonymous fn/2 in Tds.Ecto.Connection.prepare_execute/5
    (elixir) lib/enum.ex:1372: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (tds_ecto) lib/tds_ecto/connection.ex:30: Tds.Ecto.Connection.prepare_execute/5
    (ecto) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6
    (ecto) lib/ecto/adapters/sql.ex:426: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4
iex(25)>

[Hermanverschooten]

If I leave out the where: a.artcode > ^last_art then the query runs. If I replace ^last_art with a 0, it also works.

[mjaric]

Could you please execute below snip. in your IEX before you execute query? Then paste output here

:dbg.tracer()
:dbg.p(:all,:c)
:dbg.tpl(Tds.Ecto.Connection, :prepare_param, :x)

NOTE: do this in your dev box, since it is not optimised for production

You can stop tracer with :dbg.stop_clear()