http://qs1969.pair.com?node_id=469377

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.
    Boris
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=172.1.1.1; +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 (Cardinal) on Jun 23, 2005 at 13:23 UTC
    Hi,

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

    <VBcode>
    "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.

    Cheers

    Martin

    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.