Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Re: fetchall_arrayref hangs with placeholders query with no results

by mpeppler (Vicar)
on Apr 28, 2021 at 14:19 UTC ( #11131794=note: print w/replies, xml ) Need Help??


in reply to fetchall_arrayref hangs with placeholders query with no results

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

  • 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 28, 2021 at 16:10 UTC

    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
        The untyped variable ? is allowed only in ...

        That's a problem of missing type information and does not make select ? completely illegal in all SQL implementations, not even in Sybase ASE.

        Try adding type information like I did for PostgreSQL. Actually, just copy my PostgreSQL example and change the connect line to connect to Sybase. I would expect that to work in Sybase ASE.

        Should that fail, try to cast the (untyped) placeholder to a type. According to my first google result, SELECT CAST(? AS NUMERIC) ANSWER should do the trick.

        Alexander

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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (2)
As of 2022-05-22 10:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (80 votes). Check out past polls.

    Notices?