ubuntu8.04, Moodle, cmis, UnixODBC and FreeTDS

[gaz350]

I've been trying to get the Moodle to Facility integration to work and i cant seem to get the UnixODBC and FreeTDS section to work (connecting to mssql2000)

 

anyone got any tips on this part of the process?

 

 

freetds.conf

 

[pippin]
Driver = FreeTDS
Description = FacilityCMISDB
Trace = Yes
TraceFile = /tmp/msfacility.log
Servername = pippin
Database = CMIS_ADMIN
Port = 1433

ODBC.ini

[pippin]
       host = IP of server
       port = 1433
       tds version = 8.0

 

odbcinst.ini

[FreeTDS]
Description = FreeTDS Driver for Linux & MSSQL on Win32
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
UsageCount = 1
FileUsage = 1
Trace = Yes
TraceFile = /tmp/freetds.log

#DEFAULT
Driver = /usr/lib/odbc/libtdsodbc.so

 

when testing the connection via iSQL all i get is

[iSQL]ERROR: Could not SQLConnect

 

[CyberNerd]

this is for SIMS, so it should work for any MSSQL

 

this is the freetds.conf I don't think the rest is relevant

[global]
host = 192.168.0.x
port = 1427
client charset = UTF-8
tds version = 7.0
text size = 20971520

 

 

SQL2000/2005 use different port numbers

[CyberNerd]

we also have php-mssql for the php

 

rpm -ql php-mssql

/etc/php.d/mssql.ini

/usr/lib64/php/modules/mssql.so

[gaz350]

ok got things connecting.

 

when i 1st started the install i had done a 14hr day! and this was at home and before i went to bed.

 

just went through instructions again seems i hadn't installed tdsodbc, thought i had but oh well. connects now lets see how i get on with the rest of the installation now!

[gaz350]

sorry to bump this oldish thread.

 

now im back to work properly after some perternity leave/Xmas hols i would love to have another go at getting this working.

 

anyone any ideas/tips on getting this working? thought it was connecting but getting

 

[iSQL]ERROR: Could not SQLConnect

 

ps in my orginal post the odbc ini and freetds are the wrong way round but the right way round in my config files

Edited January 26, 2009 by gaz350

[CyberNerd]

some more info on this thread

 

http://www.edugeek.net/forums/virtual-learning-platforms/29084-sims-timetable-module-moodle-6.html

[gaz350]

ok here goes....

 

1) TELNET returns

 

admin@Mivonks:~$ telnet 10.19.x.x 1433
Trying 10.19.x.x...
Connected to 10.19.x.x.
Escape character is '^]'.

 

2) tsql returns

 

admin@server:~$ tsql -S SERVERNAME -U user -P password
locale is "en_GB.UTF-8"
locale charset is "UTF-8"
1>

 

3) iSQL returns

 

admin@server:~$ isql servername user password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

 

4) go to the block on moodle. click transfer students. does some stuff and comes back witht he following.

 

Transfer Completed Successfully
Transfer Log
No transfer errors occurred
Lookup Log
Lookup Failures For year 7

08studentname (last network login: 2008-12-18)
Query returned nothing:- SELECT NS.studentid ,NS.forename, NS.calledname, NS.surname, NS.leftschool, NS.dateleft, SU.classgroupid, SU.courseyear, SU.courseid FROM nstupersonal NS INNER JOIN students SU on NS.studentid=SU.studentid WHERE NS.setid='2008/2009' AND SU.setid='2008/2009' AND NS.studentId='3557'

above is just a sample of 1 student but it does this for every student!

 

so from the above error message i assume its getting the info from Active Directory. then requesting the data it needs from cmis but failing.

 

entering this statement directly on the SQL server works fine returning all the correct data.

Only thing i can think i have missed is i read something about the need to compile php with freetds. which i am pretty sure it is, but not sure if its configured properly?

 

still stuck but have a bit better understanding of whats going on now atleast.

[brudinie]

I've only just noticed this post, so sorry for the delay.

You could be having trouble with the queries because it seems your idnumbers aren't zero prefixed.

If the are zero prefixed in Facility - e.g. 001234 then that's exactly how they have to be in the active directory description field.

 

If you can't fix them in the active directory description field, I could modify the block to allow a padding string to be used- this would automatically insert the 0 prefixes before it does the lookups - the queries should then work.

[gaz350]

thx for the reply! Since my last message it's been madness will look into it over the weekend for you I think your correct that our ids don't have 00 prefixed in active directory

edit: our ID's are 1234 and definately not 001234 in both the Database and Active Directory

Edited April 24, 2009 by gaz350

[kieran8055]

Can any one tell me how to install Php-mssql on ubuntu 8.04lts.

 

And aslo any configuration (php-mssql) need to be done for moodle to talk to mssql server to retrive timetable from sims server.

 

Cheers :0)

[CyberNerd]

sudo apt-get install php5-mysql

[kieran8055]

Hi i ve already installed for php5-mysql,

 

Actually i wanted is to install php-mssql to work with FreeTDS0.82 to pull up timetable from sims.

 

Thanks

[CyberNerd]

sorry I misread ms for my.

It looks like it's not included in ubuntu, you have to build yourself.

have a look here, or try redhat/centos.

php5-mssql - Ubuntu Forums