Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

fetchall_arrayref hangs with placeholders query with no results

by TieUpYourCamel (Scribe)
on Apr 15, 2021 at 14:39 UTC ( #11131333=perlquestion: print w/replies, xml ) Need Help??

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
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

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 = ? ;
      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

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

        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.

      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?

        Thanks for your suggestion. Unfortunately not passing the hashref produces the same results.
      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.
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 ..
    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

      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

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
        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
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". ;-)
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.
      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)
        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: ...

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11131333]
Approved by haukex
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (9)
As of 2023-11-28 22:35 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?