Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Re^3: fetchall_arrayref hangs with placeholders query with no results

by mpeppler (Vicar)
on Apr 29, 2021 at 07:40 UTC ( #11131849=note: print w/replies, xml ) Need Help??


in reply to Re^2: fetchall_arrayref hangs with placeholders query with no results
in thread fetchall_arrayref hangs with placeholders query with no results

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

Replies are listed 'Best First'.
Re^4: fetchall_arrayref hangs with placeholders query with no results
by afoken (Canon) on Apr 29, 2021 at 10:39 UTC
    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". ;-)
      No - that syntax is indeed illegal in Sybase ASE (and I suspect MS-SQL).

      This has nothing to do with the placeholder type - it's because the main point of placeholders is performance - it's so that you can re-use a SQL statement many times with different parameters without having to re-compile and re-optimize it. With a complex SQL query this can make a very big difference in performance (SQL parsing and optimization can take many 100s of milliseconds, much more that the actual fetching of the data on a large system).

      With Sybase and MS-SQL what happens is that DBD::Sybase detects that one or more ?-style placeholders are in the SQL statement. This is then passed to the ct_dynamic() API, which performs the "prepare" step on the server, identifies the parameter types (based on the column types in the WHERE clause) and returns a handle to this prepared statement. This is now basically a temporary stored procedure.

      DBD::Sybase has never done any client-side placeholder processing.

      Personally I fail to see the point of using placeholders in the SELECT clause, unless there's some weird variable quoting issue that the OP wants to handle.

      BTW - the DBI docs also state that ?-style placeholders will normally only work in WHERE or VALUES clauses: https://metacpan.org/pod/DBI#Placeholders-and-Bind-Values

      Michael

Log In?
Username:
Password:

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

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



    Results (80 votes). Check out past polls.

    Notices?