5911N/A================================================================================

5911N/A================================ Install mysql =================================

5911N/A================================================================================

5911N/A

5911N/A$ pkg install database/mysql-56 database/mysql-56/client

5911N/A

5911N/A

5911N/A

5911N/A================================================================================

5911N/A========================== Init database & run daemon ==========================

5911N/A================================================================================

5911N/A

5911N/A#!/bin/bash

5911N/A

5911N/Aset -x

5911N/A

5911N/AMYSQL_TEST_DATADIR=/var/tmp/mysql

5911N/AMYSQL_VERSION=5.6

5911N/AMYSQL_BINDIR=/usr/mysql/$MYSQL_VERSION/bin

5911N/AMYSQL_TEST_USER=root

5911N/AMYSQL_TEST_PASSWORD=new-password

5911N/A

5911N/Arm -rf "$MYSQL_TEST_DATADIR"

5911N/A

5911N/A"$MYSQL_BINDIR/mysql_install_db" \

5911N/A --keep-my-cnf \

5911N/A --datadir="$MYSQL_TEST_DATADIR" \

5911N/A --basedir=/usr/mysql/$MYSQL_VERSION

5911N/A

5911N/A# Run the daemon in background.

5911N/A# --gdb makes it possible to terminate mysqld via Ctrl+C

5911N/A"$MYSQL_BINDIR/mysqld" \

5911N/A --skip-networking \

5911N/A -u $MYSQL_TEST_USER \

5911N/A --datadir="$MYSQL_TEST_DATADIR" \

5911N/A --socket="$MYSQL_TEST_DATADIR"/socket \

5911N/A --pid-file="$MYSQL_TEST_DATADIR"/pid \

5911N/A --gdb &

5911N/A

5911N/Asleep 10 # wait for db to come up

5911N/A

5911N/A"$MYSQL_BINDIR/mysqladmin" \

5911N/A --socket "$MYSQL_TEST_DATADIR"/socket \

5911N/A -u "$MYSQL_TEST_USER" \

5911N/A password "$MYSQL_TEST_PASSWORD"

5911N/A

5911N/A

5911N/A

5911N/A================================================================================

5911N/A============ In perl craete DB, table, trigger and insert some data ============

5911N/A================================================================================

5911N/A

5911N/A#!/usr/perl5/5.22/bin/perl

5911N/A

5911N/Ause DBI;

5911N/A

5911N/Amy $dbname="perl_test_db";

5911N/A

5911N/A$dsn = "DBI:mysql:;mysql_socket=/var/tmp/mysql/socket;";

5911N/A$dbh = DBI->connect($dsn, 'root', 'new-password');

5911N/A

5911N/A# List all databases available

5911N/Asay STDOUT join " : ", $dbh->func('_ListDBs');

5911N/A

5911N/A# Call mysql specific function

5911N/Abless ($dbh, "DBD::mysql::db");

5911N/A$dbh->admin('createdb', $dbname);

5911N/A

5911N/A# Connect again, this time directly to DB

5911N/A$dsn = "DBI:mysql:$dbname;mysql_socket=/var/tmp/mysql/socket;";

5911N/A$dbh = DBI->connect($dsn, 'root', 'new-password');

5911N/A

6532N/Aeval { $dbh->do("DROP TABLE IF EXISTS foo") };

5911N/A

5911N/A$dbh->do("CREATE TABLE foo (id INTEGER, name VARCHAR(20))");

5911N/A

5911N/A$dbh->do("

5911N/ACREATE TRIGGER only_even

5911N/A BEFORE INSERT ON foo FOR EACH ROW

5911N/A BEGIN

5911N/A IF NEW.id % 2 = 1

5911N/A THEN

5911N/A SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insert only odd numbers!';

5911N/A ELSE

5911N/A SET NEW.id = NEW.id * 5;

5911N/A END IF;

5911N/A END;

5911N/A");

5911N/A

5911N/A# First fails, it's odd number

5911N/A$dbh->do("INSERT INTO foo VALUES (1, " . $dbh->quote("Tim") . ")");

5911N/A$dbh->do("INSERT INTO foo VALUES (?, ?)", undef, 2, "Jochen");

5911N/A$dbh->do("INSERT INTO foo VALUES (4, " . $dbh->quote("Pete") . ")");

5911N/A

5911N/Amy $sth = $dbh->prepare("SELECT * FROM foo");

5911N/A$sth->execute();

5911N/Awhile (my $ref = $sth->fetchrow_hashref()) {

5911N/A print "Found a row: id = $ref->{'id'}, name = $ref->{'name'}\n";

5911N/A}

5911N/A$sth->finish();

5911N/A

5911N/A$dbh->disconnect();

5911N/A

5911N/A

5911N/A

5911N/A================================================================================

5911N/A============================== Perl script output ==============================

5911N/A================================================================================

5911N/A

5911N/Ainformation_schema : mysql : performance_schema : test

5911N/ADBD::mysql::db do failed: Insert only odd numbers! at ./a.pl line 39.

5911N/AFound a row: id = 10, name = Jochen

5911N/AFound a row: id = 20, name = Pete

5911N/A

5911N/A(please note that first line may vary, it just lists all currently available

5911N/Adatabases)

5911N/A

5911N/A

5911N/A

5911N/A================================================================================

5911N/A===================== Verify from shell the data are there =====================

5911N/A================================================================================

5911N/A

5911N/A$ echo 'select * from foo;' |

5911N/A mysql --socket=/var/tmp/mysql/socket -u root --password=new-password perl_test_db

5911N/A

5911N/AWarning: Using a password on the command line interface can be insecure.

5911N/Aid name

5911N/A10 Jochen

5911N/A20 Pete

5911N/A