Comment sélectionner une table dont le nom est une variable locale (informix) ?

Question

Je veux faire une sélection dans un tableau où le le nom de la table est une variable locale y le nom de la colonne est une autre variable locale mais j'obtiens l'erreur suivante :

Le tableau spécifié ll_tb_name n'est pas dans la base de données

CREATE PROCEDURE rmg_request_file ( al_task_code integer)
RETURNING integer as byte;

DEFINE ll_task_code integer;
DEFINE ll_pdf_column varchar(50);
DEFINE ll_tb_name varchar(60);
DEFINE ll_request_file REFERENCES BYTE;

SELECT pdf_column,table_name INTO  ll_pdf_column ,ll_tb_name  FROM rnmtask WHERE task_code = al_task_code;

FOREACH   

SELECT  ll_pdf_column into  ll_request_file FROM ll_tb_name   

return ll_request_file  with resume ;
end foreach;

end procedure    

---

Answer 1

Si vous disposez d'une version suffisamment récente d'Informix (11.70), vous devriez être en mesure d'utiliser Dynamic SQL dans SPL de cette manière :

BEGIN;

CREATE TABLE rnmtask
(
    pdf_column VARCHAR(32) NOT NULL,
    table_name VARCHAR(32) NOT NULL,
    task_code  INTEGER NOT NULL PRIMARY KEY
);

INSERT INTO rnmtask VALUES("symbol", "elements", 1);
INSERT INTO rnmtask VALUES("name", "elements", 2);
INSERT INTO rnmtask VALUES("atomic_number", "elements", 3);

CREATE PROCEDURE rmg_request_file(al_task_code INTEGER)
    RETURNING VARCHAR(255) AS colval;

    DEFINE ll_pdf_column    VARCHAR(50);
    DEFINE ll_tb_name       VARCHAR(60);
    DEFINE stmt             VARCHAR(255);
    DEFINE result           VARCHAR(255);

    SELECT pdf_column, table_name
      INTO ll_pdf_column, ll_tb_name
      FROM rnmtask
     WHERE task_code = al_task_code;

    LET stmt = "SELECT " || ll_pdf_column || " FROM " || ll_tb_name;
    PREPARE p FROM stmt;
    DECLARE C CURSOR FOR p;
    OPEN C;
    WHILE sqlcode = 0
        FETCH C INTO result;
        IF sqlcode != 0 THEN
            EXIT WHILE;
        END IF;
        RETURN result WITH RESUME;
    END WHILE;

    CLOSE C;
    FREE C;
    FREE p;

END PROCEDURE;

EXECUTE PROCEDURE rmg_request_file(1);
EXECUTE PROCEDURE rmg_request_file(2);
EXECUTE PROCEDURE rmg_request_file(3);

ROLLBACK;

Cela suppose que vous disposiez d'un tableau des éléments pratique dans votre base de données :

CREATE TABLE elements
(
    atomic_number   INTEGER NOT NULL PRIMARY KEY CONSTRAINT c1_elements
                    CHECK (atomic_number > 0 AND atomic_number < 120),
    symbol          CHAR(3) NOT NULL UNIQUE CONSTRAINT c2_elements,
    name            CHAR(20) NOT NULL UNIQUE CONSTRAINT c3_elements,
    atomic_weight   DECIMAL(8, 4) NOT NULL,
    period          SMALLINT NOT NULL
                    CHECK (period BETWEEN 1 AND 7),
    group           CHAR(2) NOT NULL
                    -- 'L' for Lanthanoids, 'A' for Actinoids
                    CHECK (group IN ('1', '2', 'L', 'A', '3', '4', '5', '6',
                                     '7', '8', '9', '10', '11', '12', '13',
                                     '14', '15', '16', '17', '18')),
    stable          CHAR(1) DEFAULT 'Y' NOT NULL
                    CHECK (stable IN ('Y', 'N'))
);

INSERT INTO elements VALUES(  1, 'H',   'Hydrogen',        1.0079, 1, '1',  'Y');
INSERT INTO elements VALUES(  2, 'He',  'Helium',          4.0026, 1, '18', 'Y');
INSERT INTO elements VALUES(  3, 'Li',  'Lithium',         6.9410, 2, '1',  'Y');
INSERT INTO elements VALUES(  4, 'Be',  'Beryllium',       9.0122, 2, '2',  'Y');
INSERT INTO elements VALUES(  5, 'B',   'Boron',          10.8110, 2, '13', 'Y');
INSERT INTO elements VALUES(  6, 'C',   'Carbon',         12.0110, 2, '14', 'Y');
INSERT INTO elements VALUES(  7, 'N',   'Nitrogen',       14.0070, 2, '15', 'Y');
INSERT INTO elements VALUES(  8, 'O',   'Oxygen',         15.9990, 2, '16', 'Y');
INSERT INTO elements VALUES(  9, 'F',   'Fluorine',       18.9980, 2, '17', 'Y');
INSERT INTO elements VALUES( 10, 'Ne',  'Neon',           20.1800, 2, '18', 'Y');
INSERT INTO elements VALUES( 11, 'Na',  'Sodium',         22.9900, 3, '1',  'Y');
INSERT INTO elements VALUES( 12, 'Mg',  'Magnesium',      24.3050, 3, '2',  'Y');
INSERT INTO elements VALUES( 13, 'Al',  'Aluminium',      26.9820, 3, '13', 'Y');
INSERT INTO elements VALUES( 14, 'Si',  'Silicon',        28.0860, 3, '14', 'Y');
INSERT INTO elements VALUES( 15, 'P',   'Phosphorus',     30.9740, 3, '15', 'Y');
INSERT INTO elements VALUES( 16, 'S',   'Sulphur',        32.0650, 3, '16', 'Y');
INSERT INTO elements VALUES( 17, 'Cl',  'Chlorine',       35.4530, 3, '17', 'Y');
INSERT INTO elements VALUES( 18, 'Ar',  'Argon',          39.9480, 3, '18', 'Y');

Answer 2

Je pense que vous devriez préfixer chaque occurrence de ll_tb_name y ll_pdf_column avec le symbole @, même dans votre SELECT INTO

Answer 3

Utilice NDS (SQL dynamique natif) :

EXECUTE IMMEDIATE "SELECT  ll_pdf_column into  ll_request_file FROM :@ll_tb_name"
USING @ll_tb_name;