Re: ODBC, MSSQL, FreeTDS inner join not returning data from second table

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: 802.434.7488
Fax: 802.434.7490

On Tue, Sep 16, 2014 at 3:07 PM, Andrew Rousseau <[email protected]>
wrote:

> 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?
>