SQLSET data type in descriptor

Question

How to insert SQLSET type in descriptor?

First I prepare and declare cursor with bind variables:

select * from account where aco_no in ( ? ) and ..;

My goal is to replace '?' in open cursor with list of account ('123', '234'). Cursor for statement is prepared once (server start), and then is opened several times (service start) with different data (size of account list). My goal is to prepare cursor with IN() operator and pass account list as bind variable.

Open is by statement:

$OPEN :cur USING SQL DESCRIPTOR :descWhere;

Where descWhere is a descriptor created by service (set descriptor statements).

It's work well if i'm using simple types (SQLCHAR itp.) Exp:

$SET DESCRIPTOR :desc VALUE :size
            TYPE = :type,
            LENGTH = :NO_LEN;
$SET DESCRIPTOR :desc VALUE :size
                DATA = :szBuffor;

How to insert into descriptor types SQLSET, SQLLIST, .. and if it can be done is it work witch IN() operator?

The first thing I'd do would be check whether you can use a literal SET inside the parentheses of an `IN ( … )` clause and then get it to work. If that works, then it is worth spending time on working with the descriptor approach. If the SET literal does not work, there's no point in spending time on the job. — Jonathan Leffler, Jan 20 '15 at 05:05

Maciej Piaseczny · Answer 1 · 2016-06-30T11:36:19.507

In my opinion using descriptor in your situation is a mistake. Descriptor is a mechanism that should be used when you do not know your statement when you write your code, e.g.: when you do not know what type of data will you select.

In your case you should just declare, open and execute your statement in runtime as normal dynamic SQL statement. Usually declaring cursor does not give you that much performance benefit (compared to cost of select). If you need more performance for select i would suggest checking array fetch instead of using cursor.

Anyway, if you prefer to use cursor, remember to CLOSE cursor and FREE described statement to not exceed available memory.

Check this article too for performance tuning.

SQLSET data type in descriptor

1 Answers1