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

GMGenius

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

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)

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!

jdav357

ASKER

sorry meant:
PWD is DB_SERVER_PASSWORD

GMGenius

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');

GMGenius

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