[freetds] ODBC, MSSQL, FreeTDS inner join not returning data from second table

[Andrew Rousseau]

I have FreeTDS installed on an Ubuntu 14.04 server. I am connecting to the
MSSQL database as follows:

$db = new PDO('odbc:Driver=FreeTDS; Server=<IP Address>; Port=1433;
Database=db_name; UID=user; PWD=password;');

I am running the following query:

SELECT c.*, ct.*
FROM Committee AS c
INNER JOIN CommitteeType as ct on c.CommitteeTypeID=ct.CommitteeTypeID
WHERE CommitteeID=$committee_id

Then I am running:

$statement = $db->prepare($query);
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_NAMED);

The resulting array is very much what would be expected except that the
values from the CommitteeType table are all empty. When I run the exact
same query in Navicat on the database I get values for everything from the
second table.

Is there something buggy about this driver?

I have tried many variations on the query syntax without any difference in
output. It appears that I can never get values from an inner joined table
regardless of which tables I am querying.

Anyone see anything I am missing or have experienced anything similar?

[Andrew Rousseau]

So it turns out there are issues with odbc. I am now successfully using
dblib instead.

Here is the full recipe for anyone that wants to know what I did to get
this fully working on Ubuntu 14.04:

These were the original directions I followed

1. Install the packages freetds-bin, freetds-common, tdsodbc, odbcinst,
php5-odbc and unixodbc. This provides the libraries you need.
2. Copy the contents of /usr/share/doc/freetds-common/examples/odbcinst.ini
into /etc/odbcinst.ini. This registers the FreeTDS driver with the ODBC
layer.
3. Restart your webserver to load the ODBC module into PHP.

Then connected with:

<?php
try
{
$db = new PDO('odbc:Driver=FreeTDS; Server=hostname_or_ip; Port=port;
Database=database_name; UID=username; PWD=password;');
}
catch(PDOException $exception)
{
die("Unable to open database.<br />Error message:<br /><br
/>$exception.");
}
echo '<h1>Successfully connected!</h1>';
$query = 'SELECT * FROM table_name';
$statement = $db->prepare($query);
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
?>

To correct the issue I was having with inner joins on returning data from
secondary tables I installed:

php5-sybase (ie sudo apt-get install php5-sybase)

Then connected as such:

<?php
try {
$hostname = "myhost";
$port = 10060;
$dbname = "tempdb";
$username = "dbuser";
$pw = "password";
$db = new PDO
("dblib:host=$hostname:$port;dbname=$dbname","$username","$pw");
}
catch(PDOException $exception)
{
die("Unable to open database.<br />Error message:<br /><br
/>$exception.");
}
echo '<h1>Successfully connected!</h1>';
$query = 'SELECT * FROM table_name';
$statement = $db->prepare($query);
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
?>

Andrew Rousseau
Technical Director

bluehouse*group*.com <http://bluehousegroup.com>
65 Millet Street, Suite 101
Richmond, VT 05477
Phone: [phone]
Fax: [phone]

On Tue, Sep 16, 2014 at 3:07 PM, Andrew Rousseau <andrew at bluehousegroup.com>

Post by Andrew Rousseau
I have FreeTDS installed on an Ubuntu 14.04 server. I am connecting to the
$db = new PDO('odbc:Driver=FreeTDS; Server=<IP Address>; Port=1433;
Database=db_name; UID=user; PWD=password;');
SELECT c.*, ct.*
FROM Committee AS c
INNER JOIN CommitteeType as ct on c.CommitteeTypeID=ct.CommitteeTypeID
WHERE CommitteeID=$committee_id
$statement = $db->prepare($query);
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_NAMED);
The resulting array is very much what would be expected except that the
values from the CommitteeType table are all empty. When I run the exact
same query in Navicat on the database I get values for everything from the
second table.
Is there something buggy about this driver?
I have tried many variations on the query syntax without any difference in
output. It appears that I can never get values from an inner joined table
regardless of which tables I am querying.
Anyone see anything I am missing or have experienced anything similar?

[Frediano Ziglio]

There must be something strange in the interaction between PHP and ODBC.
Our ODBC surely can retrieve data from multiple tables. Which version of
PHP and FreeTDS did you use? I think everything from Ubuntu 14.04. Can you
send a TDSDUMP ?

Frediano

Post by Andrew Rousseau
So it turns out there are issues with odbc. I am now successfully using
dblib instead.
Here is the full recipe for anyone that wants to know what I did to get
These were the original directions I followed
1. Install the packages freetds-bin, freetds-common, tdsodbc, odbcinst,
php5-odbc and unixodbc. This provides the libraries you need.
2. Copy the contents of /usr/share/doc/freetds-common/examples/odbcinst.ini
into /etc/odbcinst.ini. This registers the FreeTDS driver with the ODBC
layer.
3. Restart your webserver to load the ODBC module into PHP.
<?php
try
{
$db = new PDO('odbc:Driver=FreeTDS; Server=hostname_or_ip; Port=port;
Database=database_name; UID=username; PWD=password;');
}
catch(PDOException $exception)
{
die("Unable to open database.<br />Error message:<br /><br
/>$exception.");
}
echo '<h1>Successfully connected!</h1>';
$query = 'SELECT * FROM table_name';
$statement = $db->prepare($query);
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
?>
To correct the issue I was having with inner joins on returning data from
php5-sybase (ie sudo apt-get install php5-sybase)
<?php
try {
$hostname = "myhost";
$port = 10060;
$dbname = "tempdb";
$username = "dbuser";
$pw = "password";
$db = new PDO
("dblib:host=$hostname:$port;dbname=$dbname","$username","$pw");
}
catch(PDOException $exception)
{
die("Unable to open database.<br />Error message:<br /><br
/>$exception.");
}
echo '<h1>Successfully connected!</h1>';
$query = 'SELECT * FROM table_name';
$statement = $db->prepare($query);
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
?>
Andrew Rousseau
Technical Director
bluehouse*group*.com <http://bluehousegroup.com>
65 Millet Street, Suite 101
Richmond, VT 05477
Phone: [phone]
Fax: [phone]
On Tue, Sep 16, 2014 at 3:07 PM, Andrew Rousseau <
andrew at bluehousegroup.com>

Post by Andrew Rousseau
I have FreeTDS installed on an Ubuntu 14.04 server. I am connecting to

the

Post by Andrew Rousseau
$db = new PDO('odbc:Driver=FreeTDS; Server=<IP Address>; Port=1433;
Database=db_name; UID=user; PWD=password;');
SELECT c.*, ct.*
FROM Committee AS c
INNER JOIN CommitteeType as ct on c.CommitteeTypeID=ct.CommitteeTypeID
WHERE CommitteeID=$committee_id
$statement = $db->prepare($query);
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_NAMED);
The resulting array is very much what would be expected except that the
values from the CommitteeType table are all empty. When I run the exact
same query in Navicat on the database I get values for everything from

the

Post by Andrew Rousseau
second table.
Is there something buggy about this driver?
I have tried many variations on the query syntax without any difference

in

Post by Andrew Rousseau
output. It appears that I can never get values from an inner joined table
regardless of which tables I am querying.
Anyone see anything I am missing or have experienced anything similar?

_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds