fetchall_arrayref hangs with placeholders query with no results

TieUpYourCamel has asked for the wisdom of the Perl Monks concerning the following question:

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
[download]

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
[download]

Install DBI and Sybase

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

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();
}
[download]

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

Comment on fetchall_arrayref hangs with placeholders query with no results Select or Download Code

Replies are listed 'Best First'.
Re: fetchall_arrayref hangs with placeholders query with no results by erix (Prior) on Apr 15, 2021 at 17:49 UTC
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 = ? ;` [download]	[reply] [d/l]
Re^2: fetchall_arrayref hangs with placeholders query with no results by afoken (Chancellor) on Apr 18, 2021 at 10:46 UTC
Maybe having no table confuses the system? It should not. Executing a table-less query like `SELECT 42 AS result` [download] 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` [download] 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 -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]
Re^3: fetchall_arrayref hangs with placeholders query with no results by erix (Prior) on Apr 18, 2021 at 11:18 UTC
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.	[reply]
Re^4: fetchall_arrayref hangs with placeholders query with no results by TieUpYourCamel (Scribe) on Apr 21, 2021 at 15:21 UTC
Re^2: fetchall_arrayref hangs with placeholders query with no results by Marshall (Canon) on Apr 18, 2021 at 01:26 UTC
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?	[reply] [d/l] [select]
Re^3: fetchall_arrayref hangs with placeholders query with no results by TieUpYourCamel (Scribe) on Apr 21, 2021 at 16:20 UTC
Thanks for your suggestion. Unfortunately not passing the hashref produces the same results.	[reply]
Re^4: fetchall_arrayref hangs with placeholders query with no results by Marshall (Canon) on Apr 23, 2021 at 00:25 UTC
Re^2: fetchall_arrayref hangs with placeholders query with no results by TieUpYourCamel (Scribe) on Apr 21, 2021 at 15:16 UTC
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) \| \|-----------------------------\|------------ +---------------\|------------------------------\| [download] 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. [download] 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.	[reply] [d/l] [select]
Re: fetchall_arrayref hangs with placeholders query with no results by afoken (Chancellor) on Apr 23, 2021 at 16:03 UTC
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 -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]
Re: fetchall_arrayref hangs with placeholders query with no results by mpeppler (Vicar) on Apr 28, 2021 at 14:19 UTC
I think there are two problems - but the most important is that the syntax `select ? from ... where ..` [download] 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	[reply] [d/l]
Re^2: fetchall_arrayref hangs with placeholders query with no results by afoken (Chancellor) on Apr 28, 2021 at 16:10 UTC
the syntax `select ? from ... where ..` [download] 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();` [download] `'42' 1 rows` [download] 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();` [download] `42 1 rows` [download] Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]
Re^3: fetchall_arrayref hangs with placeholders query with no results by mpeppler (Vicar) on Apr 29, 2021 at 07:40 UTC
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` [download]	[reply] [d/l]
Re^4: fetchall_arrayref hangs with placeholders query with no results by afoken (Chancellor) on Apr 29, 2021 at 10:39 UTC
Re^5: fetchall_arrayref hangs with placeholders query with no results by mpeppler (Vicar) on Apr 29, 2021 at 12:12 UTC
Re: fetchall_arrayref hangs with placeholders query with no results by perlfan (Vicar) on Apr 15, 2021 at 17:13 UTC
How does `selectall_arrayref` work out? This necessarily uses `fetchall_arrayref`.	[reply] [d/l] [select]
Re^2: fetchall_arrayref hangs with placeholders query with no results by TieUpYourCamel (Scribe) on Apr 15, 2021 at 17:38 UTC
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);` [download] 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) [download]	[reply] [d/l] [select]
Re^3: fetchall_arrayref hangs with placeholders query with no results by perlfan (Vicar) on Apr 15, 2021 at 17:57 UTC
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: ...	[reply]
Re^4: fetchall_arrayref hangs with placeholders query with no results by Anonymous Monk on Apr 15, 2021 at 21:23 UTC


go ahead... be a heretic
	PerlMonks