perl-mysql-dbi-alappeldak_magyarazattal.pl

#! /usr/bin/perl
###### IMPORTS ################################################################

use strict;
use warnings;

use DBI;    # ez kell a db kezeleshez

###### CONFIG #################################################################

my $DATABASE = 'myDb';
my $USERNAME = 'user';
my $PASSWORD = 'secret';

my $kulcs1 = 7;
my @kulcs2 = (1,2,3,4);

###### SUBS ###################################################################

# ---- PrepareOnDemand / Ismetelt lekerdezes / Egy soros lekerdezes -----------

my $sth_lid;     # global, amiben taroljuk a prepare-elt lekerdezest majd
sub last_insert_id()
{
    # csak akkor prepare-eli, ha meg nem volt egyszer prepare.
    # Ez optimalizalas a tobbszor futtatott, de azonos SQL utasitasok eseten.
    $sth_lid = $main::dbh->prepare('SELECT LAST_INSERT_ID()') or
    die "Prepare (Example/lid) failed.\n"
    unless defined($sth_lid);
    # futtatja a prepare-elt lekerdezest.
    $sth_lid->execute() or
    die "Execute (Example/lid) failed.\n";

    # ezekbe a valtozokba gyujti ki az infokat
    my ($id,$temp);
    # sql lekerdezes sorain vegigmegy
    while (my @row = $sth_lid->fetchrow_array())
    {
    # ha tobb soros a lekerdezes die-ol (mivel itt csak
    die "Multiple rows (Example/lid).\n" if $temp;
    # eltarolja a lekeres eredmenyenek 0. elemet
    $id = $row[0];
    # itt allitja be, hogy volt mar lekerdezes
    $temp = 1;
    }
    # Ez fontos!!! MYSQL hibakat kezeli.
    die "Database error (Example/lid).\n" if $sth_lid->err();
    # Ez akkor ervenyes, ha a lekerdezesben nem volt egyetlen sor sem.
    die "LAST_INSERT_ID returned empty set (Example/lid).\n"
    unless $temp;

    return $id;
}

###### CODE ###################################################################

# csatlakozik a db-hez
$main::dbh = DBI->connect("DBI:mysql:$DATABASE", $USERNAME, $PASSWORD) or
    die "Cannot connect to database.\n";

# ---- Lekerdezes -- tobb sor -------------------------------------------------
# ahol prepare-ben ? van, az lesz az execute argumentumaban, ez lehet dinamikus is
my $temp = join(', ',map { '?' } @kulcs2);
my $sth = $main::dbh->prepare(
    'SELECT a, b, c FROM dbitest ' .
    "WHERE kulcs1 = ? AND kulcs2 IN($temp)") or
    die "Prepare (Example/1) failed.\n";
$sth->execute($kulcs1,@kulcs2) or
    die "Execute (Example/1) failed.\n";

while (my @row = $sth->fetchrow_array())
{
    # Itt kellene valamit kezdeni az adatokkal...
    # my ($a,$b,$c) = @row;
    print '(' . join(', ',@row) . ")\n";
}
die "Database error (Example/1).\n" if $sth->err();

# ---- Lekerdezes -- egy sor --------------------------------------------------

$sth = $main::dbh->prepare(
    'SELECT a, b, c FROM dbitest WHERE a = ?') or
    die "Prepare (Example/2) failed.\n";
$sth->execute(2) or
    die "Execute (Example/2) failed.\n";

$temp = 0;
my ($a,$b,$c);
while (my @row = $sth->fetchrow_array())
{
    die "Multiple rows (Example/2).\n" if $temp;
    ($a,$b,$c) = @row;
    $temp = 1;
}
die "Database error (Example/2).\n" if $sth->err();
die "Empty set (Example/2).\n" unless $temp;

print "a: $a, b: $b, c: $c\n";

# ---- Lekerdezes -- ismetelt -------------------------------------------------

$sth = $main::dbh->prepare(
    'SELECT a, b, c FROM dbitest WHERE kulcs1 = ?') or
    die "Prepare (Example/3) failed.\n";

foreach my $i (1 .. 5)
{
    $sth->execute($i) or
    die "Execute (Example/3) failed.\n";

    while (my @row = $sth->fetchrow_array())
    {
    # Itt kellene valamit kezdeni az adatokkal...
    # my ($a,$b,$c) = @row;
    print "$i: (" . join(', ',@row) . ")\n";
    }
    die "Database error (Example/3).\n" if $sth->err();
}

# ---- Lekerdezes -- megszakitott ---------------------------------------------

$sth = $main::dbh->prepare(
    'SELECT a, b, c FROM dbitest ORDER BY a') or
    die "Prepare (Example/4) failed.\n";
$sth->execute() or
    die "Execute (Example/4) failed.\n";

while (my @row = $sth->fetchrow_array())
{
    # Itt kellene valamit kezdeni az adatokkal...
    # my ($a,$b,$c) = @row;
    print '(' . join(', ',@row) . ")\n";
    last if ($row[1] =~ /bla/i);
}
die "Database error (Example/4).\n" if $sth->err();
$sth->finish();

# ---- Beszuras -- sima -------------------------------------------------------

$sth = $main::dbh->prepare(
    'INSERT INTO dbitest (b, c, kulcs1, kulcs2) VALUES (?, ?, ?, ?)') or
    die "Prepare (Example/5) failed.\n";
$sth->execute('Retek',23,1,2) or
    die "Execute (Example/5a) failed.\n";
$sth->execute('Karalabe',-1,5,3) or
    die "Execute (Example/5b) failed.\n";

# ---- Beszuras -- id visszaolvasassal ----------------------------------------

$sth = $main::dbh->prepare(
    'INSERT INTO dbitest (b, c, kulcs1, kulcs2) VALUES (?, ?, ?, ?)') or
    die "Prepare (Example/6) failed.\n";
$sth->execute('Repa',77,7,1) or
    die "Execute (Example/6) failed.\n";

# my $id = $main::dbh->last_insert_id(undef,undef,undef,undef) or
#     die "last_insert_id returned false.\n";
my $id = last_insert_id();
print "new id: $id\n";

# ---- Modosotis --------------------------------------------------------------

$sth = $main::dbh->prepare(
    'UPDATE dbitest SET b = ?, c = ? WHERE a = ?') or
    die "Prepare (Example/7) failed.\n";
$temp = $sth->execute('Karfiol',42,$id) or
    die "Execute (Example/7) failed.\n";
print "$temp sor moosova.\n";

# ---- Torles -----------------------------------------------------------------

$sth = $main::dbh->prepare(
    'DELETE FROM dbitest WHERE a = ?') or
    die "Prepare (Example/8) failed.\n";
$sth->execute($id) or
    die "Execute (Example/8) failed.\n";

# ---- Zarolas ----------------------------------------------------------------
# egymas utan tobb reszbol allo, de egy muveletkent felfoghato SQL utasitasok
# eseten zaroljuk a tablakat, h mas ne tudjon ugyan abban a pillanatban olyan
# valtozasokat eszkozolni, ami megvaltoztathatja a lekert adatokat.
# (olvasas eseten irasra, iras eseten ovlvasasra, irasra)

$temp = $main::dbh->do('LOCK TABLES dbitest READ');
die "Database error (Example/L).\n" unless defined($temp);

# ---- Feloldas ---------------------------------------------------------------

$temp = $main::dbh->do('UNLOCK TABLES');
die "Database error (Example/U).\n" unless defined($temp);

# -----------------------------------------------------------------------------

# Ezt ne felejtsuk el, hogy diszkózni kell!
$main::dbh->disconnect() or die "Disconnect failed.\n";

exit 0;