====== Database Access ======

Moodle 2 stores a handle to its database object in the global var ''$DB''. Use ''$DB'' to perform all database related activities.

Take a look at lib/dml/moodle_database.php and lib/dml/mysqli_native_moodle_database.php to see what functions are available.

In the background, Moodle uses the ADOdb Database Abstraction Library, which is documented [[http://adodb.sourceforge.net/|here]]. Please note that they also mention an extension which "provides up to 100% speedup by replacing parts of ADOdb with C code".

Please note that Moodle 2 now also sanitizes all data before an insert or update (something you had to do yourself in Moodle versions <= 1.9).

==== Connecting to an External Database ====

You can use the standard Moodle database abstraction layer to connect to a foreign database. Here's a simple example. Assumption: the foreign database is the same type (e.g. MySQL).

<code>
global $source_db, $DB, $CFG;

$db_class = get_class($DB);
$source_db = new $db_class(); // instantiate a new object of the same class as the original $DB
$source_db->connect($CFG->source_dbhost, $CFG->source_dbuser, $CFG->source_dbpass, $CFG->source_dbname, false);
</code>

Here, we've used the $CFG configuration object (from ''config.php'') to store the settings for the foreign database.

Following this example, you can now access the foreign the database using the ''$source_db'' handle.

WARNING: In my current version of Moodle 2 (2.0.2, build 20110309), not all DB functions seem to work with an external database. Example: ''get_records_select'' does not seem to work properly. Fortunately, you can always revert to using sql all the way: ''get_records_sql''.




==== DB Abstraction Layer (DML) Issues ====

=== "Raw" SQL through $DB->execute() ===

Use ''$DB->execute($your_sql)'' to execute any arbitrary sql string. Well, the string cannot be entirely arbitrary SQL. Moodle uses 'parameters' in most DB related functions, which let you insert user derived data in a save way (to prevent sql injection attacks). For each and every method, $DB->fix_sql_params() is called. This means that even when you call ''$DB->execute'' without params (as a 2nd argument), the check is still performed. Here's the problem: ''$DB->fix_sql_params()'' is going to complain if you happen to have a "parameter" in your sql code. Example:

<code>
$sql = "INSERT INTO mdl_mod_myblog (label) VALUES ('Oops:this label inadvertently contains a parameter')";
</code>

Here, '':this'' is seen as a parameter (presumably because the colon was not followed by a white space character). Now, if you try executing this sql, you'll encounter an exception:

<code>
$DB->execute($sql);
</code>

This yields: "ERROR: Incorrect number of query parameters. Expected 1, got 0."

The only solution is to check your sql and replace all occurrences of a colon followed by a non-space character, with a colon and a white space character.

Generally speaking, this format seems to be the most robust:

<code>
if (! ($records = $DB->get_records_select("compass_elements",
                "category_id = ? AND label = ? ",
                array('category_id' => $category_id, 'label' => $label))) ) 
  return false;
</code>

In other words: don't use quotes around strings, do use question marks as place holders.