Link to home
Start Free TrialLog in
Avatar of Allan_Shiels
Allan_Shiels

asked on

PHP And ODBC With Sage Line 50v12

Hi Folks,

I'm currently experimenting with PHP and it's ODBC functions to access Data from a Sage line 50v12 accounts package.  I've set up
the System DSN on the webserver to access the data area on the server that sage resides, and i have the following code.

<?php
     $connectionstring = odbc_connect("line50","xxxxx","xxxxx");


     $query = "SELECT address_1, address_2, address_3 FROM sales_order WHERE (order_number LIKE '1')";
     $queryexe = odbc_do($connectionstring, $query);
     
     while(odbc_fetch_row($queryexe))
     {
     $address1 = odbc_result($queryexe, 1);
     $address2 = odbc_result($queryexe, 2);
     $address3 = odbc_result($queryexe, 3);
     
     print ("<tr>");
     print ("<td>$address_1</td>");
     print ("<td>$address_2</td>");
     print ("<td>$address_3</td>");
     print ("</tr>");
     }
 

     odbc_close($connectionstring);

?>


Now, it appear to me that it's not even TRYING to connect...i get the following errors when running the script:

Warning: odbc_connect() [function.odbc-connect]: SQL error: Cannot find all files in data path, SQL state 08001 in SQLConnect in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test.php on line 2

Warning: odbc_do(): supplied argument is not a valid ODBC-Link resource in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test.php on line 6

Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test.php on line 8

Warning: odbc_close(): supplied argument is not a valid ODBC-Link resource in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test.php on line 22

Can anyone assist here?  I've tried another sage data source in a different location and it still doesn't seem to get connected!!

Thanks,

Allan.
Avatar of Richard Quadling
Richard Quadling
Flag of United Kingdom of Great Britain and Northern Ireland image

The first error you are getting is that you have not defined the correct location to the Line 50 files.

I don't have Line 50 here, but on Line 100, you have to define the locations to the data.

From memory, I thought the standard DSN was "SageLine50Vxx", not "line50".

Make sure you create the DSN as a system DSN and not a user or file DSN.

Try ...

<?php

if (($rConn = odbc_connect('DSN=SageLine50V12;uid=MANAGER;pwd=LETMEIN')) === False)
 {
 die('Unable to connect to the Sage Line 50 V12 ODBC datasource.');
 }

echo 'Connected.';

// Get a list of tables as I cannot remember ANY of the L50 table names!
$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);
?>


You MAY want to read about persistent connections if you are NOT using a CGI/CLI variant of PHP (i.e. ISAPI or compiled code into the web server).

Change odbc_connect to odbc_pconnect.

If persistent connections are supported, your code will be able to use the same connection each time.

If you intend to write data to the tables, you will need specific security access rights and to use Sage's own mechanism - at least you do for Payroll and Line 100 - not too sure to Line 50.

Try this out, and come back with what happens.
Oh. uid and pwd will probably be different. LETMEIN is the old password. May still be used!
Avatar of Allan_Shiels
Allan_Shiels

ASKER

Hey,

I tried the code you posted there and it gave the following:

Warning: Wrong parameter count for odbc_connect() in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test2.php on line 3
Connected.
Warning: odbc_tables(): supplied argument is not a valid ODBC-Link resource in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test2.php on line 11

Warning: odbc_result_all(): supplied argument is not a valid ODBC result resource in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test2.php on line 14

Warning: odbc_close(): supplied argument is not a valid ODBC-Link resource in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test2.php on line 17

My System DSN i renamed to 'line50' incase the old name was causing probs, in the properties of that system DNS i have the path
as s:\Line 50 Data\2004-2005\accdata
Hey, i changed the data path to the actual UNC network path - \\server\line 50 data\2004-2005\accdata

and when i ran my original script i got...

Warning: odbc_connect() [function.odbc-connect]: SQL error: Data files are wrong version, SQL state 08001 in SQLConnect in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test.php on line 2

Warning: odbc_do(): supplied argument is not a valid ODBC-Link resource in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test.php on line 6

Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test.php on line 8

Warning: odbc_close(): supplied argument is not a valid ODBC-Link resource in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test.php on line 22

Hmmm now it says on the first connection, data files are wrong version?!??

Hey i'm nearly there - im linking to non V12 data, i've linked to V12 data now and i've got rid of the first error, so it looks like it's connecting - now all i get is these from my original script:

Notice: Undefined variable: address_1 in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test.php on line 15

Notice: Undefined variable: address_2 in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test.php on line 16

Notice: Undefined variable: address_3 in C:\Program Files\Apache Group\Apache2\htdocs\line50odbctest\test.php on line 17

Looks to me like there's no data being put into the variables??

You are assigning values to $address1 but asking to print $address_1.
ASKER CERTIFIED SOLUTION
Avatar of Richard Quadling
Richard Quadling
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yup, just picked up on that one :-)

Im fairly new to this PHP malarky, it's working now and i'm well chuffed.

I'll split the points evenly.
Thanks for the split! Ha ha!
Yup, dunno what i was talking about there :-)

In addition i've now built a form page asking for a specific Sales order number and it uses that to pull the relevant data from
the sage line 50 odbc link.

I love it when things like that work.
NP. Glad to have helped.
Rquadling,

you said this above

"If you intend to write data to the tables, you will need specific security access rights and to use Sage's own mechanism - at least you do for Payroll and Line 100 - not too sure to Line 50."

Can you write data back to the line 100 tables with ODBC?  Can you give me more details like which version you are using and how you manage this?

thanks.
You CAN write back to Line 100, but you have to Sage's own library. It is RDO. You cannot use SQL UPDATE/INSERT/DELETE.

It is terrible.

The entire ODBC library is not multi-threaded. Which means you have no choice but to wait for the program to complete. It is also not thread safe. You cannot monitor the progress of a SELECT or anything.

By far the best way to get data into Line 100 is to use Retrieve and/or the Line 100 posting files.

I've been out of Sage Dev for about 18 months now. So, not sure how much has changed. As an ex-4GL developer for 15 years, I have pretty good knowledge of Line 100.

If you want a more off question chat then get my IM details in my profile.