HF SQL Doesn’t support complex Update Statements

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.

2014-03-31_1004

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 

2014-03-31_1009

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

2014-03-31_1019

Press the F9 Unit Testing Button

2014-03-31_1022

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….

One thought on “HF SQL Doesn’t support complex Update Statements”

  1. Jérôme AERTS with pcSoft contacted me via Skype and we had a very good conversation about my concerns with HF SQL. Here is a direct quote from him

    “We are working on HFSQL to be more and more efficient, powerful, and also simple to administrate. Historically, access to databases in WINDEV was with orders hFilter, HReadSeek, …, which is very easy and simple.

    With each version, we improve the support of SQL. This possibility will certainly be included for version 20”

    During our conversation he assured me that support for standard SQL statements as well as optimizer performance with ANY statements are being looked at with version 20. This type of feedback is why I know I have chosen the correct development tool to base my business on, its always nice to see they are listening!

    Like

    Reply

Leave a comment