Strict Ref : ERROR

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

#!/usr/bin/perl 

use strict;
use DBI;

open(LOG, "<20060620.act");

while (<LOG>){
chomp;
my @values = split(',',$_);
s/^"&"$/ $_ /g;

my $dbh = DBI->connect ('dbi:mysql:radius',
                        'radiususr',
                        'radiuspass') || die "Database connection not 
+made! $DBI::errstr";


foreach my $value(@values){

        if($value =~ 'Stop'){

my $sql = "insert into acct1(date,
                 time,
                                RAS_CLIENT,
                                Called_Station_Id,
                                Calling_Station_Id,
                                Acct_Input_Octets,
                                Acct_Output_Octets,
                                Acct_Session_Time,
                                Acct_Input_Packets,
                                Acct_Output_Packets,
                                Acct_Termination_Cause)
                        values(@values[0],
                                @values[1],
                                @values[2],
                                @values[28],
                                @values[29],
                                @values[34],
                                @values[35],
                                @values[38],
                                @values[39],
                                @values[40],
                                @values[41],
                                @values[42])";

my $sth = $dbh->prepare($sql);

  $sth->bind_param(1,$_->[0]);
        $sth->bind_param(2,$_ ->[1]);
        $sth->bind_param(3,$_ ->[2]);
        $sth->bind_param(4,$_ ->[3]);
        $sth->bind_param(5,$_ ->[4]);
        $sth->bind_param(6,$_ ->[5]);
        $sth->bind_param(7,$_ ->[6]);
        $sth->bind_param(8,$_ ->[7]);
        $sth->bind_param(9,$_ ->[8]);
        $sth->bind_param(10,$_ ->[9]);
        $sth->bind_param(11,$_ ->[10]);
        $sth->bind_param(12,$_ ->[11]);
        $sth->execute();
        $dbh->commit();

if ($@) {
        warn "Database error: $DBI::errstr \n";
        $dbh->rollback();
        }
$dbh->do($sql);

$sth->finish();
$dbh -> disconnect ();

}
[download]

ERROR OBTAINED:::
******************

Can't use string (""06/19/2006","23:59:48","CSCVPN0") as an ARRAY ref 
+while "strict refs" in use at try1.pl line 60, <LOG> line 1048.
[download]

SAMPLE FILE DATA:::
*********************


"06/19/2006","23:59:48","CSCVPN01","Stop","Unknown","Unknown","bmikulk
+a","172.22.0.5","946674","8","1","172.22.0.85","255.255.224.0",,,,,,,
+,,,,,"ou=pcna","82800","900",,,,"CSCVPN01",,"2","14","108311","503278
+","000E71F2",,"248","806","821",,,,"5",,"9",,,,,,,,,,,,,,,,,,,,,,,,,,
+,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
"06/20/2006","00:00:05","DFWAS1.PBSG.PVT","Stop","tmoy@PBGPPShh","200"
+,"tmoy@PBGPPShh","172.21.64.20","392","2","1","172.21.64.86",,,,,,,,,
+,,,,"SBR-CL DN="tmoy@PBGPPShh" AT="200" VR="SybaseRealm"",,,,"0963","
+2157251159","DFWAS1.PBSG.PVT",,"2","0","8841","119703","00029926","1"
+,"219","79","113","1",,,"0",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
+,,,,,,,,,,,,,,,,,,,,,,,,,,,
"06/19/2006","23:59:51","DFWVPN01M.PBSG.PVT","Stop","Unknown","Unknown
+","03138981","172.21.0.5","2042656","8","1","172.21.0.123","255.255.2
+24.0",,,,,,,,,,,,"ou=fritolay","82800","900",,,,"DFWVPN01M.PBSG.PVT",
+,"2","15","17850309","24857452","001F2B20",,"5276","28956","31809",,,
+,"5",,"9",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
+,,,,,,,

"06/19/2006","23:59:57","DFWVPN01M.PBSG.PVT","Stop","Unknown","Unknown
+","02036855","172.21.0.5","2042739","8","1","172.21.0.100","255.255.2
+24.0",,,,,,,,,,,,"ou=troppilot","82800","900",,,,"DFWVPN01M.PBSG.PVT"
+,,"2","9","7621521","15661250","001F2B73",,"4147","29079","27876",,,,
+"5",,"9",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
+,,,,,,

"06/20/2006","00:00:06","CSCVPN01","Stop","Unknown","Unknown","0117152
+5","172.22.0.5","946672","8","1","172.22.0.73","255.255.224.0",,,,,,,
+,,,,,"ou=pcna","82800","900",,,,"CSCVPN01",,"2","11","125891","157892
+","000E71F0",,"447","621","560",,,,"5",,"9",,,,,,,,,,,,,,,,,,,,,,,,,,
+,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
[download]

20060628 Janitored by Corion: Added formatting, code tags, as per Writeup Formatting Tips

20060629 Janitored by Corion: Added READMORE tag

Comment on Strict Ref : ERROR Select or Download Code

Replies are listed 'Best First'.
Re: Strict Ref : ERROR by samtregar (Abbot) on Jun 28, 2006 at 13:05 UTC
This would appear to be coming from: `$sth->bind_param(1,$_->[0]);` I'm not sure what you expected to have in $_ at this point, but it's not an array ref. To my eyes the error message you recieved is exactly what I would expect. Did you have trouble understanding it? -sam	[reply] [d/l]
Re^2: Strict Ref : ERROR by swetashah23 (Initiate) on Jun 28, 2006 at 13:15 UTC
It had $sth->bind_param(1,@$->[0]); I have been trying to find out what the error is and hence following t +ons of tricks available online and thats why I had changed it to $->[ +0] instead of @$->[0] <br/> even then it gave me the same error.<br> Thanks. yes. maybe an understanding as to what causes this error also will be +helpful.. one link said that its may happen if there are two sub rou +tines and they are unordered.. I didn't quite understand what that me +ant.. Thats why I posted my whole code here. [download]	[reply] [d/l]
Re^3: Strict Ref : ERROR by samtregar (Abbot) on Jun 28, 2006 at 15:31 UTC
Abandon complex explainations - this is a simple problem. You're trying to treat $_ as though it has an array-ref in it. It does not - it has a string instead. -sam	[reply]
Re: Strict Ref : ERROR by Ieronim (Friar) on Jun 28, 2006 at 13:08 UTC
Try to expain (in English, not in Perl) what you wanted the $_ variable to be :) In your code you take a string from file and try to use it as array reference (???)	[reply]
Re^2: Strict Ref : ERROR by swetashah23 (Initiate) on Jun 28, 2006 at 13:21 UTC
`I am trying to read each field from the file and strip it off of the q +uotes and comma and storing them in the DB. one article said that I should first bind the array element ( I mean e +ach field in the file is an element of the array)to a position in the + table and then it will be executed as required. Thanks.` [download]	[reply] [d/l]
Re^3: Strict Ref : ERROR by Ieronim (Friar) on Jun 28, 2006 at 13:53 UTC
You need to read data from comma-separated file (so-called CSV). The best way to do it is to use the Text::CSV module. Read its documentation. Then you need to load data to DB. Do it using the DBI placeholders. Read DBI documentation again! If you don't understand the code â re-read perlintro. It must help :) If you think you will work with Perl later â buy the Camel book.	[reply]
Re: Strict Ref : ERROR by jdtoronto (Prior) on Jun 28, 2006 at 13:31 UTC
swetashah23, I think a good read of the various tutorials on this site, especially the extraordinarily useful one by gmax at DBI recipes would be in order. jdtoronto	[reply]
Re^2: Strict Ref : ERROR by swetashah23 (Initiate) on Jun 28, 2006 at 13:33 UTC
`Thank you..I will read it... (again)....Its just that so many docs suggest so many things that I am panicking now.`	[reply] [d/l]
Re: Strict Ref : ERROR by Corion (Patriarch) on Jun 28, 2006 at 13:31 UTC
Your code makes no sense. You prepare a complete-yet-broken SQL statement and then try to use `bind_params` to interpolate variables into it. This will not work. Maybe you can adapt the following example of inserting data into a database to your needs: `my $sql = <<SQL INSERT INTO acct1( date, time, RAS_CLIENT, Called_Station_Id, Calling_Station_Id, Acct_Input_Octets, Acct_Output_Octets, Acct_Session_Time, Acct_Input_Packets, Acct_Output_Packets, Acct_Termination_Cause) VALUES (?,?,?,?,?,?,?,?,?,?,?)"; warn $sql; my $sth = $dbh->prepare($sql); while (<LOG>){ chomp; my @values = split(',',$_); # what do you do if one value contains a comma? # remove quotes here $sth->execute(@values); };` [download] I suspect that you will have many, many problems as the logic of your program is very twisted. Consider writing on small sheets of paper the steps your program needs to perform, and only one small unit per sheet of paper. You reopen the connection to the database for every line from your logfile - that's not very efficient.	[reply] [d/l] [select]
Re^2: Strict Ref : ERROR by swetashah23 (Initiate) on Jun 28, 2006 at 13:43 UTC
`Corion, Thanks again.. my first script looked just like that and it s +aid there was an error near ?,?,?) However, I will surely try this on +e out again. Thanks again.` [download]	[reply] [d/l]
Re: Strict Ref : ERROR by Sandy (Curate) on Jun 28, 2006 at 13:44 UTC
In english, this is what your code does. I've included comments where I think that you are mistaken about what your code is actually doing. LOOP1: 1.0 Read one line of data from log file and store in symbol $_ 2.0 Create an array of simple strings by splitting the string stored in $_ wherever there is a comma. Store this array in @values. NOTE: Each element of @values is a SINGLE string, not an array. 3.0 Connect to database NOTE: This will connect you to the database everytime you read a line from your log file because it is inside the loop that reads your log file! LOOP2: 3.1 Process each element of the array @values. 3.2 If this element is 'stop' then 3.2.1 Create sql statement. But you are using @values[0]. What does this mean? Each element of @values is a simple string. Did you only want that string, or did you expect that @values = ([array],[array]...) ??? 3.2.2 Preparing the sql statement. 3.2.3 Bind parameters to $_->[i] ??? But $_ is simply the single line of data that you read from your log file. It's a single string! Why do you expect it to be an array? 3.2.4 Execute db query 3.2.5 Rollback if error 3.2.6 Disconnect from database ... MISSING CODE.... 3.3 End of is current element of @values equal to string 'stop'? 3.4 Go back to LOOP2 to process more elements of the array @values (until all done) 4.0 Go back to LOOP1 to read next line of LOG file [download] I think you need to fix your algorithm first, then, once you have something that makes sense, convert it to perl. At this stage, your algorithm is so mangled that it is not really a 'perl' problem. Perl is doing exactly what you are telling it to do, and then get's lost when you want an array from something that is not an array.	[reply] [d/l]
Re^2: Strict Ref : ERROR by swetashah23 (Initiate) on Jun 28, 2006 at 13:50 UTC
`Thanks... I will fix things up. Helped me a lot.` [download]	[reply] [d/l]
Re: Strict Ref : ERROR by wfsp (Abbot) on Jun 28, 2006 at 13:49 UTC
Hi swetashah23, Perhaps try getting a simple example up and running and then take it from there. This uses the test db that came with my download of MySQL. There is a table called foo with two fields id and name. Note the use of placeholders that other monks have mentioned. #!/usr/bin/perl use strict; use warnings; use DBI(); my $server = 'local'; my %db = ( local => { db => 'test', host => 'localhost', user => '', pwd => '', }, ); my $dbh; $dbh = DBI->connect( "DBI:mysql:database=$db{$server}{db};host=$db{$server}{host}", $db{$server}{user}, $db{$server}{pwd}, {RaiseError => 1} ) or die "couldn't connect"; my @data = <DATA>; chomp @data; my $sth = $dbh->prepare( q{ INSERT INTO foo (id, name) VALUES (?, ?) } ) or err_db(); for (@data){ my ($id, $name) = split /,/; $sth->execute( $id, $name, ) or die "couldn't execute"; } __DATA__ 103,john 104,jack 105,jenny [download] Give this a go and let us know how you get on. One more thing - don't panic! :-)	[reply] [d/l]
Re^2: Strict Ref : ERROR by swetashah23 (Initiate) on Jun 28, 2006 at 14:06 UTC
`Thanks. I never try big things unless the tiny ones work. Therefore I + have tried all things .. and when i put those independently working +units together... This happened. But thanks anyways. I will just start from scratch yet again.` [download]	[reply] [d/l]
Re: Strict Ref : ERROR by derby (Abbot) on Jun 28, 2006 at 14:06 UTC
Try small steps first ... can you correctly parse the data and output sql? #!/usr/bin/perl use strict; use Text::CSV_XS; my $csv = Text::CSV_XS->new(); my $sqlf = "insert into acct1 ( date, time, RAS_CLIENT, Called_Station_Id, Calling_Station_Id, Acct_Input_Octets, Acct_Output_Octets, Acct_Session_Time, Acct_Input_Packets, Acct_Output_Packets, Acct_Termination_Cause ) values( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )"; while( <> ) { if( $csv->parse( $_ ) ) { my @vals = $csv->fields(); printf( $sqlf, $vals[0], $vals[1], $vals[2], $vals[28], $vals[29], $vals[34], $vals[35], $vals[38], $vals[39], $vals[40], $vals[41], $vals[42] ); } else { print "Error: " , $csv->error_input(); } } [download] That will at least get you started (and show that at least one of your example records isn't really following the de-facto CSV standard). Once you can properly create the sql ... move onto the next step. -derby	[reply] [d/l]
Re^2: Strict Ref : ERROR by swetashah23 (Initiate) on Jun 28, 2006 at 14:10 UTC
`Thanks Derby, Everything works as long as I do not ask it to write the output value +to a DB instead of another file and/or stdout. The minute I uncommented those blessed lines... things start acting up +.` [download]	[reply] [d/l]


Come for the quick hacks, stay for the epiphanies.
	PerlMonks

Strict Ref : ERROR

This is an archived low-energy page for bots and other anonmyous visitors. Please sign up if you are a human and want to interact.