LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 08-04-2005, 05:11 PM   #1
ejennings_98
Member
 
Registered: Sep 2003
Location: Canada, West Coast
Distribution: Mandriva 2012.1 i586 & x86_64
Posts: 69

Rep: Reputation: 15
Using ODBC to MS SQL


I am trying to connect from my Mandrake 10.2 box to an MS SQL server. After going through the number of tutorials on using unixODBC and freeTDS, I am still at a loss.

I have unixODBC 2.2.1 installed as well as freeTDS 0.63. I have setup the the connections as described in the tutorials, but am not able to connect using the isql command.

I am able to connect to the ms sql server from another windows box. I am able to telnet to the sql server from the linux box.

When I use isql, the ODBC client, I get the following.

$ isql -v MyDSN me mypassword
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect

I added the following to the freetds.conf file:

[TDS]
host = myserver
port = 1433
tds version = 7.0
--------------------------------

The odbc.ini file reads as follows:

[MyDSN]
Description = TDS MSSQL
Driver = TDS
Servername = myserver
Database =
UID = me
PWD = mypassword
Port = 1433
----------------------------

If anyone knows of a good resource or if there are any suggestions, it would be much appreciated.

Thanks, Eric
 
Old 08-04-2005, 05:33 PM   #2
paulsm4
LQ Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
Troubleshoot the freeTDS portion first

Hi -

1. I would definitely verify that freeTDS can talk to SQL Server first. I've always
had good experience with the folks on the freeTDS mailling list - they should
be able to help you.

2. The second step is to get unixODBC working. I'm afraid I can't help you here.

3. This article might be of interest:

http://www.phpbuilder.com/columns/alberto20000919.php3?

'Hope that helps .. PSM
 
Old 08-04-2005, 05:54 PM   #3
ejennings_98
Member
 
Registered: Sep 2003
Location: Canada, West Coast
Distribution: Mandriva 2012.1 i586 & x86_64
Posts: 69

Original Poster
Rep: Reputation: 15
paulsm4:

I was able to confirm that the freeTDS and connect to the server. Its the ODBC stuff that is not working.

The article that you reffered to was the first one I read. It is not intuitive and must be missing something because it does not work. I have followed it fairly carfully.

There is a second article from Linux Journal which was much more straight forword, except the author forgot the step about first installing the unixODBC stuff.

Eric
 
Old 08-04-2005, 06:43 PM   #4
paulsm4
LQ Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
Try freeTDS directly?

Cool - I'm glad you were able to localize the problem to the unixODBC component.

Unfortunately, my experience is only writing Windows clients to Windows servers, and Linux clients to Linux servers. Two possible suggestions:

1. See if there are any viable alternatives to unixODBC

2. Consider using freeTDS (ie. have your client make dblib API calls) instead of ODBC.

I'm pretty sure you also have other alternatives besides those two, but unfortunately I can't help you there.

Good luck .. PSM
 
Old 08-04-2005, 06:57 PM   #5
ejennings_98
Member
 
Registered: Sep 2003
Location: Canada, West Coast
Distribution: Mandriva 2012.1 i586 & x86_64
Posts: 69

Original Poster
Rep: Reputation: 15
paulsm4:

To test the freeTDS I used the following:
[root@localhost]# /usr/local/bin/tsql -S myserver -U username
Password: <password>

Then I got the prompt
1>

So, all seems okay with the freeTDS. I got this from the Linux Journal article http://www.linuxjournal.com/article/6636

I am trying to use PHP on Linux to connect to MS SQL, and that is where the challenge is. I have been developing PHP apps with a variety of databases; using both Linux and Windows. This is the time where I need to connect a Linux server to MS SQL.

Thanks for your input. Anyone else??
 
Old 08-04-2005, 06:59 PM   #6
paulsm4
LQ Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
Move PHP to the SQL Server?

Would it be possible to run PHP (under either Apache or IIS) on the SQL Server host?
 
Old 08-05-2005, 12:09 PM   #7
ejennings_98
Member
 
Registered: Sep 2003
Location: Canada, West Coast
Distribution: Mandriva 2012.1 i586 & x86_64
Posts: 69

Original Poster
Rep: Reputation: 15
paulsm4:

For the short term I guess I will have to setup IIS with PHP. This is not what I had planned as I am at a critical point with my company, trying to move them over to Linux. If they loose confidence now, due to a driver issue, I will be forced to supply Windows solutions in the future. I have recently had some great successes with Linux.
(Okay stepping off of the soap box now)

Here is what I have found so far:
Apparently I do not need and ODBC connection from Linux to the MS SQL server. I can us the freeTDS along with adding the ms-sql extension into PHP. I dont know how to do that, but I am checking into it.

I have found an RPM for setting this up:
http://phprpms.sourceforge.net/mssql
Since I am using Mandriva, which supports most RPM installs, I will either have to guess at which one to use or build my own from scratch with the source files they supply. I am not looking forward to that.

If anyone else has had success using MS SQL with PHP on Linux I would really appreciate some assistance.

Thanks, Eric
 
Old 08-05-2005, 01:10 PM   #8
ejennings_98
Member
 
Registered: Sep 2003
Location: Canada, West Coast
Distribution: Mandriva 2012.1 i586 & x86_64
Posts: 69

Original Poster
Rep: Reputation: 15
Okay, I think I may have figure it out.

The old PHP site came to the rescue. http://ca.php.net/mssql

A post from marcos1979ar at yahoo dot com dot ar, 10-Jun-2004 06:07, wrote a mini tutorial. I will post it here just in case someone runs into the same stuff as I did.

----------------------
Red Hat 9.0 Freetds 0.62.3 Apache 2.0.49 PHP 5.0.ORC3
Instucction, that premit connect a server Linux with a server Windows 2000 Server with SQL Server.

INSTALLING FREETDS

1-. Download freetds -> www.freetds.org
2-. tar -zxvf freetds-stable-tgz
3-. cd freetds-0.62.3
4-. ./configure --prefix=/usr/local/freetds --with-tdsver=8.0 --enable-msdblib --enable-dbmfix --with-gnu-ld

Note: tdsver=8.0 if you use SQL 2000, tdsver=7.0 if you use SQL 7.0

5-. make
6-. make install
7-. /usr/local/freetds/bin/tsql -S <ip of the server> -U <User SQL>

Note: For default User SQL is sa and the it have not password
For example: /usr/local/freetds/bin/tsql -S 198.168.100.2 -U sa

8-. Add the next text in freetds.conf ( /usr/local/freetds/etc )
[TDS]
host = <ip of the Server with Sql>
port = 1433
tds version = 8.0

Note: If you use SQL 2000 then tds version = 8.0
if you use SQL 7.0 then tds version = 7.0

9-. Add the next text in the file /etc/ld.so.conf
/usr/local/freetds/lib

INSTALLING APACHE

1-. Download apache www.apache.org
2-. tar -zxvf httpd-2.0.49.tar.gz
3-. cd httpd-2.0.49
4-. ./configure --prefix=/etc/httpd --enable-so
5-. make
6-. make install
7-. Configure the file -> httpd.conf ( /etc/httpd/conf/httpd.conf )
8-. Probe the apache: /etc/httpd/bin/apachectl start
/etc/httpd/bin/apachectl stop

INSTALLING PHP

1-. Download the PHP in this site (www.php.net)
2-. tar -zxvf php-5.0-ORC3.tar.gz
3-. cd php-5.0-ORC3
4-. ./configure --with-apxs2=/etc/httpd/bin/apxs --enable-versioning --with-mssql=/usr/local/freetds --disable-libxml
5-. make
6-. make install
7-. cp php.ini-DIST /usr/local/lib
8-. Add the next line in /etc/httpd/conf/httpd.conf
AddType application/x-httpd-php .php

TESTING

<html>
<body>
<?php
$con = mssql_connect ("<ip of the server SQL>", "sa", "");
mssql_select_db ("<Data Base>", $con);
$sql= "SELECT * FROM <Table>";
$rs= mssql_query ($sql, $con);
echo "The field number one is: ";
echo mssql_result ($rs, 0, 0);
mssql_close ($con);
?>
</body>
</html>
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Migrating from MS-SQL server to My-SQL emailssent Linux - Networking 2 02-07-2005 03:20 PM
ODBC drivers mabus92920 Linux - Newbie 3 01-23-2005 03:58 AM
Which SQL is suitable for EMbedded SQL on C? hk_michael Programming 4 01-10-2005 06:07 PM
ODBC sql.h question javakid Programming 0 11-16-2004 09:34 AM
Linux Odbc munna_502 Linux - Software 1 03-30-2004 04:03 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 07:27 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration