Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

odbc php connection issues and connection string help

Sage 50 general help forum - Free help and support for all general issues
Post Reply
LyndonP
User
User
Posts: 1
Joined: 18 May 2018, 00:33
Sage Version: v2018 24 UK/Europe/Africa

odbc php connection issues and connection string help

Post by LyndonP » 18 May 2018, 00:35

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

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4040
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: odbc php connection issues and connection string help

Post by brucedenney » 18 May 2018, 14:10

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;
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

Post Reply

Who is online

Users browsing this forum: Bing [Bot] and 6 guests