I am in the process of setting up Moodle 1.7 running on a SuSE 10.1 server and connecting to a MS SQL 2005 server for its database. I have downloaded freetds, but the downloaded file is giving me errors when trying to compile the source. Does anyone out there have a freetds RPM that can be installed on SuSE Linux 10.1?
Also, if anyone has a configuration similar to this running, I would be interested in hearing about your experience with it.
Thanks,
Eric
I believe ODBTSP is the recommend method to connect to MSSQL as it has a more active community and uses a more stable API to access MSSQL. With odbtsp, you should be able to install just the php-devel package and rebuild the php_odbtsp_mssql.so module for your PHP, see the ODBTSP install instructions at:
http://odbtp.sourceforge.net/install.html
and the Moodle MSSQL instructions at:
http://docs.moodle.org/en/Installing_MSSQL_for_PHP
If you run into problems, let us know by replying here.
http://odbtp.sourceforge.net/install.html
and the Moodle MSSQL instructions at:
http://docs.moodle.org/en/Installing_MSSQL_for_PHP
If you run into problems, let us know by replying here.
Thanks for the reply Jay,
Are you saying that ODBTP and FreeTDS do the same thing and we just choose which one to use as the MS SQL connector alternative?
Would I be able to use an RPM that was compiled on a different distribution of Linux? If I can use an RPM, is there any other steps that I have to take to get Moodle to communicate with MS SQL 2005?
Are you saying that ODBTP and FreeTDS do the same thing and we just choose which one to use as the MS SQL connector alternative?
Would I be able to use an RPM that was compiled on a different distribution of Linux? If I can use an RPM, is there any other steps that I have to take to get Moodle to communicate with MS SQL 2005?
> Are you saying that ODBTP and FreeTDS do the same thing and we just choose
> which one to use as the MS SQL connector alternative?
Yes, they both provide connectivity between a Unix machine and a MSSQL server. Although how they do it is actually quite different.
> Would I be able to use an RPM that was compiled on a different distribution of
> Linux?
I certainly wouldn't my recommendation is to install the php-devel on your system and recompile odbtsp for yourself. This makes certain that it gets linked properly for your specific distro/version.
> If I can use an RPM, is there any other steps that I have to take to get
> Moodle to communicate with MS SQL 2005?
Once you get ODBTSP compiled and you believe it's installed, create a test.php script on your web directory with this line of code:
<?php phpinfo(); ?>
This will print out a whole lot of info on your php install. Do a search and make sure there is a section for odbtsp there, if that's working and you're following the guide at:
http://docs.moodle.org/en/Installing_MSSQL_for_PHP
then you should be good. If you run into trouble, just post the error your getting here.
Jay
> which one to use as the MS SQL connector alternative?
Yes, they both provide connectivity between a Unix machine and a MSSQL server. Although how they do it is actually quite different.
> Would I be able to use an RPM that was compiled on a different distribution of
> Linux?
I certainly wouldn't my recommendation is to install the php-devel on your system and recompile odbtsp for yourself. This makes certain that it gets linked properly for your specific distro/version.
> If I can use an RPM, is there any other steps that I have to take to get
> Moodle to communicate with MS SQL 2005?
Once you get ODBTSP compiled and you believe it's installed, create a test.php script on your web directory with this line of code:
<?php phpinfo(); ?>
This will print out a whole lot of info on your php install. Do a search and make sure there is a section for odbtsp there, if that's working and you're following the guide at:
http://docs.moodle.org/en/Installing_MSSQL_for_PHP
then you should be good. If you run into trouble, just post the error your getting here.
Jay
Jay,
I will go with your advice on using ODBSP instead of FreeTDS. My weak area in Linux is installing from source, and I am unclear on allot of the techniques for installing from source, but this is an area that I am trying very hard to learn but most of the instructions are not detailed enough for beginners. Until now, I have done all of my Linux management with RPM's and applications that already come with the linux distribution. Would you be kind enough to specify step by step instructions on getting the ODBSP to allow Moodle to connect to MS SQL? I am certain that once I do it once successfully, I will be more capable of installing from source in the future, but I just need to get over the hump of that first source install.
Thanks for your patience,
Eric
I will go with your advice on using ODBSP instead of FreeTDS. My weak area in Linux is installing from source, and I am unclear on allot of the techniques for installing from source, but this is an area that I am trying very hard to learn but most of the instructions are not detailed enough for beginners. Until now, I have done all of my Linux management with RPM's and applications that already come with the linux distribution. Would you be kind enough to specify step by step instructions on getting the ODBSP to allow Moodle to connect to MS SQL? I am certain that once I do it once successfully, I will be more capable of installing from source in the future, but I just need to get over the hump of that first source install.
Thanks for your patience,
Eric
Certainly, here's what I did. I'm using RedHat EL4 instead of SUSE though so you may need to adjust some of the instructions to fit SUSE.
1) install the gcc compiler package and the php-devel package. On RHEL the packages need are named just that (gcc and php-devel) but SUSE may have something different for them, you need to figure out what packages provides the gcc and phpize executables and install them.
2) Download odbtsp 1.1.4 from: http://sourceforge.net/project/showfiles.php?group_id=70325 to some temporary directory for compiling (I generally just do it in /root)
3) untar/gunzip it with the following command:
tar xzvvf odbtsp-1.1.4.tar.gz
this will create a odbtsp-1.1.4 directory.
4) Change to odbtsp-1.1.4:
cd odbtsp-1.1.4
5) *Skip this step if your just running a 32-bit linux distro* If you're using a 64-bit linux like x86_64 or ia64, you need to modify one line in the odbtp.h file. Look for the line:
/* #define _C_LONG_64_ 1 */
and uncomment it by removing the /* from the beginning *and* the */ from the end.
6) configure, build and install odbtp libs with the following commands:
./configure
make
make install
If you get any errors, you may need to install additional packages to satisfy the build's needs but for me, gcc was all I needed.
7) Now that you've got odbtp libs built, you need to build the php module. The php module can actually run without the libs being present, but you need the libs installed in order to build it. To start, change to the php/ext directory:
cd php/ext
8) In order to have odbtp provide the mssql_ functions and act like the freetds mssql php module, you need to modify the Makefile. Comment out (put a # in front of) the line that says:
MODULE_CFLAGS=-DHAVE_ODBTP -DCOMPILE_DL_ODBTP
and uncomment (remove the # from) the line that says:
#MODULE_CFLAGS=-DHAVE_ODBTP -DCOMPILE_DL_ODBTP -DODBTP_MSSQL
9) Don't close out of Makefile just yet! You still need to make one more change. Look for the line that says:
PHP_INCROOT=/usr/local/php/include/php
you need to modify this line to point to the correct include path. On my RHEL system, it's /usr/include/php and I'd be willing to bet it's the same on yours. If not, look around a little. The command:
rpm -ql php-devel | less
will show you all the files that your php-devel (substitute whatever package you had to install for SUSE here) installed. You should be able to determine where the include files are from that.
10) Finally we get to build the odbtp php module. The command is simply:
make
this might generate some warnings but should give you a new file called php_odbtp.so. this is your php extension.
11) copy the extension to your php extension folder (you can find your php extension folder by editing /etc/php.ini and looking for the line:
extension_dir=
12) load the extension by either adding extension=php_odbtp.so to php.ini or by creating a odbtp.ini file in /etc/php.d/ with the same line.
13) follow the rest of the directions on the Moodle MSSQL guide. You should be good to go!
Jay
1) install the gcc compiler package and the php-devel package. On RHEL the packages need are named just that (gcc and php-devel) but SUSE may have something different for them, you need to figure out what packages provides the gcc and phpize executables and install them.
2) Download odbtsp 1.1.4 from: http://sourceforge.net/project/showfiles.php?group_id=70325 to some temporary directory for compiling (I generally just do it in /root)
3) untar/gunzip it with the following command:
tar xzvvf odbtsp-1.1.4.tar.gz
this will create a odbtsp-1.1.4 directory.
4) Change to odbtsp-1.1.4:
cd odbtsp-1.1.4
5) *Skip this step if your just running a 32-bit linux distro* If you're using a 64-bit linux like x86_64 or ia64, you need to modify one line in the odbtp.h file. Look for the line:
/* #define _C_LONG_64_ 1 */
and uncomment it by removing the /* from the beginning *and* the */ from the end.
6) configure, build and install odbtp libs with the following commands:
./configure
make
make install
If you get any errors, you may need to install additional packages to satisfy the build's needs but for me, gcc was all I needed.
7) Now that you've got odbtp libs built, you need to build the php module. The php module can actually run without the libs being present, but you need the libs installed in order to build it. To start, change to the php/ext directory:
cd php/ext
8) In order to have odbtp provide the mssql_ functions and act like the freetds mssql php module, you need to modify the Makefile. Comment out (put a # in front of) the line that says:
MODULE_CFLAGS=-DHAVE_ODBTP -DCOMPILE_DL_ODBTP
and uncomment (remove the # from) the line that says:
#MODULE_CFLAGS=-DHAVE_ODBTP -DCOMPILE_DL_ODBTP -DODBTP_MSSQL
9) Don't close out of Makefile just yet! You still need to make one more change. Look for the line that says:
PHP_INCROOT=/usr/local/php/include/php
you need to modify this line to point to the correct include path. On my RHEL system, it's /usr/include/php and I'd be willing to bet it's the same on yours. If not, look around a little. The command:
rpm -ql php-devel | less
will show you all the files that your php-devel (substitute whatever package you had to install for SUSE here) installed. You should be able to determine where the include files are from that.
10) Finally we get to build the odbtp php module. The command is simply:
make
this might generate some warnings but should give you a new file called php_odbtp.so. this is your php extension.
11) copy the extension to your php extension folder (you can find your php extension folder by editing /etc/php.ini and looking for the line:
extension_dir=
12) load the extension by either adding extension=php_odbtp.so to php.ini or by creating a odbtp.ini file in /etc/php.d/ with the same line.
13) follow the rest of the directions on the Moodle MSSQL guide. You should be good to go!
Jay
Jay,
This is great instructions, these should be used on the Moodle instruction guide. I will attempt this tomorrow morning on my SLES 10 development server, and by looking at your directions, I can't see how it can go wrong now.
This has been my only obstacle during my final configuration test, and your help has saved me allot of stress. If you are ever in need of help on a technical project, don't hesitate to let me know, I have experience with Java, Apache Tomcat, ANT, and ASP.NET web applications, Netware products, and most Microsoft products.
I will let you know how this config goes.
Eric
This is great instructions, these should be used on the Moodle instruction guide. I will attempt this tomorrow morning on my SLES 10 development server, and by looking at your directions, I can't see how it can go wrong now.
This has been my only obstacle during my final configuration test, and your help has saved me allot of stress. If you are ever in need of help on a technical project, don't hesitate to let me know, I have experience with Java, Apache Tomcat, ANT, and ASP.NET web applications, Netware products, and most Microsoft products.
I will let you know how this config goes.
Eric
Is ODBTP still the best method in Nov 2008? I need to connect to my Sims Ms Sql 2005 server to pull some student data out.
Worked hard with FreeTDS in the summer, but found the connection very flakey. Certain queries just timed out, others ran fine. I had to abandon this approach.
Still need the connection though, so looking for alternatives,
any feedback on current ODBTP usage appreciated (latest build on sourceforge is 2006),
dan
Worked hard with FreeTDS in the summer, but found the connection very flakey. Certain queries just timed out, others ran fine. I had to abandon this approach.
Still need the connection though, so looking for alternatives,
any feedback on current ODBTP usage appreciated (latest build on sourceforge is 2006),
dan
I haven't really decided if ODBTP is better than FreeTDS. ODBTP is still being developed, although at a slow pace... See this thread on the ODBTP forum where Robert Twitty explains the plans for ODBTP 1.1.5.
The FreeTDS community does seem to be quite active whereas ODBTP development is quite slow. The only issue with FreeTDS is that it's quite difficult to get hold of a recent version of the Windows php_dblib.dll extension. Frank Kromann, who provided the version that everybody seems to use, has not updated it in a while and he seems to be "uncontactable". So, unless you are able to compile your own version of the .dll, you have to weigh up the risks vs benefits of using Frank's version. But the Moodle developers are aware of this...
Can you provide more details of the problems you've had with FreeTDS? Could your "flakey" connection have been improved by changing the FreeTDS configuration settings?
Also, see MDL-14725 for an interesting discussion regarding FreeTDS and Moodle.
Hiya, and thanks for getting back to me. Unfortunately it was a few months ago now and the exact details of the problems I was having have escaped me.
The long and the short of it was I spent a long time configuring FreeTDS with no joy - in the end I found some settings that seemed to work - at least when I ran a simple query. But as I started to work with it I found that certain queries just timed out and never returned a response. Other queries worked fine. I actually found that if I re-worked a query to pull the data back in a slightly different fashion - sometimes it would work. It was very weird and I spent a long time trawling the net and looked at various settings on the sql server. I found some possible issues regarding sql server 2005 and network timeout problems. Factor php and freetds into this mix and I never found the root culprit.
After a few days of no real progress I decided I had to move on to other work. I am currently importing sims reports manually but what I really want to do is run a cron job on the server and pull the sims data in once a day for student timetable information etc.
(i am working in an ubuntu lamp environment for what its worth)
cheers, dan
The long and the short of it was I spent a long time configuring FreeTDS with no joy - in the end I found some settings that seemed to work - at least when I ran a simple query. But as I started to work with it I found that certain queries just timed out and never returned a response. Other queries worked fine. I actually found that if I re-worked a query to pull the data back in a slightly different fashion - sometimes it would work. It was very weird and I spent a long time trawling the net and looked at various settings on the sql server. I found some possible issues regarding sql server 2005 and network timeout problems. Factor php and freetds into this mix and I never found the root culprit.
After a few days of no real progress I decided I had to move on to other work. I am currently importing sims reports manually but what I really want to do is run a cron job on the server and pull the sims data in once a day for student timetable information etc.
(i am working in an ubuntu lamp environment for what its worth)
cheers, dan