Creating Table in database from Perl using SQLite3

Question

I'm having trouble creating a table in a database I created with Perl using dbi sqlite3. Using the code I have below, I want the table to contain port-probes, one line for each source ip and port. I don't know if im doing it properly, the code below does not work for some reason, any help would be greatly appreciated. The code i have is as follows.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:SQLite:dbname=test.db", 
    "",
    "",
    { RaiseError => 1}
) or die $DBI::errstr;


$dbh->do(CREATE TABLE probes (
  source CHAR(15) NOT NULL,
  port CHAR(5) NOT NULL,
  PRIMARY KEY (source,port)) );


$dbh->disconnect();

The code i have does not work, can you see what is wrong with it — user218001, Apr 05 '13 at 01:31
*Does not work* is not a very clear description. What tells you that it isn't working? — Borodin, Apr 05 '13 at 01:33

mob · Accepted Answer · 2013-04-05T03:08:48.327

2

You forgot to quote the argument to $dbh->do. There are many ways to do this.

$dbh->do( "CREATE TABLE probes ( ..." );
$dbh->do( 'CREATE TABLE probes ( ...' );
$dbh->do( qq[ CREATE TABLE probes ( ... ] );
$dbh->do( <<"END_SQL" );
CREATE TABLE probes (
   ...
END_SQL

Added: to fix this problem, you just need to put quotes around the stuff in the $dbh->do function call.

$dbh->do("CREATE TABLE probes (
  source CHAR(15) NOT NULL,
  port CHAR(5) NOT NULL,
  PRIMARY KEY (source,port))");

edited Apr 05 '13 at 03:08

answered Apr 05 '13 at 02:07

mob

117,087
18
149
283

can you incorporate one of these into my code or the code borodin provided so i may see how it is done, like how to position the arguments. – user218001 Apr 05 '13 at 02:15
Put a quote before the word "CREATE". Put a matching quote after the last parenthesis. It is OK if the quote spans multiple lines. – mob Apr 05 '13 at 02:21
I don't understand what you mean, i am a beginner in this type of stuff. can you show me with the code i already have on the top or with the extra code the other fellow provided – user218001 Apr 05 '13 at 02:28

score 0 · Answer 2 · edited Apr 05 '13 at 02:10

0

You should also have use warnings at the top of every program.

You need to supply an SQL string to the do method. It is common to use a here document to code multi-line strings, like this.

$dbh->do(<<'END_SQL');
CREATE TABLE probes (
    source CHAR(15) NOT NULL,
    port CHAR(5) NOT NULL,
    PRIMARY KEY (source, port) )
END_SQL

Beware that the END_SQL line cannot contain any whitespace at either the beginning or end of the line.

edited Apr 05 '13 at 02:10

mob

117,087
18
149
283

answered Apr 05 '13 at 01:32

Borodin

126,100
9
70
144

1

i got the following errors DBD::SQLite::db do failed: near " ": syntax error at C:\Users\Abdullah\Documents\Perl files\test.pl line 14. What exactly do you mean by white space? DBD::SQLite::db do failed: near " ": syntax error at C:\Users\Abdullah\Documents\Perl files\test.pl line 14. – user218001 Apr 05 '13 at 01:56
That code works for me. Whitespace basically means spaces or tabs in this context. `END_SQL` must be against the left margin and not indented, and there must be no spaces or tabs after it before the end of the line. – Borodin Apr 05 '13 at 02:31
according to the follow below us, i didn't add ARGUMENTS, what is your opinion on that – user218001 Apr 05 '13 at 02:35
@user218001: I don't know what you mean. No one has mentioned adding ARGUMENTS. If you just copy my code above and use it in place of your `do` call it will work. – Borodin Apr 05 '13 at 02:43

Creating Table in database from Perl using SQLite3

2 Answers2