CODEWITHSUNDEEP
sqldatabasesyntaxinformix
user3525444
user3525444

Reputation: 65

Using placeholders in Informix SQL queries

How I can use the placeholders within dynamic SQL string in case when I need to assign one of input parameters twice? Following code doesn't work.

CREATE FUNCTION somefunc( p_name VARCHAR( 32 ), p_id INT, p_weight INT ) RETURNING INT;

    LET sp_id = 0;
    LET somearea=12;


    LET c_query ='SELECT FIRST 1 pr_id FROM sometable n WHERE n.pr_id> ? and n.pr_weight = ? ( UPPER( n.sname ) LIKE %UPPER(?))'||
    ' OR( UPPER(?) LIKE %UPPER( n.name)) ORDER BY 1, 2;';

    PREPARE c_stmt
    FROM c_query;

    DECLARE c_cur CURSOR FOR c_stmt;

    OPEN c_cur USING p_id, p_weight, p_name, p_name ;

    FETCH c_cur INTO sp_id;

    CLOSE c_cur;

    FREE c_cur;

    FREE c_stmt;

    RETURN sp_id;

END FUNCTION;

Upvotes: 1

Views: 829

Answers (1)

jsagrera
jsagrera

Reputation: 2013

Umm... not quite sure to fully understand the question (or the purpose of that SQL, hope it's us a test as it does in deed look weird)

Anyway, this is your SPL with the correct syntax:

D:\infx\ids12>cat 1.sql
DROP TABLE sometable;
CREATE TABLE sometable  (name varchar(32), sname varchar(32), pr_id int, pr_weight int);

INSERT INTO sometable VALUES ('test','test',1,100);
INSERT INTO sometable VALUES ('tESt','tESt',2,200);
INSERT INTO sometable VALUES ('another','another',3,300);

DROP FUNCTION somefunc;
CREATE FUNCTION somefunc( p_name VARCHAR( 32 ), p_id INT, p_weight INT ) RETURNING INT;
    DEFINE sp_id int;
        DEFINE c_query varchar(200);

    LET sp_id = 0;
        LET c_query ="SELECT FIRST 1 pr_id FROM sometable n WHERE n.pr_id> ? and n.pr_weight = ? and ( UPPER( n.sname ) LIKE UPPER('%'||?||'%')) OR( UPPER('%'||?||'%') LIKE UPPER( n.name)) ORDER BY 1";

    PREPARE c_stmt
    FROM c_query;
    DECLARE c_cur CURSOR FOR c_stmt;
    OPEN c_cur USING p_id, p_weight, p_name, p_name ;
    FETCH c_cur INTO sp_id;
    CLOSE c_cur;

    FREE c_cur;
    FREE c_stmt;
    RETURN sp_id;

END FUNCTION;

--SELECT FIRST 1 * FROM sometable n WHERE n.pr_id> 1 and n.pr_weight = 200 and ( UPPER( n.sname ) LIKE UPPER('%'||'TEST'||'%')) OR ( UPPER('%'||'TEST'||'%') LIKE UPPER( n.name)) ORDER BY 1;

EXECUTE FUNCTION somefunc('TEST',1,200);

D:\infx\ids12>dbaccess stores7 1.sql
Database selected.
Table dropped.
Table created.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
Routine dropped.
Routine created.
(expression)
           2
1 row(s) retrieved.
Database closed.
D:\infx\ids12>

I think that what Jonathan was referring to when talking about dynamic SQL is that you don't need to build or prepare a SELECT statement using place holders (?) inside a SPL. You can execute mostly 'any' SQL statement. The SPL above could be rewrite like:

D:\infx\ids12>cat 2.sql
DROP FUNCTION somefunc_simple;

CREATE FUNCTION somefunc_simple( p_name VARCHAR( 32 ), p_id INT, p_weight INT )
RETURNING INT;
        DEFINE sp_id int;

        LET sp_id=(SELECT FIRST 1 pr_id  FROM sometable n WHERE n.pr_id> p_id and n.pr_weight = p_weight and ( UPPER( n.sname ) LIKE UPPER('%'||p_name||'%')) OR ( UPPER('%'||p_name||'%') LIKE UPPER( n.name)));

        RETURN sp_id;
END FUNCTION;

EXECUTE FUNCTION somefunc_simple('TEST',1,200);

D:\infx\ids12>dbaccess stores7 2.sql
Database selected.
Routine dropped.
Routine created.
(expression)

           2
1 row(s) retrieved.
Database closed.
D:\infx\ids12>

Have a read at the Informix SQL guide at

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/sqls.htm

It should give you an idea of how to write Informix SQL and SPL and what functionality is supported.

Also, Dynamic SQL is explained here:

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.esqlc.doc/ids_esqlc_0528.htm

Upvotes: 3

Related Questions