ODBC sage line 50, date fields create strange characters in php

Im having problems with getting dates out of an odbc connection (sage).

On my browser it produces 201ger!<REC (with the arrow filled in style)   when opened to view source it just shows 201, so I belive it to be a unrecognised character set.

when the odbc connection is opened via Microsoft Access it converts the date correctly to "14/03/2010 12:39:01" . Is there a way at the SQL level or php level to convert this to a readable string. Anyones help on this would be greatly appreciated. Has anyone had this problem before?

All the best.
Oli
$dbh = odbc_connect('SageLine50v16', "manager", "");

$sql="SELECT RECORD_CREATE_DATE FROM COMPANY";
$result_data = odbc_exec($dbh,$sql);   
    while ($row = odbc_fetch_array($result_data)) {
      echo $row['RECORD_CREATE_DATE'];
       }

Open in new window

Tornado_Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jmyeomCommented:
this may not be a proplem with unknown characters, do you have tables in your page by any chance?..

tables can sometimes display the last bit of code in a table OUTSIDE the table... if not, sorry i cant help :(
0
Tornado_Author Commented:
Html tables have not been used, cheers for your answer though its greatly appreciated.
0
Bruce DenneySage 50 Consultant and IntegratorCommented:
Are you able to get data from other fields okay, is this an issue with the handling of date formatted data or is it an issue with all data coming via odbc?

-----------

Um... are you sure you want the date the company was created in Sage?

There is only one record in the Company table.

Are you sure you don't want to be looking at another table eg SALES_LEDGER


----------

I would create a dsn for the source rather than using the driver directly, it could have issues locating the datapath

---------

0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Tornado_Author Commented:
all other fields are coming out from the database are ok. It is a problem with date field only and we are using every table from sage to pull data out. we have tried both a dsn and direct driver ways the date fields just don't want to play ball but they export to access ok.
0
Bruce DenneySage 50 Consultant and IntegratorCommented:
I suspect that the dates are being converted by PHP.

I am good with Sage not PHP or SQL so defer to others here...

Sage chose to use a comma as the delimiter for ODBC.  A comma is also a delimiter for SQL so there are cases where things become ambiguous, however, I would expect that to be an issue on the query, not the results.

My thought would be to check what happens using other sources.  I know MySQL returns dates in a different format to the MS Jet Engine, (I know this because I have used MySQL with MS Access and it has some strange side effects.)

Perhaps a better test would be to try reading data from Access with dates. I would link and access table to sage via SQL to make a quick set of test data, then link to access via SQL from PHP to see if the issue is in the way PHP handles data returned from ODBC when it is dates.




1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xorpheusCommented:
Hi,

I know this thread is over 5 years old now but I would like to contribute just in case anyone else arrives here like I did while spending an entire weekend trying to solve this exact problem.  I'm using php v5.5.12 and Sage Line 50 v18 ODBC driver.

Long story short I discovered it has been reported as a php bug:
Bug #51354 Random string data returned for timestamp odbc field

In my case the php odbc functions return "201" regardless of the actual data.  Using the PDO class returns seemingly random nonsense data which is the same for all rows in the result set but usually different for each query.

A possible workaround mentioned on the bug report page (it did indeed work on my system) is to use ADO via php COM instead of the php odbc features.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.