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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
<\/p>\n
NOTE: WordPress is hopelessly munging my code. You can download unmunged versions here: PHP<\/a>, Perl<\/a>.<\/p>\n PHP:<\/strong><\/p>\n Connecting to ODBC<br> $id=odbc_connect($dsn,’your username’,’your password’); $tables=array(); if($id==0) { $query=”END TRANSACTION”; odbc_commit($id);<\/p>\n if(odbc_error()) Perl:<\/strong><\/p>\n use DBI; my $mysql_query; my $query;<\/p>\n sub err_handler print “===> sql_state: $sql_state\\n===> msg: $msg\\n===> nativeerr: $nativeerr\\n”; $dbh->{odbc_err_handler} = \\&err_handler; $query=”INSERT INTO Import_Customer_Cards (CoLastName, FirstName, CardID) my $sth = $dbh->prepare($query) my $query=”END TRANSACTION”;<\/p>\n my $sth = $dbh->prepare($query) $dbh->commit();<\/p>\n $dbh->disconnect;<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":" 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 … Continue reading
\n<?php
\n$dsn = ‘DSN=MYOB; TYPE=MYOB; ACCESS_TYPE=READ_WRITE; DRIVER_COMPLETION=DRIVER_NOPROMPT; NETWORK_PROTOCOL=NONET; SQL_ATTR_AUTOCOMMIT=0; INSERT_TYPE=UPDATE_DUPLICATES;’;<\/p>\n
\nodbc_autocommit($id, FALSE);
\n$result=odbc_tables($id);<\/p>\n
\necho “listing all tables:”;
\nwhile(odbc_fetch_row($result)) {
\nif(odbc_result($result,”TABLE_TYPE”)==”TABLE”) {
\necho “<br>”.odbc_result($result,”TABLE_NAME”);
\n}
\n}<\/p>\n
\necho(“odbc_connect failed”);
\n}
\necho “<br>doing insert”;
\n$query=”
\nINSERT\u00c2\u00a0 INTO Import_Items
\n(ItemNumber, ItemName, Buy, Sell, Inventory, AssetAccount, IncomeAccount, ExpenseAccount)
\nVALUES\u00c2\u00a0 (‘538’, ‘php’, ‘Y’, ‘Y’, ‘Y’, ‘10000’, ‘40000’, ‘60000’)”;
\n$res=odbc_prepare($id, $query);
\nodbc_execute($res);<\/p>\n
\n$res=odbc_prepare($id, $query);
\nodbc_execute($res);<\/p>\n
\n{
\necho odbc_errormsg($id);
\n}
\n?>
\n<br>done.<\/p><\/blockquote>\n
\nuse strict;
\nuse Shell;<\/p>\n
\nmy $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’;
\nmy $dbh = DBI->connect(“dbi:ODBC:$dsn”,’your username’,’your password’,
\n{RaiseError=>1,AutoCommit=>0})
\nor die “Couldn’t connect to database: “. DBI->errstr;
\nmy @row;<\/p>\n
\n{
\nmy ($sql_state, $msg, $nativeerr) = @_;<\/p>\n
\nreturn 1;
\n}<\/p>\n
\n$dbh->{odbc_exec_direct} = 1;<\/p>\n
\nVALUES ((‘ffff’, ‘ffff’ , ‘555555’), (‘VAN BERT’, ‘RUSSELL’ , ‘12345’))”;<\/p>\n
\nor die “Unable to prepare query\\n”;
\n$sth->execute
\nor die “Unable to execute query\\n”;<\/p>\n
\nor die “Unable to prepare query\\n”;
\n$sth->execute
\nor die “Unable to execute query\\n”;<\/p>\n