Solved

Using PHP for ODBC Sage Line 50 connection

Posted on 2010-09-06
9
1,698 Views
Last Modified: 2012-06-27
Hi,
I am looking into connecting to Sage's database using PHP. It appears to use MSSQL. I am not familiar with how to connect to non-mysql datasources. These are the connection details that excel uses to do it, which work;
Driver={Sage Line 50 v15};
UID=example;
PWD=test

How would I connect to the database using these details?
Thanks for your help
0
Comment
Question by:jdav357
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 12

Expert Comment

by:GMGenius
ID: 33611865
You will need to use ODBC in PHP
eg
$rConn = odbc_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD);
 
0
 
LVL 2

Author Comment

by:jdav357
ID: 33611875
I have found where the files that I think from other posts say I need. These are located:
\\myserver\foldername\...\ACCDATA

Where would I put these details in the connection? (I have changed some bits of the string for test only)
0
 
LVL 2

Author Comment

by:jdav357
ID: 33611894
I am not entirely sure what would go in the string you posted,
I presume that UID is really the DB_SERVER_USERNAME and DB_SERVER_PASSWORD is the password?

I am not sure where I put:
\\myserver\foldername\...\ACCDATA
or
Driver={Sage Line 50 v15};
though!

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 2

Author Comment

by:jdav357
ID: 33611898
sorry meant:
PWD is DB_SERVER_PASSWORD
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33612029
You will need to create an ODBC DNS on the server where IIS is being used
 define('DB_SERVER', 'SageLine50v13'); // eg, localhost - should not be empty for productive servers
 define('DB_SERVER_USERNAME', 'MANAGER');
 define('DB_SERVER_PASSWORD', 'password');
 
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33612064
Here is a PHP page i built for my tests
It will list ALL the tables on a given connection (in this case it was v13)
You can add in the url ?table=tablename to list the contents of the table
also add &where=your where clause  - to limit the results returned
or even ?all=y which will dump ALL tables with ALL contents...
 


<?php
//SageLine50v12

$table = @$_GET["table"];
$where = @$_GET["where"];
$all = @$_GET["all"];

	$rConn = odbc_connect("SageLine50v13", "manager", "password");
	
	if ($rConn == 0)
	 {
	 die('Unable to connect to the Sage Line 50 V12 ODBC datasource.');
	 }
 
	// Get a list of tables as I cannot remember ANY of the L50 table names!
	$query = "SELECT * FROM ". $table;
	if ($where != "") {
		$query .= ' WHERE ' .$where;
	}
	 if ($all == "y") {
		echo "Listing Everything<br>";
		$rRes = odbc_tables($rConn);
		while(odbc_fetch_row ($rRes))
			{	
			echo "Table:" . odbc_result($rRes,'TABLE_NAME').'<br>';		
			$query = "SELECT  * FROM ". odbc_result($rRes,'TABLE_NAME');
			$tableexe = odbc_do($rConn, $query);  
			odbc_result_all($tableexe,'BORDER=1');
			}
	 } else {   
	    $queryexe = odbc_do($rConn, $query);  
     	odbc_result_all($queryexe,'BORDER=1');
		$rRes = odbc_tables($rConn);
	 }
	 


// Output the entire result set as a HTML table - quick dump!
odbc_result_all($rRes);


	 
// Close the ODBC connection.
odbc_close($rConn);
?>

Open in new window

0
 
LVL 10

Accepted Solution

by:
Bruce Denney earned 250 total points
ID: 33612287
There are a couple of problems with what you are looking to do.

The Sage Database needs to be on the same server as your PHP script or at least have very fast (Gigabit connection) to the database as ODBC

You will need to update your code every time the users upgrade their Sage, Sage are very keen on getting users to upgrade every year.

ODBC is VERY slow especially with large amounts of data.

There are lots of ways around this issues if they affect your usage.

For example if your PHP is on a Linux hosted server and the Data is on the Users Network you are not going to get very far.


I have done various workarounds, for example a windows scheduled task that dumps the data from the appropriate tables puts them in a CSV file uploads to the web server and then hits a url to update the SQL database on web hosting with the latest data.   This avoids the problems of narrow bandwidth and the poor performance of ODBC however, it is not real time.  I used a 3rd party bit of software that did the reverse as well, downloaded stuff from the site and put it into sage, this had the advantage that they just needed to upgrade the 3rd party stuff each time they upgraded sage, all the rest of the code was static.

I think that you need to tell us more about the actual scenario as whilst nothing anyone has suggested is wrong, it might not be a particle path for you to follow.




0
 
LVL 12

Assisted Solution

by:GMGenius
GMGenius earned 250 total points
ID: 33612429
<The Sage Database needs to be on the same server as your PHP script or at least have very fast (Gigabit connection) to the database as ODBC >
Not true, you can install the ODBC driver on any server , you just need to have the ODBC driver working on the IIS server you want to use.
<You will need to update your code every time the users upgrade their Sage, Sage are very keen on getting users to upgrade every year.>
That goes without saying but in the code I provided you just change the ODBC name or (using registry editor) you can change the ODBC Driver used for the ODBC name in the PHP page
I personally dont think its a difficult job to create the new ODBC and change a PHP config page
0
 
LVL 2

Author Closing Comment

by:jdav357
ID: 33742263
Thanks for your help. I've had to find alternative solution.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
php error 27 55
SQL Query help 3 40
error in oracle form 11 19
Access table not showing correct column 6 18
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question