Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

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: 4616
Joined: 28 Feb 2006, 09:56
Sage Version: v28 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 :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

StanleyMDominguez
User
User
Posts: 1
Joined: 21 Apr 2019, 07:22
Sage Version: Other

Re: odbc php connection issues and connection string help

Post by StanleyMDominguez » 21 Apr 2019, 07:23

inforative.

Post Reply

Who is online

Users browsing this forum: No registered users and 18 guests