StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>What sorts of placeholders (if any) are supported <a href="http://search.cpan.org/dist/DBI/DBI.pm#Placeholders_and_Bind_Values" rel="nofollow noreferrer">depends on the driver</a>:</p> <blockquote> <p><strong>Placeholders and Bind Values</strong></p> <p>Some drivers support placeholders and bind values.<br> [...]<br> Some drivers also allow placeholders like <em>:name</em> and <em>:N</em> (e.g., <em>:1</em>, <em>:2</em>, and so on) in addition to <em>?</em>, but their use is not portable.</p> </blockquote> <p>But you're in luck, the <a href="http://search.cpan.org/dist/DBD-Pg/Pg.pm#Placeholders" rel="nofollow noreferrer">PostgreSQL driver</a> supports named or numbered parameters:</p> <blockquote> <p>There are three types of placeholders that can be used in DBD::Pg. The first is the "question mark" type, in which each placeholder is represented by a single question mark character.<br> [...]<br> The method second type of placeholder is "dollar sign numbers".<br> [...]<br> The final placeholder type is "named parameters" in the format ":foo".</p> </blockquote> <p>And the <a href="http://search.cpan.org/dist/DBD-SQLite/lib/DBD/SQLite.pm#Placeholders" rel="nofollow noreferrer">SQLite driver</a> also supports them:</p> <blockquote> <p>SQLite supports several placeholder expressions, including ? and :AAAA.</p> </blockquote> <p>The downside is that you'll end up using <code>bind_param</code> a lot with the named parameters so you won't be able to use conveniences like <code>selectcol_arrayref</code> and <code>$sth->execute(1,2,3)</code> (<strong>Note:</strong> If anyone knows how to use named placeholders with <code>execute</code> I'd appreciate some pointers in a comment, I've never figured out how to do it). However, you can use the various forms of number placeholders (such as <code>select c from t where x = $1</code> for PostgreSQL or <code>select c from t where x = ?1</code> for SQLite).</p> <p>Also be aware that PostgreSQL uses colons for array slices and question marks for some operators so sometimes the standard <em>?</em> placeholders and <em>:name</em> named placeholders can cause problems. I've never had any problems with <em>?</em> but I've never used the <a href="http://www.postgresql.org/docs/current/interactive/functions-geometry.html" rel="nofollow noreferrer">geometric operators</a> either; I suspect that sensible use of whitespace would avoid any problems with <em>?</em>. If you're not using PostgreSQL arrays, then you probably don't have to worry about <a href="http://www.postgresql.org/docs/current/interactive/arrays.html#ARRAYS-ACCESSING" rel="nofollow noreferrer">array slices</a> fighting with your <code>:name</code> named placeholders.</p> <hr> <p><strong>Executive Summary</strong>: You can't use named placeholders with <code>selectcol_arrayref</code> or similar methods that work with <code>@bind_params</code>. However, with SQLite and Postgresql, you can use numbered placeholders (<code>$1</code>, <code>$2</code>, ... for Postgresql or <code>?1</code>, <code>?2</code>, ... for SQLite) with the methods that work with <code>@bind_params</code> or you can use named placeholders (<code>:name</code> for both PostgreSQL and SQLite) if you're happy using the longer <code>prepare</code>/<code>bind_param</code>/<code>execute</code>/<code>fetch</code> sequence of methods and you'll have to be careful if you use PostgreSQL arrays in your queries.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload