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

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". ;-)

Comment on Re^2: fetchall_arrayref hangs with placeholders query with no results Select or Download Code

Replies are listed 'Best First'.
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
`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". ;-)	[reply] [d/l] [select]
Re^5: fetchall_arrayref hangs with placeholders query with no results by mpeppler (Vicar) on Apr 29, 2021 at 12:12 UTC
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	[reply]