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","xxx xx","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($quer yexe))
{
$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($connectionstri ng);
?>
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\line5 0odbctest\ 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\line5 0odbctest\ 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\line5 0odbctest\ 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\line5 0odbctest\ 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.
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","xxx
$query = "SELECT address_1, address_2, address_3 FROM sales_order WHERE (order_number LIKE '1')";
$queryexe = odbc_do($connectionstring,
while(odbc_fetch_row($quer
{
$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($connectionstri
?>
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\line5
Warning: odbc_do(): supplied argument is not a valid ODBC-Link resource in C:\Program Files\Apache Group\Apache2\htdocs\line5
Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in C:\Program Files\Apache Group\Apache2\htdocs\line5
Warning: odbc_close(): supplied argument is not a valid ODBC-Link resource in C:\Program Files\Apache Group\Apache2\htdocs\line5
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.
Oh. uid and pwd will probably be different. LETMEIN is the old password. May still be used!
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\line5 0odbctest\ 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\line5 0odbctest\ 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\line5 0odbctest\ 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\line5 0odbctest\ 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
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\line5
Connected.
Warning: odbc_tables(): supplied argument is not a valid ODBC-Link resource in C:\Program Files\Apache Group\Apache2\htdocs\line5
Warning: odbc_result_all(): supplied argument is not a valid ODBC result resource in C:\Program Files\Apache Group\Apache2\htdocs\line5
Warning: odbc_close(): supplied argument is not a valid ODBC-Link resource in C:\Program Files\Apache Group\Apache2\htdocs\line5
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
ASKER
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\line5 0odbctest\ 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\line5 0odbctest\ 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\line5 0odbctest\ 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\line5 0odbctest\ test.php on line 22
Hmmm now it says on the first connection, data files are wrong version?!??
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\line5
Warning: odbc_do(): supplied argument is not a valid ODBC-Link resource in C:\Program Files\Apache Group\Apache2\htdocs\line5
Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in C:\Program Files\Apache Group\Apache2\htdocs\line5
Warning: odbc_close(): supplied argument is not a valid ODBC-Link resource in C:\Program Files\Apache Group\Apache2\htdocs\line5
Hmmm now it says on the first connection, data files are wrong version?!??
ASKER
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\line5 0odbctest\ test.php on line 15
Notice: Undefined variable: address_2 in C:\Program Files\Apache Group\Apache2\htdocs\line5 0odbctest\ test.php on line 16
Notice: Undefined variable: address_3 in C:\Program Files\Apache Group\Apache2\htdocs\line5 0odbctest\ test.php on line 17
Looks to me like there's no data being put into the variables??
Notice: Undefined variable: address_1 in C:\Program Files\Apache Group\Apache2\htdocs\line5
Notice: Undefined variable: address_2 in C:\Program Files\Apache Group\Apache2\htdocs\line5
Notice: Undefined variable: address_3 in C:\Program Files\Apache Group\Apache2\htdocs\line5
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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!
ASKER
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.
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.
ASKER
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 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.
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.
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=SageLine
{
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.