Where’s the API reference, WooCommerce?

I manage a couple of WordPress sites for clients, and some include the popular WooCommerce plugin, which transforms your WordPress installation into a shop. It’s great, allows you to configure PayPal easily, and has tons of options. I would recommend it to any shop owner looking into opening their own shop online.

A client called me today, and asked if she should upgrade to WooCommerce 2.2. I was quite surprised to see the red warning message on the “Plugins” screen – I would imagine that by now, they won’t introduce any API-breaking changes.

WooCommerce 2.2

Going through the changelog, something caught my eye – new REST APIs! I’ve been working on a small system that scans a CSV file, and creates a product in WooCommerce from each line. Having zero documentation to work with so far on how to programmatically add products to WooCommerce was a pain in the ass, and seeing that they’ve now decided on building a REST API just for that, I was excited. Documentation at last.

Or so I thought.

APIs!

I hit the nearest popular search engine in a quest to find the new APIs documentation. 1 followed by 100 zeros sent me off to a page titled “WooCommerce REST API“. Sounds promising, but that’s just some info on how to enable the API.

That page sent me off to GitHub for some more disappointment – I was looking at the documentation for version v1 of the REST API.

Lastly, having being sent again somewhere else, I arrived at my destination – “WooCommerce v2 REST API“. Finally, the reading time I had allocated for myself could be well spent. I read and read (and skipped some boring chapters). But where was the REST API reference I had searched for?

I took me a while to realize – it wasn’t there. Nor was it anywhere to be found. Apparently, the guys at WooCommerce built a REST API – so you could start reading the code and figuring out what to do. They had uploaded basic documentation about it, and a reference for Coupons and Webhooks, but nothing about Customers, Orders, or my beloved Products.

You’d expect from someone building a brand new API to document it, and do it thoroughly. Moreover, you expect it to be online when the API is ready to go.  This is especially true for a popular system (or plugin in this case).

Expectations are rarely met.

RailTicketMap updated to version 0.8

RailTicketMap has been updated to version 0.8.

It now supports Netherlands as an end destination. This is possible since Bahn.de an SBB allow you to purchase tickets from them for trains going from Germany and Switzerland (respectively) to Netherlands.

I’ll write a short summary about RailTicketMap soon, detailing how I got the idea, how I managed to link directly to rail companies system, and why I support only 4 countries.

Executing multiple queries on MySQL using JDBC on Google Apps Script

Working with Google Apps Script, I’d been given a mission – create reports from our database. Having never used JDBC, or Java for that matter (except an app I’ve written for Android last year, but never published), I went along and researched.

Why Java, you might be asking. Isn’t Google Apps Script purely Javascript?

Apparently, it isn’t. My first hunch was Google using GWT to build Apps Script. I quickly understood that’s not what GWT is for – it allows you to build web applications with Java, not the other way around. The other viable option I could think of, is an opposite of GWT – that is, you code in Javascript (or Apps Script), which gets translated to Java. That makes sense, as Java libraries are available to your disposable on Apps Script.

The one I was using was JDBC, “an API for the Java programming language that defines how a client may access a database” as Wikipedia kindly states. It’s basically a class that allows you to connect to a database of your choice.

Well, not every database. Google’s JDBC is a limited version. You can’t use a third-party driver like PostgreSQL JDBC Driver. MongoDB? Not a chance.

But the worst I’ve come across is the internal JDBC limitations. About a month ago, I posted on Stackoverflow – “Dynamic SQL returns ‘syntax error’ on Google Apps Script using JDBC and MySQL”. I built a complex SQL query which included Dynamic SQL, VARs and multiple queries. Here’s the code:

 

function selectAllUserSurveys() {
  var query ="SET@sql =NULL;\
SELECT GROUP_CONCAT(DISTINCT\
CONCAT('MAX(CASE WHEN survey_field_values.survey_field_option_id = ', survey_field_option_id,\' THEN survey_field_values.value END) AS ', CONCAT('`survey_field_option_id', survey_field_option_id,'`'))\)INTO@sql \
FROM survey_field_values \
LEFTJOIN surveys \
ON survey_field_values.survey_id = surveys.id \
WHERE surveys.survey_type ='client';\
SET@sql = CONCAT('SELECT surveys.bid_id, user.rep_name, 0, ',@sql,'\
  FROM surveys \
  INNERJOINuser\
  ON surveys.user_id =user.username \
  LEFTJOIN survey_field_values \
    ON surveys.id = survey_field_values.survey_id \
  WHERE surveys.survey_type =\'client\'\
  AND surveys.is_filled =1\
  GROUPBY surveys.id \
  ORDERBY surveys.date_filled ASC');\
PREPARE stmt FROM@sql;\
EXECUTE stmt;\
DEALLOCATE PREPARE stmt;";
  return executeSelectMultipleRowsQuery(query);
}

function executeSelectMultipleRowsQuery(query) {
  var conn = Jdbc.getConnection(dbUrl,user, userPwd);
  var start= new Date();
  var stmt = conn.createStatement();
  var results = stmt.executeQuery(query);
  var numCols = results.getMetaData().getColumnCount();
  return results;
  results.close();
  stmt.close();
}

 

Having run that using JDBC on Apps Script, I got the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN survey_field_values.survey_fi' at line 1

Marc B was kind enough to bring his expertise in, and pointed me into the right direction – running multiple queries was were I was falling. Apps Script’s implementation of JDBC doesn’t allow you to execute multiple queries in a single execute call. Trying to pass the allowMultiQueries parameter to the JDBC connection string would return the following error:

The following connection properties are unsupported: allowMultiQueries. (line X, file "foo", project "bar")

All was not lost though. Marc suggested executing each query separately with it’s own execute call. My final code would look like so:

function DBHelper() {
  this.conn = Jdbc.getConnection(dbUrl,user, userPwd);
  this.stmt = this.conn.createStatement();
}

DBHelper.prototype.executeSelectMultipleRowsQuery =function(query) {
  var results = this.stmt.executeQuery(query);
  var numCols = results.getMetaData().getColumnCount();
  return results;
}

function selectAllUserSurveys(){
  dbHelper = new DBHelper();
  var setsql ="SET @sql = NULL";
  dbHelper.executeSelectMultipleRowsQuery(setsql);
  var dynamicColumns ="SELECT GROUP_CONCAT(DISTINCT\
CONCAT('MAX(CASE WHEN survey_field_values.survey_field_option_id = ', survey_field_option_id,\' THEN survey_field_values.value END) AS ', CONCAT('`survey_field_option_id', survey_field_option_id,'`'))\)INTO@sql \
FROM survey_field_values \
LEFTJOIN surveys \
ON survey_field_values.survey_id = surveys.id \
WHERE surveys.survey_type ='client'";
  Logger.log(dbHelper.executeSelectMultipleRowsQuery(dynamicColumns));

  var mainquery = "SET@sql = CONCAT('SELECT surveys.bid_id, user.rep_name, 0, ',@sql,'\
                   FROM surveys \
                   INNERJOIN user\
                     ON surveys.user_id =user.username \
                   LEFTJOIN survey_field_values \
                     ON surveys.id = survey_field_values.survey_id \
                   WHERE surveys.survey_type =\\'client\\'\
                   AND surveys.is_filled =1\
                   GROUPBY surveys.id \
                   ORDERBY surveys.date_filled ASC')";
  dbHelper.executeSelectMultipleRowsQuery(mainquery);
  var prepare = "PREPARE stmt FROM@sql";
  dbHelper.executeSelectMultipleRowsQuery(prepare);
  var execute = "EXECUTE stmt";
  var return_value = dbHelper.executeSelectMultipleRowsQuery(execute);
  var deallocate = "DEALLOCATE PREPARE stmt;";
  dbHelper.executeSelectMultipleRowsQuery(deallocate);
  Logger.log(return_value);
  return return_Value;
}

VARs are saved per connection, rather than per execution, which made them available in the following execution calls after creating them, and allowed me to use them in the following calls.

I’m not sure why Google would disallow allowMultiQueries in their implementation of JDBC. Ring me up if you know. If not, use the solution above.

“Javascript? I’m used to VBA”

I’ve used Google Drive and related Apps lightly before joining SmartBus. The founder, Yishai, is a much more avid user. Drive is our goto for daily work - documentation, task handling, shared folder for graphics, and as of lately – reports using Spreadsheets.

Koby, the reports guy, was full of surprise when I told him about Google Apps Script. “Javascript? I’m used to VBA. You need to teach me how to work with that”. I can imagine it’s hard to get used to new technologies if that’s not your field. Javascript is a piece of cake though. There are so many tutorials out there, easy and hard.

However, he was looking for something else. a VBA to JS guide crash course that’ll set him on the right path. Just like “From VBA to Google Apps Script” offers. It’s the perfect walkthrough for anyone coming from VBA, who needs to learn Google Apps Script in and out. Bruce Mcpherson‘s wonderful site is the best informative site on the subject, and it’s by the far the best external resource for a commercial product I’ve come upon.

Unfortunately for Koby, he was drafted for the reserves. I’d taken over creating some of the reports for him, which was a bit tricker than I thought it would be, especially using JDBC. More on that on my next post.

Looking back

Having just re-started writing again, I’ve had a look back at old writings of mine. They all look bad. The other post on here looks like a five year old wrote it.

Heck, even the line above discomforts me , and it’s only 2 minutes old.

Realizing every part in what you do can be improved is only a minor comfort to yourself. Rewriting is no good – I’ll want to rewrite that again.

However, I must carry on, and constantly get better at this. There’s no other way.