ODBC SQL_BIGINT type

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

ODBC SQL_BIGINT type

Tyson Buzza
Does anyone know why the ODBC SQL_BIGINT is implemented as SQL_C_CHAR? There must be a reason for it, but this code is 10 years old so I'm not sure who to ask. I'm wondering if the original reason would even be relevant anymore.

I'm currently writing an Ecto adapter using ODBC and this is making it difficult. If you use a big int in your schema, Ecto expects an integer and gets a string instead. Either way the solution is to create a cache holding the type information and parse the string if a number is expected. I'm just wondering if this can be fixed for other people with my problem in the future.

I'm talking about this file https://github.com/erlang/otp/blob/master/lib/odbc/c_src/odbcserver.c 


_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions
Reply | Threaded
Open this post in threaded view
|

Re: ODBC SQL_BIGINT type

Jan Chochol-2
Hi Tyson,

Reason for using SQL_C_CHAR as physical encoding for SQL_BIGINT is,
that there are not much better options.
You can use SQL_C_NUMERIC, but it will not help in your case (it is
still string containing digits plus some metainformation).

Reason for not using SQL_C_SBIGINT or SQL_C_UBIGINT is, that it is not
possible to found if SQL_BIGINT is signed, or unsigned, so it can lead
to situation, that some values could not be returned. And it also
requires ODBC 3.0, which is not supported by all drivers.

What can Erlang ODBC client do, is to encode returned SQL_C_CHAR to
number, but it can affect backward compatibility (it can be
implemented in configurable way).

From my point of view, problem is that Erlang ODBC implementation
tries to simplify interface too much, so it can not be used in some
situations.
Most problematic part is, that it does not return type information
about columns (there is 'odbc:describe_table/2', but it can not be
used in all cases for all requests), and different column types are
encode to same Erlang type (we had own problems with distinguishing
between SQL_WCHAR and SQL_CHAR).
I do not see any easy way to fix this - it would probably require
complete rewrite of Erlang ODBC client.

On the other way - other languages have also big problems with ODBC -
e.g. Perl DBD::ODBC has quite a lot problems with Unicode.

Regards,
Jan Chochol

On Tue, Sep 3, 2019 at 3:13 PM Tyson Buzza <[hidden email]> wrote:

>
> Does anyone know why the ODBC SQL_BIGINT is implemented as SQL_C_CHAR? There must be a reason for it, but this code is 10 years old so I'm not sure who to ask. I'm wondering if the original reason would even be relevant anymore.
>
> I'm currently writing an Ecto adapter using ODBC and this is making it difficult. If you use a big int in your schema, Ecto expects an integer and gets a string instead. Either way the solution is to create a cache holding the type information and parse the string if a number is expected. I'm just wondering if this can be fixed for other people with my problem in the future.
>
> I'm talking about this file https://github.com/erlang/otp/blob/master/lib/odbc/c_src/odbcserver.c
>
> _______________________________________________
> erlang-questions mailing list
> [hidden email]
> http://erlang.org/mailman/listinfo/erlang-questions
_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions
Reply | Threaded
Open this post in threaded view
|

Re: ODBC SQL_BIGINT type

Ingela Andin
Hi!

I did some maintaing of this code a long time ago. I know that one objectives of the erlang ODBC-client was to try and make it easy for Erlang code to access SQL databases and the Erlang user should only have to care about Erlang vs SQL-datatypes.  However not all ODBC-backends are truly transparent in this regard, so I am afraid  it fails in this respect. Also you can input a lot of backend specific strings which makes the code dependent on the backend, in which case you might as well use a backend specific client.  Also, as I remember it, the implementation was made for a specific purpose, for which it works, and never extended to be more compleate (no communicated demand for it). As you observed this application has not really been developed for the last 10 Years and there is no plans from OTP to devleop it further (we have no own use of it). So it is really up to Open Source users to contribute if they think it useful.  As long as it is included in OTP it is possible to make pullrequests.

Regards Ingela Erlang/OTP team - Ericsson AB

Den ons 4 sep. 2019 kl 08:59 skrev Jan Chochol <[hidden email]>:
Hi Tyson,

Reason for using SQL_C_CHAR as physical encoding for SQL_BIGINT is,
that there are not much better options.
You can use SQL_C_NUMERIC, but it will not help in your case (it is
still string containing digits plus some metainformation).

Reason for not using SQL_C_SBIGINT or SQL_C_UBIGINT is, that it is not
possible to found if SQL_BIGINT is signed, or unsigned, so it can lead
to situation, that some values could not be returned. And it also
requires ODBC 3.0, which is not supported by all drivers.

What can Erlang ODBC client do, is to encode returned SQL_C_CHAR to
number, but it can affect backward compatibility (it can be
implemented in configurable way).

From my point of view, problem is that Erlang ODBC implementation
tries to simplify interface too much, so it can not be used in some
situations.
Most problematic part is, that it does not return type information
about columns (there is 'odbc:describe_table/2', but it can not be
used in all cases for all requests), and different column types are
encode to same Erlang type (we had own problems with distinguishing
between SQL_WCHAR and SQL_CHAR).
I do not see any easy way to fix this - it would probably require
complete rewrite of Erlang ODBC client.

On the other way - other languages have also big problems with ODBC -
e.g. Perl DBD::ODBC has quite a lot problems with Unicode.

Regards,
Jan Chochol

On Tue, Sep 3, 2019 at 3:13 PM Tyson Buzza <[hidden email]> wrote:
>
> Does anyone know why the ODBC SQL_BIGINT is implemented as SQL_C_CHAR? There must be a reason for it, but this code is 10 years old so I'm not sure who to ask. I'm wondering if the original reason would even be relevant anymore.
>
> I'm currently writing an Ecto adapter using ODBC and this is making it difficult. If you use a big int in your schema, Ecto expects an integer and gets a string instead. Either way the solution is to create a cache holding the type information and parse the string if a number is expected. I'm just wondering if this can be fixed for other people with my problem in the future.
>
> I'm talking about this file https://github.com/erlang/otp/blob/master/lib/odbc/c_src/odbcserver.c
>
> _______________________________________________
> erlang-questions mailing list
> [hidden email]
> http://erlang.org/mailman/listinfo/erlang-questions
_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions

_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions
Reply | Threaded
Open this post in threaded view
|

Re: ODBC SQL_BIGINT type

Michael Uvarov-2
You can try my odbc fork 

It has an option to return types and some unicode fixes.

It should work in Linux with mssql fine.

On Sun, 8 Sep 2019, 15:44 Ingela Andin, <[hidden email]> wrote:
Hi!

I did some maintaing of this code a long time ago. I know that one objectives of the erlang ODBC-client was to try and make it easy for Erlang code to access SQL databases and the Erlang user should only have to care about Erlang vs SQL-datatypes.  However not all ODBC-backends are truly transparent in this regard, so I am afraid  it fails in this respect. Also you can input a lot of backend specific strings which makes the code dependent on the backend, in which case you might as well use a backend specific client.  Also, as I remember it, the implementation was made for a specific purpose, for which it works, and never extended to be more compleate (no communicated demand for it). As you observed this application has not really been developed for the last 10 Years and there is no plans from OTP to devleop it further (we have no own use of it). So it is really up to Open Source users to contribute if they think it useful.  As long as it is included in OTP it is possible to make pullrequests.

Regards Ingela Erlang/OTP team - Ericsson AB

Den ons 4 sep. 2019 kl 08:59 skrev Jan Chochol <[hidden email]>:
Hi Tyson,

Reason for using SQL_C_CHAR as physical encoding for SQL_BIGINT is,
that there are not much better options.
You can use SQL_C_NUMERIC, but it will not help in your case (it is
still string containing digits plus some metainformation).

Reason for not using SQL_C_SBIGINT or SQL_C_UBIGINT is, that it is not
possible to found if SQL_BIGINT is signed, or unsigned, so it can lead
to situation, that some values could not be returned. And it also
requires ODBC 3.0, which is not supported by all drivers.

What can Erlang ODBC client do, is to encode returned SQL_C_CHAR to
number, but it can affect backward compatibility (it can be
implemented in configurable way).

From my point of view, problem is that Erlang ODBC implementation
tries to simplify interface too much, so it can not be used in some
situations.
Most problematic part is, that it does not return type information
about columns (there is 'odbc:describe_table/2', but it can not be
used in all cases for all requests), and different column types are
encode to same Erlang type (we had own problems with distinguishing
between SQL_WCHAR and SQL_CHAR).
I do not see any easy way to fix this - it would probably require
complete rewrite of Erlang ODBC client.

On the other way - other languages have also big problems with ODBC -
e.g. Perl DBD::ODBC has quite a lot problems with Unicode.

Regards,
Jan Chochol

On Tue, Sep 3, 2019 at 3:13 PM Tyson Buzza <[hidden email]> wrote:
>
> Does anyone know why the ODBC SQL_BIGINT is implemented as SQL_C_CHAR? There must be a reason for it, but this code is 10 years old so I'm not sure who to ask. I'm wondering if the original reason would even be relevant anymore.
>
> I'm currently writing an Ecto adapter using ODBC and this is making it difficult. If you use a big int in your schema, Ecto expects an integer and gets a string instead. Either way the solution is to create a cache holding the type information and parse the string if a number is expected. I'm just wondering if this can be fixed for other people with my problem in the future.
>
> I'm talking about this file https://github.com/erlang/otp/blob/master/lib/odbc/c_src/odbcserver.c
>
> _______________________________________________
> erlang-questions mailing list
> [hidden email]
> http://erlang.org/mailman/listinfo/erlang-questions
_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions
_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions

_______________________________________________
erlang-questions mailing list
[hidden email]
http://erlang.org/mailman/listinfo/erlang-questions