{"id":2477,"date":"2018-02-24T01:26:51","date_gmt":"2018-02-23T14:26:51","guid":{"rendered":"https:\/\/littlesystems.com.au\/?p=2477"},"modified":"2018-02-24T04:48:53","modified_gmt":"2018-02-23T17:48:53","slug":"myob-odbc-bridge-to-linux","status":"publish","type":"post","link":"https:\/\/littlesystems.com.au\/blog\/2018\/02\/24\/myob-odbc-bridge-to-linux\/","title":{"rendered":"MYOB ODBC Bridge to Linux"},"content":{"rendered":"<p>Distributed as Open Source Software under the\u00a0<a href=\"http:\/\/www.perlfoundation.org\/artistic_license_2_0\">Artistic License 2.0<\/a>.<\/p>\n<p><strong>Note:<\/strong>\u00a0 <span style=\"text-decoration: underline;\">Little Systems historical from 2009<\/span>.\u00a0 MYOB has since released a new API, with information in their <a href=\"http:\/\/developer.myob.com\/\">developer area<\/a>.<\/p>\n<ul>\n<li><a href=\"#overview\">Overview<\/a><\/li>\n<li><a href=\"#screenshots\">Screenshots<\/a><\/li>\n<li><a href=\"#requirements\">Requirements<\/a><\/li>\n<li><a href=\"#download\">Download<\/a><\/li>\n<li><a href=\"#installation\">Installation<\/a><\/li>\n<\/ul>\n<h2 id=\"overview\">Overview<\/h2>\n<p>The bridge migrates the MYOB database to a Linux SQL server.<br \/>\n(Because MYOB ODBC is slow, Windows only, and has limited SQL syntax.)<\/p>\n<p>The bridge consists of:<\/p>\n<ul>\n<li><span style=\"text-decoration: underline;\">Server (on Windows)<\/span>\n<ul>\n<li>Reads the Data Dictionary<\/li>\n<li>Reads MYOB via ODBC<\/li>\n<li>Outputs XML<\/li>\n<\/ul>\n<\/li>\n<li><span style=\"text-decoration: underline;\">Client (on Linux)<\/span>\n<ul>\n<li>Gets XML from server<\/li>\n<li>Recreates MYOB database on an SQL server*<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>* Presently supported database(s): PostgreSQL<\/p>\n<h2 id=\"screenshots\">Screenshots<\/h2>\n<h5>Server<\/h5>\n<figure id=\"attachment_2496\" aria-describedby=\"caption-attachment-2496\" style=\"width: 150px\" class=\"wp-caption alignleft\"><a href=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_serverscreen.gif\"><img loading=\"lazy\" class=\"wp-image-2496 size-thumbnail\" src=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_serverscreen-150x150.gif\" alt=\"\" width=\"150\" height=\"150\" srcset=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_serverscreen-150x150.gif 150w, https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_serverscreen-300x300.gif 300w\" sizes=\"(max-width: 150px) 100vw, 150px\" \/><\/a><figcaption id=\"caption-attachment-2496\" class=\"wp-caption-text\">Connected to the MYOB Clearwater sample file<\/figcaption><\/figure>\n<figure id=\"attachment_2495\" aria-describedby=\"caption-attachment-2495\" style=\"width: 150px\" class=\"wp-caption alignleft\"><a href=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_schemascreen.gif\"><img loading=\"lazy\" class=\"wp-image-2495 size-thumbnail\" src=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_schemascreen-150x150.gif\" alt=\"\" width=\"150\" height=\"150\" srcset=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_schemascreen-150x150.gif 150w, https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_schemascreen-300x300.gif 300w\" sizes=\"(max-width: 150px) 100vw, 150px\" \/><\/a><figcaption id=\"caption-attachment-2495\" class=\"wp-caption-text\">Rendering the XML schema<\/figcaption><\/figure>\n<figure id=\"attachment_2494\" aria-describedby=\"caption-attachment-2494\" style=\"width: 150px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_datascreen.gif\"><img loading=\"lazy\" class=\"wp-image-2494 size-thumbnail\" src=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_datascreen-150x150.gif\" alt=\"\" width=\"150\" height=\"150\" srcset=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_datascreen-150x150.gif 150w, https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_datascreen-300x300.gif 300w\" sizes=\"(max-width: 150px) 100vw, 150px\" \/><\/a><figcaption id=\"caption-attachment-2494\" class=\"wp-caption-text\">XML data<\/figcaption><\/figure>\n<h5>Client<\/h5>\n<figure id=\"attachment_2505\" aria-describedby=\"caption-attachment-2505\" style=\"width: 150px\" class=\"wp-caption alignleft\"><a href=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_clientscreen.gif\"><img loading=\"lazy\" class=\"wp-image-2505\" src=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_clientscreen-150x150.gif\" alt=\"\" width=\"150\" height=\"109\" srcset=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_clientscreen-300x218.gif 300w, https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_clientscreen-768x557.gif 768w\" sizes=\"(max-width: 150px) 100vw, 150px\" \/><\/a><figcaption id=\"caption-attachment-2505\" class=\"wp-caption-text\">Reading XML and creating the database on Linux<\/figcaption><\/figure>\n<figure id=\"attachment_2503\" aria-describedby=\"caption-attachment-2503\" style=\"width: 150px\" class=\"wp-caption alignleft\"><a href=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_client_psql.gif\"><img loading=\"lazy\" class=\"wp-image-2503\" src=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_client_psql-150x150.gif\" alt=\"\" width=\"150\" height=\"181\" \/><\/a><figcaption id=\"caption-attachment-2503\" class=\"wp-caption-text\">The database on Linux with MYOB tables<\/figcaption><\/figure>\n<figure id=\"attachment_2504\" aria-describedby=\"caption-attachment-2504\" style=\"width: 150px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_client_psql_table.gif\"><img loading=\"lazy\" class=\"wp-image-2504\" src=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_client_psql_table-150x150.gif\" alt=\"\" width=\"150\" height=\"107\" srcset=\"https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_client_psql_table-300x215.gif 300w, https:\/\/littlesystems.com.au\/wp-content\/uploads\/2018\/02\/myobodbcbridgetolinux_client_psql_table-768x550.gif 768w\" sizes=\"(max-width: 150px) 100vw, 150px\" \/><\/a><figcaption id=\"caption-attachment-2504\" class=\"wp-caption-text\">MYOB data from the Clearwater sample file<\/figcaption><\/figure>\n<h2 id=\"requirements\">Requirements<\/h2>\n<p>MYOB company file must be enabled for ODBC access.\u00a0 Visit MYOB\u00a0for further information.<\/p>\n<p>In general, the installation requires basic system administration experience in Windows, Linux and networking.<\/p>\n<p>The server needs MYOB ODBC driver and PHP.\u00a0\u00a0The client needs Perl and PostgreSQL.<\/p>\n<h2 id=\"download\">Download<\/h2>\n<table border=\"1\" width=\"100%\" cellpadding=\"3\">\n<tbody>\n<tr>\n<td colspan=\"2\"><strong>Server<\/strong><\/td>\n<td colspan=\"2\"><strong>Client<\/strong><\/td>\n<\/tr>\n<tr>\n<td>Language<\/td>\n<td>Download<\/td>\n<td>Language<\/td>\n<td>Download<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">PHP<\/td>\n<td valign=\"top\"><a href=\"\/wp-content\/uploads\/special\/bridge-server.1.0.zip\">bridge-server.1.0.zip<\/a><\/p>\n<p><a href=\"\/wp-content\/uploads\/special\/data.html\">(View sample code as HTML)<\/a><\/td>\n<td valign=\"top\">Perl<\/td>\n<td valign=\"top\"><a href=\"\/wp-content\/uploads\/special\/bridge-client.1.0.tar.gz\">bridge-client.1.0.tar.gz<\/a><\/p>\n<p><a href=\"\/wp-content\/uploads\/special\/refresh.pl.html\">(View sample code as HTML)<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Little Systems may soon provide a full XML schema of the MYOB database based on the MYOB data dictionary.<\/p>\n<h2 id=\"installation\">Installation<\/h2>\n<h5>Server<\/h5>\n<p>(Assumes the MYOB company file is configured for ODBC.)<br \/>\n(Assumes the Windows ODBC Data Source Administrator is configured accordingly.)<\/p>\n<ul>\n<li>Unzip the bridge-server.zip file.<\/li>\n<li>Move the &#8220;bridge&#8221; directory to the web server (under the document tree as appropriate).<\/li>\n<li>Grant the necessary file permissions to the script(s).\u00a0\u00a0(Not doing so may cause ODBC errors).<\/li>\n<li>Edit .\/config_global.php to set the DSN, username and password.\u00a0\u00a0(By default, it is configured for the Clearwater sample file).<\/li>\n<li>Edit .\/config_myob.php to define the tables and fields to be recreated on Linux.\u00a0\u00a0(Included are selected fields from the Address, Cards, Customers and Items tables.\u00a0\u00a0To define additional tables, consult the MYOB data dictionary included with the ODBC drivers.)<\/li>\n<li>Load .\/index.php in your web browser, and you should see the &#8220;MYOB ODBC Bridge to Linux&#8221; page as shown above in Screen Shots.<\/li>\n<li>Click on the &#8220;Schema&#8221; and &#8220;Data&#8221; links and you should see valid XML.\u00a0\u00a0This means that the server is working.<\/li>\n<\/ul>\n<h5>Client<\/h5>\n<p>(Assumes PostgreSQL is installed and duly configured for access)<\/p>\n<ul>\n<li>Login as root<\/li>\n<li>Chmod to postgres (su postgres)<\/li>\n<li>Change to the postgres home directory (cd ~)<\/li>\n<li>Untar the bridge-client.tar.gz file (tar xvfz bride-client.tar.1.0.tar.gz)<\/li>\n<li>Change to the bridge directory (cd bridge)<\/li>\n<li>Note down the working directory (pwd)<\/li>\n<li>Edit refresh.pl to set the &#8220;Primary configuration variables&#8221; towards the top of the file, particularly:\n<ul>\n<li>set $bridgeURL to the URL of the bridge directory on the web server, then append the xml directory.\u00a0\u00a0For example, http:\/\/your.server.com\/bridge\/xml<\/li>\n<li>set $bridgeLocalRoot to the working directory (noted just above) where the bridge client (refresh.pl) is installed<\/li>\n<\/ul>\n<\/li>\n<li>Exit your editor<\/li>\n<li>Run psql (psql)\n<ul>\n<li>Note: You should not be prompted for a password if you are logged on as postgres.\u00a0\u00a0Otherwise, you may need to adjust PostgreSQL configuration\/security files accordingly.<\/li>\n<li>Create an appropriate user, for example &#8220;myob&#8221; (as set by default in the refresh.pl &#8220;Primary configuration variables&#8221;) (CREATE USER myob WITH PASSWORD myob)<\/li>\n<\/ul>\n<\/li>\n<li>Run refresh.pl (always as user postgres) and troubleshoot as necessary\n<ul>\n<li>Note: Several additional files will be created and maintained in the directory, including log files.<\/li>\n<\/ul>\n<\/li>\n<li>To set Linux to run refresh.pl at regular interval adjust the postgres cronfile accordingly (crontab -e)<\/li>\n<\/ul>\n<h2>Questions, Discussion or Feedback<\/h2>\n<p>Questions, discussion or feedback are welcome.\u00a0\u00a0<a href=\"https:\/\/littlesystems.com.au\/contact\/\">Contact<\/a> Little Systems.<\/p>\n<h3>Consulting<\/h3>\n<p><a href=\"https:\/\/littlesystems.com.au\/contact\/\">Contact<\/a>\u00a0Little Systems for conversation or a quote.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Distributed as Open Source Software under the\u00a0Artistic License 2.0. Note:\u00a0 Little Systems historical from 2009.\u00a0 MYOB has since released a new API, with information in their developer area. Overview Screenshots Requirements Download Installation Overview The bridge migrates the MYOB database to a Linux SQL server. (Because MYOB ODBC is slow, Windows only, and has limited &hellip; <a href=\"https:\/\/littlesystems.com.au\/blog\/2018\/02\/24\/myob-odbc-bridge-to-linux\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">MYOB ODBC Bridge to Linux<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[],"_links":{"self":[{"href":"https:\/\/littlesystems.com.au\/wp-json\/wp\/v2\/posts\/2477"}],"collection":[{"href":"https:\/\/littlesystems.com.au\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/littlesystems.com.au\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/littlesystems.com.au\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/littlesystems.com.au\/wp-json\/wp\/v2\/comments?post=2477"}],"version-history":[{"count":9,"href":"https:\/\/littlesystems.com.au\/wp-json\/wp\/v2\/posts\/2477\/revisions"}],"predecessor-version":[{"id":2532,"href":"https:\/\/littlesystems.com.au\/wp-json\/wp\/v2\/posts\/2477\/revisions\/2532"}],"wp:attachment":[{"href":"https:\/\/littlesystems.com.au\/wp-json\/wp\/v2\/media?parent=2477"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/littlesystems.com.au\/wp-json\/wp\/v2\/categories?post=2477"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/littlesystems.com.au\/wp-json\/wp\/v2\/tags?post=2477"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}