Beefy Boxes and Bandwidth Generously Provided by pair Networks vroom
Welcome to the Monastery

Before asking a database related question ...

by gmax (Abbot)
on Jun 09, 2003 at 22:47 UTC ( #264485=perlmeditation: print w/ replies, xml ) Need Help??

In addition to the usual advice on how to post and how not to, I feel the need of gathering some specific guidelines concerning questions about database programming. Please feel free of offering additional advice and feedback.

When you are working with a database and you are stuck with a problem, the frustrating thing is that you often don't know where the real issue is. In database applications you are dealing basically with: input from one or more forms, input from files, questioning a database engine, arranging its answers into suitable output and maybe even more complicated side issues. If something goes wrong, the wrongness could be anywhere in between. If you are not comfortable with database programming, you may assume that the problem is there, and perhaps you are right, but to find out the truth, you should develop some good habits.

What follows is a checklist that you should go through before submitting your question to the Monastery. Chances are that this routine will help you find the solution even before clicking the "submit" button.

Know the basics

Database programming in Perl means, basically, dealing with the DBI. There are other less fashionable branches that you can easily ignore unless someone tries to impose them on you.

Knowing how to interact with the DBI is essential. There are a few good Tutorials in the Monastery, which I heartily recommend:

  • Reading from a database will give you the basics of DBI. Once you are familiar with them, you are ready to face the real docs: the DBI docs are the official documentation. The DBI::faq show you the most common questions on this subject 1. Look there: yours may be already in.
  • Tricks with DBI Perhaps an unfortunate name, since it is not tricks, but excellent advanced advice on how to use the DBI. Be familiar with it, and again your understanding of the tool will improve.

1 Also in your own computer: perldoc DBI or perldoc DBI::faq

Stick to good practices

Having learned how to program a database interface, you should stick to the good practice established by the ones who have tried this thing before you.

I'll give you an example.

Early documentation from the DBI asserted that fetchrow_array in scalar context returns the first column of a data set from a SELECT statement. It turned out that it was not accurate, and recent versions of the DBI return a random column instead of the first one. The fix is easy. Instead of the counter intuitive code

my $column = $sth->fetchrow_array();
where you expect to get the first column, you can achieve your goal by using the stricter syntax
my ($column) = $sth->fetchrow_array();
Do it the way you'd do according to normal Perl rules:
my $count = @array; # gets the array size my ($first) = @array # gets the first item

Read the good docs

In addition to the above tutorials, you can get a great deal of experience by reading chromatic's article on DBI.

Also, know where to get more information on the DBI. The official documentation site lists both the good and the bad ones, so you can have your pick.

Search for similar questions

You may not be the first to have faced this problem. If others have asked the same question here, the answer may be waiting for you to read it.

Get acquainted with Super Search. Somebody has spent quite a lot of time to make it useful and efficient. Use it.

However, don't expect to find your question asked with exactly the same words. Be creative. If you are experiencing trouble with inserting a BLOB field, for example, don't focus only on "BLOB," but try "binary column," "binary data," "image", all associated with "database."

If your problem is with a specific database, say Sybase, don't look for that only. Maybe someone has asked the same question for MySQL, DB2, MS SQL, and the answer could be satisfactory for you as well. Don't assume that your particular brand of database has unique features that create unique problems. Most database questions asked in the Monastery can be generalized to embrace all SQL dialects and DBMS architectures. Sometimes it is not the case, but don't be shortsighted to begin with.

Do your testing

Using the DBI properly means sometimes that you lose track of your query. You may have created a query with placeholders, which is the recommended way, and nothing is wrong with it, except that what you see in your script is not what gets passed to the database engine. Or you may have created a complex query using one of the advanced idioms mentioned before.

Either way, your query is refused by the database and you are wondering what is wrong.

Use DBI tracing

Don't despair. The DBI has a wonderful debugging tool, known as tracing.

Just add a call to the trace method before your query is executed and you may see at once what is wrong.

$dbh->trace(2); my $sth=$dbh->prepare(qq{SELECT name from emp where ID = ?}); $sth->execute(qq("ABC123")); $dbh->trace(0);

There is a mistake in this code, and while it is easy to spot for a trained eye, it could be really puzzling for a beginner. However, the tracing mechanism will show the full query as

SELECT name from emp where ID = "'ABC123'";

thus telling you immediately that your parameter was quoted twice. Removing the quotes from the initial query will solve the problem.

Use DBI error checking

Another good source of immediate feedback is the DBI internal error checking system.

Refer to the aforementioned Tricks with DBI for the details.

Test outside the Perl environment

One more thing you can do, to ascertain that the problem is in the database part of the code, is to test your query autonomously, using an external tool (usually provided with your database of choice) to see if the SQL part runs smoothly.

This is important, because you need to rule out the possibility that your query has a SQL syntax error. If that is the case, Perl's responsibility is out of the picture, and all you need to do is checking your database reference manual to get the syntax right.

Present a clean problem

Then, back to the Seekers of Perl Wisdom section. You have gone through the motions of searching for solutions by yourself. You have ruled out the SQL syntax issue, and you are ready to present your case in front of the Experts. Help them to help you, by presenting a clean case. If you submit 400 lines of code full of HTML and references to exotic routines, it will be some time before you find someone with enough patience to look at it and spot the itchy point. Remember that the ones who are good at database programming, usually have a job, and can't spend one hour analyzing your script. Chances are that your script is analyzed by somebody who has plenty of time but not enough experience in this particular field, so you'll get criticized for your regexes, your indentation style, your choice of module X instead of module Y, and while you may feel enlightened by such comments, you haven't advanced one inch toward the solution of your problem.

Find the minimum amount of code

Therefore, spend some time finding the minimum amount of code that replicates your problem, add some test data that triggers the error and you are ready to post.

However, before posting, put yourself in the shoes of a poor Monks who may be reading Newest Nodes and look at the problem as if you were requested to answer it. This is the moment where you are looking not at the original problem, but at an abstraction of it. If you manage to be detached from all the noisy environment that originated your trouble, such as input/output peculiarities, you may be able to find the solution yourself. If not, then post it. You've done everything you could and you can pass the ball to the Monastery.

I am confident that if you apply this method, you'll improve your skills and you will get quicker and more precise answers.

Good luck.

 _  _ _  _  
(_|| | |(_|><

Comment on Before asking a database related question ...
Select or Download Code
Re: Before asking a database related question ...
by rob_au (Abbot) on Jun 10, 2003 at 01:57 UTC
    Of course there is also the excellent The fine art of database programming but I would imagine it would be considered gratuitous if you had linked to this directly :-)

    Excellent post gmax++


    perl -le 'print+unpack"N",pack"B32","00000000000000000000001001101001"'

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://264485]
Approved by larsen
Front-paged by particle
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (8)
As of 2014-04-18 07:05 GMT
Find Nodes?
    Voting Booth?

    April first is:

    Results (462 votes), past polls