ODBC, Linux, Win2k - how to automate DSN setup?

[Tom Jones]

I need to access databases on both win2k and Linux systems but I have to set
up the DSNs under program control ... no uses using GUIs ...

Has anyone done this? if so can we discuss it either in here or offline?

thanks

Tom

[Google Mike]

Re: ODBC, Linux, Win2k - how to automate DSN setup?

"Tom Jones" <tomjones@lucen t.com> wrote:[color=blue]
> I need to access databases on both win2k and Linux systems but I have to set
> up the DSNs under program control ... no uses using GUIs ...
>
> Has anyone done this? if so can we discuss it either in here or offline?
>
> thanks
>
> Tom[/color]

I have not been successful with the ODBC route on Linux. As far as
unixODBC is concerned -- it's a joke to me because I see no way to
configure or use it.

Try FreeTDS from freetds.org, which then integrates with PHP's mssql_*
API library. TDS is the native protocol of SQL Server 6.5, 7, and 2K.

side note {
BTW, have another database out there on the Windows platform that you
want to connect to? You can use the SQL Server 7 or 2K's Linked
Servers technique (see a howto by doing a search on Google) in SQL
Server to link it to MS Access, MS Excel, or almost any database you
want on the Windows side. Linked Servers is under the "Security"
folder in SQL 7 or 2K. Once linked, you use a special syntax that
looks like this:

select * from <server>.<datab ase>.dbo.<table > --> for a SQL server
connection
or
select * from <app name>...<table> --> for something like an MS Access
connection
}

Anyway, back on FreeTDS...

1. Prove to yourself that your Linux firewall config is setup right by
pinging the other MS SQL Servers and see if you get a response, then
trying to telnet to port 1433 on that remote SQL Server. If you can
even get in, but cannot type anything without a decent response, then
your firewall is setup to give you access.

2. Now use FreeTDS's tsql test tool to test it by doing:

tsql -S ip.ip.ip.ip -H windows01 -p 1433 -U sa -P ''

Of course, replace ip.ip.ip.ip, windows01, sa, and '' with what is
appropriate for your environment.

Once in, you'll see:

1>

Type:

use pubs
go
select * from authors
go
\q

This proves that FreeTDS works.

3. You'll need to find out how to recompile PHP and Apache with mssql
support via FreeTDS. This is no cakewalk and I've had some failed
attempts. One of the big problems is that the process for compilation
to get FreeTDS support is a changing thing -- sites on the web, and
even on the PHP.net website, give conflicting or erroneous
information. Still, however, I persist, because I think eventually I
will get it right. Jochen Daum out of New Zealand has been giving me
advice in this newsgroup for the past couple of days, but I have yet
to get it right on my system. On Monday and Tuesday (11/24-25/2003)
I'm going to be trying feverishly hard to give it a good stabbing
again.

4. Although it is not recommended, if I cannot get freeTDS support in
PHP, I just might use PHP shell commands to execute tsql, get a
result, parse it, and use it. The tsql app recommends you don't do it,
but if it works, and I need a quick interim measure, I might have no
choice.

5. Another route I've considered, which would be a big project, is to
bring up a socket server on Windows using Visual Basic 6 and the
Winsock control. Many books and websites describe how to properly
build a socket server by using a "control array" in VB6. Anyway, the
socket server will sit on a TCP port. I would have to use my own
protocol so that the Linux system could login, make a SQL request,
receive a response, and parse it. PHP has a socket API that I could
use for calling the Windows system. On the Windows system, I could use
the ADO library in COM to access the SQL Server, Access, or other
database, get a response, and send it back. A big problem, however,
might be lockups, so I expect that I'll need to build something that
runs from Windows Scheduled Tasks to kill the VB app if the last
request it made was longer than 15 minutes ago. As for the protocol,
one could use a very simple XML, or could come up with something
simpler and with less parsing (and processor time) required. I've
thought the protocol could be unencrypted, but that I would require a
password on each SQL request, and a proprietary CRC set of bytes would
need to be included with every TCP stream in order to ensure that
hackers couldn't get in very easily. I might also restrict which host
IP could get into that socket server as a means to block hackers too.

Anyway, here's the calamity I'm in, and why I'm desperate to connect
PHP/Linux to MS SQL Server. I was working a lot in Linux/JSP, which
has more than one MS SQL Server JDBC driver. I sold my boss on going
with Linux/JSP rather than MS/ASP or MS/.NET. Then, I discovered PHP,
and had a lot of success with it and could knock out more work than
JSP, so I convinced the boss that this was the way to go. When I did
that, however, I never realized that MS SQL Server access would be so
dang aggravatingly difficult from PHP, requiring PHP and Apache
recompilation on RH9. So now I have very little time to prove to my
boss that PHP is great, and can suit our needs in the office, before
he pulls the plug on me and says we're going to go Linux/JSP, which I
find more time-consuming.