Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Re: DBI recipes

by Abigail-II (Bishop)
on Aug 17, 2003 at 23:51 UTC ( #284458=note: print w/ replies, xml ) Need Help??


in reply to DBI recipes

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


Comment on Re: DBI recipes
Download Code
Re: Re: DBI recipes
by mpeppler (Vicar) on Aug 18, 2003 at 06:13 UTC
    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

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

       

Re: Re: DBI recipes
by gmax (Abbot) on Aug 18, 2003 at 09:53 UTC

    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.
     _  _ _  _  
    (_|| | |(_|><
     _|   
    
      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

      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.

        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...
      I, for one, appreciate that you've focused on just one thing. This stuff is confusing enough as it is.
Re: Re: DBI recipes
by hsmyers (Canon) on Aug 18, 2003 at 14:31 UTC
    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."
      gmax could also just look into the DBI manual page, and fold that into the tutorial.

      Abigail

        <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.
Re: Re: DBI recipes
by Anonymous Monk on Aug 18, 2003 at 16:15 UTC
    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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://284458]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (7)
As of 2014-12-20 04:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (95 votes), past polls