typeCast not supported with .execute

[aikar]

using typeCast option in .query works, but does not work in .execute. The field is not type casted.

[sidorares]

typeCast only documented to work with .query()

The aim of typeCast() is to 1) give access to internal raw representation of data and 2) conrol what's generated out of this raw data

With .execute() this raw data is completely different ( prepared statements use so called 'binary protocol' and plain queries 'text protocol' )

Also I think current api of typeCast is flawed: you as consumer responsible of reading all raw data in full and in correct order. If you miss something everything breaks.

[aikar]

@sidorares I understand why it's more difficult to do, but the options really should work for both methods...

I ultimately had to re-implement typeCast in an abstraction layer that any time .execute is called on my layer, it immediately iterates the fields response and does type conversion.

For example, someone decided to use a VARBINARY column type for string keys in order to enforce case sensitivity (very old table before mysql supported this natively), and I now need to convert that to string.

A solution is to simply take the fields metadata response, and rebuild the same data structure the .query based call is supplied.

[sidorares]

I'll try to check how easy to do that, maybe actually not that difficult

[javiertury]

I am trying to make Sequelize use binded parameters here. The Sequelize team prefers node-mysql2 as a mysql driver, but unfortunately Sequelize needs typeCast() functionality.

Also, the overall information returned by the execute() method is poorer than for query().

[javiertury]

Apart from applying timezone, typeCast is also useful to convert geometries to geoJSON format. If it is not possible to add typeCast support for .execute, perhaps some of these common typeCasts can be implemented as built-in options for node-mysql2, e.g. options.geoJSON = true.

[trevorr]

I've been using typeCast to convert bit(1) columns from Buffer to boolean. I couldn't figure out why it stopped working until I realized a changed query to execute and found this issue. Even if it there's not an easy fix, it would be a big help to document the difference and possibly generate a warning the first time execute is used on a connection with a typeCast. I'll submit a PR for either or both if you want @sidorares.

While I'm on the subject, would it be reasonable to have a built-in conversion for bit(1) returned as boolean? For compatibility, it could be enabled via an option. I could submit a PR for that as well.

[wellwelwel]

Closing due to #2398 🎉

Please feel free to ask anything.