Uniface User Forum

 View Only
  • 1.  Sql command buffer overflow

    PARTNER
    Posted 06-23-2022 02:29
    Hello everybody,

    maybe it's an informix specific question. Unfortunately I don't know (google search was unfortunately not successful )
    and I'm hoping now somebody of you guys can give me a hint.

    Our database environment is Informix 14.10 with client SDK 4.1

    The users have a form for entering search criteria for a database selection. So far so normal.

    The table to be selected is pretty wide (about 230 columns) and the users are entering a lot of OR-criteria separated by <GOLD><PIPE> into a particular field as a search profile.
    Debugger shows me a pretty large SQL-Statement of about 12000 characters.

    Trying to perform a retrieve now results in a "Informix: Driver ERROR (50): Sql command buffer overflow"

    As I can successfully perform that generated SQL-Statement via informix dbaccess or via Squirrel (jdbc) I'm not sure where my (uniface) problem comes from.

    What's causing the error? A Uniface limitation itselves, or is it caused by client sdk?
    What is the maximum sql command buffer size?

    BTW: It's even possible to get uniface crashing (tested with 9.7 and 10.3.03) if the search profile is getting larger :-(

    Best regards

    ------------------------------
    Michael Rösch
    Abrechnungszentrum Emmendingen
    ------------------------------


  • 2.  RE: Sql command buffer overflow

    ROCKETEER
    Posted 06-23-2022 03:02
    Hello Michael,

    The Sql command buffer of the Informix driver can hold up to 10MB. In case you have to process a search profile that is larger then you need to break the query down into several pieces and use retrieve/a to add more records to the hit list.

    You can easily convert the profile into a list by replacing the <GOLD><PIPE> characters with <GOLD>; (list separators). And then you simply loop through the items and create a new (sub-)list with a search profile that has a maximum of (e.g.) 40 OR-criteria. Then use retrieve/a with each sub-list until all OR-criteria have been processed.

    I hope this helps.

    Best regards,

    ------------------------------
    Daniel Iseli
    Principal Technical Support Engineer
    Uniface Services
    Rocket Software, Switzerland
    ------------------------------



  • 3.  RE: Sql command buffer overflow

    PARTNER
    Posted 06-23-2022 03:31
    Hello Daniel,

    thank you very much for your prompt reply.

    But... My SQL-statement with about 12000 characters is much less than 10 MB and nevertheless the "Sql command buffer overflow" occurs.

    The retrieve/a is an interesting option (I've never used this before). My solution was, to build an native SQL IN-clause and combined this condition using "where" in the read trigger.

    Best regards

    ------------------------------
    Michael Rösch
    Abrechnungszentrum Emmendingen
    ------------------------------



  • 4.  RE: Sql command buffer overflow

    ROCKETEER
    Posted 06-23-2022 03:38
    Sorry, made a typo: it should say 10KB (10240).

    ------------------------------
    Daniel Iseli
    Principal Technical Support Engineer
    Uniface Services
    Rocket Software, Switzerland
    ------------------------------



  • 5.  RE: Sql command buffer overflow

    PARTNER
    Posted 06-23-2022 05:28
    Hello Daniel,

    okay... this would clarify the matter.

    Thank you very much and best regards

    ------------------------------
    Michael Rösch
    Abrechnungszentrum Emmendingen
    ------------------------------