Unable to establish ODBC connection using PHP

Noji · 2011-08-22

Please forgve my newbie-ness, if I have posted this in the wrong forum (I couldn't tell by the names which applies) or whether I am asking an old or redundant question.

We have Navision 2009 (Nav 6?) running on a Windows Server 2008 R2, and all our employees are running happily with it. Now, I need to create a web page that will allow anybody in the world to access (read-only) our Nav database by issuing a query, etc. The problem I'm having is that I can't seem to get the PHP call odbc_connect() to establish a connection. My complete connection code is as follows:

<?php
    $username = 'flooruser';
    $password = 'flooruser';
    $odbc = odbc_connect("Driver={SQL Server};Server=qdsql2\qdsql2;Trusted_Connection=yes;Database=QD6NAVSQL;Uid=flooruser;Pwd=flooruser;", $username, $password)
        or die('The server is currently unavailable');
?>
<html lang="eng">
    .
    .
    .
</html>

First, the server name "qdsql2\qdsql2" is actually correct. Second, yes, I specify the username and password redundantly, but that is acceptable, as far as I understand. The result is that odbc_connect returns zero (failure) every time, and I don't know why.

I have spent the past three days scouring your (and others') forums, etc, to find my answer, and two posts seem to come close, but both were dead-ends for me. Any hand-holding would be appreciated.

Thanks,
Noji Ratzlaff
Quartzdyne, Salt Lake City

kriki · 2011-08-24

[Topic moved from 'NAV Tips & Tricks' forum to 'SQL General' forum]

Luc_VanDyck · 2011-08-25

Noji wrote:

We have Navision 2009 (Nav 6?) running on a Windows Server 2008 R2, and all our employees are running happily with it.

Did you read Freddy's blog posts in his series "Connecting to NAV Web Services from …": Connecting to NAV Web Services from PHP
Instead of querying the NAV database through ODBC, he uses NAV webservices to read the data. This requires the Service Tier to be installed, even is you are using the classic client instead of the Role Tailored Client. If your employees are using the RTC, then this Service Tier is already up and running.

I would give it a shot with this approach, as webservices are here to stay. And when the need is there to write data back into NAV, you have already the perfect framework for this, as all business logic will be executed as well (which you don't get when writing data to the database through ODBC).

Sorry for not being able to give you a working example of your code, but my experiences with PHP and database are limited to mySQL only, resulting in this code:

$link = mysql_connect($dbhost, $dbuser, $dbpasswd);
mysql_select_db($dbname, $link);
$result = mysql_query("SELECT * FROM DatabaseTable");
if(!$result) die("Query Failed.");
mysql_close($link);

Luc_VanDyck · 2011-08-25

Instead of using a DSN-less connection, you could create a System DSN through the Data Source Name wizard ("Data Sources (ODBC)" in Windows 7): http://www.devasp.com/samples/dsn_sql.asp . A System DSN allows you to connect to a database server using an alias rather than writing out a long connection string.

The PHP-code to connect to the database will then look like this:

$dsn="MyDSN";
$username = 'flooruser';
$password = 'flooruser';
$sqlconnect=odbc_connect($dsn,$username,$password);

Unable to establish ODBC connection using PHP

Comments

Categories