Koozali.org: home of the SME Server

Howto: Connect to Microsoft SQL server from SME7

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« on: January 20, 2006, 03:12:04 PM »
Hi all,

I have recently manged to get my SME7pre1 box to connect to an MS-SQL (MSSQL) server, and have a PHP application using the database.

I am willing to write a detailed step-by-step if it would be useful... :hammer:

Any Takers?
...
Nick

"No good deed goes unpunished." :-x...

ergozd

Howto: Connect to Microsoft SQL server from SME7
« Reply #1 on: January 21, 2006, 09:21:14 AM »
Hi Nick!

If you've already seen it check http://phprpms.sf.net The have many extensions and also one for mssql, hope this would help out.

Code: [Select]
What is PHPRPMs?

PHPRPMs is a collection of RPM packages that add various PHP extensions to the PHP that you have as a part of your RPM-based Linux Distribution (Red Hat Enterprise Linux, CentOS, WhiteBox or Fedora Core). The advantages of this approach are:

    * you don't have to rebuild PHP from the source if you just need one of the less-frequently used or non-GPL extensions;
    * since you don't rebuild PHP, you get to keep your "factory" PHP packages with all distro-specific patches and, what's more important,
    * you can continue to receive the standard updates for the "factory" PHP packages via RHN / up2date / yum

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #2 on: January 21, 2006, 12:01:54 PM »
hehe.. thanks Ergin... I wish I'd known about that two months ago  :-D


I had tried unsuccessfully to get Interbase support in PHP working numerous times and utterly failed, the extensions said they were installed, phpinfo said they were installed, everything looked good except it just didn't work!

It seems that all the howto's out there assume you are connecting to a firebird server on another linux box, but I was actually trying to connect to an interbase server on a Wintel box, so I didn't have the required library files.

In the end I used unixODBC to do the job, and when the Database peeps decided to move to MSSQL, all I had to do was create the MSSQL driver, create a new DSN for it, and slightly alter my odbcConnect() function.

If anyone is still interested in a Howto: Connect to an MSSQL/Sybase server using unixODBC, please let me know
...
Nick

"No good deed goes unpunished." :-x...

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #3 on: January 21, 2006, 12:05:49 PM »
Actually, as it turns out, the mssql package from phprpms still requires to install freeTDS, and I don't see an rpm for CENTOS, (can you use rpms for RHEL, or FC on CENTOS?)
...
Nick

"No good deed goes unpunished." :-x...


Offline vincentmeek

  • *
  • 29
  • +0/-0
    • Triple Point Solutions
Howto: Connect to Microsoft SQL server from SME7
« Reply #5 on: January 22, 2006, 09:48:31 PM »
By all means create the howto.  Someone else may find it usefull in the future.

Thanks
Vincent Meek
Network Consultant

RonM

Howto: Connect to Microsoft SQL server from SME7
« Reply #6 on: January 23, 2006, 01:15:48 AM »
Actually, I think I may very well find this useful in the pretty near future. I was thinking of connecting MSSQL as a foreign database into MySQL, but it's a lot more elegant to connect directly from perl or php (not to mention saving me the headache of try to figure it out ;-)

If you have a chance to put this how to together, it would be much appreciated. Any interoperability always ends up with major benefits.

Thanks, RonM

Offline Franco

  • *
  • 1,171
  • +0/-0
    • http://contribs.org
Howto: Connect to Microsoft SQL server from SME7
« Reply #7 on: January 23, 2006, 03:03:02 AM »
Yes, How-to would be good!

Thanks,

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #8 on: January 23, 2006, 09:18:27 AM »
Oki Doki,

I'll have one written in teh next few days :)
...
Nick

"No good deed goes unpunished." :-x...

cubique

FreeTDS installation on SME601
« Reply #9 on: February 01, 2006, 12:22:32 PM »
Quote from: "ergozd"
Check out Dag's repository

http://dag.wieers.com/packages/freetds/freetds-0.63-1.2.el4.rf.i386.rpm
http://dag.wieers.com/packages/freetds/freetds-devel-0.63-1.2.el4.rf.i386.rpm


Hi, I have used the files you have told, also tried prior version, but when install the rpms the following message appears:

libc.so.6(GLIBC_2.3)   is needed by freetds-0.63-1.2.el4.rf

I have SME 6.0.1 box.

I have search the net for similar problems, a lot of explanation, nothing to solve (my) this issue.

What is missing in my SME box installation? What package to install in order to fix this conflict?

Thx in advance for you help.
Eugen

Offline p-jones

  • *
  • 594
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #10 on: February 01, 2006, 12:42:38 PM »
I would very much welcome the HOWTO. I also realise how time can be a vary scarce comodity !!

Rgds
Peter
...

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #11 on: February 01, 2006, 01:08:58 PM »
Hi All,

The Howto is nearly complete, I'll be putting it on my server either today or tomorrow.

Sorry about the delay; Work has been quite busy recently, and one of my databases PHP frontends suddenly developed a load of bugs that I've been battling with.  :hammer:
...
Nick

"No good deed goes unpunished." :-x...

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #12 on: February 01, 2006, 01:48:34 PM »
cubique,
I've not tried this on 6.01, but you need the library file libc.so.6, which is part of the GLIBC package

I can't find a GLIB_2.3 rpm for RH7.3, the closest is 2.25

ftp://fr.rpmfind.net/linux/redhat/7.3/en/os/i386/RedHat/RPMS/glibc-2.2.5-34.i386.rpm

You could give it a go....
...
Nick

"No good deed goes unpunished." :-x...

Offline CharlieBrady

  • *
  • 6,918
  • +3/-0
Re: FreeTDS installation on SME601
« Reply #13 on: February 01, 2006, 05:40:06 PM »
Quote from: "cubique"
Quote from: "ergozd"
Check out Dag's repository

http://dag.wieers.com/packages/freetds/freetds-0.63-1.2.el4.rf.i386.rpm
http://dag.wieers.com/packages/freetds/freetds-devel-0.63-1.2.el4.rf.i386.rpm


Hi, I have used the files you have told, also tried prior version, but when install the rpms the following message appears:

libc.so.6(GLIBC_2.3)   is needed by freetds-0.63-1.2.el4.rf

I have SME 6.0.1 box.


You need a RH 7.3 version of those RPMs, not a RHEL4 version. SME 6.x is RH7.3 compatible, SME 7 is RHEL4 compatible.

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #14 on: February 01, 2006, 06:11:04 PM »
Ta da!!

Howto complete.

For the time being it is on my server at home:

http://www.lmeit.co.uk/freetds.html

Please let me know what you think.

I'm going to put it in the howto's section on Contribs.org, then its time for a :pint: methinks  :-D
...
Nick

"No good deed goes unpunished." :-x...

Offline swissis

  • **
  • 61
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #15 on: June 20, 2006, 01:43:01 PM »
Great totarial,

But at the install of php-odbc something goes wrong.

There was an dependancy error that said that php 4.3.9-3.9 is needed.
 
On my sme 7rc3 is 4.3.9-3.12 installed?

As Nick suggested i tried

yum --enablerepo=updates install php-odbc

But that doen't work as well
...

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #16 on: June 20, 2006, 01:52:16 PM »
Hmmm.  the updates repository for Centos definately has the correct RPm in there.:

Code: [Select]
[root@aquarius ~]# yum --enablerepo=updates search php-odbc
==============================================================
WARNING: Additional commands may be required after running yum
==============================================================
Searching Packages:
Setting up repositories
Reading repository metadata in from local files


php-odbc.i386                            4.3.9-3.9              base
Matched from:
php-odbc
The php-odbc package contains a dynamic shared object that will add
database support through ODBC to PHP. ODBC is an open specification
which provides a consistent API for developers to use for accessing
data sources (which are often, but not always, databases). PHP is an
HTML-embeddable scripting language. If you need ODBC support for PHP
applications, you will need to install this package and the php
package.


php-odbc.i386                            4.3.9-3.12             updates
Matched from:
php-odbc
The php-odbc package contains a dynamic shared object that will add
database support through ODBC to PHP. ODBC is an open specification
which provides a consistent API for developers to use for accessing
data sources (which are often, but not always, databases). PHP is an
HTML-embeddable scripting language. If you need ODBC support for PHP
applications, you will need to install this package and the php
package.
================================================================
No new rpms were installed. No additional commands are required.
================================================================
[root@aquarius ~]#


Could you post your /etc/yum.conf here please?
...
Nick

"No good deed goes unpunished." :-x...

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #17 on: June 20, 2006, 01:54:23 PM »
...
Nick

"No good deed goes unpunished." :-x...

Offline swissis

  • **
  • 61
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #18 on: June 20, 2006, 04:52:49 PM »
Here it is...

Code: [Select]

#------------------------------------------------------------
#       !!DO NOT MODIFY THIS FILE!!
#
# Manual changes will be lost when this file is regenerated.
#
# Please read the developer's guide, which is available
# at http://wiki.contribs.org/development/
#
# Copyright (C) 1999-2006 Mitel Networks Corporation
#------------------------------------------------------------
[main]
cachedir=/var/cache/yum
debuglevel=2
exactarch=1
installonlypkgs=kernel kernel-smp kernel-hugemem kernel-enterprise kernel-debug kernel-unsupported kernel-smp-unsupported kernel-hugemem-unsupported
logfile=/var/log/yum/yum.log
obsoletes=1
tolerant=1

[addons]
enabled=0
name=CentOS - addons
baseurl=http://mirror.centos.org/centos/4/addons/$basearch
gpgcheck=1
enablegroups=0

[base]
enabled=0
name=CentOS - os
baseurl=http://mirror.centos.org/centos/4/os/$basearch
gpgcheck=1
enablegroups=0

[centosplus]
enabled=0
name=CentOS - centosplus
baseurl=http://mirror.centos.org/centos/4/centosplus/$basearch
gpgcheck=1
enablegroups=0

[contrib]
enabled=0
name=CentOS - contrib
baseurl=http://mirror.centos.org/centos/4/contrib/$basearch
gpgcheck=1
enablegroups=0

[extras]
enabled=0
name=CentOS - extras
baseurl=http://mirror.centos.org/centos/4/extras/$basearch
gpgcheck=1
enablegroups=0

[smeaddons]
enabled=1
name=SME Server - addons
baseurl=http://mirror.contribs.org/smeserver/releases/7/smeaddons/$basearch
gpgcheck=1
enablegroups=1

[smedev]
enabled=0
name=SME Server - dev
baseurl=http://mirror.contribs.org/smeserver/releases/7/smedev/$basearch
gpgcheck=1
enablegroups=1

[smeos]
enabled=1
name=SME Server - os
baseurl=http://mirror.contribs.org/smeserver/releases/7/smeos/$basearch
gpgcheck=1
enablegroups=1

[smetest]
enabled=0
name=SME Server - test
baseurl=http://mirror.contribs.org/smeserver/releases/7/smetest/$basearch
gpgcheck=1
enablegroups=1

[smeupdates]
enabled=1
name=SME Server - updates
baseurl=http://mirror.contribs.org/smeserver/releases/7/smeupdates/$basearch
gpgcheck=1
enablegroups=1

[smeupdates-testing]
enabled=0
name=SME Server - updates testing
baseurl=http://mirror.contribs.org/smeserver/releases/7/smeupdates-testing/$basearch
gpgcheck=1
enablegroups=1

[testing]
enabled=0
name=CentOS - testing
baseurl=http://mirror.centos.org/centos/4/testing/$basearch
gpgcheck=1
enablegroups=0

[updates]
enabled=0
name=CentOS - updates
baseurl=http://mirror.centos.org/centos/4/updates/$basearch
gpgcheck=1
enablegroups=0
...

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #19 on: June 20, 2006, 05:12:09 PM »
I can't see anything wrong with your YUM config...

Could you please post the Text of the error you get in here, maybe someone else will recognise the problem and suggest something.

Failing that, you need to post a new thread in one of the new forums (this one has been deprecated)

The problem you currently have isn't with my howto, its with YUM on your machine.

Sorry I couldn't be of more help.
...
Nick

"No good deed goes unpunished." :-x...

Offline swissis

  • **
  • 61
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #20 on: June 21, 2006, 08:06:35 AM »
Yesterday late i gave it another go and guess what....

It works !!!!
...

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #21 on: June 21, 2006, 11:05:11 AM »
Cool

I'm glad you got there in the end!  :-D
...
Nick

"No good deed goes unpunished." :-x...

Offline mrshark

  • 19
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #22 on: June 21, 2006, 11:24:43 AM »
thanks for your guide! I've some problems, however...

from commandline, via isql, i can connect, but
1) using this script:
Code: [Select]
<? $db_conn = odbc_connect("Cal2000","sa","")
    or die( "<strong>ERROR: Connection to MYSERVER failed</strong>" );
?>


i get: Fatal error: Call to undefined function: odbc_connect() in /home/e-smith/files/ibays/crm/html/prova3.php on line 8

2) so tried mssql_connect:
Code: [Select]
<? $db_conn = mssql_connect("Cal2000","sa","")
    or die( "<strong>ERROR: Connection to MYSERVER failed</strong>" );
?>

and i get:
Fatal error: Call to undefined function: mssql_connect() in /home/e-smith/files/ibays/crm/html/prova2.php on line 3

3) i tried installing php-mssql-4.3.9-3.2.1.i386.rpm from http://phprpms.sourceforge.net/mssql
but:
[root@voip ~]# yum localinstall php-mssql-4.3.9-3.2.1.i386.rpm
... bla bla bla ...
--> Processing Dependency: libsybdb.so.4 for package: php-mssql
--> Finished Dependency Resolution
Error: Missing Dependency: libsybdb.so.4 is needed by package php-mssql

dag's and phprpms' packages contains only libsybdb.so.5, not .4...

any solution? thanks in advance...

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #23 on: June 21, 2006, 11:37:29 AM »
Hi MrShark,

I don't use the mssql php module at all so Can't help you there,

If you are getting 'undefined function' when using odbc_connect() then you haven't installed the php_odbc module yet.

If you are running SME7<rc3 do a:
Code: [Select]
yum --enablerepo=base install php_odbc

If you are running rc3
Code: [Select]
yum --enablerepo=updates install php_odbc
...
Nick

"No good deed goes unpunished." :-x...

Offline mrshark

  • 19
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #24 on: June 21, 2006, 11:45:23 AM »
Code: [Select]
[root@voip html]# rpm -qa|grep -i odbc
mysql-connector-odbc-3.51.12-nh01
php-odbc-4.3.9-3.9
unixODBC-2.2.11-1.RHEL4.1

nop, it's there. But, have i to expand any template, maybe php.ini?

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #25 on: June 21, 2006, 11:57:27 AM »
No, there's no template to expand....

Check that your /etc/php.d/odbc.ini && /usr/lib/php4/odbc.so files are there.

Failing that, remove and re-install the rpm.
...
Nick

"No good deed goes unpunished." :-x...

Offline mrshark

  • 19
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #26 on: June 21, 2006, 12:19:08 PM »
signal-event post-upgrade ; signal-event reboot
solved the problem :-D

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #27 on: June 21, 2006, 03:55:48 PM »
Great stuff!
...
Nick

"No good deed goes unpunished." :-x...

marcus75

[IM002][unixODBC][Driver Manager]Data source name not found
« Reply #28 on: December 07, 2006, 06:17:40 PM »
Hi at all

I had follow step by step your guide but when I try to connect the sme server on my mssql I recived this answerer:
[IM002][unixODBC][Driver Manager]Data source name not found, and no
default driver specified
what is wrong on my configuration?
I have check it on million times but without saw the  solution..

please help me.

etc/freetds.conf

[MSSQL01]
host = SQL\E
port = 1433
tds version = 8.0


etc/odbc.ini

[MSSQL01]
Driver       = FreeTDS
Description  = Connection to the thingy Database on MSSQL01
Trace        = No
Server       = SQL\E
Database     = AZI_SILMAR
Port         = 1433
TDS_Version  = 8.0


etc/odbcinst.ini

[FreeTDS]
Description   = FreeTDS
Driver        = /usr/lib/libtdsodbc.so

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #29 on: January 02, 2007, 08:35:53 PM »
Hi,

Sorry about the length of time it has taken to get back to you,
For future reference, you should have posted in SME 7.x Contribs, as this forum is now deprecated and no-one looks in them.

Anyway...

1) What command are you entering to get the error message?
2) What is with the backslash in the Server name?

Nick
...
Nick

"No good deed goes unpunished." :-x...

marcus75

Howto: Connect to Microsoft SQL server from SME7
« Reply #30 on: January 03, 2007, 09:48:19 PM »
Quote from: "NickCritten"
Hi,

Sorry about the length of time it has taken to get back to you,
For future reference, you should have posted in SME 7.x Contribs, as this forum is now deprecated and no-one looks in them.

Anyway...

1) What command are you entering to get the error message?
2) What is with the backslash in the Server name?

Nick


Thank's for your answer I have just solved my problem, the problem was stupid but unfortunately it took a lot of time to understand the mistake..

I put off all useless bar space from my configuring files like etc/odbc.ini, etc/freetds.conf and etc/odbcinst.ini

Offline NickCritten

  • *
  • 245
  • +0/-0
Howto: Connect to Microsoft SQL server from SME7
« Reply #31 on: March 06, 2007, 09:39:38 AM »
FYI,

If you have followed my contrib, then the recent YUM updates to SME7.1 will fail.
Code: [Select]
Error: Missing Dependency: php = 4.3.9-3.22 is needed by package php-odbc
================================================================
No new rpms were installed. No additional commands are required.
================================================================


you First need to remove php-odbc:
Code: [Select]
[root@sleuth ~]# yum remove php-odbc
Do the yum updates
Code: [Select]
[root@sleuth ~]# yum update
Then install the updated php-odbc
Code: [Select]
[root@sleuth ~]# yum --enablerepo=base --enablerepo=updates install php-odbc
...
Nick

"No good deed goes unpunished." :-x...