Check if external database exists $DB->get_records_sql_menu

Check if external database exists $DB->get_records_sql_menu

by Andrew C -
Number of replies: 3

Hi Everyone,


I need to pull data from a separate system into a plugin I've written. It works fantastically well. I'd like to put a fail safe into my plugin so that if for some reason the table in my external database is not present, then the data is not loaded. I would add a warning instead.


Currently, I get the message:

Error reading from database

Table x dosn't exist...


A simple solution would be for me to import the table into the Moodle database where I can use commands to check for it's presence. However, I would prefer an IF EXISTS, If ! == NULL or other code based solution to make this work.


Is this possible?


Thanks,

Andrew



Average of ratings: -
In reply to Andrew C

Re: Check if external database exists $DB->get_records_sql_menu

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

There are functions you can use for checking the existence of tables, but they're really designed for use during install/upgrade.

$dbman = $DB->get_manager();
if ($dbman->table_exists('name_of_table')) {

But, you should be aware that this uses a cached list of tables that Moodle knows about, so will probably not work with tables created outside of the Moodle install/upgrade code.

I'd assume, in this case, you're likely to need to manually check for the table existence (or wrap a try-catch block around the attempts to read from it, to show a more user-friendly error message).


Average of ratings: Useful (1)
In reply to Davo Smith

Re: Check if external database exists $DB->get_records_sql_menu

by Andrew C -

Thanks Davo, I thought that may be the case.

On the external database, I may create a checking table first based on a table I know will always be present such as the information.schema with a CASE statement to setting a record value to 1 or 0 and then use this for my IF statement. Still not a perfect solution but a solution though.

Average of ratings: -
In reply to Andrew C

Re: Check if external database exists $DB->get_records_sql_menu

by Andrew C -

I've decided to add a Moodle tracker recommendation. I just think it makes sense to be able to check whether an external database tables exists before performing an action so that I can have control over the error output.

https://tracker.moodle.org/browse/MDL-60017

In my situation, because my plugin is part of an activity, a database error knocks out the whole activity. While this isn't likely to be a problem, I prefer to have a fallback.

Thanks,

Andrew

Average of ratings: -