[FIXED] Navision ODBC connection with PHP

hairyjim

Hi all,

Im not sure how much response I will get with this since web searches have been rather fruitless. Hopefully though someone may have a bit of information to get me going.

I have created a system DSN on my Win Xp machine. The connection works fine I can get info from the Navision Db using the DSN connection in Excel and Microsoft Query.

I am ultimatly trying to get PHP to talk to Navision via a ODBC connection.

I keep getting the following error returned to me though:

Warning: odbc_connect() [function.odbc-connect]: SQL error: [Navision a/s][Navision Attain ODBC Driver]ISAM error , SQL state S0000 in SQLConnect in C:\Inetpub\wwwroot\Navision\index.php on line 10

The code I am using in the PHP file is thus:

<?php
$conn=odbc_connect('NavisionTest','test','test');
if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT * FROM contacts";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<table><tr>";
echo "<th>Company Name</th>";
echo "<th>Contact Name</th></tr>";
while (odbc_fetch_row($rs))
{
  $compname=odbc_result($rs,"Institution Name");
  $conname=odbc_result($rs,"Name");
  echo "<tr><td>$compname</td>";
  echo "<td>$conname</td></tr>";
}
odbc_close($conn);
echo "</table>";
?>

Hopefully someone may be able to help with this.

Cheers
James

Phenno

although that error says nothing to me, I noticed two things.

first, you haven't checked if odbc_exec executed properly
second, table names in Navision (though, you see them as Contact) are in different "format" like CompanyName$TableName. At least, that's how they are in MS SQL.

hairyjim

Phenno wrote:

although that error says nothing to me, I noticed two things.

first, you haven't checked if odbc_exec executed properly

It fails on the odbc_connect function, it does not even get to the odbc_exec.

We use the proprietary database with Navision, I know the DSN works because I am able to connect to the Test Navision DB through Excel.

Any further thoughts from anyone?

Cheers
Jim

Phenno

hairyjim wrote:

Phenno wrote:

although that error says nothing to me, I noticed two things.

first, you haven't checked if odbc_exec executed properly

It fails on the odbc_connect function, it does not even get to the odbc_exec.

We use the proprietary database with Navision, I know the DSN works because I am able to connect to the Test Navision DB through Excel.

Any further thoughts from anyone?

Cheers
Jim

I still think that the problem is not (directyl) caused by odbc_connect.
php says that your code has problem on 10th line and for odbc_connect says some warning...

Phenno

another thing that you should check are user rights if you have NTFS fs.

hairyjim

Phenno wrote:

I still think that the problem is not (directyl) caused by odbc_connect.
php says that your code has problem on 10th line and for odbc_connect says some warning...

Ahhh sorry the 10th line is actually odbc_connect, there are some lines of HTML I did not post.

Out of curiosity I changed the DSN so it pointed to our 'live' system on another server. So the connection settings for the DSN changed from local to Server. Out of the blue it all works fine.

Not sure if anyone else will get the same problem but changing the settings of the DSN from 'local' to 'Server' may work. It certainly fixed my problem.

Thanks for your input Phenno!

Jim

Jim