Databases
StackOverflow2013
Postsdbo
POHow to load SQLite3 database with log file entries using perl
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to load SQLite3 database with log file entries using perl
    primarykey
    data
    text
    <p>I have an assignment In my Perl class that contains two programs, the first is to create a database and table using the DBI::SQLite for Perl, i have already completed the first part of the program, but i have no idea how to go about doing the second part. The second part involves getting the source IP and destination port pairs from a log file and inserting them into the table using the SQL insert statement. The table created in the first program contains two columns, one for source IP and one for Destination PORTS, the program shouldn't produce any output except a count of the rows added to the table. as you can see below the code that i have put represents how the table was setup, i would really appreciate some help on learning how to fill the table. The log file needed for the second program is available @ this link: <a href="http://fleming0.flemingc.on.ca/~chbaker/COMP234-Perl/sample.log" rel="nofollow">http://fleming0.flemingc.on.ca/~chbaker/COMP234-Perl/sample.log</a></p> <p>The code for my first program</p> <pre><code>#!/usr/bin/perl use strict; use warnings; use DBI; my $dbh = DBI-&gt;connect( "dbi:SQLite:dbname=test.db", "", "", { RaiseError =&gt; 1} ) or die $DBI::errstr; $dbh-&gt;do(&lt;&lt;'END_SQL'); CREATE TABLE probes ( source CHAR(15) NOT NULL, port CHAR(5) NOT NULL, PRIMARY KEY (source, port) ) END_SQL </code></pre> <p>Code for the second program (not working out)</p> <pre><code>#!/usr/bin/perl use strict; use warnings; use DBI; my %ip2port; my $IPCount = keys %ip2port; my $portCount = 0; my $filename = "./sample.log"; open my $LOG, "&lt;", $filename or die "Can't open $filename: $!"; LINE: while (my $line = &lt;$LOG&gt;) { my ($src_id) = $line =~ m!SRC=([.\d]+)!gis; my ($dst_port) = $line =~ m!DPT=([.\d]+)!gis; my $dbh = DBI-&gt;connect( "dbi:SQLite:dbname=test.db", "", "", { RaiseError =&gt; 1 }, ) or die $DBI::errstr; $dbh-&gt;do("INSERT INTO probes VALUES($src_id, $dst_port )"); $dbh-&gt;do("INSERT INTO probes VALUES(2,'$dst_port',57127)"); my $sth = $dbh-&gt;prepare("SELECT SQLITE_VERSION()"); $sth-&gt;execute(); my $ver = $sth-&gt;fetch(); print @$ver; print "\n"; $sth-&gt;finish(); $dbh-&gt;disconnect(); } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser218001
    1. USuser218001
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CObreak the problem into 2 parts: 1) extracting the ip and port, and 2) inserting them into the db.
      singulars
      1. POHow to load SQLite3 database with log file entries using perl
      1. USstevenl
    2. COi have tried the code i am putting under the first program
      singulars
      1. POHow to load SQLite3 database with log file entries using perl
      1. USuser218001
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload