Open Source

Quick Tip – PostgreSQL Equivalent of ISNUMERIC()

Very much like my previous MySQL ISNUMERIC() post I have recently been setting up a data source to collect records with telephone numbers from a Postgres database and one of the essential validation tests is to make sure that the field really does contain a number.

Despite the fact that many regard Postgres as the best open source database platform I find myself frustrated by it’s lack of standard functions.  I understand that Postgres is designed to be extensible and that user defined functions can be built but I need my code to be both portable and read-only so I have to work with what I’m given.  Ideally what I’d be looking for is an equivalent of Microsoft SQL Server’s ISNUMERIC() or Excel’s ISNUMBER() functions but very much like MySQL I had to turn to regular expressions although as you’ll see, Postgres does not have a clean and clear REGEXP() function…

SELECT DISTINCT contact_number
FROM customers
WHERE (contact_number ~ ‘^[0-9]+$’)

I hope that helps any of you out there that encounter the same problem, thanks to the poster here for my original answer.

Using MySQL BLOB Data via ODBC in SSIS, SQL Server & Business Objects

Whilst trying to build a centralised cross-platform alerting system I spotted a peculiar issue when trying to move the output of a SHOW FULL PROCESSLIST command on MySQL via ODBC.  It seems that the output of the SHOW FULL PROCESSLIST command returns both integers and binary (BLOB) data types even though to they eye (that is, in the MySQL Query Browser) most of the columns appear to be short text fields.

Despite the fact that the data looks like text whenever I tried to return the data into an application, I tried SSIS, SQL Server Linked Servers and Business Objects, each time the data would come back unusable or an error would be returned.  Business Objects gave me the key by declaring “This is a BLOB.” as you can see in the following screenshots…

SQL Server Integration Services

SQL Server Linked Server

Business Objects Desktop Intelligence

As with my recent post about loading data into MySQL with SSIS the saviour turns out to be an ODBC configuration setting, this time in the Metadata tab of the MySQL ODBC driver.  All you have to do is check the “Always handle binary function results as character data” and instantly your problems will be solved…

Out of a crazy fit of completeness I also took screenshots of the final results and it’d be a shame to waste them so here they are…

SQL Server Integration Services

SQL Server Linked Server

Business Objects Desktop Intelligence

Loading Data into MySQL with SSIS via ODBC (sql_mode = ANSI)

I was performing a test earlier, a proof-of-concept to show that we could use Integration Services to move data from one platform (PostgreSQL) to another (MySQL) without SQL Server itself being involved at all. 

Unfortunately I hit a slight snag on the first simple attempt, I could read the data without a hitch but despite multiple attempts at tweaking SSIS settings and reconfiguring connections I could not manage to get data into the MySQL target table.  Amongst other messages in the Output window in BIDS I could see the following error…

[MySQL][ODBC 5.1 Driver][mysqld-5.0.45-log]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 ‘”tblDestination” (“col1″, “col2″, “col3″, “col4″, “col5″, “c’ at line 1

After some Googling I determined that the issue was down to an incompatibility between the SQL being generated by SSIS and the default MySQL engine, specifically that MySQL was not setup to accept ANSI compliant SQL.  Most of the solutions out there seemed to require that the entire server was configured in the ANSI compliant mode which was a problem for me as it’s a shared server and I can’t go reconfiguring it at will.  Thankfully MySQL provides the ability to set the mode on a per connection level with the following command…

SET SESSION sql_mode= 'ansi';

The trick is – how to get SSIS to run this before inserting the data without messing with the package itself?  The easiest way is to have the ODBC driver send the command at the point SSIS opens the connection, this can be configured in the settings of the ODBC source…

This is a relatively common feature of ODBC drivers and allows you to setup initial configuration options for this exact reason, now I know that I will not impact any other users of the server and my SSIS package works just fine.

Using SQL Server 2008 R2 Linked Servers with PostgreSQL 64-bit

Having setup a Linked Server in Management Studio talking to a PostgreSQL 8 database I encountered the following error when attempting to run any valid query:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "PG_SERVER" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "PG_SERVER".

After some digging I came across a handy article on Microsoft Connect describing the same issue, with thanks to Nenea Nelu here’s the solution…

• Expand Server Objects > Linked Servers > Providers. 
  
• Right-click on MSDASQL and select Properties…
• In the Properties dialogue un-check “Allow inprocess” as follows…
  
• Click OK and re-run your query. 

Hopefully that should solve your problem, please note that this will affect all Linked Servers using that provider however as the Connect article points out – this is best practice for linked servers anyway.

Veggie Phrases App Available on Android

Just a short post to say that Veggie Phrases is now available on the Android Marketplace, you can read the details on AppBrain - if you’ve not seen the app before it’s a handy pocket phrasebook to help travelling vegetarians and vegans communicate their dining preferences when overseas and best of all it’s completely free!

I’ll follow up when I get the chance with a brief description of the process I followed but for now, please grab your phone and give it a try. Let me know if you have an issues, I only had a chance to test it briefly since I’m an iPhone user!

Download Veggie Phrases for Android

Interesting Open Source BI Blog

I’ve been interested in the idea of Open Source BI for quite a while and for certain organisations or requirement sets I believe that Open Source BI has a very bright future.  Open Source offerings may never oust the ‘big boys’ like Informatica and Oracle Data Integrator but they will become valid competitors in time, though vendors offering paid  ‘Enterprise’ editions need to be careful that their high-end offerings aren’t priced too high since people spending large sums are bound to tend towards proven solutions from stable companies. 

Anyway, to get to the point – today I stumbled across an intersting blog by Slawomir Chodnicki covering Open Source BI and ETL products from Pentaho, JasperSoft and SQL Power Group.  Much of the content is of the ‘how to’ variety but if you’re interested in the topic as a whole it’s worth stopping by Adventures With Open Source BI.