Connecting to External DataBase

Connecting to External DataBase

by sirisha g@ -
Number of replies: 6

Hello Everyone,

I am using Moodle 2.8.1, with the Mysqli database. I am trying to access External Database from the moodle by using a select command in mysql. I have given the appropriate information of the External Database in Manage Authentication -> External Database -> Settings . And I have done TestSettings and it shows no error.  

The command I have used is:

"SELECT siteid, COUNT(*) FROM users WHERE siteid IS NOT NULL AND siteid <> ''  GROUP BY siteid ORDER BY COUNT(*) DESC, siteid ASC "

Where, "users" is the table form external database and siteid is the column form teh "users" table.

Then it shows me the error as below.

No table

So, I understood that the command is searching for the table within the moodle's database. So, I made changes to the command as follows such that it searches for external database.

"SELECT siteid, COUNT(*) FROM main.users WHERE siteid IS NOT NULL AND siteid <> ''  GROUP BY siteid ORDER BY COUNT(*) DESC, siteid ASC "

Where, "main" is the external database which has the table "users". AND IT SHOWS THE FOLLOWING ERROR.

select

But the user it is showing "webuser_training"  is not the user of External Database, it is the moodle's database user which is mentioned in the config.php file. So, I duplicated the config.php file with another name which has the details of the external database . I gave the new file as the reference instead of  old Config.php. And now it shows be this error.


I am stuck at this point and Have no idea how to proceed forward. Please, Help me out with this. Thanks in advance smile

Average of ratings: -
In reply to sirisha g@

Re: Connecting to External DataBase

by David Mudrák -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators

I am sorry but I am afraid this won't work as you think it would. The "External database" authentication module is not intended to be used for executing custom SQL queries. You can check the auth_db or enrol_database modules in Moodle to see how they use the adodb library to connect to the external database, and use similar approach in your code.

Average of ratings: -
In reply to David Mudrák

Re: Connecting to External DataBase

by David Mesa -

hello David Mudrák

report_overviewstats and install plugins in my personal moodle works perfectly but I wanted to install on the platform of the university also uses moodle to see how I showed the statistics but generate them was loaded but went blank page not if many records .

Not if you happened but I'm looking beyond the problem and nothing would be of great help you to recommend me something. 


Average of ratings: -
In reply to David Mesa

Re: Connecting to External DataBase

by David Mudrák -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators
Hi Jose. Always you get a blank page in Moodle, it typically means there is an error and the site is configured not to display errors. You can either check the web server / PHP error logs, or turn on errors display in Moodle administration UI. Some useful error message should appear instead of the blank page.
Average of ratings: Useful (1)
In reply to David Mudrák

Re: Connecting to External DataBase

by David Mesa -
Hello David , thanks for the reply was very helpful , and I could see the error moodle showing me and the next Fatal error: Allowed memory size of 134217728 bytes exhausted ( tried to allocate 8208 bytes) in / var / www / moodle / admin / tool / log / store / standard / classes / log / store.php on line 81 , and it is out of memory then I went to the line 81 and shows me the following $ data [ 'other '] = unserialize ($ data [' other' ] ) ; moodle --- Linea 81 , if not be error moodle you can help me with this error thanks David .


https://tracker.moodle.org/browse/MDLSITE-3683

Average of ratings: -
In reply to David Mesa

Re: Connecting to External DataBase

by David Mudrák -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of Translators
https://docs.moodle.org/en/Errors_FAQ#Fatal_error_allowed_memory_size_exhausted._How_do_I_increase_my_php_memory_limit.3F
Average of ratings: -
In reply to David Mudrák

Re: Connecting to External DataBase

by David Mesa -
 hi David thanks for the reply I will check to see how it goes , finally you wanted to say something , I need to create some reports , I will be generated from moodle these reports would be a report that shows me the income of the student to see if they are using the platform , the other teachers would be the same case and finally a report that shows me the statistics on how many people entered the platform would appreciate David would be helpful if you have the idea of ​​how to create these reports.
Thanks.
Average of ratings: -