The pain of MYOB ODBC

OK, if you’re like me, you’re used to coding on UNIX. So when, as is pretty much inevitable (unless you’re living in a university or somewhere unrealistic like that), you have to code on Windows, it can be a bit of a shock. Which is to be expected… proprietary binaries everywhere… argh.

But I wasn’t expecting it to be quite this painful. Connecting to MYOB using their ODBC connector is akin to standing on one leg, touching your nose just so, and trying to piss into a specific well in an ice-tray. Put that one in your freezer and freeze it! To make matters worse, we want to use PHP for all of this, and MYOB provides examples only in Microsoft’s favorite languages like C# and *gasp* ASP. AND, before version 6, you couldn’t even insert stuff into their database identified by the primary key. Read that again. You have to insert it based on last name. LAST FRIGGIN NAME. So if you have two Smiths, well… it assigns it to the first one. Gah. Anyway, it seems that with MYOB Premier version 10 and ODBC version 6, you can actually do these fundamental things.

So y’all don’t have to go through the same pain we did, here is our example code. One in Perl, the other in PHP.

NOTE: WordPress is hopelessly munging my code. You can download unmunged versions here: PHP, Perl.

PHP:

Connecting to ODBC<br>
<?php
$dsn = ‘DSN=MYOB; TYPE=MYOB; ACCESS_TYPE=READ_WRITE; DRIVER_COMPLETION=DRIVER_NOPROMPT; NETWORK_PROTOCOL=NONET; SQL_ATTR_AUTOCOMMIT=0; INSERT_TYPE=UPDATE_DUPLICATES;’;

$id=odbc_connect($dsn,’your username’,’your password’);
odbc_autocommit($id, FALSE);
$result=odbc_tables($id);

$tables=array();
echo “listing all tables:”;
while(odbc_fetch_row($result)) {
if(odbc_result($result,”TABLE_TYPE”)==”TABLE”) {
echo “<br>”.odbc_result($result,”TABLE_NAME”);
}
}

if($id==0) {
echo(“odbc_connect failed”);
}
echo “<br>doing insert”;
$query=”
INSERT  INTO Import_Items
(ItemNumber, ItemName, Buy, Sell, Inventory, AssetAccount, IncomeAccount, ExpenseAccount)
VALUES  (‘538’, ‘php’, ‘Y’, ‘Y’, ‘Y’, ‘10000’, ‘40000’, ‘60000’)”;
$res=odbc_prepare($id, $query);
odbc_execute($res);

$query=”END TRANSACTION”;
$res=odbc_prepare($id, $query);
odbc_execute($res);

odbc_commit($id);

if(odbc_error())
{
echo odbc_errormsg($id);
}
?>
<br>done.

Perl:

use DBI;
use strict;
use Shell;

my $mysql_query;
my $dsn = ‘DSN=MYOB; TYPE=MYOB; ACCESS_TYPE=READ_WRITE; DRIVER_COMPLETION=DRIVER_NOPROMPT; NETWORK_PROTOCOL=NONET; SQL_ATTR_AUTOCOMMIT=0; INSERT_TYPE=UPDATE_DUPLICATES;IDENTIFY_BY=RECORD_ID’;
my $dbh = DBI->connect(“dbi:ODBC:$dsn”,’your username’,’your password’,
{RaiseError=>1,AutoCommit=>0})
or die “Couldn’t connect to database: “. DBI->errstr;
my @row;

my $query;

sub err_handler
{
my ($sql_state, $msg, $nativeerr) = @_;

print “===> sql_state: $sql_state\n===> msg: $msg\n===> nativeerr: $nativeerr\n”;
return 1;
}

$dbh->{odbc_err_handler} = \&err_handler;
$dbh->{odbc_exec_direct} = 1;

$query=”INSERT INTO Import_Customer_Cards (CoLastName, FirstName, CardID)
VALUES ((‘ffff’, ‘ffff’ , ‘555555’), (‘VAN BERT’, ‘RUSSELL’ , ‘12345’))”;

my $sth = $dbh->prepare($query)
or die “Unable to prepare query\n”;
$sth->execute
or die “Unable to execute query\n”;

my $query=”END TRANSACTION”;

my $sth = $dbh->prepare($query)
or die “Unable to prepare query\n”;
$sth->execute
or die “Unable to execute query\n”;

$dbh->commit();

$dbh->disconnect;