view release on metacpan or  search on metacpan

KB.pm  view on Meta::CPAN

situations in which you will be executing similar data many times, the default
plan will probably work out well. Programs such as KBBouncer which cache connections 
at a low level should not use prepared statements via DBD::KB, or must take 
extra care in the application to account for the fact that prepared statements 
are not shared across database connections. Further discussion on this subject is beyond
the scope of this documentation: please consult the pgsql-performance mailing
list, L<http://archives.postgresql.org/pgsql-performance/>

Only certain commands will be sent to a server-side prepare: currently these
include C<SELECT>, C<INSERT>, C<UPDATE>, and C<DELETE>. DBD::KB uses a simple
naming scheme for the prepared statements themselves: B<dbdpg_XY_Z>, where B<Y> is the current 
PID, B<X> is either 'p' or 'n' (depending on if the PID is a positive or negative 
number), and B<Z> is a number that starts at 1 and increases each time a new statement 
is prepared. This number is tracked at the database handle level, so multiple
statement handles will not collide.

You cannot send more than one command at a time in the same prepare command 
(by separating them with semi-colons) when using server-side prepares.

The actual C<PREPARE> is usually not performed until the first execute is called, due
to the fact that information on the data types (provided by L</bind_param>) may
be provided after the prepare but before the execute.

A server-side prepare may happen before the first L</execute>, but only if the server can
handle the server-side prepare, and the statement contains no placeholders. It will 
also be prepared if the L<pg_prepare_now|/pg_prepare_now (boolean)> attribute is passed in and set to a true 
value. Similarly, the pg_prepare_now attribute can be set to 0 to ensure that
the statement is B<not> prepared immediately, although the cases in which you would
want this are very rare. Finally, you can set the default behavior of all prepare
statements by setting the pg_prepare_now attribute on the database handle:

  $dbh->{pg_prepare_now} = 1;

The following two examples will be prepared right away:

  $sth->prepare("SELECT 123"); ## no placeholders

  $sth->prepare("SELECT 123, ?", {pg_prepare_now => 1});

The following two examples will NOT be prepared right away:

  $sth->prepare("SELECT 123, ?"); ## has a placeholder

  $sth->prepare("SELECT 123", {pg_prepare_now => 0});

There are times when you may want to prepare a statement yourself. To do this,
simply send the C<PREPARE> statement directly to the server (e.g. with
the L</do> method). Create a statement handle and set the prepared name via
the L<pg_prepare_name|/pg_prepare_name (string)> attribute. The statement handle can be created with a dummy
statement, as it will not be executed. However, it should have the same
number of placeholders as your prepared statement. Example:

  $dbh->do('PREPARE mystat AS SELECT COUNT(*) FROM pg_class WHERE reltuples < ?');
  $sth = $dbh->prepare('SELECT ?');
  $sth->bind_param(1, 1, SQL_INTEGER);
  $sth->{pg_prepare_name} = 'mystat';
  $sth->execute(123);

The above will run the equivalent of this query on the backend:

  EXECUTE mystat(123);

which is the equivalent of:

  SELECT COUNT(*) FROM pg_class WHERE reltuples < 123;

You can force DBD::KB to send your query directly to the server by adding
the L<pg_direct|/pg_direct (boolean)> attribute to your prepare call. This is not recommended,
but is added just in case you need it.

=head4 B<Placeholders>

There are three types of placeholders that can be used in DBD::KB. The first is
the "question mark" type, in which each placeholder is represented by a single
question mark character. This is the method recommended by the DBI specs and is the most
portable. Each question mark is internally replaced by a "dollar sign number" in the order
in which they appear in the query (important when using L</bind_param>).

The second type of placeholder is "dollar sign numbers". This is the method
that Postgres uses internally and is overall probably the best method to use
if you do not need compatibility with other database systems. DBD::KB, like
Kingbase, allows the same number to be used more than once in the query.
Numbers must start with "1" and increment by one value (but can appear in any order 
within the query). If the same number appears more than once in a query, it is treated as a 
single parameter and all instances are replaced at once. Examples:

Not legal:

  $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $2'; # Does not start with 1

  $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $3'; # Missing 2

Legal:

  $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1';

  $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2';

  $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $2 AND $1'; # legal but confusing

  $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2 AND reltuples > $1';

  $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1 AND reltuples > $1';

In the final statement above, DBI thinks there is only one placeholder, so this
statement will replace both placeholders:

  $sth->bind_param(1, 2045);

While a simple execute with no bind_param calls requires only a single argument as well:

  $sth->execute(2045);

The final placeholder type is "named parameters" in the format ":foo". While this
syntax is supported by DBD::KB, its use is discouraged in favor of 
dollar-sign numbers.

The different types of placeholders cannot be mixed within a statement, but you may
use different ones for each statement handle you have. This is confusing at best, so 
stick to one style within your program.