fetchall_arrayref hangs with placeholders query with no results

[TieUpYourCamel]

I have a situation with DBI and the Sybase drivers. If I specify a TDS level, and I use a query with placeholders, and that query returns no results, the fetchall_arrayref() call hangs. If I don't do all three of those things, the call returns an array, as expected.

Steps to reproduce on Ubuntu 20.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 test code above runs a total of six queries with different combinations of (1) specifying the TDS level (2), running a query with or without placeholders , and (3) running a query that does or does not return results. The test query that produces the hang is run last.

Full disclosure: I first wrote this test script on one computer and then created a virtual machine to test the "steps to reproduce" shown above. On the second computer, the connection with no TDS level specified does not work. However, I believe this to be a separate issue.

The SQL Server here is MS SQL Server Standard version 12.0.5223.6

[erix]

What errors are in log file(s) (of application, DBI, dbms)?

What *does* work? Does it work without placeholder?

Perhaps it's the wrong placeholder-style?

Maybe having no table confuses the system? I'd use a statement like:

select 'whatever'
from tablename 
where columnname = ? 
;

[afoken]

Maybe having no table confuses the system?

It should not. Executing a table-less query like

SELECT 42 AS result

works on most SQL databases. The remaining ones, Oracle being probably the most known example, always expects a dummy table or view, but also always provides that dummy table or view specifically for this purpose. Its name is DUAL on Oracle, and you need to use

SELECT 42 AS result FROM dual

on Oracle. Some databases also provide a table or view named DUAL just for Oracle compatibility.

See Wikipedia: DUAL table for a nice overview.

Alexander

[erix]

I know.

PostgreSQL, for instance, knows how to since time immemorial (and certainly all maintained versions).

SQL Server too (I checked all the same on sqlfiddle).

In fact, I was writing a post to Marshall containing pretty much everything that you said...

Anyhow, it seems the OP has lost interest, or maybe solved his problem.

[Marshall]

I like your idea of having a tablename. I've never used a DB where there is some default table with no name. I do know that prepare statements are table specific (i.e. the table name cannot be from a placeholder). I see from the thread that this Sybase thing does something weird during the prepare, I am wondering if not having a table name somehow causes some confusion in the temporary stored procedure in the error case of "no results"?

While experimenting, I would also try without the hashref part, instead of $sth->fetchall_arrayref({}); use just $sth->fetchall_arrayref(); Asking for a less complicated data structure maybe makes a difference?

[TieUpYourCamel]

Thanks for your suggestion. Unfortunately not passing the hashref produces the same results.

[TieUpYourCamel]

Thanks for your suggestions. The log files of my application just stop when the fetchall_arrayref call is made. I'll look into how to find the DBI and dbms logs.

What does work? Perhaps a matrix will be more clear:

Machine #1 ( used for initial testing )
                           |-----------------------------|------------
+---------------|------------------------------|
                           | no placeholders no results  | placeholder
+s with results | placeholders with no results |
                           |-----------------------------|------------
+---------------|------------------------------|
not specifying a TDS level | empty array                 | array of re
+sult hashes    | empty array                  |
                           |-----------------------------|------------
+---------------|------------------------------|
specifying a TDS level     | empty array                 | array of re
+sult hashes    | (application hangs)          |
                           |-----------------------------|------------
+---------------|------------------------------|

Machine #2 ( used for "Steps to reproduce on Ubuntu 20.04" in original
+ post )
                           |-----------------------------|------------
+---------------|------------------------------|
                           | no placeholders no results  | placeholder
+s with results | placeholders with no results |
                           |-----------------------------|------------
+---------------|------------------------------|
not specifying a TDS level | (connection fails)          | (connection
+ fails)        | (connection fails)           |
                           |-----------------------------|------------
+---------------|------------------------------|
specifying a TDS level     | empty array                 | array of re
+sult hashes    | (application hangs)          |
                           |-----------------------------|------------
+---------------|------------------------------|

For some reason the test program in my original post doesn't connect at all on machine #2 when no TDS level is specified. This is the error message, but I believe this is not related to my problem. (Or perhaps to state it more accurately, it's not the problem I'm trying to solve)

DBI connect('server=172.28.79.294','machinename',...) failed: OpenClie
+nt message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (49)
Server 172.28.79.294, database 
Message String: Unexpected EOF from the server
OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = 
+(34)
Server 172.28.79.294, database 
Message String: Adaptive Server connection failed
OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = 
+(34)
Server 172.28.79.294, database 
Message String: Adaptive Server connection failed
 at go.pl line 14.
Died at go.pl line 14.

My original code that produced this error had SQL queries included tables. My test code for reproducing the problem wouldn't produce the same results if you didn't have the same tables with the same columns, so I simplified them for testing.

[mpeppler]

I think there are two problems - but the most important is that the syntax

select ? from ... where ..

isn't valid SQL.

?-style placeholders are used to pass variables into WHERE clauses, not to pass literals into the SELECT statement. I suspect that this is why your query only works if you specify the 4.x protocol level, as that does NOT support placeholders.

I'll still take a look at the issue you opened on GitHub, but I suspect that this is really a client side SQL issue more than anything else.

Michael

[afoken]

the syntax

select ? from ... where ..

isn't valid SQL.

Are you sure? Any links to documents that specify that?

At least SQLite accepts it:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $dbh=DBI->connect('dbi:SQLite:dbname=/tmp/foo.sqlite','','',{ Raise
+Error => 1 });
my $sth=$dbh->prepare('select ? as answer');
$sth->execute(42);
$sth->dump_results();

'42'
1 rows

PostgreSQL can't determinate the column type without a little help, but accepts it with type information. SQLite has a very relaxed relation to data types, so it is no surprise that SQLite does NOT need help.

#!/usr/bin/perl

use strict;
use warnings;
use DBI qw( SQL_INTEGER );

my $dbh=DBI->connect('dbi:Pg:dbname=postgres','postgres','postgres',{ 
+RaiseError => 1 });
my $sth=$dbh->prepare('select ? as answer');
$sth->bind_param(1,42,SQL_INTEGER);
$sth->execute();
$sth->dump_results();

42
1 rows

Alexander

[mpeppler]

Yes - at least for sure with Sybase ASE.

Using your script:

BD::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 UPDAT
+E statement or the VALUES list of an INSERT statement

[afoken]

Steps to reproduce on Ubuntu 20.04:

[...]

The SQL Server here is MS SQL Server Standard version 12.0.5223.6

An alternative to using DBD::Sybase and FreeTDS might be DBD::ODBC. On Unix, it needs an ODBC manager like unixODBC or iodbc and an ODBC driver for MS SQL Server (e.g. from easysoft). The easysoft driver is not free.

A third way might be using DBD::Proxy (or DBD::Gofer, if its limitations are acceptable), and running a DBD proxy server on the Windows server hosting the SQL Servier. On Windows, all you need is Perl (e.g. Strawberry), DBI, DBD::ODBC or DBD::ADO. ODBC manager and ODBC driver are provided by Microsoft, ODBC manager as part of Windows, ODBC driver as part of SQL Server.

Unfortunately, I don't have experience with ODBC on Unix; and I try to avoid MS SQL Server as much as possible, especially when Unix systems are involved.

See also DBD::ODBC FAQ.

Alexander

[perlfan]

How does selectall_arrayref work out? This necessarily uses fetchall_arrayref.

[TieUpYourCamel]

Thanks for the suggestion; unfortunately it produces the same results.

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

print "\nTesting placeholders with results $dsn\n";
my $sthSecond = $dbh->prepare("select ? where 1=1");
print Dumper $dbh->selectall_arrayref($sthSecond);

print "\nTesting placeholders with no results $dsn\n";
my $sthThird = $dbh->prepare("select ? where 1=0");
print Dumper $dbh->selectall_arrayref($sthThird);

Results:

Testing no placeholders, no results dbi:Sybase:server=xxx.xxx.xxx.xxx
$VAR1 = [];

Testing placeholders with results dbi:Sybase:server=xxx.xxx.xxx.xxx
$VAR1 = [
          [
            undef
          ]
        ];

Testing placeholders with no results dbi:Sybase:server=xxx.xxx.xxx.xxx
$VAR1 = [];

Testing no placeholders, no results dbi:Sybase:server=xxx.xxx.xxx.xxx;
+tdsLevel=CS_TDS_495
$VAR1 = [];

Testing placeholders with results dbi:Sybase:server=xxx.xxx.xxx.xxx;td
+sLevel=CS_TDS_495
$VAR1 = [
          [
            undef
          ]
        ];

Testing placeholders with no results dbi:Sybase:server=xxx.xxx.xxx.xxx
+;tdsLevel=CS_TDS_495
(hang)

[perlfan]

I'd be worried if it didn't produce the same result.

I was surprised to read this in DBD::Sybase, might be your client lib:

>DBD::Sybase supports the use of ? placeholders in SQL statements as long as the underlying library and database engine supports it. It does this by using what Sybase calls Dynamic SQL. The ? placeholders allow you to write something like: ...