Acesso ao banco de dados Perl

Este tutorial irá ensiná-lo a acessar um banco de dados dentro de seu script Perl. A partir do Perl 5, tornou-se muito fácil escrever aplicativos de banco de dados usandoDBImódulo. DBI significaDatabase Independent Interface para Perl, o que significa que o DBI fornece uma camada de abstração entre o código Perl e o banco de dados subjacente, permitindo que você alterne as implementações do banco de dados com facilidade.

O DBI é um módulo de acesso ao banco de dados para a linguagem de programação Perl. Ele fornece um conjunto de métodos, variáveis ​​e convenções que fornecem uma interface de banco de dados consistente, independente do banco de dados real que está sendo usado.

Arquitetura de um aplicativo DBI

DBI é independente de qualquer banco de dados disponível no backend. Você pode usar DBI se estiver trabalhando com Oracle, MySQL ou Informix, etc. Isso fica claro no diagrama de arquitetura a seguir.

Aqui, o DBI é responsável por obter todos os comandos SQL por meio da API (ou seja, interface de programação de aplicativo) e despachá-los para o driver apropriado para execução real. E, por fim, o DBI é responsável por pegar os resultados do driver e devolvê-los ao script de chamada.

Notação e Convenções

Ao longo deste capítulo, serão usadas as anotações a seguir e é recomendável que você também siga a mesma convenção.

$dsn    Database source name
  $dbh    Database handle object
  $sth    Statement handle object
  $h      Any of the handle types above ($dbh, $sth, or $drh)
  $rc     General Return Code  (boolean: true=ok, false=error)
  $rv     General Return Value (typically an integer)
  @ary    List of values returned from the database.
  $rows   Number of rows processed (if available, else -1)
  $fh     A filehandle
  undef   NULL values are represented by undefined values in Perl
  \%attr  Reference to a hash of attribute values passed to methods

Conexão de banco de dados

Supondo que vamos trabalhar com banco de dados MySQL. Antes de conectar-se a um banco de dados, certifique-se do seguinte. Você pode obter ajuda de nosso tutorial MySQL caso não saiba como criar banco de dados e tabelas em banco de dados MySQL.

• Você criou um banco de dados com o nome TESTDB.

• Você criou uma tabela com o nome TEST_TABLE em TESTDB.

• Esta tabela contém os campos FIRST_NAME, LAST_NAME, AGE, SEX e INCOME.

• ID de usuário "testuser" e senha "test123" são configurados para acessar TESTDB

• O Módulo Perl DBI está instalado corretamente em sua máquina.

• Você passou pelo tutorial do MySQL para entender os fundamentos do MySQL.

A seguir está o exemplo de conexão com o banco de dados MySQL "TESTDB"

#!/usr/bin/perl

use DBI
use strict;

my $driver = "mysql"; 
my $database = "TESTDB";
my $dsn = "DBI:$driver:database=$database";
my $userid = "testuser";
my $password = "test123";

my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

Se uma conexão for estabelecida com a fonte de dados, um identificador de banco de dados é retornado e salvo em $ dbh para uso posterior, caso contrário, $ dbh é definido com o valor undef e $ DBI :: errstr retorna uma string de erro.

Operação INSERT

A operação INSERT é necessária quando você deseja criar alguns registros em uma tabela. Aqui, estamos usando a tabela TEST_TABLE para criar nossos registros. Assim, uma vez que nossa conexão com o banco de dados seja estabelecida, estamos prontos para criar registros em TEST_TABLE. A seguir está o procedimento para criar um único registro em TEST_TABLE. Você pode criar quantos registros quiser usando o mesmo conceito.

A criação do registro segue as seguintes etapas

• Prearing instrução SQL com instrução INSERT. Isso será feito usandoprepare() API.

• Execução de consulta SQL para selecionar todos os resultados do banco de dados. Isso será feito usandoexecute() API.

• Liberando a alça de instrução. Isso será feito usandofinish() API

• Se tudo correr bem então commit esta operação, caso contrário, você pode rollbacktransação completa. Commit e Rollback são explicados nas próximas seções.

my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
                       (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )
                        values
                       ('john', 'poul', 'M', 30, 13000)");
$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Usando valores Bind

Pode haver um caso em que os valores a serem inseridos não sejam fornecidos com antecedência. Portanto, você pode usar variáveis ​​de ligação que assumirão os valores necessários em tempo de execução. Os módulos Perl DBI usam um ponto de interrogação no lugar do valor real e, em seguida, os valores reais são passados ​​pela API execute () no tempo de execução. A seguir está o exemplo:

my $first_name = "john";
my $last_name = "poul";
my $sex = "M";
my $income = 13000;
my $age = 30;
my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
                       (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )
                        values
                       (?,?,?,?)");
$sth->execute($first_name,$last_name,$sex, $age, $income) 
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Operação READ

A operação READ em qualquer banco de dados significa buscar algumas informações úteis do banco de dados, ou seja, um ou mais registros de uma ou mais tabelas. Assim, uma vez que nossa conexão com o banco de dados seja estabelecida, estamos prontos para fazer uma consulta neste banco de dados. A seguir está o procedimento para consultar todos os registros com IDADE maior que 20. Isso levará quatro etapas

• Removendo a consulta SQL SELECT com base nas condições necessárias. Isso será feito usandoprepare() API.

• Execução de consulta SQL para selecionar todos os resultados do banco de dados. Isso será feito usandoexecute() API.

• Buscando todos os resultados um por um e imprimindo-os. Isso será feito usando fetchrow_array() API.

• Liberando a alça de instrução. Isso será feito usandofinish() API

my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME
                        FROM TEST_TABLE 
                        WHERE AGE > 20");
$sth->execute() or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;
while (my @row = $sth->fetchrow_array()) {
   my ($first_name, $last_name ) = @row;
   print "First Name = $first_name, Last Name = $last_name\n";
}
$sth->finish();

Usando valores Bind

Pode haver um caso em que a condição não seja dada com antecedência. Portanto, você pode usar variáveis ​​de ligação que assumirão os valores necessários em tempo de execução. Os módulos Perl DBI usam um ponto de interrogação no lugar do valor real e, em seguida, os valores reais são passados ​​pela API execute () no tempo de execução. A seguir está o exemplo:

$age = 20;
my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME
                        FROM TEST_TABLE
                        WHERE AGE > ?");
$sth->execute( $age ) or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;
while (my @row = $sth->fetchrow_array()) {
   my ($first_name, $last_name ) = @row;
   print "First Name = $first_name, Last Name = $last_name\n";
}
$sth->finish();

Operação ATUALIZAR

ATUALIZAR Operação em qualquer banco de dados significa atualizar um ou mais registros já disponíveis nas tabelas do banco de dados. A seguir está o procedimento para atualizar todos os registros que têm SEXO como 'M'. Aqui, aumentaremos a IDADE de todos os homens em um ano. Isso levará três etapas

• Remoção de consulta SQL com base nas condições exigidas. Isso será feito usandoprepare() API.

• Execução de consulta SQL para selecionar todos os resultados do banco de dados. Isso será feito usandoexecute() API.

• Liberando a alça de instrução. Isso será feito usandofinish() API

• Se tudo correr bem então commit esta operação, caso contrário, você pode rollbacktransação completa. Consulte a próxima seção para APIs de confirmação e reversão.

my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   AGE = AGE + 1 
                        WHERE SEX = 'M'");
$sth->execute() or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Usando valores Bind

Pode haver um caso em que a condição não seja dada com antecedência. Portanto, você pode usar variáveis ​​de ligação que assumirão os valores necessários em tempo de execução. Os módulos Perl DBI usam um ponto de interrogação no lugar do valor real e, em seguida, os valores reais são passados ​​pela API execute () no tempo de execução. A seguir está o exemplo:

$sex = 'M';
my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   AGE = AGE + 1
                        WHERE SEX = ?");
$sth->execute('$sex') or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Em alguns casos, você gostaria de definir um valor que não é fornecido com antecedência, para que possa usar o valor obrigatório da seguinte maneira. Neste exemplo, a renda de todos os homens será definida como 10.000.

$sex = 'M';
$income = 10000;
my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   INCOME = ?
                        WHERE SEX = ?");
$sth->execute( $income, '$sex') or die $DBI::errstr;
print "Number of rows updated :" + $sth->rows;
$sth->finish();

Operação DELETE

A operação DELETE é necessária quando você deseja excluir alguns registros de seu banco de dados. A seguir está o procedimento para deletar todos os registros de TEST_TABLE onde AGE é igual a 30. Esta operação irá realizar as seguintes etapas.

• Remoção de consulta SQL com base nas condições exigidas. Isso será feito usandoprepare() API.

• Execução de consulta SQL para excluir registros necessários do banco de dados. Isso será feito usandoexecute() API.

• Liberando a alça de instrução. Isso será feito usandofinish() API

• Se tudo correr bem então commit esta operação, caso contrário, você pode rollback transação completa.

$age = 30;
my $sth = $dbh->prepare("DELETE FROM TEST_TABLE
                        WHERE AGE = ?");
$sth->execute( $age ) or die $DBI::errstr;
print "Number of rows deleted :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Usando do Declaração

Se você estiver fazendo um UPDATE, INSERT ou DELETE, não haverá dados que retornem do banco de dados, portanto, há um atalho para realizar esta operação. Você pode usardo para executar qualquer um dos comandos a seguir.

$dbh->do('DELETE FROM TEST_TABLE WHERE age =30');

doretorna um valor verdadeiro se tiver êxito e um valor falso se falhar. Na verdade, se for bem-sucedido, ele retorna o número de linhas afetadas. No exemplo, ele retornaria o número de linhas que foram realmente excluídas.

Operação COMMIT

Commit é a operação que dá um sinal verde ao banco de dados para finalizar as alterações e, após esta operação, nenhuma alteração pode ser revertida para sua posição original.

Aqui está um exemplo simples para chamar commit API.

$dbh->commit or die $dbh->errstr;

Operação ROLLBACK

Se você não estiver satisfeito com todas as alterações ou se encontrar um erro no meio de qualquer operação, você pode reverter essas alterações para usar rollback API.

Aqui está um exemplo simples para chamar rollback API.

$dbh->rollback or die $dbh->errstr;

Iniciar transação

Muitos bancos de dados oferecem suporte a transações. Isso significa que você pode fazer um monte de consultas que modificam os bancos de dados, mas nenhuma das mudanças é realmente feita. Então, no final, você emite a consulta SQL especialCOMMIT, e todas as alterações são feitas simultaneamente. Alternativamente, você pode emitir a consulta ROLLBACK, caso em que todas as alterações são descartadas e o banco de dados permanece inalterado.

Módulo Perl DBI fornecido begin_workAPI, que ativa as transações (desativando o AutoCommit) até a próxima chamada para confirmar ou reverter. Após o próximo commit ou rollback, o AutoCommit será automaticamente ativado novamente.

$rc  = $dbh->begin_work  or die $dbh->errstr;

Opção de AutoCommit

Se suas transações são simples, você pode evitar o trabalho de emitir muitos commits. Ao fazer a chamada de conexão, você pode especificar umAutoCommitopção que executará uma operação de confirmação automática após cada consulta bem-sucedida. Esta é a aparência:

my $dbh = DBI->connect($dsn, $userid, $password,
              {AutoCommit => 1}) 
              or die $DBI::errstr;

Aqui, o AutoCommit pode assumir o valor 1 ou 0, onde 1 significa que o AutoCommit está ativado e 0 significa que o AutoCommit está desativado.

Tratamento automático de erros

Ao fazer a chamada de conexão, você pode especificar uma opção RaiseErrors que trata os erros para você automaticamente. Quando ocorre um erro, o DBI aborta seu programa em vez de retornar um código de falha. Se tudo o que você deseja é abortar o programa em um erro, isso pode ser conveniente. Esta é a aparência:

my $dbh = DBI->connect($dsn, $userid, $password,
              {RaiseError => 1})
              or die $DBI::errstr;

Aqui, RaiseError pode assumir o valor 1 ou 0.

Desconectando banco de dados

Para desconectar a conexão do banco de dados, use disconnect API da seguinte forma:

$rc = $dbh->disconnect  or warn $dbh->errstr;

O comportamento da transação do método de desconexão é, infelizmente, indefinido. Alguns sistemas de banco de dados (como Oracle e Ingres) confirmarão automaticamente todas as alterações pendentes, mas outros (como Informix) reverterão quaisquer mudanças pendentes. Os aplicativos que não usam o AutoCommit devem chamar explicitamente o commit ou rollback antes de chamar a desconexão.

Usando valores NULL

Valores indefinidos, ou undef, são usados ​​para indicar valores NULL. Você pode inserir e atualizar colunas com um valor NULL como faria com um valor não NULL. Estes exemplos inserem e atualizam a idade da coluna com um valor NULL:

$sth = $dbh->prepare(qq{
       INSERT INTO TEST_TABLE (FIRST_NAME, AGE) VALUES (?, ?)
       });
 $sth->execute("Joe", undef);

Aqui qq{} é usado para retornar uma string entre aspas para prepareAPI. No entanto, deve-se ter cuidado ao tentar usar valores NULL em uma cláusula WHERE. Considerar:

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

Vincular um undef (NULL) ao espaço reservado não selecionará as linhas que têm uma idade NULL! Pelo menos para mecanismos de banco de dados que estejam em conformidade com o padrão SQL. Consulte o manual SQL do seu mecanismo de banco de dados ou qualquer livro SQL para obter as razões para isso. Para selecionar NULLs explicitamente, você deve dizer "WHERE age IS NULL".

Um problema comum é fazer com que um fragmento de código manipule um valor que pode ser definido ou undef (não NULL ou NULL) em tempo de execução. Uma técnica simples é preparar a instrução apropriada conforme necessário e substituir o marcador de posição para casos não NULL:

$sql_clause = defined $age? "age = ?" : "age IS NULL";
$sth = $dbh->prepare(qq{
       SELECT FIRST_NAME FROM TEST_TABLE WHERE $sql_clause
       });
$sth->execute(defined $age ? $age : ());

Algumas outras funções DBI

drivers_disponíveis

@ary = DBI->available_drivers;
@ary = DBI->available_drivers($quiet);

Retorna uma lista de todos os drivers disponíveis procurando por módulos DBD :: * nos diretórios em @INC. Por padrão, um aviso é dado se alguns drivers forem ocultados por outros com o mesmo nome em diretórios anteriores. Passar um valor verdadeiro para $ quiet inibirá o aviso.

installed_drivers

%drivers = DBI->installed_drivers();

Retorna uma lista de nomes de driver e pares de identificadores de driver para todos os drivers 'instalados' (carregados) no processo atual. O nome do driver não inclui o prefixo 'DBD ::'.

fontes de dados

@ary = DBI->data_sources($driver);

Retorna uma lista de fontes de dados (bancos de dados) disponíveis por meio do driver nomeado. Se $ driver estiver vazio ou undef, o valor da variável de ambiente DBI_DRIVER será usado.

citar

$sql = $dbh->quote($value);
$sql = $dbh->quote($value, $data_type);

Cite uma string literal para usar como valor literal em uma instrução SQL, escapando de quaisquer caracteres especiais (como aspas) contidos na string e adicionando o tipo necessário de aspas externas.

$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
                $dbh->quote("Don't");

Para a maioria dos tipos de banco de dados, aspas retornariam 'Não' (incluindo as aspas externas). É válido para o método quote () retornar uma expressão SQL avaliada como a string desejada. Por exemplo:

$quoted = $dbh->quote("one\ntwo\0three")

may produce results which will be equivalent to

CONCAT('one', CHAR(12), 'two', CHAR(0), 'three')

Métodos comuns a todos os identificadores

errar

$rv = $h->err;
or
$rv = $DBI::err
or
$rv = $h->err

Retorna o código de erro do mecanismo de banco de dados nativo do último método de driver chamado. O código é normalmente um número inteiro, mas você não deve presumir isso. Isso é equivalente a $ DBI :: err ou $ h-> err.

errstr

$str = $h->errstr;
or
$str = $DBI::errstr
or
$str = $h->errstr

Retorna a mensagem de erro do mecanismo de banco de dados nativo do último método DBI chamado. Isso tem os mesmos problemas de vida útil que o método "errar" descrito acima. Isso é equivalente a $ DBI :: errstr ou $ h-> errstr.

filas

$rv = $h->rows;
or
$rv = $DBI::rows

Isso retorna o número de linhas afetadas pela instrução SQL anterior e equivalente a $ DBI :: linhas.

vestígio

$h->trace($trace_settings);

O DBI possui uma capacidade extremamente útil de gerar informações de rastreamento de tempo de execução do que está fazendo, o que pode economizar muito tempo ao tentar rastrear problemas estranhos em seus programas DBI. Você pode usar valores diferentes para definir o nível de rastreamento. Esses valores variam de 0 a 4. O valor 0 significa desabilitar o rastreio e 4 significa gerar o rastreio completo.

Declarações interpoladas são proibidas

É altamente recomendável não usar instruções interpoladas da seguinte forma:

while ($first_name = <>) {
   my $sth = $dbh->prepare("SELECT * 
                            FROM TEST_TABLE 
                            WHERE FIRST_NAME = '$first_name'");
   $sth->execute();
   # and so on ...
}

Portanto, não use declaração interpolada em vez de usar bind value para preparar a instrução SQL dinâmica.