Page 1 of 1

odbc php connection issues and connection string help

Posted: 18 May 2018, 00:35
by LyndonP
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! :-) See comments on https://stackoverflow.com/questions/504 ... ing-syntax

I have a working connection string on excel odbc -
Driver={Sage Line 50 v24};UID=123;PWD=123;DIR=\\server1\sage\xxx\accdata\;
But that is a user DSN, I know for PHP you have to have system dsn, and I have checked system dsn works in e.g. excel.
I believe this is the connection string used by system dsn
DSN=SageLine50v24;UID=123;PWD=123;

Is sage blocking this? do I have to change Sdata setting or something?

<?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>";
?>

Re: odbc php connection issues and connection string help

Posted: 18 May 2018, 14:10
by brucedenney
I know next to nothing of PHP.

I would expect the connection string to be something like

Driver={Sage Line 50 v24};UID=123;PWD=123;DIR=\\\\server1\\sage\\xxx\\accdata;

or

DSN=SageLine50v24;UID=123;PWD=123;DIR=\\\\server1\\sage\\xxx\\accdata;

Re: odbc php connection issues and connection string help

Posted: 21 Apr 2019, 07:23
by StanleyMDominguez
inforative.