[SOLVED] Perl DBD Postgresql SQL prepare problem

rnturn · 01-15-2018, 10:47 PM

I'm trying to perform a series of selects from a PostgreSQL database using the code below. "$key" is a list of identifiers retrieved from a previous query from a different table.

Code:

$sth = $dbh->prepare( "SELECT path FROM file_paths WHERE key = '?'" );
$sth->bind_param( 1, $key );
$sth->execute;
while ( @rows = $sth->fetchrow_array ) {
    #  Do something clever with the filepaths returned...
}

(Note: I've omitted the ' || die( $sth->errstr )' portion of several of the statements.)

I'm getting the error:

Code:

Statement has no placeholders to bind at ./dbtest line NN.

where "NN" points to the "bind_param" statement.

Any ideas what the correct syntax of this is supposed to be? All the examples I've run across seem to indicate that my "prepare" is written correctly. If I leave off the single quotes around the '?' placeholder--yes... that wouldn't normally be valid SQL syntax---I get no runtime error but no results from the query either.

Ideas?

TIA...

--
Rick

firstfire · 01-15-2018, 11:27 PM

Hi.

I just checked and it looks like you should not quote the question mark (placeholder) -- it works just fine without the quotes. Check the value of the $key variable and make sure your query works in psql.

rnturn · 01-16-2018, 09:58 AM

Quote:

Originally Posted by firstfire

Hi.

I just checked and it looks like you should not quote the question mark (placeholder) -- it works just fine without the quotes. Check the value of the $key variable and make sure your query works in psql.

It does work without the quotes, after all. It turns out the error was in the way I was interpreting the results of 'fetchrow_array' and how it controlled the while().

Thanks, though, for the assist on the the prepare syntax.

I'll mark this one as 'solved'.

sundialsvcs · 01-16-2018, 10:27 AM

In SQL, '?' (in quotes) is "a literal string that coincidentally consists of a single question-mark."

Without the quotes, the symbol is recognized as a placeholder.

I believe that all SQL engines work this way.

Ramurd · 01-26-2018, 11:48 AM

Quote:

Originally Posted by sundialsvcs

In SQL, '?' (in quotes) is "a literal string that coincidentally consists of a single question-mark."

Without the quotes, the symbol is recognized as a placeholder.

I believe that all SQL engines work this way.

Most do; Some want $ or $1, $2 etc instead... makes hard switching (been there, done that, got the tshirt)
And then there's spring in the java world, to make matters more complex.

rnturn · 01-27-2018, 10:22 PM

Quote:

Originally Posted by Ramurd

Most do; Some want $ or $1, $2 etc instead... makes hard switching (been there, done that, got the tshirt)
And then there's spring in the java world, to make matters more complex.

Well, the confusion is that the syntax of 'prepare' wants something a little different than one would code if, say, issuing the query directly in psql.

It's been solved for a while any way.