Unable to establish ODBC connection using PHP

NojiNoji Posts: 2Member
edited 2011-08-25 in SQL General
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

Comments

  • krikikriki Posts: 7,917Member, Moderator
    [Topic moved from 'NAV Tips & Tricks' forum to 'SQL General' forum]
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

  • Luc_Van_DyckLuc_Van_Dyck WilrijkPosts: 3,624Member, Moderator, Administrator
    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);
    
    No support using PM or e-mail - Please use this forum. || Search is your friend
    NAV TechDays 2016: 17 & 18 November 2016, Antwerp (Belgium)
  • Luc_Van_DyckLuc_Van_Dyck WilrijkPosts: 3,624Member, Moderator, Administrator
    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);
    
    No support using PM or e-mail - Please use this forum. || Search is your friend
    NAV TechDays 2016: 17 & 18 November 2016, Antwerp (Belgium)
Sign In or Register to comment.