External DB connection inside an extension ?

[Stephane-8]

Hi Typo'Gurus

I nedd your help. I have a question about external db connection inside
typo3 extension.

I'm writing an extension who needs to get data from another db (I know,
accessing directly to external db is not the best way, but it's the more
efficient approach (for speed) than I found after testing SOAP (SOAP is
very very very slow) and Co).

Then, in my extension, I open a new connection to my external db via
mysql_connect command. I'm able to get all necessaries datas with
queries ... no problem .. until here.

The problem come just after. After this mysql_query on external db, I
don't know why but I lost the connection to Typo3 db :(.

$GLOBALS['TYPO3_DB']->sql_num_rows return always return 0 (records exist
of course), until I call back $GLOBALS['TYPO3_DB']->connectDB().
After this call I could again access to Typo3 db.

Sample code / algo :

$mylink = mysql_pconnect($server, $username, $password);
$mydb = mysql_select_db($dbname, $mylink);
...
$result = mysql_query($myquery, $mylink);
[ ok I'm able to get external records ]
...
$res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('*', $this->table,
$whereClause);
[always = 0 :( ]
...
$GLOBALS['TYPO3_DB']->connectDB();
[now it's ok again, I could access to Typo3 db (normal I create a new
link ressource) ... :s ]

I have try to use also "datasources" and "adob" approach (as done in
rlmp_extdbauth for example) instead of "direct php approach", but with
the same result :(. I try also pconnect or not, using
t3lib_DB::sql_pconnect functions ... a lot of test but without better
results ... Typo3 seems to use current active database, and not the
$link information

I'm using Typo3 4.2.1 and develop a service extension ...

What is wrong in my approach ? It's a Typo3 know issue (with a solution
;)) or a problem on my php/mysql configuration ?

Thanks a lot for your help (and sorry for my english)

BR

Stef
_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Mauro Lorenzutti]

Hi Stephane,

> Hi Typo'Gurus
>
> I nedd your help. I have a question about external db connection inside
> typo3 extension.
>
> I'm writing an extension who needs to get data from another db (I know,
> accessing directly to external db is not the best way, but it's the more
> efficient approach (for speed) than I found after testing SOAP (SOAP is
> very very very slow) and Co).
>
> Then, in my extension, I open a new connection to my external db via
> mysql_connect command. I'm able to get all necessaries datas with
> queries ... no problem .. until here.
>
> The problem come just after. After this mysql_query on external db, I
> don't know why but I lost the connection to Typo3 db :(.
>
> $GLOBALS['TYPO3_DB']->sql_num_rows return always return 0 (records exist
> of course), until I call back $GLOBALS['TYPO3_DB']->connectDB().
> After this call I could again access to Typo3 db.
>
> Sample code / algo :
>
> $mylink = mysql_pconnect($server, $username, $password);
>  

I already noticed this problem and I solved it with a mysql_nconnect
insteas of mysql_pconnect. I hope this will solve your problem.

P.S. If you are connecting an external DB in TYPO3, have you ever tried
the "DB Integration" (wfqbe) extension?

Regards,

--
 Mauro Lorenzutti

e-mail:  mauro.lorenzutti@...
---------------------------------------------------------
WEBFORMAT srl | Corte Europa, 12 | I-33097 SPILIMBERGO PN
     Tel [phone]  --  Fax [phone]
       info@...  --  http://www.webformat.com
---------------------------------------------------------

_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Stephane-8]

Hi

Mauro Lorenzutti wrote:

> Hi Stephane,
>
>> Hi Typo'Gurus
>>
>> I nedd your help. I have a question about external db connection inside
>> typo3 extension.
>>
>> I'm writing an extension who needs to get data from another db (I know,
>> accessing directly to external db is not the best way, but it's the more
>> efficient approach (for speed) than I found after testing SOAP (SOAP is
>> very very very slow) and Co).
>>
>> Then, in my extension, I open a new connection to my external db via
>> mysql_connect command. I'm able to get all necessaries datas with
>> queries ... no problem .. until here.
>>
>> The problem come just after. After this mysql_query on external db, I
>> don't know why but I lost the connection to Typo3 db :(.
>>
>> $GLOBALS['TYPO3_DB']->sql_num_rows return always return 0 (records exist
>> of course), until I call back $GLOBALS['TYPO3_DB']->connectDB().
>> After this call I could again access to Typo3 db.
>>
>> Sample code / algo :
>>
>> $mylink = mysql_pconnect($server, $username, $password);
>>  
>
> I already noticed this problem and I solved it with a mysql_nconnect
> insteas of mysql_pconnect. I hope this will solve your problem.

mysql_Nconnect ? or you talk about ADOdb->NConnect ?

If you talk about mysql_connect / mysql_pconnect, this approach work
only if you use a mysql connect # as Typo3 config.
To resume, if you try 2 mysql_connect, I lost the T3 connection
If you try with 2 mysql_pconnect, I lost the connection too
With 1 mysql_connect and 1 mysql_pconnect, this approach works but I
think it's not a good one (pconnect should be keep as T3 parameter
depending of hosting configuration).
>
> P.S. If you are connecting an external DB in TYPO3, have you ever tried
> the "DB Integration" (wfqbe) extension?
I have a look on your extension. But you use also ADOdb, and I do
exactly the same thinks as inside tx_wfqbe_connect::connect function.
But it doesn't work in my case (thanks for your extension but I just
need to do 1 request, I don't new all the nice stuff presented in cour
extension).

BR

Stef
>
> Regards,
>
_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Stephane-8]

Hi again

I just done a test with $dbConn->NConnect($server, $username, $password,
$dbname); instead of connect or pconnect (with ADOdb) and it's seems to
work well now.

Thanks a lot !!!

Stephane

Mauro Lorenzutti wrote:

> Hi Stephane,
>
>  
>> Hi Typo'Gurus
>>
>> I nedd your help. I have a question about external db connection inside
>> typo3 extension.
>>
>> I'm writing an extension who needs to get data from another db (I know,
>> accessing directly to external db is not the best way, but it's the more
>> efficient approach (for speed) than I found after testing SOAP (SOAP is
>> very very very slow) and Co).
>>
>> Then, in my extension, I open a new connection to my external db via
>> mysql_connect command. I'm able to get all necessaries datas with
>> queries ... no problem .. until here.
>>
>> The problem come just after. After this mysql_query on external db, I
>> don't know why but I lost the connection to Typo3 db :(.
>>
>> $GLOBALS['TYPO3_DB']->sql_num_rows return always return 0 (records exist
>> of course), until I call back $GLOBALS['TYPO3_DB']->connectDB().
>> After this call I could again access to Typo3 db.
>>
>> Sample code / algo :
>>
>> $mylink = mysql_pconnect($server, $username, $password);
>>  
>>    
>
> I already noticed this problem and I solved it with a mysql_nconnect
> insteas of mysql_pconnect. I hope this will solve your problem.
>
> P.S. If you are connecting an external DB in TYPO3, have you ever tried
> the "DB Integration" (wfqbe) extension?
>
> Regards,
>
>  

_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Mauro Lorenzutti]

Hi Stephane,

>> I already noticed this problem and I solved it with a mysql_nconnect
>> insteas of mysql_pconnect. I hope this will solve your problem.
>>    
> mysql_Nconnect ? or you talk about ADOdb->NConnect ?
>  

yes, you are right, it's an ADODB function...


Regards,

--
 Mauro Lorenzutti

e-mail:  mauro.lorenzutti@...
---------------------------------------------------------
WEBFORMAT srl | Corte Europa, 12 | I-33097 SPILIMBERGO PN
     Tel [phone]  --  Fax [phone]
       info@...  --  http://www.webformat.com
---------------------------------------------------------

_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Mauro Lorenzutti]

Stephane ha scritto:
> Hi again
>
> I just done a test with $dbConn->NConnect($server, $username, $password,
> $dbname); instead of connect or pconnect (with ADOdb) and it's seems to
> work well now.
>
> Thanks a lot !!!
>  

great!

Have a nice coding night ;-)

--
 Mauro Lorenzutti

e-mail:  mauro.lorenzutti@...
---------------------------------------------------------
WEBFORMAT srl | Corte Europa, 12 | I-33097 SPILIMBERGO PN
     Tel [phone]  --  Fax [phone]
       info@...  --  http://www.webformat.com
---------------------------------------------------------

_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Dmitry Dulepov [typo3]]

Hi!

Stephane wrote:
> Then, in my extension, I open a new connection to my external db via
> mysql_connect command. I'm able to get all necessaries datas with
> queries ... no problem .. until here.
>
> The problem come just after. After this mysql_query on external db, I
> don't know why but I lost the connection to Typo3 db :(.

This happens in PHP if you use the same user name and password to different databases. PHP just selects another database and reuses the same connections. Normally each database must have its own user.

> $GLOBALS['TYPO3_DB']->sql_num_rows return always return 0 (records exist
> of course), until I call back $GLOBALS['TYPO3_DB']->connectDB().
> After this call I could again access to Typo3 db.

Yes, this call will select TYPO3 database again :)

--
Dmitry Dulepov
TYPO3 Core team
More about TYPO3: http://typo3bloke.net/
Subscribe: http://typo3bloke.net/rss.xml
_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Ernesto Baschny [cron IT]]

Dmitry Dulepov [typo3] wrote: on 10.07.2008 19:49:

>> Then, in my extension, I open a new connection to my external db via
>> mysql_connect command. I'm able to get all necessaries datas with
>> queries ... no problem .. until here.
>>
>> The problem come just after. After this mysql_query on external db, I
>> don't know why but I lost the connection to Typo3 db :(.
>
> This happens in PHP if you use the same user name and password to
> different databases. PHP just selects another database and reuses the
> same connections. Normally each database must have its own user.

True! And Stephane, Mauro: The reason why with AdoDB it works, is
because NConnect calls mysql_connect with the $new_link=TRUE parameter,
which defaults to "FALSE" (re-use existing link).

See:

http://www.php.net/mysql_connect

for more details on the mysql_connect parameter $new_link.

Cheers,
Ernesto
_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[ries van Twisk-2]

you can use DBAL or rvt_adodb.

rvt_adodb uses a DBAL compatible syntax, but will not slow down your  
overall TYPO3 installation.

rvt_adodb will just open an additional  connection for you at the  
moment TYPO3 initializes a DB connection.


Ries

On Jul 10, 2008, at 11:07 AM, Stephane wrote:

> Hi Typo'Gurus
>
> I nedd your help. I have a question about external db connection  
> inside
> typo3 extension.
>
> I'm writing an extension who needs to get data from another db (I  
> know,
> accessing directly to external db is not the best way, but it's the  
> more
> efficient approach (for speed) than I found after testing SOAP (SOAP  
> is
> very very very slow) and Co).
>
> Then, in my extension, I open a new connection to my external db via
> mysql_connect command. I'm able to get all necessaries datas with
> queries ... no problem .. until here.
>
> The problem come just after. After this mysql_query on external db, I
> don't know why but I lost the connection to Typo3 db :(.
>
> $GLOBALS['TYPO3_DB']->sql_num_rows return always return 0 (records  
> exist
> of course), until I call back $GLOBALS['TYPO3_DB']->connectDB().
> After this call I could again access to Typo3 db.
>
> Sample code / algo :
>
> $mylink = mysql_pconnect($server, $username, $password);
> $mydb = mysql_select_db($dbname, $mylink);
> ...
> $result = mysql_query($myquery, $mylink);
> [ ok I'm able to get external records ]
> ...
> $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('*', $this->table,
> $whereClause);
> [always = 0 :( ]
> ...
> $GLOBALS['TYPO3_DB']->connectDB();
> [now it's ok again, I could access to Typo3 db (normal I create a new
> link ressource) ... :s ]
>
> I have try to use also "datasources" and "adob" approach (as done in
> rlmp_extdbauth for example) instead of "direct php approach", but with
> the same result :(. I try also pconnect or not, using
> t3lib_DB::sql_pconnect functions ... a lot of test but without better
> results ... Typo3 seems to use current active database, and not the
> $link information
>
> I'm using Typo3 4.2.1 and develop a service extension ...
>
> What is wrong in my approach ? It's a Typo3 know issue (with a  
> solution
> ;)) or a problem on my php/mysql configuration ?
>
> Thanks a lot for your help (and sorry for my english)
>
> BR
>
> Stef
> _______________________________________________
> TYPO3-dev mailing list
> TYPO3-dev@...
> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Stephane-8]

Thanks Dimitry and Ernesto (and all) for this both informations.

Stephane

Ernesto Baschny [cron IT] wrote:

> Dmitry Dulepov [typo3] wrote: on 10.07.2008 19:49:
>
>  
>>> Then, in my extension, I open a new connection to my external db via
>>> mysql_connect command. I'm able to get all necessaries datas with
>>> queries ... no problem .. until here.
>>>
>>> The problem come just after. After this mysql_query on external db, I
>>> don't know why but I lost the connection to Typo3 db :(.
>>>      
>> This happens in PHP if you use the same user name and password to
>> different databases. PHP just selects another database and reuses the
>> same connections. Normally each database must have its own user.
>>    
>
> True! And Stephane, Mauro: The reason why with AdoDB it works, is
> because NConnect calls mysql_connect with the $new_link=TRUE parameter,
> which defaults to "FALSE" (re-use existing link).
>
> See:
>
> http://www.php.net/mysql_connect
>
> for more details on the mysql_connect parameter $new_link.
>
> Cheers,
> Ernesto
> _______________________________________________
> TYPO3-dev mailing list
> TYPO3-dev@...
> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev
>  

_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Jigal van Hemert-3]

Very late reply; finally catching up with loads of e-mail :-)

Dmitry Dulepov [typo3] wrote:

> Stephane wrote:
>> Then, in my extension, I open a new connection to my external db
>> via mysql_connect command. I'm able to get all necessaries datas
>> with queries ... no problem .. until here.
>>
>> The problem come just after. After this mysql_query on external db,
>> I don't know why but I lost the connection to Typo3 db :(.
>
> This happens in PHP if you use the same user name and password to
> different databases. PHP just selects another database and reuses the
> same connections. Normally each database must have its own user.

There is a fourth (and fifth) parameter for mysql_connect():

resource mysql_connect  ([ string $server  [, string $username  [,
string $password  [, bool $new_link  [, int $client_flags  ]]]]] )

If you set $new_link to TRUE, it will force a new connection even if the
same server + username + password are used. No need to reconnect the
TYPO3_DB again :-)

--
Jigal van Hemert.

_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev