Sage 50 ODBC on PHP connection string syntax

Question

Sage 50 ODBC on PHP connection string syntax

1k views Asked by Lyndon Penson At 25 November 2024 at 01:06

Connection string issues . Versions - Sage Line 50v24, PHP 7.2.2, Windows Server 2012 R2 64 bit.

Sorry if there is something I have missed - please ask for any missing info in comments. I have spent hours and hours and hours researching this and trying different things. Thank you! :-)

<?php  
$user="myuser";
$pass="mypass";
$connstring="Driver={SageLine50v24};Server=localhost;Database=\\\server1\\uncpath\\companyname\\accdata\\";
echo $connstring;
$conn=odbc_connect($connstring,$user,$pass); 
$sql="SELECT * FROM SALES_LEDGER";
$rs=odbc_exec($conn,$sql);
var_dump($rs); 
odbc_close($conn); 
?>

I believe the ODBC driver is configured ok because it works through Excel.

I have googled extensively and read probably all the SO questions on it. That pointed out things like the final backslash after ACCDATA being critical in PHP (although the ODBC driver doesnt need it in Excel or other programs;)

Variants of the above code I have tried include;

Database local path instead of UNC path
Driver name with and without spaces
Server and Database with and without inverted commas

Another variant of the code I tried, however the error reporting did not return any answers;

<?php 
error_reporting(E_ALL);
echo "<html>";
echo "<body>";
$user="myuser";
$pass="mypass";
$connstring="'Driver={SageLine50v24};Server=localhost;Database=\\\server1\\uncpath\\companyname\\accdata\\'";
echo $connstring;
$conn=odbc_connect($connstring,$user,$pass);
echo odbc_error($conn);
if (!$conn){exit("Connection Failed: " . $conn);}
$sql="SELECT * FROM SALES_LEDGER";
$rs=odbc_exec($conn,$sql);
var_dump($rs);
if (!$rs){exit("Error in SQL");}
echo "<table><tr>";
echo "<th>account</th>";
echo "<th>name</th></tr>";
while (odbc_fetch_row($rs))
{
$account=odbc_result($rs,"account_ref");
$coname=odbc_result($rs,"name");
echo "<tr><td>$account</td>";
echo "<td>$coname</td></tr>";
}
odbc_close($conn);
echo "</table>";
echo "</body>";
echo "</html>";
?>

Original Q&A

There are 1 answers

**Davids Stack** · Answer 1 · 2019-12-23 16:28:35

This is probably over 18 months too late for you but might help someone else. I struggled for a while too when moving from Server 2008 and PHP 5.3.28 to Server 2016 and PHP 7.3.7. Both were running PHP under IIS (version 10 on Server 2016).

The following are the steps that I took to get it working followed by an example of the PHP with the connection string:

Install Sage v24 on the server. You can probably get away with only doing a server install to get the ODBC driver and DSN in place but I installed the full application.
Check that the ODBC driver extension has been added to the PHP configuration as it isn't included by default with PHP 7. e.g. edit C:\Program Files\PHP\v7.3\php.ini to include extension=php_odbc.dll in the [ExtensionList] section. The library used is 32 bit and so it uses the 32-bit DSN.
Update the DSN (32-bit version) to point to the correct Sage data location. You need to use the 32-bit version of the ODBC Data Source Administrator for this (as found in C:\Windows\SysWOW64\odbcad32.exe. An example of the Data Path for the SageLine50V24 System DSN is V:\SageData\2018\accdata
Recycle the App Pool for your website in IIS. I don't know if this is actually necessary but it happened on the way to solving my problem.

The PHP then looks like:

function sage_connect(){
  $uid = "username";
  $pwd = "password";
    if (($rConn = odbc_connect("DSN=SageLine50v24;uid=$uid;pwd=$pwd", $uid, $pwd)) === false) {
      die('Unable to connect to the Sage Line 50 V24 ODBC datasource.');
  }
  return $rConn;
}

TechQA.

Sage 50 ODBC on PHP connection string syntax

There are 1 answers

Related Questions in PHP

Related Questions in CONNECTION

Related Questions in ODBC

Related Questions in SAGE50

Popular Questions

Popular Tags

Trending Questions