General developer forum

 
 
Picture of Catherine Birch
Databases: MySQL / Windows / Visual Studio / Tools / Versions
 

moodle 1.9.1, MySQL 5.0.something, WIndows XP

New to moodle, don't know MySQL, need to write some programs to access the MySQL database directly from Visual Studio.  Will eventually move to MSSQL as that is the site standard, but not yet. Helping a client make their moodle based eLearning system a bit more robust, and as part of that will migrate to latest stable version (1.9.9+).

 

A few questions:

When I upgrade moodle to 1.9.9+, will the moodle upgrade scripts also upgrade  MySQL to 5.5? If not, should I upgrade MySQL to 5.5 myself? What are the moodle considerations for the MySQL version?

What is the best way to do SQL queries, including updates, in MySQL from Visual Studio? I can choose either VS 2008 or 2010. I might need to create tables. I am just reading about something called Connector\NET.  Is this a Good Thing for someone like me who is a MySQL ignoramus but familiar with using MSSQL from Visual Studio? Or is there a better approach?

http://dev.mysql.com/doc/refman/5.0/en/connector-net-visual-studio.html

Would appreciate to hear comments or other experiences as this is a new environment to me and it is a short term project.

Also, any recommendations for GUI Interface to MySQL for Database Administrator type stuff like creating tables, doing SQL queries, writing stored procs?

 
Average of ratings: -
Picture of David Perry
Re: Databases: MySQL / Windows / Visual Studio / Tools / Versions
Group Testers

I have no experience of visual studio so can't comment on the connector.

However connecting to a MySQL database only requires 2 PHP lines:

$db = mysql_connect("hostname_of_mysql_server","username","password");
then
$db = mysql_select_db("moodle",$db);
(assuming your moodle database is called 'moodle').

Admin tools, we use MySQL Query Browser for running queries, saving results of them etc, and MySQL Administrator for backing up databases manually (there are command line tools as well, like mysqldump to save a database to a .sql file - we run this overnight to back our moodle up).

Moodle upgrades only upgrade the moodle code and database content - you have to upgrade the MySQL Server application yourself. The moodle download page (http://download.moodle.org) tells you what version you need as a MINIMUM - currently (from quick google search):
Requires: PHP 4.3.0, MySQL 4.1.16 or Postgres 8.0 or MSSQL 9.0 or Oracle 9.0
Although we have PHP 5.2.6 and MySQL 5.0.67 which is stable.

Hope that helps.

Dave

 
Average of ratings: -
Picture of Catherine Birch
Re: Databases: MySQL / Windows / Visual Studio / Tools / Versions
 

Thanks for yout reply David, particularly your info on minimum version requirements. I'll have to check out MySQL Query Browser for Windows and MySQL Administrator for WIndows.

Are there any other admin tools that people commonly use?

I want to write as much code as poosible in Visual Studio, not PHP. I come from an MS (Microsoft) background, using .NET and asp pages.  It is an MS site. I need to write a few utility programs to import/export selected data to a file as part of syncing with another (non-relational) database.

I'd really appreciate it if anyone could share their info about linking to the moodle MySQL database from Visual Studio, even if it seems trivial. This is a small but important (to some people) project with little time for trial and error with tools. Has anyone heard of or used this connector thing? Does anyone recommend an ODBC driver?

 

 

 

 
Average of ratings: -
Picture of Dan Marsden
Re: Databases: MySQL / Windows / Visual Studio / Tools / Versions
Group DevelopersGroup Moodle Course Creator Certificate holdersGroup Particularly helpful MoodlersGroup Translators

it's probably a better Idea for you to attempt to use Moodle/PHP code to get your data out - that way when/if you move to MS SQL then your scripts will still work. If you really want to keep using Visual Studio I'd grab a copy of VS.PHP - (I've used it in the past and found it pretty good)

If Vs.PHP is too pricey check out Eclipse or NetBeans you'll find them similar to Visual Studio and it shouldn't take you too long to figure out the basics.

All you need to do so you can access the Moodle db from your script is:

include moodles config.php at the top of the php file

use functions like get_record() get_records get_records_sql() to obtain your data

use something like "require_login()" to prevent guests from accessing your pages.

then just format it in the page using html

If you're familar with ASP then it shouldn't take you too long to figure out how to quickly knock out some PHP based on the examples in existing Moodle code.

good luck!

 
Average of ratings:Useful (1)
Picture of Catherine Birch
Re: Databases: MySQL / Windows / Visual Studio / Tools / Versions
 

Yes, I've kind of assumed that it's a matter of looking at the PHP code that does something similar and beating it into shape. smile

 
Average of ratings: -
Picture of Catherine Birch
Re: Databases: MySQL / Windows / Visual Studio / Tools / Versions
 

Dan, I have installed VS.PHP. Thankyou for pointing me in its direction.

Can you clue me in on xdebug? Did you download it from the same site as VS.PHP (jcx)? I notice there is a version of xdebug included in the default WIndows moodle installation package that I am using for development.

 
Average of ratings: -
Picture of Dan Marsden
Re: Databases: MySQL / Windows / Visual Studio / Tools / Versions
Group DevelopersGroup Moodle Course Creator Certificate holdersGroup Particularly helpful MoodlersGroup Translators

it's been quite a while since I've used vs.php, but I'm pretty sure you only need to download install xdebug seperately if you want to do development work on a seperate machine - I usually have a web server installed on my local machine for debugging/development so didn't need it.

easy way to get Moodle/web server installed on your windows pc:http://www.microsoft.com/web/gallery/Moodle.aspx

good luck!

 
Average of ratings: -
Picture of Tomasz Muras
Re: Databases: MySQL / Windows / Visual Studio / Tools / Versions
Group DevelopersGroup Particularly helpful MoodlersGroup Translators

You should not upgrade to MySQL 5.5 yet - it is not released yet. Oracle has recently announced only a Release Candidate.

Your current version of MySQL should be enough to run Moodle.

Tomasz Muras
Enovation Solutions

 
Average of ratings: -
Picture of Catherine Birch
Re: Databases: MySQL / Windows / Visual Studio / Tools / Versions
 

Thanks for that. I've just installed using the WIndows moodle 1.9.9+ package (with Apache and MySQL) on my home PC, because there is a delay upgrading at work, and I notice that it installed MySQL 5.1.33. So I'm kind of thinking of upgrading to that at work. Although I've noticed in the moodle Administration->Database page it says "Your PHP MySQL library version 5.0.51a differs from your MySQL server version 5.1.33. This may cause unpredictable behavior."

 
Average of ratings: -
Picture of Catherine Birch
Re: Databases: MySQL / Windows / Visual Studio / Tools / Versions
 

I notice that the MySQL GUI Tools Bundle (Administrator, Query Browser, Migration Toolkit) Users of the MySQL GUI Tools Bundle is now replaced by MYSQL Workbench. I installed it and it ran an SQL SELCt statement. All good so far.

http://dev.mysql.com/downloads/gui-tools/5.0.html

 
Average of ratings: -