Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

Insert into database table

by curtisb (Monk)
on Jan 05, 2005 at 13:38 UTC ( #419609=perlquestion: print w/replies, xml ) Need Help??

curtisb has asked for the wisdom of the Perl Monks concerning the following question:

All I'm having a problem with my while loop. It reads the data out of the file and displays the first record of the array for until the end-of-file.
However, I cannot get it to insert this value into the table. Any guidence would be helpful. Thanks, Bobby

#!/usr/bin/perl # #Connects to a Sybase Database use DBI; #Loads DBI module #Connect using ARSAMSDBA.... my $dbh = DBI->connect("dbi:ODBC:usamma", "arsamsdba", "arsamssql", { PrintError => 0, RaiseError => 0 }) or die "Can't connect to database: $DBI::errstr\n"; #Prepare Create Parser Table statement.... my $sth = $dbh->prepare("CREATE TABLE PARSER_TABLE ( NIIN char(9) NOT NULL, FSC char(4) NOT NULL, PRIME_NSN varchar(16) NULL, MATCAT char(5) NULL, SOS char(3) NULL, DML char(1) NULL, EC char(1) NULL, RICC char(1) NULL, ARC char(1) NULL, SCIC char(1) NULL, AAC char(1) NULL, LCC char(1) NULL, PHC varchar(1) NULL, RC char(1) NULL, SCMC char(2) NULL, AMDF_PRICE varchar(20) NULL, CRED_SERV varchar(20) NULL, CRED_UNSERV varchar(20) NULL, UI char(2) NULL, UM char(2) NULL, MEAS_QTY varchar(20) NULL, ARI char(1) NULL, MR char(1) NULL, Nomen char(21) NULL, EIC char(3) NULL, CIIC char(1) NULL, LIN char(6) NULL, smr_cd varchar(5) NULL, WRTY char(1) NULL, WSC char(3) NULL, model varchar(50) NULL, Notes varchar(128) NULL, DEMIL char(1) NULL )" ) or die "Can't prepare SQL statement: $DBI::errstr\n"; #Excute Create Table statement.... $sth->execute or die "Can't execute SQL statment: $DBI::errstr\n"; #Disconnect ARSAMSDBA..... $dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n"; #Connect using DBA (Opens DB Handle).... $dbh = DBI->connect("dbi:ODBC:usamma", "dba", "sql", { PrintError => 0, RaiseError => 0 }) or die "Can't connect to database: $DBI::errstr\n"; #Execute to delete records from arsamsdba.parser_table.... my $sth0 = $dbh->do("DELETE arsamsdba.PARSER_TABLE") or die "Can't pre +pare SQL statement: $DBI::errstr\n"; #Prepare for input into arsamsdba.parser_table.... my $sth1 = $dbh->prepare("INSERT INTO ARSAMSDBA.PARSER_TABLE (NIIN) VA +LUES (?)"); my $sth2 = $dbh->prepare("commit"); open AFH, "D:/WorkingFiles/Scripts/perl/amdfdata.txt" or die "Can't op +en D:/WorkingFiles/Scripts/perl/amdfdata.txt: $!\n"; while(!eof AFH) { my $line = <AFH>; #take in new line chomp; #trim leading (and trailing) quotes $line =~ s/^\"//; @row=split(/\"[^\"]\"\,{0,1}/,$line); print "$row[0]\n"; $sth1->bind_param(1, $niin); $sth1->execute($row[0]); } $sth2->execute(); close AFH or warn "Can't close D:/WorkingFiles/Scripts/perl/amdfdata.t +xt: $!\n"; $dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n"; exit; #, FSC, PRIME_NSN, MATCAT, SOS, DML, EC, RICC, ARC, SCIC, AAC, LCC,PHC +, RC, SCMC, AMDF_PRICE, CRED_SERV, CRED_UNSERV, UI, UM, MEAS_QTY, AR +I, MR, #NOMEN, EIC, CIIC, #LIN, DEMIL) # VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? +,?,?,?,?)"); #$niin, $fsc, $prime_nsn, $matcat, $sos, $dml, $ec, $ricc, $arc, $scic +, $aac, $lcc, $phc, $rc, $scmc, $amdf_price, $cred_serv, #$cred_unserv, $ui, $um, $meas_qty, #$ari, $mr, $nomen, $eic, $ciic, $ +lin, $demil);

Replies are listed 'Best First'.
Re: Insert into database table
by dragonchild (Archbishop) on Jan 05, 2005 at 13:48 UTC
    What error(s) are you seeing? How do you know it's not working?

    One thing that leaps out at me is that you're using both bind_param() and passing a value to execute(). Unless you absolutely have to, don't use bind_param.

    Also, you aren't using strict, which means you're not seeing the fact that you never declare or assign to $niin ... that, I suspect, is your problem.

    Suggested solution: Comment out the call to bind_param().

    Being right, does not endow the right to be rude; politeness costs nothing.
    Being unknowing, is not the same as being stupid.
    Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
    Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Re: Insert into database table
by erix (Parson) on Jan 05, 2005 at 14:04 UTC

    I would advise to 'use strict;' but also to 'RaiseError => 1'. This will show you the $DBI::errstr (the error encountered).

    You'd normally enclose the DBI-calling code in a eval block:

    eval { yourDBIsubroutine() }; if ($@) { print "error:\n$@\n" }

    From the DBI docs:

    If you turn "RaiseError" on then you'd normally turn "PrintError" off. If "PrintError" is also on, then the "PrintError" is done first naturally).

    (Try 'perldoc DBI' on the commandline)

Re: Insert into database table
by CountZero (Bishop) on Jan 05, 2005 at 16:08 UTC
    There are some strange things in your script.

    • As was already said: use strict
    • You first create a table (which will be empty) and then you delete its contents, which are not (yet) there or is it possible that meanwhile someone else could have filled the table ((s)he won't be happy to see the data disappear!)?
    • You disconnect from the DB after creating the table and immediately reconnect, which seems a bit wasteful on resources (but it could have something to do with user-privileges; I don't know much about Sybase in that respect)
    • It is considered advisable to use a fresh lexical variable as filehandle in open. As per perlopentut
      open's first argument can be a reference to a filehandle. As of perl 5.6.0, if the argument is uninitialized, Perl will automatically create a filehandle and put a reference to it in the first argument, like so:
      open( my $in, $infile ) or die "Couldn't read $infile: $!"; while ( <$in> ) { # do something with $_ } close $in;
      Indirect filehandles make namespace management easier. Since filehandles are global to the current package, two subroutines trying to open INFILE will clash. With two functions opening indirect filehandles like my $infile, there's no clash and no need to worry about future conflicts. Another convenient behavior is that an indirect filehandle automatically closes when it goes out of scope or when you undefine it:
      sub firstline { open( my $in, shift ) && return scalar <$in>; # no close() required }
    • At the expense of legibility (although it is considered standard coding practice in Perl), you should use while(<AFH>) {...} and the line read in will be in the magical variable $_ on which both chomp, s/ / / and split work by default. As it is now, chomp has no effect on $line
    • The argument to split is a bit complex. Are you perhaps trying to parse a CSV-style file? In that case you could think of using the Text::CVS_XS module
    • drop the $sth1->bind_param(1, $niin); line. It serves no purpose here. As per the docs in DBI either you use bind_param to provide the data to be put in place of the "?" placeholder or you provide this data as an argument to execute
      $sth->bind_param($p_num, $bind_value) $sth->bind_param($p_num, $bind_value, \%attr) $sth->bind_param($p_num, $bind_value, $bind_type)
      The bind_param method takes a copy of $bind_value and associates it (binds it) with a placeholder, identified by $p_num, embedded in the prepared statement. Placeholders are indicated with question mark character (?). For example:
      $dbh->{RaiseError} = 1; # save having to check each method ca +ll $sth = $dbh->prepare("SELECT name, age FROM people WHERE name LIKE ? +"); $sth->bind_param(1, "John%"); # placeholders are numbered from 1 $sth->execute;
    • Add the usual or die ... after the $sth1->execute($row[0]) so you know what went wrong.


    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Re: Insert into database table
by rdfield (Priest) on Jan 05, 2005 at 13:51 UTC
    And which one of your error messages do you see first?

    Things to note:

    • "Create table" should be done using a "$dbh->do" type command
    • You don't do a "$dbh->prepare('commit')", "$dbh->commit" is what you need


Re: Insert into database table
by reneeb (Chaplain) on Jan 05, 2005 at 13:55 UTC
    You should use error-handling like
    my $sth = $dbh->prepare($sql) or die $dbh->errstr(); # and $sth->execute() or die $dbh->errstr();
Re: Insert into database table
by r34d0nl1 (Pilgrim) on Jan 05, 2005 at 18:21 UTC
    People told already but I like to say: use strict is good for you as well as use warnings :p
    Another thing is to look at perldoc DBI and to find more information about how to use DDL (like create table statement)
    and DML with or without binds
    Also don't forget to open and close your file handles properly (perldoc -f open).

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://419609]
Approved by BrowserUk
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (6)
As of 2019-11-19 00:22 GMT
Find Nodes?
    Voting Booth?
    Strict and warnings: which comes first?

    Results (93 votes). Check out past polls.