Skip to main content

Uniface Development: Using bind variables for SQL called within Uniface via SQL proc

  • November 19, 2018
  • 3 replies
  • 0 views
Jan Cees Boogaard

Using bind variables for SQL called within Uniface via SQL proc

Author: oakleyjd@hotmail.com (James Oakley)

Hiya,

We have a requirement to code directly an SQL select using the SQL proc, however we cannot get this to work when we change the SQL to use bind variables.

We have tried using the 'blockdata' function to structure the SQL, but this does not work and the Oracle driver does not allow the use of semi colons in the SQL statement passed.

Has anyone managed to find a way to do this?

We are trying with trivial SQL at the moment:

variable inst varchar2(10);
exec :inst := 'A20';
select * from ins_ins where ins_code = :inst;

Thanks

James

    3 replies

    Jan Cees Boogaard

    Using bind variables for SQL called within Uniface via SQL proc

    Author: oakleyjd@hotmail.com (James Oakley)

    Hiya,

    We have a requirement to code directly an SQL select using the SQL proc, however we cannot get this to work when we change the SQL to use bind variables.

    We have tried using the 'blockdata' function to structure the SQL, but this does not work and the Oracle driver does not allow the use of semi colons in the SQL statement passed.

    Has anyone managed to find a way to do this?

    We are trying with trivial SQL at the moment:

    variable inst varchar2(10);
    exec :inst := 'A20';
    select * from ins_ins where ins_code = :inst;

    Thanks

    James

    Hi James,

    why not a straight foreward SQL like:

    SQL/PRINT "select * from ins_ins where ins_code = 'A20';"

    or a dynamic:

    parameters
       string inst
    endparameters

    if (inst = "")
       inst = "A20"
    endif

    SQL/PRINT "select * from ins_ins where ins_code = '%%inst%%%';"

    If you really want to use bind variables, try it with a "stored procedure" (see the documentation).

    Success, Uli

    P.S. I have not so much praxis in this area,
    maybe someone creating stored procs day-by-day may have more recommendataions for us.


    Author: ulrich-merkel (ulrichmerkel@web.de)
    Jan Cees Boogaard

    Using bind variables for SQL called within Uniface via SQL proc

    Author: oakleyjd@hotmail.com (James Oakley)

    Hiya,

    We have a requirement to code directly an SQL select using the SQL proc, however we cannot get this to work when we change the SQL to use bind variables.

    We have tried using the 'blockdata' function to structure the SQL, but this does not work and the Oracle driver does not allow the use of semi colons in the SQL statement passed.

    Has anyone managed to find a way to do this?

    We are trying with trivial SQL at the moment:

    variable inst varchar2(10);
    exec :inst := 'A20';
    select * from ins_ins where ins_code = :inst;

    Thanks

    James

    Hi Uli,

    Thanks for response. 
    We're trying to use bind variables because they are much more efficient at the Oracle level.   Oracle will cache the SQL statement and the execution plan in the SGA, if we use bind variables then the statement cache can be reused as it does not contain absolute values, this is why the Uniface Oracle driver uses them too.
     
    Unfortunately we cannot easily use a stored proc either as the SQL statement is made up by a previous query on our own meta-data layer, so we have a simple proc that can be used to return columns from a number of different tables without having to code them all.
     
    Being difficult here I know, but I guess what I’m after is a way of getting the SQL proc to allow multi line SQL statements.
     
    Thanks in advance for any other suggestions.
    James
     

    Author: James Oakley (oakleyjd@hotmail.com)
    Jan Cees Boogaard

    Using bind variables for SQL called within Uniface via SQL proc

    Author: oakleyjd@hotmail.com (James Oakley)

    Hiya,

    We have a requirement to code directly an SQL select using the SQL proc, however we cannot get this to work when we change the SQL to use bind variables.

    We have tried using the 'blockdata' function to structure the SQL, but this does not work and the Oracle driver does not allow the use of semi colons in the SQL statement passed.

    Has anyone managed to find a way to do this?

    We are trying with trivial SQL at the moment:

    variable inst varchar2(10);
    exec :inst := 'A20';
    select * from ins_ins where ins_code = :inst;

    Thanks

    James

    Hi James,

    I agree 100% on all the things you say about caching evaluates statements etc.
    In my PL/1 years, I coded a lot of these "best performance" precompiled SQL statements.

    Unfortunately, the current uniface database drivers do not support multi-commands.

    One option is a wish to CPWR to support multi-commands in SQL/PRINT. But this will take time.

    Alternatives:

    1 -- still using a suite of  "stored procedures" (whenever one is missing, create it)

    2 -- specify your data-collections in VIEWS and access these

    3 -- We create a tiny DLL which connects to your database and handles your SQL statements.

    The bind variables are "owned" by the DLL and we use a signature to pass information from/to Uniface.

    What do you think about this option? (we can discuss details if you mail me on ulrichmerkel@web.de).

    Success, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement