Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

update database question

by wertert (Sexton)
on Jun 23, 2005 at 13:14 UTC ( #469377=perlquestion: print w/replies, xml ) Need Help??
wertert has asked for the wisdom of the Perl Monks concerning the following question:

I've been given a quick task to perform which involves updating a database on a server. I have little or no idea how to do this and I need to get it done today. The main problem is that i've been given a non-perl code snippet ( VB )
sub open_conn ' Connect to the Onepoint Database for a list of all hosts dim strConnect: strConnect = "Provider=SQLOLEDB.1;Data Source=iblons3 +506\oi_c1_prd4;Initial Catalog=OMDPortal;User ID=OMDPortal_General;Pa +ssword=oBfuScaTed;" set oConn = CreateObject("ADODB.Connection") oConn.Open strConnect end sub
which is what is currently used and I want to perform the same thing with perl. If anyone could point me in the right direction i'll buy them a beer. Jeff

Password obfuscated by holli

Replies are listed 'Best First'.
Re: update database question
by borisz (Canon) on Jun 23, 2005 at 13:21 UTC
    You should use DBI perl's generic database interface and a DBD:: driver suitable for your database.
Re: update database question
by JediWizard (Deacon) on Jun 23, 2005 at 13:21 UTC

    See DBI and ODBC.

    my $dbh = DBI->connect('dbi:ODBC:driver={SQL Server};Server=; +database=dbname;uid=user;pwd=pswd;', 'user', 'pword') or die DBI->err +_str; my $sth = $dbh->prepare('sql statement'); $sth->execute();

    They say that time changes things, but you actually have to change them yourself.

    —Andy Warhol

Re: update database question
by terce (Friar) on Jun 23, 2005 at 15:18 UTC
    You can, if you want to, use Win32::OLE to imitate the behaviour of the VB code:
    sub open_conn { use Win32::OLE; use Win32::OLE::Const 'Microsoft ActiveX Data Objects'; $oConn = Win32::OLE->new('ADODB.Connection'); # creates a connection +object my $connectionString = '"Provider=SQLOLEDB.1;DataSource=iblons3506\oi +_c1_prd4;InitialCatalog=OMDPortal;UserID=OMDPortal_General;Password=o +BfuScaTed;'; $oConn->Open ($connectionString); }

    But with any luck the answers provided by the other monks have already got you on your way.
    You can run your SQL with $oConn->Execute($sql_command)
Re: update database question
by marto (Bishop) on Jun 23, 2005 at 13:23 UTC

    Perhaps you should have a read at Before asking a database related question ... first.

    "which is what is currently used and I want to perform the same thing with perl. "

    Also please be aware of what you are posting, your code has a password which may (or may not be) the password to your database.

    Hope this helps.



    Update: holli has since edited the post replacing the SQL password with a dummy password
Re: update database question
by VSarkiss (Monsignor) on Jun 23, 2005 at 14:12 UTC

    The only thing that sub is doing is opening a connection, whereas your title seems to indicate you're doing an update -- someplace else in your code, maybe?

    The exact equivalent to your sub, using DBI and DBD::ADO is this:

    use DBI; sub open_conn { # join isn't stricly necessary, # it just improves readability. my $strConnect = join ';', 'Provider=SQLOLEDB.1', 'Data Source=iblons3506\oi_c1_prd4', 'Initial Catalog=OMDPortal', 'User ID=OMDPortal_General', 'Password=ObFuScAtEd'; my $dbh = DBI->connect("dbi:ADO:$strConnect", '', '') or die "Can't connect: $DBI::errstr\n"; }
    That snippet shares some of the problems in the VB code: it doesn't do anything intelligent if the connection fails (just dies), and it uses embedded passwords. But this one does return a value, the database handle (presumably in your VB program oConn is a global?)

    But the point is, you can see how I mechanically translated your code. Depending on how dependent on ADO your code is, you may want to change the code more to take advantage of Perl's capabilities, or just translate part by part, using Win32::OLE or some such.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://469377]
Approved by ghenry
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (3)
As of 2017-06-29 02:36 GMT
Find Nodes?
    Voting Booth?
    How many monitors do you use while coding?

    Results (653 votes). Check out past polls.