By ccorbett on

Morning All --

I'm working on a module where I want to do some DB interactions with an external database. The external database, for the moment, is a 2nd database residing in my MYSQL installation on my local machine.

My settings.php has the following:

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'wcpd',
      'username' => 'root',
      'password' => '',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
    'mock_banner' => array (
    'default' => array (
      'database' => 'mock_banner',
      'username' => 'root',
      'password' => '',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

And my module has the following:

function db_test_menu() {
  $items['dbtest-page'] = array(
    'page callback' => 'db_test_page',
    'access arguments' => array('access content'),
    'type' => MENU_CALLBACK,
  );
  return $items;
}

function db_test_page() {
  return "Trying to insert into TBRACCD";

  db_set_active('mock_banner');
  
    $insert = db_insert('tbraccd')
    ->fields(array(
      'tran_number' => '123456',
      'term_code' => '201302',
      'user' => $user->uid,
    ))
    ->execute();

  return $insert;

  db_set_active('default');

When I visit http://localhost/dbtest-page in my browser, the page is displayed and I see my "Trying to insert" text. However, when I look in PHPmyadmin, I see that nothing has been inserted into my external database table. The fieldnames in my table all have tbraccd_ as a prefix. I originally had those defined in my three field names. I removed them and tried it as I pasted above, with the same result.

Could someone tell me if they see something wrong with what I've done? What can I do to trouble shoot further to see if the query is executing successfully or not?

Any help would be appreciated.

Thank you!

-- Chris

⋅ Categories: Drupal 7.x

Comments

nevets’s picture

nevets commented

Since this
return "Trying to insert into TBRACCD";
is a return statement, nothing after it executes.

Try moving it to be the last line in the function.

ccorbett’s picture

ccorbett commented

I changed the return to print. Now it prints out my text and a slew of SQL errors since I forgot to set default values on my columns. I fixed those, now I'm getting an integrity violation on my user column:

Additional uncaught exception thrown while handling exception.
Original

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'tbraccd_user' cannot be null: INSERT INTO {tbraccd} (tbraccd_tran_number, tbraccd_term_code, tbraccd_user) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array ( [:db_insert_placeholder_0] => 123456 [:db_insert_placeholder_1] => 201302 [:db_insert_placeholder_2] => ) in db_test_page() (line 26 of C:\xampp\htdocs\wcpd\sites\all\modules\custom\db_test\db_test.module).
Additional

PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mock_banner.filter_format' doesn't exist: SELECT ff.* FROM {filter_format} ff WHERE (status = :db_condition_placeholder_0) ORDER BY weight ASC; Array ( [:db_condition_placeholder_0] => 1 ) in filter_formats() (line 427 of C:\xampp\htdocs\wcpd\modules\filter\filter.module).
Uncaught exception thrown in session handler.

PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mock_banner.sessions' doesn't exist: SELECT 1 AS expression FROM {sessions} sessions WHERE ( (sid = :db_condition_placeholder_0) AND (ssid = :db_condition_placeholder_1) ) FOR UPDATE; Array ( [:db_condition_placeholder_0] => 8EqtUP6M5DzXW6xJARRlA3PAyX50p2K2AiswZeS9Aqc [:db_condition_placeholder_1] => ) in _drupal_session_write() (line 209 of C:\xampp\htdocs\wcpd\includes\session.inc).

Not sure about the integrity error. That column is set to be not null and has a default value.

And some missing tables filter_format and sessions? How would I have known I needed these?

ccorbett’s picture

ccorbett commented

Reading more about the Database API, I now believe that it can/does not work against an external database. so I've changed it to the following:

function db_test_menu() {
  $items['dbtest-page'] = array(
    'page callback' => 'db_test_page',
    'access arguments' => array('access content'),
    'type' => MENU_CALLBACK,
  );
  return $items;
}

function db_test_page() {
  print "Trying to insert into DB TEST";

  db_set_active('testdb');
  
  mysql_query("
    INSERT INTO test_connect SET
      first_name = 'Chris'
    ");
    


  db_set_active('default');
}

I've dumbed it down so all I'm trying to insert is one field via the mysql_query command

So I change the active database, execute the command then immediately switch the database back to default.

When I visit the page, I see the text I print out to the screen and nothing else. No layout, no errors. Looking in my external db, nothing is inserted.

So, my questions remain: What am I missing and how can I troubleshoot further?

Any further guidance would be greatly appreciated.

Thank you!

nevets’s picture

nevets commented

Instead of mysql_query() you want to use db_query().

ccorbett’s picture

ccorbett commented

That got me another step closer. Thanks, nevets.

Now, I'm getting errors similar to this

I also tried the method suggested on the page that you linked to from the page above to no avail. I'm sure its something simple that I'm just not seeing.

Is there a method I can use to test for the database connection before I try to run the query? That way I could at least get the page to end gracefully and give me an idea of whats happening rather than bomb out...

ccorbett’s picture

ccorbett commented

I've now taken my external db connections out of settings.php and I have it set up on the fly in the module itself. I also added a not condition to the db_query as follows:

function db_test_page() {
  print "Trying to insert into DB TEST";
  
  
      $testdb = array (
      'database' => 'testdb',
      'username' => 'root',
      'password' => '',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => ''
    );

    Database::addConnectionInfo('test_db', 'default', $testdb);
    
  db_set_active('test_db');
  
  if (!db_query("
    INSERT INTO {test_connect}
      (first_name)
      values ('Chris')
    ")) {
    print 'Could Not Connect';
  };

  db_set_active();
}

and that worked!