I'm not an expert on freetds by any means. This stuff is all new to me
as well.
When I log into "myserver.mydomain.com" using Remote Desktop, and open
the Microsoft SQL Server Management Studio, I see the following in the
left navigation window:
- MYSERVER (SQL Server 9.0.3042)
- Databases
+ System Databases
+ Database Snapshots
+ MYDATABASE
+ DATABASE2
+ DATABASE3
My entry into the freetds.conf file is:
# PeopleSoft FIN88QA database (Microsoft SQL Server 2000 configuration)
[MYDATABASE]
host = myserver.mydomain.com
port = 1433
tds version = 8.0
I'm actually using a text name for the host, not numbers. It looks like
"myserver.mydomain.com". It does not look like 123.456.789.0. I assume
it would work either way, though.
Here is a PHP script that works for me (all names have been changed to
protect the innocent):
<html>
<head></head>
<body>
<table border="1">
<tr><th>Business Unit</th><th>Customer</th><th>Item</th></tr>
<?php
$conn = mssql_connect('MYDATABASE', 'username', 'password');
if ($conn) {
mssql_select_db("MYDATABASE");
$customer = "12345";
$query = "SELECT BU,CUSTOMER,ITEM FROM MYTABLE WITH(NOLOCK)
WHERE CUSTOMER='$customer'";
$result = mssql_query($query);
while ($record = mssql_fetch_array($result)) {
print ("<tr><td>" . $record["BU"] . "</td><td>" .
$record["CUSTOMER"] . "</td><td>" . $record["ITEM"] . "</td></tr>");
}
}
?>
</table>
</body>
</html>
I assume you know the username and password are for the SQL Server
database--not an AS400 username and password.
If it's still giving you problems, you can submit a support ticket with
Zend and/or ask for help at the Zend forums.
Kelly
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx]
On Behalf Of Larry Kleinman
Sent: Wednesday, September 30, 2009 1:36 PM
To: Web Enabling the AS400 / iSeries
Subject: Re: [WEB400] PHP and microsoft sql server
No good....I got the same result. But I'm confused: there are
several
different databases on this particular SQL server. I thought that the
name in the freetds file is just a name that matches the name in the
mssql_connect statement (so that it can find the server), which means it
can be any name we want; and the name in the mssql_select_ statement
must
match the name of the particular database to which we want to connect
(which is called FBC_DEV_106)
Larry Kleinman
Kleinman Associates, Inc.
212-949-6469
203-255-4100
"Kelly Cookson" <KCookson@xxxxxxxxxxxx>
Sent by: web400-bounces@xxxxxxxxxxxx
09/30/2009 01:56 PM
Please respond to
Web Enabling the AS400 / iSeries <web400@xxxxxxxxxxxx>
To
"Web Enabling the AS400 / iSeries" <web400@xxxxxxxxxxxx>
cc
Subject
Re: [WEB400] PHP and microsoft sql server
It looks like you are trying to use two different names for the
database. If you make this entry into the freetds.conf file:
# PeopleSoft FIN88QA database (Microsoft SQL Server 2000 configuration)
[FBC_DEV_106]
host = 192.168.100.5
port = 1433
tds version = 8.0
then your PHP code should look like this:
$conn = mssql_connect('FBC_DEV_106','username','password');
if ($conn) {
mssql_select_db("FBC_DEV_106");
On the other hand, if you make this entry into the freetds.conf file:
# PeopleSoft FIN88QA database (Microsoft SQL Server 2000 configuration)
[FEDBAR]
host = 192.168.100.5
port = 1433
tds version = 8.0
then your PHP code should look like this:
$conn = mssql_connect('FEDBAR','username','password');
if ($conn) {
mssql_select_db("FEDBAR");
Please try making those changes and see if it works. In terms of log
files, I don't know whether freetds generates log files or where they
would be kept.
Kelly
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx]
On Behalf Of Larry Kleinman
Sent: Wednesday, September 30, 2009 11:46 AM
To: Web Enabling the AS400 / iSeries
Subject: Re: [WEB400] PHP and microsoft sql server
Kelly - thanks for the advice. Unfortunately, I don't seem to be
connecting to the SQL server. Is there some log that I can look at that
would tell me what is going wrong. Below is the PHP script.
<html>
<head></head>
<body>
Here is some stuff
<table border = "1">
<tr><th>Column Name 1</th><th>Column Name 2</th><th>Column Name
3</th><th>Sequence</th><th>Desc</th></tr>
<?php
$conn = mssql_connect('FedBar','Sa','Fbc');
if ($conn) {
mssql_select_db("FBC_DEV_106");
$query = "SELECT * FROM MYTABLE"; }
?>
</table>
</body>
</html>
Here is the addition to the freetds file that I made (the rest of the
file
is unchanged from how it was shipped)
# Fed Bar Microsoft SQL Server 2000 configuration
[FedBar]
192.168.100.5
port = 1433
tds version = 8.0
Larry Kleinman
Kleinman Associates, Inc.
212-949-6469
203-255-4100
"Kelly Cookson" <KCookson@xxxxxxxxxxxx>
Sent by: web400-bounces@xxxxxxxxxxxx
09/25/2009 05:06 PM
Please respond to
Web Enabling the AS400 / iSeries <web400@xxxxxxxxxxxx>
To
"Web Enabling the AS400 / iSeries" <web400@xxxxxxxxxxxx>
cc
Subject
Re: [WEB400] PHP and microsoft sql server
Yes. Open iSeries Navigator and go to this folder:
/usr/local/Zend/Core/etc
Look for the file named:
freetds.conf
Make a copy of the file for backup. Then open the file for editing (I
move it to my desktop and use NotePad). Scroll down to the bottom of the
file. You need to make an entry like this one:
# SQL Server database named MYDATA (Microsoft SQL Server 2000
configuration)
[MYDATA]
host = myserver.mydomain.com
port = 1433
tds version = 8.0
Look at the examples and be sure to use the format appropriate for your
version of SQL Server. Save the configuration file.
The PHP code looks something like this:
$conn = mssql_connect('MYDATA','username','password');
if ($conn) {
mssql_select_db("MYDATA");
$query = "SELECT * FROM MYTABLE";
$result = mssql_query($query);
$row = mssql_fetch_array($result);
<process $row as desired>
}
Hope this helps.
Kelly
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx]
On Behalf Of Larry Kleinman
Sent: Friday, September 25, 2009 3:48 PM
To: Web Enabling the AS400 / iSeries
Subject: [WEB400] PHP and microsoft sql server
Is there something special that needs to be done in order to have a PHP
script - running either on Zend or on a non-system i server - talk to
SQL
Server?
Larry Kleinman
Kleinman Associates, Inc.
212-949-6469
203-255-4100
midrange.com
