fetchall_arrayref hangs with placeholders query with no results

[dsgh6180]

Reposted from https://www.perlmonks.org/?node_id=11131333

If I (1) specify a TDS level, and (2) use a query with placeholders, and (3) that query returns no results, the fetchall_arrayref() call hangs. All three of those things work fine individually and the call returns an array, as expected. When used all together, the call hangs.

Steps to reproduce on fresh install of Ubuntu 18.04:

Install perlbrew

sudo apt install gcc make -y
wget -O - https://install.perlbrew.pl | bash
echo "source ~/perl5/perlbrew/etc/bashrc" >> ~/.profile
source ~/perl5/perlbrew/etc/bashrc
perlbrew install perl-5.33.8
perlbrew switch perl-5.33.8
perlbrew install-cpanm

Install FreeTDS

wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-1.2.20.tar.gz
tar xfvz freetds-1.2.20.tar.gz
cd freetds-1.2.20/
./configure --prefix=/home/username/tdslib
make
make install

Install DBI and Sybase

export SYBASE=/home/username/tdslib
cpanm install DBI
cpanm DBD::Sybase --verbose --force

run test script

use warnings;
use strict;
use DBI;
use Data::Dumper;

runTest('');
runTest(';tdsLevel=CS_TDS_495');

sub runTest {
    my ($tdsLevel) = @_;
    my $dsn        = "dbi:Sybase:server=172.28.79.294$tdsLevel";
    my $UserName   = 'myusername';
    my $Password   = 'supersecretpassword';
    my $dbh        = DBI->connect( $dsn, $UserName, $Password, )
      or ( print "Can't connect to the DB: $DBI::errstr\n" and die );

    print "\nTesting no placeholders, no results $dsn\n";
    my $sth = $dbh->prepare("select 'test' where 1=0");
    $sth->execute();
    my $results = $sth->fetchall_arrayref( {} );
    print Dumper $results;

    print "\nTesting placeholders with results $dsn\n";
    my $sthSecond = $dbh->prepare("select ? where 1=1");
    $sthSecond->execute("test");
    my $resultsSecond = $sthSecond->fetchall_arrayref( {} );
    print Dumper $resultsSecond;

    print "\nTesting placeholders with no results $dsn\n";
    my $sthThird = $dbh->prepare("select ? where 1=0");
    $sthThird->execute("test");
    my $resultsThird = $sthThird->fetchall_arrayref( {} );
    print Dumper $resultsThird;

    $dbh->disconnect();
}

The SQL Server here is MS SQL Server Standard version 12.0.5223.6.

I have tested with Ubuntu 18.04 and CentOS 8.1. I have also installed SQL Server on Linux. All produced the same results. Connecting with no TDS level specified does not work at all on Ubuntu 20.04 (presumably a separate issue).

[mpeppler]

I'll look - however, using a placeholder in the "select" clause (i.e. "select ? where ...") is not valid syntax, and will normally not be handled by the dynamic SQL API. And placeholders (aka "dynamic SQL") will not work with the 4.x protocol version.

[dsgh6180]

Thanks I will modify my test script. I assume

select 'test' where 1 = ?

is valid.

[mpeppler]

So here's the deal:

With FreeTDS, when you specify TDS protocol level 4.x the Dynamic SQL capability is disabled. This means that DBD::Sybase will not attempt to submit the statement with the ? placeholder to the server for processing as a dynamic SQL statement, but instead send it as a normal SQL statement. This then hangs on the fetch, with no error message - but I suspect that this is an issue with the server. With the Sybase client libs setting the protocol level to 4.x has no impact on this statement, and you get the "normal" error message returned that ? placeholders must be in the WHERE, SET or VALUES clause of the SQL statement:

DBD::Sybase::db prepare failed: Server message number=7332 severity=15 state=1 line=1 server=SYBASE procedure=DBD1 text=The untyped variable ? is allowed only in in a WHERE clause or the SET clause of an UPDATE statement or the VALUES list of an INSERT statement

Using a cast() or convert() in the statement makes no difference.