asked on

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

jmyeom

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 :(

Tornado_

ASKER

Html tables have not been used, cheers for your answer though its greatly appreciated.

Bruce Denney

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

---------

Tornado_

ASKER

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.

ASKER CERTIFIED SOLUTION

Bruce Denney

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xorpheus

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.