nakagami / firebirdex Goto Github PK

hmm, strange. When trying to use 0.3.3 in a project I get the following error message when doing a query with charset: :cp1252 as a connection option.

[error] GenServer #PID<0.844.0> terminating
** (DBConnection.ConnectionError) client #PID<0.967.0> stopped: ** (Codepagex.Error) Unknown encoding :"VENDORS/MICSFT/WINDOWS/CP1252"
(codepagex 0.1.6) lib/codepagex.ex:519: Codepagex.from_string!/4
(firebirdex 0.3.3) lib/firebirdex/connection.ex:62: Firebirdex.Connection.handle_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
(firebirdex 0.3.3) lib/firebirdex.ex:23: Firebirdex.query/4

Hi,

it looks like when a varchar field contains an empty string "", it is not returned as an empty string, but as an empty array [].
Is it possible to return it as an empty string?

Hi,

it looks like there is an encoding problem when using transactions.
For example when I try to do this inside a function:

    Firebirdex.transaction(Cvodvd.WisaDB.conn(), fn conn ->
      Firebirdex.query(conn, "DELETE FROM CVODVD_ODB")
      Enum.each(records,fn record ->
        Firebirdex.query(conn, sql, [
          record["voo_code"],
          record["vo_code"],
          record["odb_code"],
          record["vov"],
          record["vok"],
          record["oc"]
        ])
      end)

I get the following stack trace:

[error] GenServer #PID<0.538.0> terminating
** (DBConnection.ConnectionError) client #PID<0.657.0> stopped: ** (FunctionClauseError) no function clause matching in Firebirdex.Encoding.from_string!/2
(firebirdex 0.3.3) lib/firebirdex/encoding.ex:2: Firebirdex.Encoding.from_string!("DELETE FROM CVODVD_ODB", nil)
(firebirdex 0.3.3) lib/firebirdex/connection.ex:62: Firebirdex.Connection.handle_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:595: DBConnection.parsed_prepare_execute/5
(db_connection 2.4.2) lib/db_connection.ex:587: DBConnection.prepare_execute/4
(firebirdex 0.3.3) lib/firebirdex.ex:23: Firebirdex.query/4
(cvodvd 0.1.0) lib/cvodvd/sync/odb_codes.ex:49: anonymous fn/2 in Cvodvd.Sync.OdbCodes.update_wisa/1
(db_connection 2.4.2) lib/db_connection.ex:1562: DBConnection.run_transaction/4
(stdlib 3.17) erl_eval.erl:685: :erl_eval.do_apply/6
(elixir 1.13.3) src/elixir.erl:296: :elixir.recur_eval/3
(elixir 1.13.3) src/elixir.erl:274: :elixir.eval_forms/3
(iex 1.13.3) lib/iex/evaluator.ex:310: IEx.Evaluator.handle_eval/3
(iex 1.13.3) lib/iex/evaluator.ex:285: IEx.Evaluator.do_eval/3
(iex 1.13.3) lib/iex/evaluator.ex:274: IEx.Evaluator.eval/3
(iex 1.13.3) lib/iex/evaluator.ex:169: IEx.Evaluator.loop/1
(iex 1.13.3) lib/iex/evaluator.ex:32: IEx.Evaluator.init/4
(stdlib 3.17) proc_lib.erl:226: :proc_lib.init_p_do_apply/3

(db_connection 2.4.2) lib/db_connection/connection.ex:198: DBConnection.Connection.handle_cast/2
(connection 1.1.0) lib/connection.ex:810: Connection.handle_async/3
(stdlib 3.17) gen_server.erl:695: :gen_server.try_dispatch/4
(stdlib 3.17) gen_server.erl:771: :gen_server.handle_msg/6
(stdlib 3.17) proc_lib.erl:236: :proc_lib.wake_up/3

Last message: {:"$gen_cast", {:stop, #Reference<0.50204116.1522925569.38858>, %DBConnection.ConnectionError{message: "client #PID<0.657.0> stopped: ** (FunctionClauseError) no function clause matching in Firebirdex.Encoding.from_string!/2\n (firebirdex 0.3.3) lib/firebirdex/encoding.ex:2: Firebirdex.Encoding.from_string!("DELETE FROM CVODVD_ODB", nil)\n (firebirdex 0.3.3) lib/firebirdex/connection.ex:62: Firebirdex.Connection.handle_prepare/3\n (db_connection 2.4.2) lib/db_connection/holder.ex:354: DBConnection.Holder.holder_apply/4\n (db_connection 2.4.2) lib/db_connection.ex:1349: DBConnection.prepare/4\n (db_connection 2.4.2) lib/db_connection.ex:1342: DBConnection.run_prepare/4\n (db_connection 2.4.2) lib/db_connection.ex:1354: DBConnection.run_prepare_execute/5\n (db_connection 2.4.2) lib/db_connection.ex:595: DBConnection.parsed_prepare_execute/5\n (db_connection 2.4.2) lib/db_connection.ex:587: DBConnection.prepare_execute/4\n (firebirdex 0.3.3) lib/firebirdex.ex:23: Firebirdex.query/4\n (cvodvd 0.1.0) lib/cvodvd/sync/odb_codes.ex:49: anonymous fn/2 in Cvodvd.Sync.OdbCodes.update_wisa/1\n (db_connection 2.4.2) lib/db_connection.ex:1562: DBConnection.run_transaction/4\n (stdlib 3.17) erl_eval.erl:685: :erl_eval.do_apply/6\n (elixir 1.13.3) src/elixir.erl:296: :elixir.recur_eval/3\n (elixir 1.13.3) src/elixir.erl:274: :elixir.eval_forms/3\n (iex 1.13.3) lib/iex/evaluator.ex:310: IEx.Evaluator.handle_eval/3\n (iex 1.13.3) lib/iex/evaluator.ex:285: IEx.Evaluator.do_eval/3\n (iex 1.13.3) lib/iex/evaluator.ex:274: IEx.Evaluator.eval/3\n (iex 1.13.3) lib/iex/evaluator.ex:169: IEx.Evaluator.loop/1\n (iex 1.13.3) lib/iex/evaluator.ex:32: IEx.Evaluator.init/4\n (stdlib 3.17) proc_lib.erl:226: :proc_lib.init_p_do_apply/3\n", reason: :error, severity: :error}, %Firebirdex.Connection{charset: nil, conn: {:conn, #Port<0.19>, '', '', :cp1252, true, 249183553855695752259025901539470500830, 103567129941906026288434133422668171601172440229759169995026977034920371872160430608384320594353571829468899157299956474780500090406357796085895868999995244459472424811556563038572862879791243349977983990579044355312857790295210876632078415198225837565311458142442744960410283559510011215171673400394910688133, :undefined, 'Srp256', true, :undefined, :undefined, 0, 118, 12, 'Europe/Brussels', %{}, %{}}}}}
State: {Firebirdex.Connection, %Firebirdex.Connection{charset: nil, conn: {:conn, #Port<0.19>, '', '', :cp1252, true, 249183553855695752259025901539470500830, 103567129941906026288434133422668171601172440229759169995026977034920371872160430608384320594353571829468899157299956474780500090406357796085895868999995244459472424811556563038572862879791243349977983990579044355312857790295210876632078415198225837565311458142442744960410283559510011215171673400394910688133, :undefined, 'Srp256', true, :undefined, :undefined, 0, 118, 12, 'Europe/Brussels', %{}, %{}}}}
** (FunctionClauseError) no function clause matching in Firebirdex.Encoding.from_string!/2

The following arguments were given to Firebirdex.Encoding.from_string!/2:

    # 1
    "DELETE FROM CVODVD_ODB"

    # 2
    nil

Attempted function clauses (showing 10 out of 39):

    def from_string!(s, :utf_8)
    def from_string!(s, :cp932)
    def from_string!(s, :cp737)
    def from_string!(s, :cp437)
    def from_string!(s, :cp850)
    def from_string!(s, :cp865)
    def from_string!(s, :cp860)
    def from_string!(s, :cp863)
    def from_string!(s, :cp775)
    def from_string!(s, :cp862)
    ...
    (29 clauses not shown)

(firebirdex 0.3.3) lib/firebirdex/encoding.ex:2: Firebirdex.Encoding.from_string!/2
(firebirdex 0.3.3) lib/firebirdex/connection.ex:62: Firebirdex.Connection.handle_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:595: DBConnection.parsed_prepare_execute/5
(db_connection 2.4.2) lib/db_connection.ex:587: DBConnection.prepare_execute/4
(firebirdex 0.3.3) lib/firebirdex.ex:23: Firebirdex.query/4
(cvodvd 0.1.0) lib/cvodvd/sync/odb_codes.ex:49: anonymous fn/2 in Cvodvd.Sync.OdbCodes.update_wisa/1
(db_connection 2.4.2) lib/db_connection.ex:1562: DBConnection.run_transaction/4

The server works with the dBeaver JDBCDriver
But, I'm getting a :killed signal following the example from the readme:

This is my docker-compose file

version: "3.8"
services:
  firebird:
    image: jacobalberty/firebird:v3.0
    restart: unless-stopped
    environment:
      ISC_PASSWORD: masterkey
    ports:
      - 3050:3050
    volumes:
      - ./data:/firebird/data

opts = [ 
  hostname: "localhost:3050",
  username: "SYSDBA",
  password: "masterkey",
  database: "/firebird/data/MY.fdb"
]

{:ok, pid} = Firebirdex.start_link(opts)

I'm geting this error:

Hi,

This is not really an issue, but more of an idea I would like to discuss with you.
The whole thing with the prepared statements made me thinking. The way Firebirdex is handling it right now is OK in the sense that there is no build-up of prepared statements on the server anymore. Prepared statements however are interesting because of their performance enhancements.
Therefore I'm thinking about creating a cache of prepared statements on the client, maybe by storing them in an ETS table with the hash of the statement as the key. Possible improvements could be to store a certain maximum of statements, and deleting statements that have not been used when the maximum is reached.
This could be done by some options on the connection (keep_prepared_statements?, max_prepared_statements, ...), and/or options on the Firebird.query function itself.
Is this something you see as valuable to Firebirdex, then I will try to build it into Firebirdex. If it's something that you don't like to have in Firebirdex, I can still develop it as a front-end to Firebirdex for myself.

What do you think?

Hi,

when I use the following expression in Firebirdex 0.9.1 in a SELECT to decompose an INTEGER field used as a bit-field, I get an error. It works in my query tool, but not in Firebirdex. I also have not been able to find a workaround.
expression :
SELECT
CASE WHEN Bin_And(Power(2, 0), imv.IMV_LESDAGEN) <> 0 THEN 'mavm,' ELSE Trim('') END ||
CASE WHEN Bin_And(Power(2, 1), imv.IMV_LESDAGEN) <> 0 THEN 'divm,' ELSE Trim('') END as lesmomenten
FROM ...

error:
** (exit) an exception was raised:
** (ArithmeticError) bad argument in arithmetic expression
:erlang.rem(:undefined, 256)
(efirebirdsql 0.9.1) /elixir/cvodvd/deps/efirebirdsql/src/efirebirdsql_op.erl:650: :efirebirdsql_op.more_select_describe_vars/3
(efirebirdsql 0.9.1) /elixir/cvodvd/deps/efirebirdsql/src/efirebirdsql_op.erl:702: :efirebirdsql_op.parse_select_column/4
(efirebirdsql 0.9.1) /elixir/cvodvd/deps/efirebirdsql/src/efirebirdsql_op.erl:711: :efirebirdsql_op.parse_select_columns/4
(efirebirdsql 0.9.1) /elixir/cvodvd/deps/efirebirdsql/src/efirebirdsql_op.erl:728: :efirebirdsql_op.get_prepare_statement_response/2
(firebirdex 0.3.5) lib/firebirdex/connection.ex:57: Firebirdex.Connection.handle_prepare/3

Any way you can help?

Hi,

I noticed that when using the library the number of pending transactions increases continuously, ultimately leading to a crash of the database. I' now doing a reconnect every 15 minutes, and you can see this pattern clearly in the database monitoring tool (see attached image - this is a test database that is hardly used, and probably the increase in pending transactions is caused here by the ping function).
Although the reconnect is a workaround, it would be good if the cause of the issue could be found and solved.

Hi,

while using Firebirdex and monitoring the Firebirdsql database I noticed a cumulation of prepared statements in the database server, ultimately leading to a crash.
Apparently prepared queries are cashed by the database server on a connection, and must explicitly be closed to be cleaned up.
This issues does not present itself when you just open the database connection, do a query, and close the connection. It does present itself however when you open a connection, and do lots of queries (Firebirdex.query) on that same connection.

In the DBConnection specification there is a close function, with a handle_close callback which should do this cleanup of a prepared statement, but I noticed in your implementation of the Firebirdex.Connection.handle_close function you are closing the connection (:efirebirdsql_protocol.close), which is basically the same as Firebirdex.Connection.disconnect.

Is it possible you misinterpreted the close/handle_close as being a disconnect, while it actually should be a cleanup of a prepared query?

I don't know enough of the underlying :efirebirdsql_protocol, to know which function to call to do the prepared query cleanup to create a pull request.
Can you help?

Hi,

when using Firebirdex it seems to default to a utf8 character set. I have a database which uses a WIN1252 charset, and all text containing accents or special characters are returned wrong to the client.
To deal with this there are 2 options:

• using a client library that converts all strings returned from the database from the database charset to utf8 and back to the database charset on inserts/updated
• use the Firebird connection parameter that sets the charset on the connection, and let the Firebird database handle all conversions.

The second option would be the preferred one, but, unless I'm completely overlooking it, I don't think Firebirdex currently supports a charset connection parameter.
Would it be possible to add this connection parameter? (if it does not already exist)
I suppose it would be based on the isc_dpb_lc_ctype parameter in this specification: https://firebirdsql.org/file/documentation/pdf/en/firebirddocs/wireprotocol/firebird-wire-protocol.pdf