view release on metacpan or  search on metacpan

lib/DBD/MariaDB.pod  view on Meta::CPAN

C<DBI::SQL_SMALLINT()> or C<DBI::SQL_VARCHAR()>.

=item mariadb_type_name

Similar to L<I<mariadb_type>|/mariadb_type>, but type names and not numbers are
returned. Whenever possible, the ANSI SQL name is preferred.

=item mariadb_warning_count

The number of warnings generated during execution of the SQL statement. This
attribute is available on both statement handles and database handles.

=back

=head1 UNICODE SUPPORT

All string orientated variable types (char, varchar, text and similar types) are
represented by the DBD::MariaDB as Unicode strings according to the standard
Perl Unicode model. It means that Perl scalars contain Unicode code points and
not UTF-8 bytes. Internally the DBD::MariaDB uses the MySQL's C<utf8mb4> charset
for the network communication with MariaDB and MySQL servers. It automatically
transforms the network MySQL's C<utf8mb4> charset to the Unicode Perl scalars
and vice-versa.

MySQL's C<utf8mb4> charset for the network communication is configured by
C<MYSQL_SET_CHARSET_NAME> libmariadb/libmysqlclient C library API which is a
requirement to have working L<quote|DBI/quote> method and an emulated client
side placeholders replacement.

Do not try to change network charset (e.g. via SQL command C<SET NAMES>
manually) to anything different then UTF-8 as it would confuse underlying C
library and DBD::MariaDB would misbehave (e.g. would lead to broken/insecure
L<quote|DBI/quote> method or an emulated client side placeholders replacement).

Using a non-UTF-8 charset for a column, table or database is fine because
MariaDB or MySQL server automatically transforms the storage charset to the
charset used by the network protocol (C<utf8mb4>). Note that when DBD::MariaDB
is connecting to the MariaDB or MySQL server it calls SQL command
C<SET character_set_server = 'utf8mb4'> to ensure that the default charset for
new databases would be UTF-8. Beware that a default charset for new tables is
set from a database charset.

In the case MySQL server does not support MySQL's C<utf8mb4> charset for a
network protocol then DBD::MariaDB would try to use MySQL's C<utf8> charset
which is a subset of UTF-8 encoding restricted to the 3 byte UTF-8 sequences.
Support for MySQL's C<utf8mb4> charset was introduced in MySQL server version
5.5.3.

=head2 Working with binary data

Perl scalars do not distinguish between binary I<byte> orientated buffers and
I<Unicode> orientated strings. In Perl it is always up to the caller and the
callee to define in its API if functions and methods expect I<byte> buffers or
I<Unicode> strings. It is not possible (or rather Perl application should not
try) to distinguish if Perl scalar contains a I<byte> buffer or I<Unicode>
string.

When fetching data from MariaDB and MySQL servers, DBD::MariaDB treats all
fields marked with MySQL's charset C<utf8mb4> (and also C<utf8>) as I<Unicode>
strings. Everything else is treated as binary I<byte> oriented buffers.
Therefore, the only difference is that UTF-8 fields are automatically decoded to
Unicode. Binary blob fields remain untouched and corresponding Perl scalars
would contain just ordinals C<0..255> (classic sequence of bytes). Unicode
string scalars would contain sequence of Unicode code points.

There is a small problem with input data, more preciously with SQL statements
and their bind parameters. By definition a SQL statement is a string and
therefore it is expected and handled by DBD::MariaDB as a I<Unicode> string (not
I<byte> oriented buffer). There is no way to treat a SQL statement as a binary,
but this is not a problem. All SQL commands are encoded in ASCII and all ASCII
characters are invariants in UTF-8 (have the same representation as a sequence
of Unicode code points and also when UTF-8 encoded in a byte buffer). For the
remaining part of a SQL statement, placeholders with bind parameters can and
should be used.

=head2 Binary parameters

Unfortunately, neither MariaDB nor MySQL server provide any type information for
prepared SQL statements; therefore, DBD::MariaDB has absolutely no way to know
if a particular bind parameter for a placeholder should be treated as I<Unicode>
string or as I<byte> oriented buffer. So Perl applications which use
DBD::MariaDB must provide information about the correct type.

Moreover, DBI API for L<do|DBI/do>, L<execute|DBI/execute> and all
L<select*|DBI/selectrow_array> methods binds all parameters as C<SQL_VARCHAR>
type. Currently it is an API limitation which does not allow one to specify the
bind type. Varchar is a string and so DBD::MariaDB treats all of them as
I<Unicode> strings.

The only way how to specify a type in DBI is via the
L<bind_param|DBI/bind_param> method. Its third argument takes C<SQL_*> constant
which defines a type for the passed bind parameter.

Following type constants are treated as binary by DBD::MariaDB: C<SQL_BIT>,
C<SQL_BLOB>, C<SQL_BINARY>, C<SQL_VARBINARY>, C<SQL_LONGVARBINARY>.

This approach of handling binary data was implemented in DBD::MariaDB because it
does not violate how Perl's Unicode model is working, follows exactly DBI API
documentation, and, more importantly, is how other DBI drivers (including
L<DBD::Pg|DBD::Pg> and L<DBD::SQLite|DBD::SQLite>) in their recent versions
work. This ensures good compatibility for Perl applications which use multiple
database backends and several DBI drivers.

Please note that the old L<DBD::mysql|DBD::mysql> driver in version 4.041 works
differently and has completely broken Unicode support.

To illustrate the usage, see the following example:

  # Prepare statement
  my $sth = $dbh->prepare(
      'INSERT INTO users (id, name, picture) VALUES (?, ?, ?)'
  );

  # Bind number, 7-bit ASCII values are always in Unicode and binary context
  $sth->bind_param(1, 10);

  # Bind name, may contains Unicode character, in this case U+00E9
  $sth->bind_param(2, "Andr\x{E9}");

  # Bind picture, it is a sequence of binary bytes, not Unicode code points
  $sth->bind_param(3, "\x{D8}\x{A0}\x{39}\x{F8}", DBI::SQL_BINARY);