Re: Error with DBI and Strawberry Perl under 64 bit W7

Mad_Mac

I've got nothing to say about your problem (as I have't programmed in Windows 7, nor do I use Strawberry perl). I just have a couple random notes:

When you have a repeated clause in your SQL statement, if it can repeat a variable number of times, you can build the clause dynamically.
I generally try to keep my embedded SQL stuff looking clean, so I would have added the leading and trailing '%' symbols in the call, rather than in the code. It makes the code a little easier to read (IMHO).
You don't need to wrap a variable in quotes to use it.

So, I would have written it something like this:

my $SQL = q{SELECT SongTitle, SongPath FROM Songs WHERE };
$SQL .= join('OR', map { '(Songs.Artist LIKE ?)' } @inc_artists);

my $DBO = DBI->connect("dbi:SQLite:$mediaMonkeyPath", "", "")
            or die "Couldn't connect to MediaMonkey - $DBI::errstr\n";

my $STO = $DBI->prepare($SQL);
my $results = $STO->execute( map { "\%$_\%" } @inc_artists);
[download]

...roboticus

When your only tool is a hammer, all problems look like your thumb.

Comment on Re: Error with DBI and Strawberry Perl under 64 bit W7 Download Code

Replies are listed 'Best First'.
Re^2: Error with DBI and Strawberry Perl under 64 bit W7 by Mad_Mac (Beadle) on Mar 12, 2011 at 06:44 UTC
roboticus, Thanks. Good idea. Although, I'd also need to include the `\|\|` concatenation operators for SQL (I think that syntax is peculiar to SQLite). I could do that in the map function, I guess.	[reply] [d/l]
Re^3: Error with DBI and Strawberry Perl under 64 bit W7 by roboticus (Chancellor) on Mar 12, 2011 at 22:09 UTC
Mad_Mac: Nah, you don't need the concatenation operators. That's why I put the map in there. I think you're getting stuck on making the SQL concatenate the strings together, rather than letting perl do it for you. Think of it this way, you get a list of artists to search for: `my @inc_artists = ( 'Watson', 'Cline', 'Horne' );` [download] The first chunk of code would generate an SQL statement that looks like: `SELECT SongTitle, SongPath FROM Songs WHERE (Songs.Artist LIKE ?) OR (Songs.Artist LIKE ?) OR (Songs.Artist LIKE ?)` [download] The map statement with the execute wraps your artist names with the '%' symbols you want, so they end up look like: `( '%Watson%', '%Cline%', '%Horne%' );` [download] Finally, the execute statement would effectively have a statement like: `SELECT SongTitle, SongPath FROM Songs WHERE (Songs.Artist LIKE '%Watson%') OR (Songs.Artist LIKE '%Cline%') OR (Songs.Artist LIKE '%Horne%')` [download] which is what I think you want to feed to SQLite. ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply] [d/l] [select]
Re^4: Error with DBI and Strawberry Perl under 64 bit W7 by Mad_Mac (Beadle) on Mar 13, 2011 at 17:33 UTC
OIC. Yea, you're right. I'm making harder than it needs to be. Thx.	[reply]