Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Re: fetchall_arrayref hangs with placeholders query with no results

by erix (Prior)
on Apr 15, 2021 at 17:49 UTC ( #11131341=note: print w/replies, xml ) Need Help??


in reply to fetchall_arrayref hangs with placeholders query with no results

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 = ? ;
  • Comment on Re: fetchall_arrayref hangs with placeholders query with no results
  • Download Code

Replies are listed 'Best First'.
Re^2: fetchall_arrayref hangs with placeholders query with no results
by afoken (Canon) 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

    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.

        Still interested, just busy. My problem isn't solved, but I have an okay workaround for now: I'm not using placeholders in any of my queries. The other things that cause the hang (specifying a TDS level and a query that produces no results) aren't really an option to not do. Connecting with no TDS level doesn't work on my production machine.
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?

      Thanks for your suggestion. Unfortunately not passing the hashref produces the same results.
        I was a afraid of that. I didn't have much hope that would work. I did have a lot more hope for using only named tables. I hope that you tried that also?

        This is truly a bizarre symptom! I mean it is not like we are testing some obscure, seldom used feature. A SELECT that returns no data is a completely normal thing to have happen! Geez, you can't claim to have a working DBI interface if you can't handle that!

        Sounds like something related to TDS level is likely to be the root cause: Sybase TDS Level

        I was wondering if there was a way to find out the number of rows that would be returned before asking for the array ref. But no luck. The execute method returns the number of rows affected or the string "0E0" (the "true" but numeric zero value). But that ONLY works for non-Select statements! (like ALTER or whatever).

        The only "super kludge" that I can think of is (a)don't use placeholders - which I guess you are doing now or (b) first run a select with (COUNT *) to get the row count and then re-run the select without the COUNT to get the data if count>0. Of course then your DB performance is now 1/2! That's nasty. Not using placeholders is also not so good because the prepare work has to be done again and again. Without a solution to the TDS level issue, I am afraid options are limited.

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) | |-----------------------------|------------ +---------------|------------------------------|
    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.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (2)
As of 2022-01-16 18:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    In 2022, my preferred method to securely store passwords is:












    Results (49 votes). Check out past polls.

    Notices?