All posts by littlesystems

MYOB ODBC Bridge to Linux

Distributed as Open Source Software under the Artistic License 2.0.

Note:  Little Systems historical from 2009.  MYOB has since released a new API, with information in their developer area.

Overview

The bridge migrates the MYOB database to a Linux SQL server.
(Because MYOB ODBC is slow, Windows only, and has limited SQL syntax.)

The bridge consists of:

  • Server (on Windows)
    • Reads the Data Dictionary
    • Reads MYOB via ODBC
    • Outputs XML
  • Client (on Linux)
    • Gets XML from server
    • Recreates MYOB database on an SQL server*

* Presently supported database(s): PostgreSQL

Screenshots

Server
Connected to the MYOB Clearwater sample file
Rendering the XML schema
XML data
Client
Reading XML and creating the database on Linux
The database on Linux with MYOB tables
MYOB data from the Clearwater sample file

Requirements

MYOB company file must be enabled for ODBC access.  Visit MYOB for further information.

In general, the installation requires basic system administration experience in Windows, Linux and networking.

The server needs MYOB ODBC driver and PHP.  The client needs Perl and PostgreSQL.

Download

Server Client
Language Download Language Download
PHP bridge-server.1.0.zip

(View sample code as HTML)

Perl bridge-client.1.0.tar.gz

(View sample code as HTML)

Little Systems may soon provide a full XML schema of the MYOB database based on the MYOB data dictionary.

Installation

Server

(Assumes the MYOB company file is configured for ODBC.)
(Assumes the Windows ODBC Data Source Administrator is configured accordingly.)

  • Unzip the bridge-server.zip file.
  • Move the “bridge” directory to the web server (under the document tree as appropriate).
  • Grant the necessary file permissions to the script(s).  (Not doing so may cause ODBC errors).
  • Edit ./config_global.php to set the DSN, username and password.  (By default, it is configured for the Clearwater sample file).
  • Edit ./config_myob.php to define the tables and fields to be recreated on Linux.  (Included are selected fields from the Address, Cards, Customers and Items tables.  To define additional tables, consult the MYOB data dictionary included with the ODBC drivers.)
  • Load ./index.php in your web browser, and you should see the “MYOB ODBC Bridge to Linux” page as shown above in Screen Shots.
  • Click on the “Schema” and “Data” links and you should see valid XML.  This means that the server is working.
Client

(Assumes PostgreSQL is installed and duly configured for access)

  • Login as root
  • Chmod to postgres (su postgres)
  • Change to the postgres home directory (cd ~)
  • Untar the bridge-client.tar.gz file (tar xvfz bride-client.tar.1.0.tar.gz)
  • Change to the bridge directory (cd bridge)
  • Note down the working directory (pwd)
  • Edit refresh.pl to set the “Primary configuration variables” towards the top of the file, particularly:
    • set $bridgeURL to the URL of the bridge directory on the web server, then append the xml directory.  For example, http://your.server.com/bridge/xml
    • set $bridgeLocalRoot to the working directory (noted just above) where the bridge client (refresh.pl) is installed
  • Exit your editor
  • Run psql (psql)
    • Note: You should not be prompted for a password if you are logged on as postgres.  Otherwise, you may need to adjust PostgreSQL configuration/security files accordingly.
    • Create an appropriate user, for example “myob” (as set by default in the refresh.pl “Primary configuration variables”) (CREATE USER myob WITH PASSWORD myob)
  • Run refresh.pl (always as user postgres) and troubleshoot as necessary
    • Note: Several additional files will be created and maintained in the directory, including log files.
  • To set Linux to run refresh.pl at regular interval adjust the postgres cronfile accordingly (crontab -e)

Questions, Discussion or Feedback

Questions, discussion or feedback are welcome.  Contact Little Systems.

Consulting

Contact Little Systems for conversation or a quote.

Page Builder Express, Proprietary CMS

Little Systems developed Page Builder Express (PBE), a full commercial Linux/Apache/MySQL/PHP (LAMP) Content Management System (CMS), between 1999 and 2001.

See below for features, history, screenshots and example sites.

Features

  • Streamlined user-interface, including the “one-click edit” feature from the public page to the editing dashboard for Blocks, Objects and Wizards.
  • Integrated documentation and Quick-Start guide.
  • Password protected sites, with email-based password management.
  • “Wizards” for end-users regular tasks.
  • Auto-generated pages, including colour-coded site/last updated map, disclaimer page and privacy policy.
  • The Webalizer visitor statistics support.
  • Apache Dynamically Configured Mass Virtual Hosting support.
  • Administration backend, with template editor.
  • MySQL and Oracle support.

History

In 2001, Little Systems licensed PBE to Geelong Science and Technology (GSAT) for Victorian educational institutions.  In this arrangement, PBE was used for several years by Australian primary schools, high schools and a university department through to 2005, and on.

(The PBE system was only retired in 2017.  See also Credit.  See also Little Systems Services, including SchoolWebsites.net.au, and Little Systems Code, including the Open Source Builder Express user-interface plugin for WordPress.)

Screenshots

     

Example Sites (and Example Pages)

Primary School

      

     

     

      

      

Secondary School

      

   

University

     

Accounting Systems Integration with Manufacturing Execution System (MES)

Systems integration for a manufacturing firm.

A medium-sized precision engineering firm in country Victoria, wanted their live customer and inventory data from MYOB, available in the user-interface of their manufacturing execution system (MES).

Little Systems performed information systems consulting to complete this section of the MES work.

The solution.

When the MYOB API was new (version 1), Little Systems constructed a simple PHP script for Windows/IIS to render MYOB data as XML.  This made the MYOB data available via a URL on the internal network (intranet).

Little Systems then integrated the data with the MES.  The MES was a customised Open Source Perl web application on Linux.  Little Systems developed a Perl script to read the MYOB data as XML and to populate the local PostgreSQL database with MYOB customer and inventory data.  The script was automated to run periodically.  Little Systems then integrated the data in PostgreSQL with the Open Source web application.

The result.

Live customer and inventory data from MYOB, was made available in the user-interface of the web-based manufacturing execution system (MES).

Little Systems also provided additional related consulting, to create custom and printable job instruction sheets (JIS) for the manufacturing floor.

Little Systems is available for consulting.  See also Services.

Little Systems can also provide consulting for an Open Source manufacturing execution system (MES) for small-to-medium sized manufacturing firms.

Contact Little Systems for a quote.