The script is doing exactly what you asked it to do.

1. create a table mytable
2. query the new, empty, table

Obviously, since you never insert any records into the new table, your query will never return anything except column names.

Equally obvious, since your script tries to create the table mytable each time it is run, each run after the first will fail since the table mytable already exists.

Scott

Thanks for the reply. I understand that when the mytable table is created the next time i run the script it will give me an error when i try to create one again. Thats not my problem.

To your second point i am not querying the new table. If you look again you will see that i am querying another table. The other table has strings in it which am trying to retrieve and for some reason it will not the first time i run the script. Thanks

Walk me through these two lines:

 my @pages;
 my $column_creator = join(' INTEGER,', @pages) . ' INTEGER';
[download]

What is the join to achieve? @pages is empty so as far as I can see $column_creator can only ever be ' INTEGER' (including the leading space - which may or may not be important).

I suggest you look at the technique suggested in I know what I mean. Why don't you? for creating a CSV file to use as a database to provide data for a stand alone test snippet that reproduces the problem. I suspect that there is more here than you code shows. If we can't run it, we can't test it.

---

DWIM is Perl's answer to Gödel

Well, given the following data:

sqlite> create table data (id integer, text);
sqlite> insert into data values (1, 'test data');
sqlite> select * from data;
id          text
----------  ----------
1           test data
sqlite> .q
[download]

And corrections to your original code

#!/usr/bin/perl

 use warnings;
 use strict;

 use DBI;

 my $dbh = DBI->connect( "dbi:SQLite2:dbname=mydatabase.db", "", "" )|
+| die "Cannot connect: DBI";

 my @pages;
 # Create the table and make the columns
 my $column_creator = join(' INTEGER,', @pages) . ' INTEGER';
 my $create_columns = "CREATE TABLE mytable ($column_creator)";
 $dbh->do($create_columns);

 my $id_number = 1;
 my $current_column;
 my @splitted_string1;


 my $sth = $dbh->prepare("SELECT text FROM data WHERE id = '$id_number
+'");
 $sth->execute();
 while (my $record=$sth->fetchrow_hashref()) {
     print $record->{text} . "\n";
     @splitted_string1 = split " ", $record->{text};
 }
 $dbh->disconnect();
[download]

Then running the script produces:

helphand@helphand:~> perl sqlite.pl
test data
helphand@helphand:~>
[download]

But, note that @pages is never set to anything, so your mytable ends up with a schema like this CREATE TABLE mytable ( INTEGER);, which is probably not what you want.

Scott

1. your shebang is broken (#!/usr/bin/perl)
2. you use @pages before declaring it. Is this some SQLite thing?