is it possible to store an array in a sql database?? 1

[wixsas]

Hi just wondering if it was possible to store an array in an sql database???
Here is my code:


my @ListOfSongs = param("songs");

my $dbh = DBI->connect("DBI:SQLite:sessions.db")
or die "Cannot connect: " . $DBI::errstr;

$sth = $dbh->prepare("UPDATE sessions SET contents = @ListOfSongs WHERE cartID = $cookie")
or die "Cannot prepare";
$sth->execute() or die "Cannot execute";


As you can see i have an array called @ListOfSongs which holds song titles. I want to store it in the array so that i can then pull it out later when it is needed.

Cheers

 

[ishnid]

You could, but best practice with regard to designing databases would argue against it. What you need is a table with a column for cartID and another for song title. Then create one record for each song in your cart. Retrieving the list of songs for a particular cart is then something like:
SELECT song_title FROM new_table WHERE cartID=1;

 

[wixsas]

Hi thanks heaps for the help. That will work perfectly however in the implementation of that I have come across a problem.

Here is my code :

my $cookie = $q->cookie('cart_id');
my @ListOfSongs = param("songs");
my $sizeOfArray = 0;

my $dbh = DBI->connect("DBI:SQLite:songs.db")
or die "Cannot connect: " . $DBI::errstr;

foreach $songs (@ListOfSongs) {

$sth = $dbh->prepare("INSERT INTO songs (cartID, song) VALUES ($cookie, $songs)") or die "Cannot prepare";
$sth->execute() or die "Cannot execute";
print "$songs\n";
print "<br /><br />\n";
$sizeOfArray = $sizeOfArray + 1;
}

This code gives me a "Cannot prepare" error which i know is because ive put $songs as one of the parameters. If i take $songs out and put 'test' it works fine. I have also tried putting $cookie in both positions and it also works fine.
Why is it that the $cookie variable works but the $songs variable doesn't. It's got me baffled.
Does a variable in an array contain symbols that couldn't be held in a "CHAR" record???

Cheers everyone

 

[ishnid]

Depends on what's in your $songs variable. Executing your SQL statements by embedding variables directly into the string you prepare as your statement is dangerous. You should be using placeholders and bind parameters. Essentially, that gets DBI to do the work of correctly quoting your variables for you, which is great from both a security and data-integrity point of view. Simply, when you're preparing the statment, use '?' characters as "placeholders" to show DBI where you want your variables inserted, then pass the values to the "execute" method, like so:

$sth = $dbh->prepare("INSERT INTO songs (cartID, song) VALUES (?, ?)") or die "Cannot prepare";
$sth->execute($cookie,$songs) or die "Cannot execute";

 

[Rieekan]

Also, you don't need to prepare the statement for every iteration through your loop. Do the prepare outside the loop (with the placeholders) and just run the execute statement within.

my $cookie = $q->cookie('cart_id');
  my @ListOfSongs = param("songs");
  my $sizeOfArray = 0;
      
   my $dbh = DBI->connect("DBI:SQLite:songs.db")
                          or die "Cannot connect: " . $DBI::errstr;
                
        $sth = $dbh->prepare("INSERT INTO songs (cartID, song) VALUES (?, ?)") or die "Cannot prepare";

     foreach $songs (@ListOfSongs) {
     
        $sth->execute($cookie, $songs) or die "Cannot execute";  
        print "$songs\n";
        print "<br /><br />\n";
     }

I also noticed that you have a variable called $sizeOfArray that you keep incrementing every time you run through the loop. Using the length function, you can get this value without having to count it yourself.

- George

 

[wixsas]

Hey guys thanks for your responses. It all makes sense and I will make those changes. But it is still giving me an error on the execute line now.

$sth = $dbh->prepare("INSERT INTO songs (cartID, song) VALUES (?, ?)") or die "Cannot prepare";

foreach $songs (@ListOfSongs) {
$sth->execute($cookie, $songs) or die "Cannot execute";
print "$songs\n";
print "<br /><br />\n";
$sizeOfArray = $sizeOfArray + 1;
}

The error is on the 'execute' line and says "Unknown named parameter". This is weird as i can print what is in $cookie and what is in $songs and there is definitely the right content being held in those variables.

Cheers everyone

 

[ishnid]

Are "cartID" and "song" the correct names of the fields in your table?

 

[wixsas]

this is was how i created the table:

(cartID CHAR(200) NOT NULL,
song CHAR(200) NOT NULL);

That should be right shouldn't it???

 

[stevexff]

CHAR(200) seems a bit excessive for an ID variable. It's more usual to use an INT. Probably want to go for VARCHAR(200) or NVARCHAR(200) for the song, too. No sense in actually storing 200 bytes of song title unless it's Several Species of Small Furry Animals Gathered Together in a Cave and Grooving with a Pict, and even that is less than 100...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[LawnBoy]

Steve gets a star for the obscure reference.

 

[MillerH]

I agree with everything that has been said about creating a true relational database. Nevertheless, in the effort of confusing the issue, here is one way to accomplish the original goal with the original tables.

[url=http://perldoc.perl.org/functions/use.html][black][b]use[/b][/black][/url] [green]Data::Dumper[/green][red];[/red]

[black][b]use[/b][/black] [green]strict[/green][red];[/red]

[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$cartid[/blue] = [blue]$q[/blue]->[maroon]cookie[/maroon][red]([/red][red]'[/red][purple]cart_id[/purple][red]'[/red][red])[/red][red];[/red]
[black][b]my[/b][/black] [blue]@songs[/blue] = [maroon]param[/maroon][red]([/red][red]"[/red][purple]songs[/purple][red]"[/red][red])[/red][red];[/red]

[black][b]my[/b][/black] [blue]$dbh[/blue] = DBI->[maroon]connect[/maroon][red]([/red][red]"[/red][purple]DBI:SQLite:sessions.db[/purple][red]"[/red][red])[/red]
	or [url=http://perldoc.perl.org/functions/die.html][black][b]die[/b][/black][/url] [red]"[/red][purple]Cannot connect: [/purple][red]"[/red] . [blue]$DBI::errstr[/blue][red];[/red]

[black][b]my[/b][/black] [blue]$sth[/blue] = [blue]$dbh[/blue]->[maroon]prepare[/maroon][red]([/red][red]q{[/red][purple]UPDATE sessions SET contents=? WHERE cartID=?[/purple][red]}[/red][red])[/red]
[blue]$sth[/blue]->[maroon]execute[/maroon][red]([/red][maroon]stringifyArray[/maroon][red]([/red][blue]@songs[/blue][red])[/red], [blue]$cartid[/blue][red])[/red] or [black][b]die[/b][/black] [blue]$dbh[/blue]->[maroon]errstr[/maroon][red];[/red]

[url=http://perldoc.perl.org/functions/sub.html][black][b]sub[/b][/black][/url] [maroon]stringifyArray[/maroon] [red]{[/red]
	[gray][i]# Change the default behavior of Dumper to being more compact.[/i][/gray]
	[url=http://perldoc.perl.org/functions/local.html][black][b]local[/b][/black][/url] [blue]$Data::Dumper::Indent[/blue]		= [fuchsia]0[/fuchsia][red];[/red]	[gray][i]# Default of 2[/i][/gray]
	[black][b]local[/b][/black] [blue]$Data::Dumper::Terse[/blue]		= [fuchsia]1[/fuchsia][red];[/red]	[gray][i]# Default of 0[/i][/gray]
	[black][b]local[/b][/black] [blue]$Data::Dumper::Quotekeys[/blue]	= [fuchsia]0[/fuchsia][red];[/red]	[gray][i]# Default of 1[/i][/gray]

	[url=http://perldoc.perl.org/functions/return.html][black][b]return[/b][/black][/url] [red]'[/red][purple]([/purple][red]'[/red] . [url=http://perldoc.perl.org/functions/join.html][black][b]join[/b][/black][/url][red]([/red][red]'[/red][purple],[/purple][red]'[/red], [url=http://perldoc.perl.org/functions/map.html][black][b]map[/b][/black][/url] [red]{[/red][maroon]Dumper[/maroon][red]([/red][blue]$_[/blue][red])[/red][red]}[/red] [blue]@_[/blue][red])[/red] . [red]'[/red][purple])[/purple][red]'[/red][red];[/red]
[red]}[/red]

To pull the data from the database.

[url=http://perldoc.perl.org/functions/use.html][black][b]use[/b][/black][/url] [green]strict[/green][red];[/red]

[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$cartid[/blue] = [blue]$q[/blue]->[maroon]cookie[/maroon][red]([/red][red]'[/red][purple]cart_id[/purple][red]'[/red][red])[/red][red];[/red]

[black][b]my[/b][/black] [blue]$dbh[/blue] = DBI->[maroon]connect[/maroon][red]([/red][red]"[/red][purple]DBI:SQLite:sessions.db[/purple][red]"[/red][red])[/red]
	or [url=http://perldoc.perl.org/functions/die.html][black][b]die[/b][/black][/url] [red]"[/red][purple]Cannot connect: [/purple][red]"[/red] . [blue]$DBI::errstr[/blue][red];[/red]

[black][b]my[/b][/black] [blue]$songs[/blue][red];[/red]
[black][b]my[/b][/black] [blue]$sth[/blue] = [blue]$dbh[/blue]->[maroon]prepare[/maroon][red]([/red][red]q{[/red][purple]SELECT contents FROM sessions WHERE cartID=?[/purple][red]}[/red][red])[/red]
[blue]$sth[/blue]->[maroon]execute[/maroon][red]([/red][blue]$cartid[/blue][red])[/red] or [black][b]die[/b][/black] [blue]$cbh[/blue]->[maroon]errstr[/maroon][red];[/red]
[blue]$sth[/blue]->[maroon]bind_columns[/maroon][red]([/red]\[blue]$songs[/blue][red])[/red][red];[/red]
[blue]$sth[/blue]->[maroon]fetch[/maroon][red];[/red]

[black][b]my[/b][/black] [blue]@songs[/blue] = [url=http://perldoc.perl.org/functions/eval.html][black][b]eval[/b][/black][/url] [blue]$songs[/blue][red];[/red]

[tt]------------------------------------------------------------
Pragmas (perl 5.8.8) used :
[ul]
[li]strict - Perl pragma to restrict unsafe constructs[/li]
[/ul]
Core (perl 5.8.8) Modules used :
[ul]
[li]Data:umper - stringified perl data structures, suitable for both printing and eval[/li]
[/ul]
[/tt]

- Miller

 

[KevinADC]

Several Species of Small Furry Animals Gathered Together in a Cave and Grooving with a Pict


hehehe... a blast from the past.

------------------------------------------
- Kevin, perl coder unexceptional!

 

[MillerH]

For those curious about the reference, start with WikiPedia

http://en.wikipedia.org/wiki/Severa...d_Together_in_a_Cave_and_Grooving_with_a_Pict

- Miller

 

[wixsas]

Hi I think i'd rather use the method I am using now. That is creating a new record for each song.

Can anyone tell me why $songs will not work when i try and pass it in?? BTW I have changed the CHARS to (30) and (30) respectively... LOL

 

[stevexff]

Yeah, well, it was just the longest song title I could think of. (Note: this is not a cue to start a competition...)

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[wixsas]

LOL wat was pink floyd thinking? Putting that aside could someone please help me???

 

[stevexff]

DBI normally gets its quoting right, but can get confused. What values are in $cookie and $songs?

$sth->execute($cookie, $songs) or die "Execute:\tcookie($cookie)\n\tsong($songs)\n\t$DBI::errstr";

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[wixsas]

An example of what $cookie and $songs hold is

$cookie : 186795506
$songs : Forest.mp3

 

[stevexff]

Try making cartID an INTEGER on the database and see what happens. I suspect that DBI might think that the numeric field is an integer rather than a CHAR...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[wixsas]

Have changed cartID to an INTEGER but still doesnt work. If i put '' around the ? marks then i get a "Cannot execute" error which is what i have specified it to say when it errors.
If i don't put the '' around the ? i get a "unknown named parameter" error.