[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 839
  • Last Modified:

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

0
Tornado_
Asked:
Tornado_
1 Solution
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now