Link to home
Create AccountLog in
Avatar of jdav357
jdav357

asked on

Using PHP for ODBC Sage Line 50 connection

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
Avatar of GMGenius
GMGenius
Flag of United Kingdom of Great Britain and Northern Ireland image

You will need to use ODBC in PHP
eg
$rConn = odbc_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD);
 
Avatar of jdav357
jdav357

ASKER

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)
Avatar of jdav357

ASKER

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!

Avatar of jdav357

ASKER

sorry meant:
PWD is DB_SERVER_PASSWORD
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');
 
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

ASKER CERTIFIED SOLUTION
Avatar of Bruce Denney
Bruce Denney
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jdav357

ASKER

Thanks for your help. I've had to find alternative solution.