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