Re: DBI recipes

[Abigail-II]

Too bad you aren't checking whether your actions succeed, and neither are you using transactions when you are modifying your database. Now your examples are equivalent of Perl code that isn't strict, doesn't have warnings turned on, and isn't checking the return values of system calls.

Pity.

There's no execuse to not wrap your database modifying statements inside a transaction, and to not rollback on failure.

As for checking for the existance of a table, that's rather easy in Sybase and MSSQL:

    SELECT 1 
      FROM sysobjects
     WHERE name = "whatever"  -- Name of the table
       AND type = "U"         -- User defined table.
                              -- Use "S" for a system table.

Abigail

[gmax]

Thanks for the Sybase syntax.

There's no execuse to not wrap your database modifying statements inside a transaction, and to not rollback on failure.

Nonetheless, I'll give you some.

I had error checking and transaction support in my test script, but I removed them on purpose.

Why?

• This post is focused on idioms, and I didn't want to burden the examples with something that doesn't belong there.
• Error checking is an idiom in itself. You can check for errors by testing the object, by creating a handle sub, by using eval. Depending on your needs, the error checking could take several shapes, and I have approached this subject elsewhere.
• Transactions are not a mandatory DBI feature. The DBI can deal with the big boys (Oracle, MS SQL, IBM DB2, and so on) but it can also handle DBF files, Excel spreadsheets, CSV files, and others where transactions are not supported.
• When transactions are supported, while it is true that every update BELONGS TO a transaction, it is not true that every update IS a transaction (Update meaning that the transaction-related code could be around a group of statements rather than around a single one.) So, in addition to my goal of keeping the code focused on the example, I didn't want to give the impression that the transaction code is necessary for that particular idiom to work.

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

[mpeppler]

When transactions are supported, while it is true that every update BELONGS TO a transaction, it is not true that every insertion IS a transaction.

I'm nitpicking, but with Oracle, DB2, Sybase, etc. every DML operation is run in a transaction. The transaction may automatically commit at the end of the operation (i.e. if AutoCommit is on), but there is always at least an implicit transaction as even a single row insert may in fact generate more operations through triggers (inserts to a shadow table, updates of summary tables, etc.) and these all will be guaranteed to perform as a single operation, even in the absence of explicit transactions in the DML.

That being said I think that keeping the transaction logic out of the examples is a good thing, as long as their use and functionality is explained somewhere in the document - after all transactions are pretty central to RDBMS systems...

Michael

[Abigail-II]

Error checking is an idiom in itself.

Is it? If you were to discuss idiom for reading in a file line-by-line, would you present something like:

    open my $fh, "/path/to/file";
    while (<$fh>) {
        chomp;
        ...
    }

and dismiss the checking of the return value of open "it being an idiom in itself"? Error checking should be part of the idiom, and not something you bolt on later, when you are more experienced.

Abigail
--
Now that you have passed your driving test, let me introduce you to the function of safety belts.

[demerphq]

would you present something like:

Hmm, I guess you would be upset with me if I wrote that as

@ARGV=qw(/path/to/file);
while (<>) {
        chomp;
        ...
}

which of late ive taken to doing in quick and dirty scripts? *grin*


---
demerphq

_{<Elian> And I do take a kind of perverse pleasure in having an OO assembly language...}

[ibm1620]

I, for one, appreciate that you've focused on just one thing. This stuff is confusing enough as it is.

[mpeppler]

DBD::Sybase supports the tables() method.

However, this relies on the existence of the sp_tables stored procedure, which I know exists in most recent versions of Sybase (i.e. 11.x and later), but I don't know if MSSQL has it.

Michael

[EdwardG]

For the record, sp_tables exists in MSSQL versions 7, 2000, and as at 2005-10-14, also in the MSSQL 2005 CTP.

[Anonymous Monk]

Like many monks rewrite code to improve it, I am going to rewrite the first three paragraphs in the attempt to add a touch of tact to them:

Thank you for contributing gmax, that is an impressive start of a cookbook. But ... shouldn't you have incorporated more error checking and transaction support? These are, IMHO, an integral part of DBI - actually, I strongly feel that there's no excuse to not wrap your database modifying statements inside a transaction, and to not rollback on failure. Along those lines, one could accuse your examples of being equivalent to Perl code that isn't strict, doesn't have warnings turned on, and isn't checking the return values of system calls.

By the way, Re: Style, *again* appears to be the only node where you actually thanked someone here. Many, many have thanked you in the past - you should return the favor more often, if nothing more than for being appreciated.

Years ago my mother used to say to me, she'd say, "In this world, Elwood, you must be" -- she always called me Elwood -- "In this world, you must be oh so smart or oh so pleasant." Well, for years I was smart. I recommend pleasant. And you may quote me.

Elwood P. Dowd

[hsmyers]

Abigail---how about a compromise here? You write the section on error handling and gmax folds it into the tutorial?

--hsm

"Never try to teach a pig to sing...it wastes your time and it annoys the pig."

[Abigail-II]

gmax could also just look into the DBI manual page, and fold that into the tutorial.

Abigail

[smitz]

<anonymous comic book guy mode on>
Best. Come. Back. Ever.

<update>
Big fat Llama dung. Looky here at how smitz manages to waste his 100th post.