type_info fails when open statement handle - suggest workaround [rt.cpan.org #78862]

[mpeppler]

Migrated from rt.cpan.org#78862 (status was 'open')

Requestors:

• EDAVIS@cpan.org

From edavis@cpan.org on 2012-08-09 12:12:40
:

On recent versions of MSSQL code like the following will break:

my $sth = $dbh->prepare('select 5');
execute $sth;
my $type = $dbh->type_info($sth->{TYPE}->[0]);

It fails with 'cannot call method execute on an undefined value'.
type_info_all tries to do

    my $sth = $dbh->prepare("sp_datatype_info");

but this fails because there is already an active statement handle.
I believe that older MSSQL versions, as well as Sybase, were more
tolerant somehow so you could call sp_datatype_info even with an open sth.

I tried the simple fix of calling
$dbh->selectall_arrayref("sp_datatype_info") but that doesn't help; it
still fails if there is an open statement.

What I suggest is two things:

- There should be a new global method to fetch all type info and cache
it for future calls to type_info.  You could call this immediately after
connecting, if you are planning to use type_info later in the program. 
(It could also happen on every connection, but I am reluctant to slow
down programs which don't need the type info.)

- type_info should catch the failure to prepare the statement handle and
give a more helpful error message, prompting the user to call the new
cache_type_info() method first before preparing a statement.

I started writing code for this, but I could not work out how to add a
new method to the database handle object, given that the RootClass
attribute on connection is not working (see previous bug).  What do you
think?

From edavis@cpan.org on 2012-08-09 12:22:07
:

Or it could instead be a new Sybase-specific connection option.  That
might be cleaner than adding a whole new method, if marginally less
flexible for the caller.  If the option is given then type info will be
cached on connection (or perhaps immediately before preparing the first
statement handle).

[mpeppler]

Added caching of type_info_all data.