0

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;

  1. Database local path instead of UNC path
  2. Driver name with and without spaces
  3. 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>";
?>
Lyndon Penson
  • 65
  • 1
  • 8
  • Please rephrase your question title to something meaningful. Your currently title just redundantly repeats the tag information and adds the words *problems on WIndows server*, which provides no useful information. Your title should be clear enough to explain the specific problem or question in a way that will be meaningful to a future user here seeing it in a list of search results. It would also be useful if you included information about any error messages you're getting. Thanks. – Ken White May 17 '18 at 23:55
  • Also, presumably you've searched Google for [sage50 odbc connection string](https://stackoverflow.com/q/50382246/62576)? – Ken White May 18 '18 at 00:01
  • @KenWhite I got no error messages sorry. Is there something missing from my code that prevents error messages being displayed? If I had got any I would certainly have researched them specifically and posted them here too. – Lyndon Penson May 18 '18 at 00:11
  • @KenWhite I have googled that yes, mostly got results relating to excel and access odbc which is working anyway. Researched php string specifically, not much coverage on it, did try things like script on https://www.experts-exchange.com/questions/26454364/Using-PHP-for-ODBC-Sage-Line-50-connection.html but no good – Lyndon Penson May 18 '18 at 00:17
  • @KenWhite is that better for the title? Thanks for your help – Lyndon Penson May 18 '18 at 00:18
  • Connecting through ODBC is connecting through ODBC, regardless of the language you're using (that's the point of using ODBC). The connection string may require minor changes (escaping backslash characters, for instance), but it works on all of them. If you have a connection string that works properly through Excel, then the issue is most likely somewhere else. Regarding your title edit: The tags are entirely redundant, and should not be in the title (the info is in the tags). So if you saw *connection string syntax* searching here now among a long list of the search results, would it help you? – Ken White May 18 '18 at 00:20

1 Answers1

1

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;
}
Davids Stack
  • 78
  • 1
  • 4