Problem with " db_set_active " function : not working properly !

ralfph - June 22, 2009 - 13:29

hello,

I'm having trouble implementinfg the function db_set_active, i followed accurateley its way of usage.
i'm developing a new module that requires connection to MSSQL, i did all the configuration for this connection to work and it is working properly and i 'm able to connect to it.
now:

in settings.php:
$db_url['default'] = 'mysql://root@localhost/drupal';
$db_url['NDJ'] = 'mssql://sa:pwd:1029@192.168.0.144:1029/NDJ';
$db_prefix = '';

in one function of my module i set the following:

db_set_active('NDJ');// beginning of my function
.
.
.
'query' => '
SELECT Nom,Prenom FROM [NDJ].[dbo].[Anc_Eleves]
',
.
.
.
db_set_active('default');// at the end of my fonction

the problem is that the page which calls this function takes time to display and finally displays a BLANK page.

Thank u! any help is apreciated

» Login or register to post comments

Does you function return any

nevets - June 22, 2009 - 13:35

Does you function return any text?

HERE IS MY WHOLE FUNCTION:

ralfph - June 22, 2009 - 14:04

HERE IS MY WHOLE FUNCTION: WELL YES IT IS RETUTNING $RENDERED (AS U CAN SEE AT THE END)

<?php
function ajaxtable_render($id,$uid='',$refresh=FALSE,$input_value='') {
db_set_active('NDJ');

  // Keep track of what css files have been included to include only oncce
  global $ajaxtable_css_included;
  // An array containing variables that can be accessed by other ajaxtable functions
  global $ajaxtable_vars;
    
    // Add jscalendar function script
    if (!$ajaxtable_vars['included_js']) {
        drupal_add_js(drupal_get_path('module','ajaxtable') . '/jscalendar.js');
        $ajaxtable_vars['incuded_js'] = 1;
    }
    
  // Run function to include the table parameters
  $function_name = 'ajaxtable_table_' . $id;
  if (!function_exists($function_name)) {
    return '<p><strong>ajaxtable: There is no definition for the table "' . $id . '"</strong></p>';
  } else {
    $table = $function_name(); 
    // Set defaults
    $default_table = array(
      'rows' => 50,
      'theme_module' => 'ajaxtable',
      'pages' => 8,
      'theme' => 'default',
      'search_inputs' => array(),
            //'click_row' => TRUE,
            //'hover' => TRUE,
      'no_access' => '<p>Sorry, you don\'t have permission to view this table.</p>',
      'empty' => '<p>There were no results that match your query.</p>',
      'query' => '
        SELECT Nom,Prenom FROM [NDJ].[dbo].[Anc_Eleves] 
      ',
      'columns' => array(
        array(
          'col' => 'nid',
          'label' => 'NID',
          'sortable' => TRUE,
          'searchable' => TRUE,
          'default_sort' => 'desc',
          'help' => 'NID stands for Node ID. Most types of data are based on nodes, and each has a unique ID. This ID is useful for tracking the history of the node and other reporting. The NID is incremented with each entry, so larger numbers were created more recently.',
        ),
        array(
          'col' => 'title',
          'label' => 'Title',
          'sortable' => TRUE,
          'searchable' => TRUE,
          'help' => 'The title of the node',
        ),
      ),
    );
    foreach($default_table as $key=>$value) {
      if ($table[$key] == "") {
        $table[$key] = $value;
      }
            // Set defaults of 'click_row' and 'hover' to true
            /*
            // Commenting this out for now. Doesn't provide a huge value
            if ($key == 'click_row' && $value !== FALSE) {
                $table[$key] = TRUE;
            }
            if ($key == 'hover' && $value !== FALSE) {
                $table[$key] = TRUE;
            }
            */
    }
    
    
    $ajaxtable_vars['table'] = $table;
    
    // Set default theme
    if ($table['theme'] == '') {
      $table['theme'] = 'default';
    }
    // Get the theme directory
    $ajaxtable_vars['theme_dir'] = drupal_get_path('module',$table['theme_module']) . '/ajaxtable_themes/' . $table['theme'] . '/';
    // Include theme css file
    if (!$ajaxtable_css_included[$table['theme']]) {
      drupal_add_css($ajaxtable_vars['theme_dir'] . 'styles.css');
      $ajaxtable_css_included[$table['theme']] = TRUE;
    }
    // Return or print the table
    $rendered = _ajaxtable_render($table,$id,$uid,$refresh,$input_value);
    $ajaxtable_vars['table'] = $table;
    if ($refresh) {
      echo $rendered;
    }
    return $rendered;
    db_set_active('default');
    
  }
}
?>

SO HOW TO FIx THE ISSUE ?

One source of problem return

nevets - June 22, 2009 - 17:12

One source of problem

return $rendered;
db_set_active('default');

When using db_set_active I always call right after access the database. In your code every place there is a return you need to call db_set_active('default'); before the return (otherwise it is never called).

Still it is not working :(

ralfph - June 23, 2009 - 10:30

Still it is not working :( ...
anyone else has any suggestion ?
i still have that BLANK page .

NB: my module is working perfectly on the local databse ( the drupal one) and i also added the database.mssql.inc file

thank u !

are you sure your module can

siodong - June 23, 2009 - 11:08

are you sure your module can work properly on your local? and do you already test your that it passed function_exists($function_name)? because there's no db_set_active('default') function before return too. meanwhile, i just can suggest, switch to debug mode and run your code step by step, so you can trace where the problem is, or simply print some string then exit just to let you know that code has run to some line.

hello siodung, well yes my

ralfph - June 23, 2009 - 14:12

hello siodung, well yes my module worked perfectly locally on drupal (mysql database). i believe that the problem resides in the change of databse. another thing is that i cant debug using print, because once i put db_set_active('NDJ') the page keeps turning until finally displays a blank page. once i remove db_set_active('NDJ') i have the error mssql_db_query() is not defined.

hope u can help me otherwise, cause i ll get crazy soon :D

hmm.. if the error is

siodong - June 23, 2009 - 16:08

hmm.. if the error is "mssql_db_query() is not defined", do you already check that mssql_db_query exists? cmiiw, if you are trying to query mssql in natural php function, there's mssql_query.
moreover, on your module stated that you call drupal functions which may cause a problem, it is :
- drupal_get_path which is calling drupal_get_filename that query to drupal database(mysql), while db active is not default (NDJ)

try to place db_set_active in a better placement to reduce error on your module.

thanks anyway siodong, but

ralfph - June 23, 2009 - 17:43

thanks anyway siodong, but the truth is that it is not going to work, look down at the reply i did at the bottom of the page.
the thing is that in drupal i cant switch from different type of database. i want to switch from mysql to mssql but db_set_active only works for databases of same type UFORTUNATELY.

thanks anyway for ur help.

well.. sorry, i didn't read

siodong - June 24, 2009 - 02:21

well.. sorry, i didn't read your last reply before. but, i think it still possible to connect drupal to mssql, even you need to build your own module. maybe you can start to finding out (if you want to) from a nice discussion about mssql and drupal here.

regards,

I'd suggest modifying your

barckhoff - June 23, 2009 - 11:21

I'd suggest modifying your SELECT statement. It's recommended to use curly braces {} around the table name. (See here: http://api.drupal.org/api/group/database) Is your table named [NDJ].[dbo].[Anc_Eleves] ?

I would suggest trying this with a simplified version of your code to simply print out data directly from your SELECT query to troubleshoot it to make sure you can actually retrieve data, then when you've got that sorted out, you can put the rest of the code back in.

Good luck!

hello barckoff, i did the

ralfph - June 23, 2009 - 13:46

hello barckoff,

i did the simpple connect.php file which runs perfectly:

<?php
    $link = mssql_connect('192.168.0.144:1029', 'sa', 'admin3112');
    if(!$link) {
        echo'Could not connect';
        die('Could not connect: ' . mssql_error());
    }
    echo'Successful connection';
    $version =mssql_query('SELECT * FROM Anc_Eleves');
    $row = mssql_fetch_array($version);

echo $row[0];
    mssql_close($link);
?>

I added in my code the two curly brackets: {Anc_Eleves}

i'm scared of a little thing:
in my database.mssql.inc(which i googled it and pasted it), i modified each function such that its name begins with mssql, for ex:

db_connect-> mssql_db_connect etc..

and in ajaxtable.module file(it is a module that i downloaded) i modified all the fuctions for ex: db_connect->mssql_db_connect etc...

do u think that it is because of those modifications ?

at first i left the two files as they where but i was having the error that one of the functions( that begins with db_) is being redefined that's why i changed all the functions that begins with db_ to mssql_db.

NB: i tested the ajax table on the drupal database it worked perfectly.

thank u for ur help appreciated !

OK fine it wont

ralfph - June 23, 2009 - 14:50

OK fine it wont work:(http://drupal.org/node/18429)
This only works with two databases of the same type. For example the following code will not work.

<?php
// ... header of the settings.php file

$databases = array (
"default" => "mysql://user:pass@host/db",
"second" => "pgsql://user:pass@host/db"
);

// ...
?>

i wnat to switch from mssql to mysql so it will not work !!!
thank u anyway for ur help !!