A site I made for retrieving multiple records

Author: 1oliverwright@gmail.com (linebeak)

I was using a Uniface-based system called SITS a few years back and, due to needing one myself, made a website to turn lists of values into a long string separated by the Uniface 'or' wildcard (also known as the 'Gold |', I believe): http://linebeak.com/ So, essentially if somebody's working off a spreadsheet, it makes it easy to retrieve a large amount of records in one go by pasting it into the site and clicking the 'SITS or Wildcards' button. It gets a few hundred hits a day from universities around the world and it occurred to me that it might be of use to people using other things made in Uniface too. If so, I hope somebody finds it useful!

8 Comments

  1. Indeed a handy tool to create long retrieve profiles in Uniface, for instance: form1 form2 form3 gets converted to form1·|form2·|form3 after clicking the ‘SITS or Wildcards’ button. Thanks for sharing!


    Author: Arjen van Vliet (arjen.van.vliet@uniface.com)
  2. ... but have in mind that the generated SQL code doesn't exceed the documented limit. This will result in an "invalid SQL" error; which some found pretty hard to interpret. An alternative would be a loop over the generated list to start for each item a retrieve/a


    Author: ulrich-merkel (ulrichmerkel@web.de)
  3. Thanks for checking it out, funnily enough at the time I wrote it I didn't realise it would be of use for any other systems except SITS as I wasn't (and, really, am still not) very familiar with Uniface. Ulrich-merkel, may I ask what a 'documented limit' is? I noticed that SITS (a Uniface-based system) won't accept more than 400 items in a retrieval string, is that it? Editing to add: There's also another button which might be of use, the 'SITS Exclusion' one. Essentially, it will take: form1 form2 form3 And produce the following for retrieving all except the items in the list: ·!form1·&·!form2·&·!form3


    Author: linebeak (1oliverwright@gmail.com)
  4. linebeak said Ulrich-merkel, may I ask what a 'documented limit' is? I noticed that SITS (a Uniface-based system) won't accept more than 400 items in a retrieval string, is that it?

    I'm pretty sure that's what Uli is referring to. The limit is not so much about the number of items, but more an absolute field length.


    Author: Arjen van Vliet (arjen.van.vliet@uniface.com)
  5. To add a little background (IIRC): uniface composes the SQL command as s string which will become "the limiting factor". long fieldnames and/or long profiles will "eat up" more space than short ones will do. AFAIK, there is no way to check the length of the generated string out of the uniface 4GL; so it's more a "rule of thumb" to check the length of the list and check "not more than 300 entries";


    Author: ulrich-merkel (ulrichmerkel@web.de)
  6. Thanks for letting me know, sounds like I should add a character counter under the output box then!


    Author: linebeak (1oliverwright@gmail.com)
  7. Hello, some time ago, we faced a strange limit on retrieve profile (using gold |). It was surprisingly small and not fixed (I think it was somewhere around 90 profile characters or so) and (if I remember correctly) Uniface threw no error. After reporting this to a technical support, this was changed and documented. The limit was raised to 400 "gold" characters as for Oracle DB. Since we use (almost) only Oracle, we don't care (much) about other databases. :-) Now, it's clearly documented in the Uniface library: "Retrieve profiles can contain a maximum number of 400 operators. If a where clause exceeds this limit, error -88 Invalid WHERE clause expression requested is raised." After this change, we're quite happy... 400 is enough for most situations, it's documented and if exceeded, the error is thrown. It might be a reason why the mentioned site except "only" 400 items...  Just my 2 cents... Zdeněk


    Author: sochaz (zdenek.socha@fullsys.cz)
  8. sochaz said Now, it's clearly documented in the Uniface library: "Retrieve profiles can contain a maximum number of 400 operators. If a where clause exceeds this limit, error -88 Invalid WHERE clause expression requested is raised."

    Intriguing! After some trial and error in SITS many moons back it did seem to always be exactly 400, so this must be why. I put a note up on the site to mention it as I was getting a lot of emails from people trying huge retrievals. Like you say, 400 is generally enough.


    Author: linebeak (1oliverwright@gmail.com)