Wednesday, March 31, 2010

Everything is searchable

Interesting things happening while I wasn't watching. Not only the previously mentioned, or the newspapers moving in the same direction, but also ...

I'm really curious what effect will this have on databases. In theory, with the right authentication in place, everything could be exposed online and EDI would be vastly simplified, from sneakernet to HL7, everything would be replaced by REST calls.

At any rate, Freebase's attempt to organize everything is ambitious/stunning.

Wikipedia's own API, here.

Related: Talis.

US Government's open data initiative. Interesting, have to find out more about what's there. The potential for mashups and visualizations is great... if the data is trusted and current.
Here is the equivalent UK site.
I need to look into this some more, for now a lot of the data seems to be Excel files that can be downloaded. No universal REST/JSON access?

Sunday, March 21, 2010

Interface engines

Unbeknownst to myself, in a previous job I wrote an interface engine. It was a (initially) simple VB application which was reading files from a directory and copying them to another directory, then deleting them from the source. Then more source directories were added. Then, file endings had to determine the destination. Then, file contents (i.e., header or template-based triggers) had to determine which files were copied and which not. Finally, database tables became source/destinations. The thing had grown immensely since the beginning and it was running 24/7, with a 30 second polling interval, and a large number of customers were relying in fact on a rickety PC running a hopelessly spaghetti-coded application with manually-installed DLLs. If only they knew.

I did not know at the time that this was an interface engine. Somewhat restricted to healthcare and financial IT, one wonders why this concept hasn't taken off in mainstream computing more significantly. I.e., in databases, interfaces could provide a strong and pervasive (cross-platform, even) replication/synchronization mechanism.

Or perhaps the concept has been folded into workflow engines and transaction-based orchestration systems; Biztalk or Oracle Fusion can presumably do what interface engines do, and much more – but at a hefty footprint.

Mirthcorp's Java-based interface engine, below, running against a Cache database over JDBC/ODBC.

Message passing:

Channel definition:

A web-based interface engine? Perhaps using technology similar to YQL. Monitoring a queue of some kind and updating your Facebook status? Have to think about this one.

Saturday, March 20, 2010

Cache dates

Cache's multiple interfaces (RDMBS, OO) can cause confusion even for simple operations, such as inserting dates. Here is how that is done:


insert into CM.StrmClass(PatientAddress, PatientName, PatientDateOfBirth)

values('5 Soi Rangnam Bangkok, Thailand', 'TG', {d '1990-03-17'})

Rather ugly ODBC formatting for the date field. In Object Script:

Set p.PatientDateOfBirth = $ZDateh("12/21/1977")

By the way, if a %Save fails (does not make the results commit to the database), then there is a problem with the data validation. Running the container from the Terminal should show the result (e.g. W p.%Save())

Another interesting feature of Cache, which accounts for its performance: 'Using a unique feature known as “subscript mapping,” you can specify how the data within one or more arrays is mapped to a physical database file. Such mapping is a database administration task and requires no change to class/table definitions or application logic. Moreover, mapping can be done within a specific sparse array; you can map one range of values to one physical location while mapping another to another file, disk drive, or even to another database server. This makes it possible to reconfigure Caché applications (such as for scaling) with little effort.'

Monday, March 15, 2010

New media uselessness

I'm not a new media expert but even I know that…

  • Calling an ad a 'welcome screen' is a pointless trickery. I understand you need to pay for content somehow, no need to sugarcoat it
  • Sometimes, more media is bad: if there is an article that lists the top 10 cities to live, 14 most troubled real estate markets, 10 top moments in British TV history, etc, well, I take that as information. I can just read it, I don't need photos to go with it, or if there are photos, they should be optional. Making me scroll through 10 or 14 screens, each heavy on images but low on info, that will result in me simply leaving an otherwise potentially interesting article

So maybe the death of print is largely exaggerated.

Updating Cache from Excel

Excel (2007) is great as a quick database front end tool – reasonably versatile and easy to set up. This ease of use might make many want to be able to update a database from Excel, and that is not so easy.

It has to be done in code. One way would be through linking the Excel file to the database server (there are different mechanisms for doing this, e.g. OPENROWSET in SQL Server or the SQL Gateway in Cache). However, if you're running Office in 64-bit mode you soon discover that there is no 64-bit compliant Excel ODBC driver.

A quick and more natural solution is to embed the database update code in Excel itself; in the case of a Cache backend, there are two ways to write this code:

  • Using ADO/ODBC

    Dim cm As Command

    Dim cn As Connection

    Dim cns As String

    cns = "DSN=CacheWeb User"

    Set cn = New Connection

    cn.Open cns

    Set cm = New Command

    cm.ActiveConnection = cn

    cm.CommandType = adCmdText

    cm.CommandText = "INSERT INTO Income(UserId, Income, Country) VALUES('@u', @i, '@c')"


  • Using Cache ActiveX

A bit more interesting since it gets into some Cache internals:

Dim Factory As CacheObject.Factory

Set Factory = New CacheObject.Factory

If Factory.Connect("cn_iptcp:[1972]:TESTDBNS") = False Then

MsgBox "Failed to connect"

Exit Function

End If

Dim Income As Object

Set Income = Factory.New("Income")

Income.UserId = "2"

Income.Income = 120 = "PAK"



Set Income = Nothing

Saturday, March 13, 2010


It seems there is a government-run GoogleHealth-like service in the UK.

I am still miffed by the resistance people show to this type of project. After all, they bank online. Why not keep track of their health info online?