As many of you know I have been slowly become more of a convert to HyperFileSQL and WX Queries. And generally use HF for my projects while they are in the R&D stage, only moving them to PostgreSQL if they are going to involve very large/complex data sets. Or if the client requires some other backend (MS-SQL, Oracle, etc).
But every time I start feeling the HF love, something comes along that puts our relationship back on ice. Today its the lack of support for complex Update Statements.
I am doing some data clean up DBA type work on a HF database today using the WDSQL tool, as a DBA I often work directly with SQL. So I typed in a fairly simple statement like I have been doing since the 1980’s
UPDATE survey SET attribute1name = (SELECT attribute1name FROM participantgroup WHERE participantgroupid = survey.participantgroupid)
And I get treated to a very rude error.
So in desperation I break out a new style UPDATE statement with an INNER JOIN. I rarely use these as I always find them harder to write for some reason.
UPDATE survey INNER JOIN participantgroup ON participantgroup.participantgroupid = survey.participantgroupid SET survey.attribute1name = participantgroup.attribute1name
The results of this statement is every scarier. The statement reported success, but didn’t actually update anything!
With the help of Ben Riebens, I found this little ditty in the online help
Which says that neither of those formats is allowed. Now for the Rant…
Come on pcSoft get your act together, if you want HF SQL to be accepted as a true backend SQL database solution, that can be recommended to corporate america, you need to at least support standard SQL statements that every other SQL backend has been supporting for 30 years!!!! Until support for standard statements like this, and the optimizer performance issues with some ANY statements are addressed, HF SQL will always be a development and small database solution for me.
So now I am left with still needing to clean up the data. And just to give pcSoft some love back so they don’t think I am taking them for granted, I will say this was a whole lot quicker and easier than it would have been in my previous environment. I opened up my project. Created a temporary procedure with the following code
Press the F9 Unit Testing Button
And let it run. No need to go through a compile, etc. so at least it was a “quick” little fix. In this instance that is ok, but if I had millions of records it would have been an issues since this involved lots of client communication, where the SQL statement would have been executed completely on the server by the SQL engine and been far more efficient.
Now, back to your regular schedule program….