Hi Jens,

Thank you for trying to deal with this mess, whatever happens. The RT ticket

https://rt.cpan.org/Ticket/Display.html?id=100551 was not based on any deep knowledge of SQL, its standards, and its implementations, but just on the observation that the hex representation of 16 (base 10) was not "1".

I am not sure what I would do if I were trying to address this issue. On the one hand, letting someone like Math::Base::Convert deal with the problem is a very tempting solution. On the other hand, this may involve a change in functionality.

I have had trouble getting Google's link to the SQL-92 standard to display, but other search results seem to say that CONV is not a standard function. There _is_ a standard function called CONVERT, but its signature is completely different.

The rest is my response to your questions. Please understand that in these responses I speak only for myself. I have no idea what other people might need.

----- Original Message -----Hi,

during review of SQL::Statement wrt. RT#100551 I realized that the introduced

SQL_FUNCTION_CONV

(https://github.com/perl5-dbi/SQL-Statement/blob/master/lib/SQL/Statement/Functions.pm#L454)

is unnecessary over-complex.

Since I learned that silent removal of once shipped functionality is a evil -

I'd like to get some suggestions.

For existing implementation I miss a proper documentation of edge-cases:

* How is 1.E converted to octal and why is that necessary in SQL?

I no longer know exactly why I needed this kind of functionality, but I do remember I was only interested in converting integers. I suppose the octal representation of 1.5 would be 1.4. I know of no scientific notation for any number in any base other than 10.

* Why do the alphabets lower 63 and 63+ based number differ?

The documentation SQL::Statement::Functions says (or at least implies) that the change is so that conversion to base 64 is compatible with MIME Base-64 encoding.

* If different encoding schemes are supported either, why is xnt and b85

missing and what's about plain ascii?

I do not know.

From my perspective - unless proper answers are given - the implementation of

SQL_FUNCTION_CONV should be replaced by an easy to use wrapper around

https://metacpan.org/release/Math-Base-Convert to rely on dedicated tested

and approved.

For my personal use I would be happy with this implementation. Maybe check for base 64 and use Mime::Base64 in that case. Maybe warn if you detect a case where the result changes. But I personally need neither of these.

If it turns out that CONV is in fact non-standard, another way to go would be to simply deprecate and remove it, and replace it (if you decide to replace it at all) with functionality that does what you want.

I started a rough test whether it's possible to keep the existing API and run

into different results for 1st chosen test

{

test => 'conv 2->92',

sql => "SELECT CONV('101 0100 1111 0111 0110 1011', 2, 92)",

result => [ ['HN(/'] ],

},

Mike's module resulted 'HN)/' - so there seem to be more quirks in individual

S::S solution.

Any comments?

Cheers

--

Jens Rehsack

rehsack@gmail.com

Thank you again for your work on this.

Tom