Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

sql and hash confusion

by grashoper (Monk)
on Mar 19, 2009 at 18:54 UTC ( #751834=perlquestion: print w/replies, xml ) Need Help??

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

I am trying to figure out how to dump some data files I have into a database and I have never tried to do this before. so if I am making a retarded mistake please take it easy on me. so far i have read the data file into a hash, created a dsn and connected to my database using win32::odbc, I am not sure what I need to do next. I have several problems. 1) my data..(not quite what I wanted for some values, this is just a rough test so I am ok with not being 100% here) 2) order of the hash is reversed and I am not sure how to fix it. $values, $key instead of the other way around. and finally the sql, how do I insert using a hash? here is my code so far..
use Win32::ODBC; use DBI; $DSN="Ptest"; $db = new Win32::ODBC("DSN=Ptest;UID=userid;PWD=password;"); if(!($db)){ print "Error connecting"; exit; } else { print "Hey you are connected \n"; } my $file="data.txt"; my %hash=(); open(file,$file) or die; { local $/ = undef; %hash = split /\,/,<file>; $sqlstatement="Insert '$value->{0}' into dbo.Results"; while ( my ($key, $value) = each(%hash) ){ print "$value => $key\n"; if($db->SQL($sqlstatement)){ print "abysmal failure try again"; } close file; ---Data---Sample of data-- 1/5/2009 3:55:30 PM,Login,18.490683,SearchLoad,15.012853,SearchCount,0 +:0.392878,SearchResults,16.048765,SearchSave,6.629372,SearchDelete,9. +614098,SearchDetails,4.587307,TaxLoad,5.215133,TaxResults,0:2.956125, +TaxDetails,5.226226,ClientAdd,4.834234,CMALoad,1.841135,CMASave,12.44 +1073,CMADelete,0.218266,ClientDelete,0.268565,Logout,0.822440,1/5/200 +9 5:04:39 PM,Login,12.841120,SearchLoad,5.733201,SearchCount,0:0.1874 +62,SearchResults,6.998567,SearchSave,1.812129,SearchDelete,0.906065,S +earchDetails,4.108532,TaxLoad,2.343270,TaxResults,0:2.765057,TaxDetai +ls,2.468244,ClientAdd,1.593423,CMALoad,1.655911,CMASave,8.092093,CMAD +elete,0.187461,ClientDelete,0.187461,Logout,0.781090

Replies are listed 'Best First'.
Re: sql and hash confusion
by lostjimmy (Chaplain) on Mar 19, 2009 at 19:42 UTC

    If you used Data::Dumper to look at your hash, you might be able to see more easily why things were screwing up. The first key you end up with is 1/5/2009 3:55:30 PM, which has a value of "Login". That's not what you want. You want to strip off the date beforehand.

    I imagine that the dates are an important piece of information, so I don't think you'll want to get rid of them outright. And anyway, even if you did get rid of the dates, you'll have problems putting this into a hash. Hashes can only have one value per key, and your values repeat for each login record, so you'll just overwrite the first set of values.

    If this is always the format of the data, then you could do something like the following:

    use strict; use warnings; my $file = 'data.txt'; open my $fh, '<', $file or die "failed to open $file: $!"; my @tokens = split /,/, <$fh>; close $fh; chomp @tokens; my $found_logout = 1; my $date; my $i = 0; while ($i < @tokens) { if ($found_logout) { $date = $tokens[$i++]; $found_logout = 0; print "*****************************\n"; print "New date: $date\n"; } else { my $key = $tokens[$i++]; my $value = $tokens[$i++]; print "$key = $value\n"; # do DB stuff with $key and $value $found_logout = 1 if $key eq "Logout"; } }

    I haven't used Win32::ODBC, but hopefully someone else can help you out with that part.

    Update: And if you really need it in a hash:

    my $found_logout = 1; my $i = 0; my %hash; while ($i < @tokens) { if ($found_logout) { %hash = (date => $tokens[$i++]); $found_logout = 0; } else { my $key = $tokens[$i++]; my $value = $tokens[$i++]; $hash{$key} = $value; if ($key eq "Logout") { $found_logout = 1; do_db_stuff(\%hash); } } } sub do_db_stuff { print Dumper shift; }

      you are correct what I want to do with the date is add a new key called date into the hash so that it maps date->value, then login->time etc, I was thinking that inputting the data would need the column name, followed by the value, fields are always in same order in the data file, I would also like to add a value for site which would contain a code indicating which account is being measured. I really don't know where to go for figuring out how to get this into sql which is my main problem, seems there are not many examples out there for using parameters in a query programmatically I am guessing because stored procedures are the preferred mechanism for doing so. I don't want to build a stored procedure for it as I am not that versed in sql. I guess that would be a viable option to pursue though.
Re: sql and hash confusion
by olus (Curate) on Mar 19, 2009 at 19:33 UTC

    There seems to be some inconsistency on the sample data that lead me to think that this simple split on commas won't give you a correct hash representation of the information. You will need to study the file to better understand its structure and then find a proper way to parse it.

    I'm not familiar with Win32::ODBC so I can't tell you if you are using it correctly but, I'd recommend that you look into the DBI documentation in order to learn how to connect to a database and execute queries on it. Take particular notice on placeholders.

    Your SQL statement is wrong. Find an SQL cheat sheet (just to get you started) and look for the correct syntax for insert statements. Just search for sql syntax.

Re: sql and hash confusion
by bichonfrise74 (Vicar) on Mar 19, 2009 at 21:28 UTC
    I don't think you need a hash to convert your data into something that can be inserted into a database... Please see below or I just didn't understand what you were trying to do.
    #!/usr/bin/perl use strict; while( <DATA> ) { s/,/\","/g; chomp; print "INSERT INTO dbo.RESULTS values (\"$_\");\n"; } __DATA__ 1/5/2009 3:55:30 PM,Login,18.490683,SearchLoad,15.012853,SearchCount,0 +:0.392878,SearchResults,16.048765,SearchSave,6.629372,SearchDelete,9. +614098,SearchDetails
Re: sql and hash confusion
by Anonymous Monk on Mar 20, 2009 at 01:07 UTC

    It always makes me uncomfortable to see “read the entire file into a ...” because basically what you are doing is using virtual-memory as a(nother) disk file. If you've already got the data as an existing flat-file, it seems to me that you ought to be reading it and processing it “a record at a time.”

    Also, don't overlook any possibilities to take advantage of (disk-based) sorting. It's an uncommonly-fast and efficient operation, and when you're dealing with a sorted file you know that all of the records for any particular sort-key value must be adjacent ... or they're not there at all. I have not looked closely at your example so I really don't know if this applies to you. Anyhow, “it was good enough for COBOL and punched-cards and mangnetic tapes,” and those advantages still apply today.

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (2)
As of 2022-01-20 00:22 GMT
Find Nodes?
    Voting Booth?
    In 2022, my preferred method to securely store passwords is:

    Results (56 votes). Check out past polls.