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

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

2014-09-17 3:31 GMT+01:00 Andrew Rousseau <[email protected]>:

> 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?
> >
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>