My problem is that I need to use two connections to the same database, which DBD::ODBC doesn't support with SQL Server. Using DBI I can get around this by opening two handles to the same database and use those:
my $ro1 = DBI->connect($foo, $bar, $baz) or die $DBI::errstr; my $ro2 = DBI->connect($foo, $bar, $baz) or die $DBI::errstr;
However, the same trick doesn't work with Template::Plugin::DBI:
USE one = DBI(rodsn, rouid, ropwd); USE two = DBI(rodsn, rouid, ropwd); query = one.prepare(sql); trans = two.prepare(sql2); FOREACH item = query.execute(id) FOREACH tl = trans.execute(item.blah); ... END; END;
because it dies with error SQL-HY000 "handle already in use". Is there any way to make this return two different handles?
Hi,
Perhaps I am being obtuse here, but why do you need two database handles? Well I don't usually contribute on account of me knowing next to nothing about PERL but I have been using TT for a few months now developing a complex web application and I am somewhat surprised no one else has given the answer below - so please correct me if I have got something hideously wrong.
As MySQL still doesn't support subselects I have found that I have to do lots of queries that look very like yours above, i.e. a FOREACH inside a FOREACH using a result from the outer foreach - i.e. effectively doing a subselect - the slow way (Any one know how long it will be before the next version of MySQL?).
e.g. (Highly simplified version of some of my code with simplified queries for clarity - suffice to say in the real one none of the subselect workarounds work, it sounds like they might in your situation if you have been playing with JOINS)
[% TRY %]
[% USE DBI %]
[% IF DBI.connect('dbi:mysql:foo, 'bar', 'baz') %]
FAIL DBi CONNECT
[% STOP %]
[% ELSE %]
[% # connect success%]
[% END %]
[% CATCH %]
ERROR! Type: [% error.type %]
Info: [% error.info %]
[% END %][% str_query = DBI.prepare(' SELECT exercises.* FROM exercises WHERE ( workout_id = ? ); ') %]
[% str_sets_query = DBI.prepare(' SELECT * FROM sets WHERE ( exercises_id = ? ); ') %]
[% workout.workout_id = 1; # actually this also comes from an outer foreach loop %]
[% FOREACH exercises_strength = str_query.execute( workout.workout_id ) %]
[% FOREACH ess = str_sets_query.execute( exercises_strength.exercises_strength_id ) %]
[% temp = "exercises_strength.reps_set" _ ess.set_no %]
[% SET $temp = ess.reps %]
[% temp = "exercises_strength.wt_set" _ ess.set_no %]
[% SET $temp = ess.weight %]
[% END %]
<!-- printing stuff out from queries -->
[% exercises_strength.reps_set1 %]
[% exercises_strength.wt_set1 %]
[% exercises_strength.reps_set2 %]
[% exercises_strength.wt_set2 %]
[% END %]
Anyway it works and uses the same database handle. I understand why you might think it might not work - but it seems fine and stable in my code and nothing gets confused between the two queries (Presumably some wizardry in the TT code).
Failing that, I have tried using a SELECT statement that joins two tables, but have found myself unable to access any value that required qualification:
q = DBI.prepare("SELECT t1.id, a, b FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t1.id=?"); FOREACH item = q.execute('blah'); item.id; END
This should work - see below about naming conflicts
...and item.id is empty. item.t1.id doesn't work either. I then tried this:
q = DBI.prepare("SELECT t1.id AS t1id ...");
...and using item.t1id also doesn't work. Any way I can access that?
Should also work - perhaps item a table in the SQL also?
Have you tried your SQL in MySQL control center, good way to debug the SQL.
SQL = SELECT t1.ID .....
FOREACH item = SQL.execute('blah');The t1.ID in the SQL will be returned as the variable: item.ID regardless of what joins you do, but problems might arise if there is another field called ID in another table, (Likely to occur when you use t1.* t2.*) you can use the AS to change the name of any fields with the same name.
I assume all that is fairly obvious to you, so I am not sure why it didn't work for you, it should, just double check your SQL and check what field names it is actually returning. I do seem to vaguely recall an issue I had in the past with name conflicts, can't remember what it was perhaps the FOREACH item = SQL.execute('blah'); where item is a table or field in the returned SQL also... maybe that is it, look for any name conflicts - I tend to always play it safe, and ensure there are no naming conflicts anywhere.
Hope that helps Simon
--- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.495 / Virus Database: 294 - Release Date: 30/06/2003
