my $dbh = DBI->connect($DNS, {RaiseError=>1}); my $sth = $dbh->prepare(qq{SELECT product, price, quantity FROM products WHERE quantity < ? }); $sth->execute(100); sub ask_customer { # display product details # ask customer input # do something smart with customer answer } while (my ($product, $price, $quantity) = $sth->fetchrow_array()) { ask_customer ($product, $price, $quantity); } $dbh->disconnect();
+----------------------+ +-----------------------+ | customer | | order | +-----+--------+-------+ +--------+--------+-----+ | ID | name | state | | custID | prodID | qty | +-----+--------+-------+ +--------+--------+-----+ | C01 | Joe | NY | | C02 | P03 | 9 | | C02 | Frank | NY | | C02 | P01 | 2 | | C03 | Bill | TX | | C02 | P02 | 200 | | C04 | Moe | MA | | C05 | P01 | 3 | | C05 | Sue | CA | | C05 | P02 | 450 | +-----+--------+-------+ | C01 | P04 | 5 | | C01 | P03 | 1 | +----------------------+ +--------+--------+-----+ | product | +-----+-------+--------+ | ID | price | name | +-----+-------+--------+ | P01 | 1.90 | hammer | | P02 | 0.05 | nail | | P03 | 5.50 | pliers | | P04 | 4.00 | cutter | +-----+-------+--------+
my @customers = ( [ 'C01', 'Joe', 'NY' ], [ 'C02', 'Frank', 'NY' ], [ 'C03', 'Bill', 'TX' ], [ 'C04', 'Moe', 'MA' ], [ 'C05', 'Sue', 'CA' ] ); my @products = ( [ 'P01', 'hammer', 1.90 ], [ 'P02', 'nail', 0.05 ], [ 'P03', 'pliers', 5.50 ], [ 'P04', 'cutter', 4.00 ] ); my @orders = ( [ 'C02', 'P03', 9 ], [ 'C02', 'P01', 2 ], [ 'C02', 'P02', 200 ], [ 'C05', 'P01', 3 ], [ 'C05', 'P02', 450 ], [ 'C01', 'P04', 4 ], [ 'C01', 'P03', 1 ] );
SELECT cust.name AS customer, prod.name AS product, price, qty, qty*price AS total FROM order INNER JOIN customer cust ON (cust.ID = custID) INNER JOIN product prod ON (prod.ID = prodID) +------------------------------------------+ | query results | +----------+---------+-------+-----+-------+ | customer | product | price | qty | total | +----------+---------+-------+-----+-------+ | Frank | pliers | 5.50 | 9 | 49.50 | | Frank | hammer | 1.90 | 2 | 3.80 | | Frank | nail | 0.05 | 200 | 10.00 | | Sue | hammer | 1.90 | 3 | 5.70 | | Sue | nail | 0.05 | 450 | 22.50 | | Joe | cutter | 4.00 | 5 | 20.00 | | Joe | pliers | 5.50 | 1 | 5.50 | +----------+---------+-------+-----+-------+ SELECT cust.name AS customer, SUM(qty*price) AS total FROM order INNER JOIN customer cust ON (cust.ID = custID) INNER JOIN product prod ON (prod.ID = prodID) GROUP BY customer +------------------+ | query results | +----------+-------+ | customer | total | +----------+-------+ | Frank | 63.30 | | Sue | 28.20 | | Joe | 25.50 | +----------+-------+
my %orders_by_customer = ( 'Frank' => [ { product => 'pliers', qty => 9 }, { product => 'hammer', qty => 2 }, { product => 'nail', qty => 200 } ] , 'Sue' => [ { product => 'hammer', qty => 3 }, { product => 'nail', qty => 450 } ] , 'Joe' => [ { product => 'cutter', qty => 5 }, { product => 'pliers', qty => 1 } ] );
my %orders_by_customer = (); while (my $href = $sth->fetchrow_hashref()) { my %order = ( 'product' => $href->{'product'}, 'qty' => $href->{qty} ); push @{$orders_by_customer{$href->{'customer'}}}, \%order; }
my $surname = 'Jones'; my $query= qq{SELECT name, surname FROM employees WHERE surname = $surnam +e }; my $sth = $dbh->prepare($query); $sth->execute();
my $query= qq{SELECT name, surname FROM employees WHERE surname = +? }; my $sth = $dbh->prepare($query); $sth->execute('Jones');
_ _ _ _ (_|| | |(_|>< _|
An RDBMS is based on set theory, and a particular row has no inherent position in its table (although some database systems fake this).
Hence the very common question: how do I fetch rows 100-120, where the database independant answer is always: use an appropriate WHERE clause.
Michael
SELECT * FROM table LIMIT 99, 21;
SELECT * FROM table ORDER BY col LIMIT 99, 21;
SELECT TOP 10 col FROM table
SELECT col FROM table FETCH FIRST 10 ROWS ONLY
SELECT col FROM table A WHERE 10 > (SELECT COUNT(*) FROM table B WHERE A.col < B.col) ORDER BY col DESC
mysql> describe test_order; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | | PRI | 0 | | | name | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from test_order; #all the records +----+------+ | id | name | +----+------+ | 1 | aaaa | | 2 | bbbb | | 3 | cccc | | 4 | dddd | | 5 | eeee | | 6 | ffff | | 7 | gggg | | 8 | hhhh | | 9 | iiii | | 10 | jjjj | +----+------+ 10 rows in set (0.01 sec) mysql> select * from test_order limit 4,3; #1st request. OK so far +----+------+ | id | name | +----+------+ | 5 | eeee | | 6 | ffff | | 7 | gggg | +----+------+ 3 rows in set (0.00 sec) # now we remove one of the records we were selecting through LIMIT mysql> delete from test_order where id = 5; Query OK, 1 row affected (0.00 sec) # and we insert another record mysql> insert into test_order values (11, "kkkk"); Query OK, 1 row affected (0.00 sec) # Then we insert the deleted record again mysql> insert into test_order values (5, "eeee"); Query OK, 1 row affected (0.00 sec) mysql> select * from test_order limit 4,3; +----+------+ | id | name | +----+------+ | 11 | kkkk | | 6 | ffff | | 7 | gggg | +----+------+ 3 rows in set (0.00 sec)
Despite what gmax says about not being afraid of writing raw SQL and using DBI directly I'd still say that you should try and route all your database access through one or two modules (either tools from CPAN, or home rolled) as a way of limiting your dependencies and avoiding the problem of a single change in the database leading to the Death of a thousand cuts as you trawl your codebase hunting down everything that deals with the affected tables.
The 'performance hit' you'll see as a result of doing this will be nothing compared to increase in 'cost of change' you see when scatter SQL throughout your code like an incontinent puppy. (Guess who's had to work with code like that recently. It's painful.) And then there's testability. When you access the database only through your 'mediator' modules, it becomes possible to drop in a replacement that only pretends to talk to a 'real' database. This can be a remarkably powerful testing technique...
don't do it until you absolutely have to
you should try and route all your database access through one or two modules (either tools from CPAN, or home rolled) as a way of limiting your dependencies
I disagree with this. When designing a database application the structure and relationship of the data is a fundamental. Making it a development afterthought can lead to some big time structural problems as you get hit with new demands on data tooled for code with completly different goals. This would be a lot like advising someone new to object oriented programming not to worry about the relationship between the objects until after they've got all of functionality of the methods worked out so that they can get a clearer picture of how they need to work together.
It's usually not hard to tell applications that have been designed in this manner: Flatfilesque table designs. Lot's of duplication of data to meet specific code needs. No abstraction in the data models. Most of the time you can tell the difficulty of refactoring an application simply by looking at its database schema.
When designing any database application, the first question I ask is what is the information that I need and what is the bast way that I can store it? Once I have those relational models in place things like Classes and their relationships just seem to flow out of it. This approach may be slower in terms of initial development, but you'll avoid a lot of major headaches down the road. It's a lot easier to fix bad code with good data than it is to fix bad data with good code.
People interested in database design should check out the writings of Ralph Kimball.
()-() \"/ `
This is just the XP "You Aren't Gonna Need It" principle in action. Until the code tells me I need a database I won't worry about it. Once I know one is needed I'll take the time to make sure it's well factored and well normalized because that's the only way to keep my cost of change down.
The problem I have with RDBMSes is that the Relational model doesn't really map onto objects and back again that well; What you want is some kind of general solution that you can chuck an arbitrary object at and have it automagically stored, giving you back a magic cookie that you can use to retrieve it later. Given careful design it's possible to work things so that the day to day running of your code doesn't require complex queries (because complex queries are *nasty* with the kind of database that'll stash arbitrary objects).
So, the approach I'm taking with the code I'm working at the moment is to have two different databases. One object database (not yet written, we don't actually need it yet) that doesn't mess around trying to do cunning queries or relational magic, which will probably use a combination of BerkeleyDB and Storable, and a reporting/logging database which will be properly relational, allowing for the kind of reporting and queries that are needed for invoice generation, user reporting and all that jazz.
Speaking of which, the customer is pushing that to the front of the story queue, time to sit down with a bunch of index cards, a handy whiteboard, and the office's token big iron database programmer.
One module I'd like to point out that can come in really useful for cross-database applications is DBIx::AnyDBD. This follows the principle laid out above - knowing your database is extremely important, and so you should have access to it's features.
But beyond that, it ensures that you can always port your application to a new database in a clean and consistent manner, and in a way that your users won't have to care about (apart from changing the DBI connect string). It does this by simply providing a subclass for each database. So you write your default query stuff in a Default.pm file, and put Oracle specific stuff in Oracle.pm, Sybase specific stuff in Sybase.pm, and PostgreSQL specific stuff in Pg.pm. It then uses simple rules at connect time to give you the right class, and it does this correctly with the Windows abstraction layers ODBC and ADO too.
Another point in its favour is that DBIx::AnyDBD is going to be incorporated into DBI core in the next major DBI revision.
