DBIx::SearchBuilder and two database connections

Hello folks,

I have the problem that my RT-Search doesn´t work correctly. The reason is
that I have createt a second local DBIx::Handle for my own database
connection. Now RT searches for tickets in my database sometimes, but of
course it couldn´t find relation tickets in my database. But RT does it not
always. Sometimes I get results, when I click two or three time the reload
button!

When I created the $CDB::Handle global, so RT works but searchs always in my
own database, although the Handle has different names and parameters.

I create the local $CDB::Handle connection in my own autohandler, which is in
path /rt3/share/html/Ticket/. I also tried to disconnect my database in
/rt3/share/html/autohandler, with no success.

It also doesn´t work to create my $handle = DBIx::SearchBuilder->new() in my
own autohandler.

Now my question. Does I can use DBIx::SearchBuilder for my second database
connection? Does SearchBuilder bear up more than one database-connection in
one application, expecially RT?
How can I realise two database-connection else, without creating and closing
a connection in every file I need it?

I use DBIx::SearchBuilder 1.00_06, a Postgres database, RT 3.0.11, with Apache
2 and mod_perl2 on a SuSE 9.1 system.

Thanks for your help!

Grettings,
Tina

Tina Schade wrote:

Hello folks,

I have the problem that my RT-Search doesn�t work correctly. The reason is
that I have createt a second local DBIx::Handle for my own database
connection. Now RT searches for tickets in my database sometimes, but of
course it couldn�t find relation tickets in my database. But RT does it not
always. Sometimes I get results, when I click two or three time the reload
button!

When I created the $CDB::Handle global, so RT works but searchs always in my
own database, although the Handle has different names and parameters.
How do you create this object? Some code?

I create the local $CDB::Handle connection in my own autohandler, which is in
path /rt3/share/html/Ticket/. I also tried to disconnect my database in
/rt3/share/html/autohandler, with no success.

It also doesn�t work to create my $handle = DBIx::SearchBuilder->new() in my
own autohandler.
It’s wrong idea. You shoul subclass DBIx::SearchBuilder and
DBIx::SearchBuilder::Record or use RT’s classes.

Now my question. Does I can use DBIx::SearchBuilder for my second database
connection? Does SearchBuilder bear up more than one database-connection in
one application, expecially RT?
How can I realise two database-connection else, without creating and closing
a connection in every file I need it?
It’s not a problem of DBIx::SB as I think. IMHO you override something
in RT namespace that break things in RT.

Tina Schade wrote:

Hello folks,

I have the problem that my RT-Search doesn´t work correctly. The reason
is that I have createt a second local DBIx::Handle for my own database
connection. Now RT searches for tickets in my database sometimes, but of
course it couldn´t find relation tickets in my database. But RT does it
not always. Sometimes I get results, when I click two or three time the
reload button!

When I created the $CDB::Handle global, so RT works but searchs always in
my own database, although the Handle has different names and parameters.

How do you create this object? Some code?

Ruslan, I forgot to post the mail to list…here it comes.

I create a new folder CDB in …/rt3/lib/ and put there my files for my own
database-connection (see attachment). There are also a Handle.pm. In
./rt3/lib/ I put CDB.pm and called it in webmux.pl with use CDB and
CDB::Init(). In Init() I create a new instance of my CDB::Handle, but the
database-connection I create in my own autohandler.

When I create my own SearchBuilder and Record in folder CDB, so it could maybe
work. Does I have to regard something else?

Am Freitag, 23. Juli 2004 14:33 schrieben Sie:

Tina Schade wrote:

Am Freitag, 23. Juli 2004 11:54 schrieben Sie:

Tina Schade wrote:

Hello folks,

I have the problem that my RT-Search doesn´t work correctly. The reason
is that I have createt a second local DBIx::Handle for my own database
connection. Now RT searches for tickets in my database sometimes, but
of course it couldn´t find relation tickets in my database. But RT does
it not always. Sometimes I get results, when I click two or three time
the reload button!

When I created the $CDB::Handle global, so RT works but searchs always
in my own database, although the Handle has different names and
parameters.

How do you create this object? Some code?

I created a new folder in /rt3/lib/ which is called CDB. In this folder
are my classes for my own database, like in RT (see attachment-Machine.pm
and Machines.pm). There are also a class Handle.pm (see attachment). Then
I have the file CDB.pm in /rt3/lib/. In webmux.pl I initialized it with
use CDB; and CDB::Init(). How you can see I doesn´t create the connection
for my database in CDB::Init(). This I make in my own autohandler with
local $CDB::Handle = $CDB::Handle->new() and $CDB::Handle->Connect().

You don’t need localize $CDB::Handle( I tried it too, but had problems I
don’t remeber exactly which ). Just create new $CDB::Handle object if it
doesn’t exist(you do it) and always call connect. Apache::DBI will do
work for you. And also you’ll be able to run your code from console
scripts.

Ok, I tried to create $CDB::Handle global, as you can see in CDB.pm. But with
this, RT-search doesn´t work at all!!! So my idea was to make it local and so
at least RT-search works sometimes. Is the mistake that I initialize CDB in
webmux.pl after RT? Also in this way:
use RT;
use CDB;

RT::Init();
CDB::Init()

I can´t understand why the RT-search brakes with my Handle? I mailed with
someone else who had similar problems two years ago. But there was the
problem that SearchBuilder makes the Handle global. He meant that
Searchbuilder patched this already…
Could it be thats that already a problem? I printed out the parameter of the
$session{‘tickets’}. There stand that DBIxHandle is the RT::Handle. And thats
the same like in SearchBuilder $self. But in $session{‘tickets’} I haven´t
the parameter ‘records’. It stands for the statementhandle, doesn´t it? Would
it be useful to set the parameter to $session{‘tickets’} in Web.pm?

Here are my printouts when I search after tickets:

in /html/autohandler - Handle = RT::Handle=HASH(0x9436dcc),
Apache::DBI::db=HASH(0x8f2a0d8)und das andere Handle?
CDB::Handle=HASH(0x943a884)
Rt::Handle: dsn = dbi:Pg:dbname=rt3;host=localhost
Rt::Handle: DisconnectHandleOnDestroy =
CDB Handle: dsn = dbi:Pg:dbname=keystone;host=roessl
CDB Handle: DisconnectHandleOnDestroy =
Web.pm 599 - TicketsHash _open_parens = HASH(0x88f803c)
Web.pm 599 - TicketsHash _sql_watchersc = 0
Web.pm 599 - TicketsHash table = Tickets
Web.pm 599 - TicketsHash _sql_linkalias =
Web.pm 599 - TicketsHash _sql_trattachalias =
Web.pm 599 - TicketsHash tables =
Web.pm 599 - TicketsHash _sql_localdepth = 0
Web.pm 599 - TicketsHash is_limited = 1
Web.pm 599 - TicketsHash order =
Web.pm 599 - TicketsHash _sql_query = ( ‘Owner’ = ‘40’ ) AND ( ‘Status’ =
‘open’ ) AND ( ‘Created’ > ‘2004-03-31 22:00:00’ )
Web.pm 599 - TicketsHash user = RT::CurrentUser=HASH(0x88f7598)
Web.pm 599 - TicketsHash restrictions = HASH(0x88f7bf8)
Web.pm 599 - TicketsHash primary_key = id
Web.pm 599 - TicketsHash count_all = 0
Web.pm 599 - TicketsHash _sql_opstack = ARRAY(0x88f7d90)
Web.pm 599 - TicketsHash looking_at_effective_id = 0
Web.pm 599 - TicketsHash must_redo_search = 1
Web.pm 599 - TicketsHash subclauses = HASH(0x88f7dcc)
Web.pm 599 - TicketsHash _sql_looking_at = HASH(0x88f7df0)
Web.pm 599 - TicketsHash _sql_depth = 0
Web.pm 599 - TicketsHash alias_count = 0
Web.pm 599 - TicketsHash where_clause =
Web.pm 599 - TicketsHash RecalcTicketLimits = 0
Web.pm 599 - TicketsHash _sql_subclause = a
Web.pm 599 - TicketsHash order_clause = ORDER BY main.id DESC
Web.pm 599 - TicketsHash restriction_index = 6
Web.pm 599 - TicketsHash _sql_transalias =
Web.pm 599 - TicketsHash DBIxHandle = RT::Handle=HASH(0x88f7ea4)
Web.pm 599 - TicketsHash limit_clause =
Web.pm 599 - TicketsHash _sql_keywordsc = 0
Web.pm 599 - TicketsHash auxillary_tables =
Web.pm 599 - TicketsHash _sql_keywordalias =
Web.pm 599 - TicketsHash itemscount = 0
Web.pm 599 - TicketsHash show_rows = 50
Web.pm 599 - TicketsHash _sql_columns_to_display = ARRAY(0x88f7f1c)
Web.pm 599 - TicketsHash _sql_first = 0
Web.pm 599 - TicketsHash left_joins = HASH(0x88f7f34)
Web.pm 599 - TicketsHash _sql_linksc = 0
Web.pm 599 - TicketsHash aliases = ARRAY(0x88f7f58)
Web.pm 599 - TicketsHash TicketRestrictions = HASH(0x88f8018)
Web.pm 599 - TicketsHash first_row = 0
Web.pm 599 - TicketsHash looking_at_type = 0
Web das Handle: dsn = dbi:Pg:dbname=rt3;host=localhost
Web das Handle: DisconnectHandleOnDestroy =
SearchBuilder.pm 216 - Query: SELECT count(main.id) FROM Tickets main
WHERE ((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’)) AND ( ( (main.Owner = ‘40’) ) AND ( (main.Status
= ‘open’) ) AND ( (main.Created > ‘2004-03-31 20:00’) ) )
SearchBuilder.pm 224 - Welches Handle habe ich ? dsn =
dbi:Pg:dbname=rt3;host=localhost
SearchBuilder.pm 224 - Welches Handle habe ich ? DisconnectHandleOnDestroy
= SearchBuilder.pm 236 self->records/Statementhandle =
DBI::st=HASH(0x96c7f34)

In rt.log I get always this errors:
[Thu Jul 22 09:16:52 2004] [warning]: DBD::Pg::st execute failed: ERROR:
Relation “tickets” does not exist at
/usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder.pm line 243.
(/opt/rt3/lib/RT.pm:250)
[Thu Jul 22 09:16:52 2004] [warning]: DBIx::SearchBuilder error:ERROR:
Relation “tickets” does not exist
Query String is SELECT count(main.id) FROM Tickets main WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’)) AND ( ( (main.Owner = ‘40’) ) AND ( (main.Status
= ‘open’) ) AND ( (main.Created > ‘2004-03-31 20:00’) ) )
(/opt/rt3/lib/RT.pm:250)
[Thu Jul 22 09:16:52 2004] [warning]: DBD::Pg::st fetchrow_array failed:
no statement executing at
/usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder.pm line 257.
(/opt/rt3/lib/RT.pm:250)

But I think, when I make subclasses of SearchBuilder and
SearchBuilder::Record and put them in my folder CDB, then it can work,
couldn´t it?

You can do it as RT do. It will allow you set default handle, and
primary key. As I can see you code is ok. You have only one small glitch
you shouldn’t change $self->{‘aliases’}, current table always get alias
’main’.

Try to use standalone script to test your API. It must work.

I can try it, but there where I need my own database connection to show the
datas in the customfields, I get the right datas. And also anything else in
RT works except the search! So Apache::DBI can differ the two db-connection,
because RT wouldn´t work at all. The question is also, why does the search of
RT break with the second handle? And what doesn´t I regard in use RT or
Mason?

Thanks already.