select over another database
Hi, How can I do one external select to another database on the same server? I have looked over documentation and tried several forms but always get error. the form database:table it returns error on : I am using informix 12.10.FC12W1 version. I also would like to use one variable for database name, is it possible on informix sql? If somebody can send me some examples I would appreciate. Thanks for any help, SP sergio.peres@airc.pt
I have no problem if using the database:table notation.
Test case:
CREATE DATABASE db1 WITH LOG;
CREATE TABLE tab1 (id INT);
INSERT INTO tab1 VALUES (1);
CREATE DATABASE db2 WITH LOG;
CREATE TABLE tab2 (id CHAR(1));
INSERT INTO tab2 VALUES ('a');
DATABASE db2;
SELECT * FROM db1:tab1;id
1
DATABASE db1;
SELECT * FROM db2:tab2;id
a
What client are you using for your queries?
Hi Luis,
Thanks for your reply, concerning my problem maybe I am doing something wrong.
I have done the test with your code and it works, but my problem is that I
need to use database name as variable and that is returned from the last
instruction.
I have tryed also to use INTO clause but the problem remains...
set isolation to dirty read;
select x0.sid,
x0.username,
x0.hostname,
x1.sqs_dbname into dbname,
dbinfo("UTC_TO_DATETIME",x0.connected) AS conn_dt
from
sysmaster:"informix".syssessions x0,
sysmaster:"informix".syssqlstat x1,sysmaster:"informix".sysnetworkio n
where ( x0.sid = x1.sqs_sessionid ) and hostname is not NULL and
trim(hostname) <> ''
and trim(hostname) <> '-' AND sqs_dbname <> '-' AND n.sid = x0.sid
and trim(hostname) <> '-' AND sqs_dbname NOT LIKE 'sys%' AND sqs_dbname <> '-'
AND n.sid = x0.sid;
database dbname;
select * from grlf_lig
this code returns error!
Thanks for your help.
You can't use INTO variable in "normal" Informix SQL.
You either use an external program to collect the intermediary results and
them build queries based on that information or you write a SPL routine.
In an SPL routine you can build dynamic queries ( with some limitations ).
For example, to get you started:
CREATE DATABASE db1 WITH LOG;
CREATE TABLE tab1 (id INT);
INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (2);
CREATE DATABASE db2 WITH LOG;
DATABASE db2;
CREATE FUNCTION spl_example ()
RETURNING INTEGER AS id;
DEFINE c_database VARCHAR( 30 );
DEFINE c_query VARCHAR(250);
DEFINE i_id INTEGER;
SELECT 'db1'
INTO c_database
FROM sysmaster:systables
WHERE tabid = 1;
-- build query text by concatenating strings and variables
LET c_query = 'SELECT id FROM ' || c_database || ' : tab1;';
PREPARE c_stmt FROM c_query;
DECLARE c_cur CURSOR FOR c_stmt;
OPEN c_cur;
WHILE (1 = 1)
FETCH c_cur INTO i_id;
IF (SQLCODE != 100) THEN
RETURN i_id WITH RESUME;
ELSE
EXIT;
END IF
END WHILE
CLOSE c_cur;
FREE c_cur;
FREE c_stmt;
END FUNCTION;
EXECUTE FUNCTION spl_example();
id
1
2
In this case the SPL routine can return multiple values.
Check the online documentation here:
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqlt.doc/id
s_sqt_412.htm
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id
s_sqs_1301.htm
The PREPARE statement and WHILE loop to fetch the results was mostly copied
from this example:
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id
s_sqs_1881.htm
Sergio, this functionality works since Informix 4.0 ( 1990 ... )