query needs to use dynamic database:table name

al91206 · #1 (**permalink**) 03-04-09, 13:30

Hi,

We have an informix environment where we have several customer databases:

Customer001
Customer002
Customer003

Obviously this is not a great way to do things, and we have a long-term project to combine them into 1 database, but for the meantime I need to come up w/ sql to get information out of each one for a report.

select item
from customer001:item <-- this is where I need the dynamic "db selector"

Any help is appreciated.

Thanks,

Al in SoCal

artemka · #2 (**permalink**) 03-05-09, 13:50

why don't you do something like a for loop in a shell script

file_name contains the names of the databases

for i in `cat file_name'
do
dbaccess $i <<!!
select * from tablename
!!

ceinma · #3 (**permalink**) 03-17-09, 16:12

After version 11.50 this is possible in Stored Procedures, but unfortunately I never see this in sql statement .

copied from Release notes of 11.50 UC1

Quote:

New Support for Dynamic SQL Statements in SPL Routines
In earlier versions of the Informix SPL language, the form of SQL statements in a UDR was known when the SPL routine was written. Users can now specify queries dynamically at runtime in SPL routines, using the EXECUTE IMMEDIATE and PREPARE statements to execute dynamically constructed SQL statements and to define and manage cursors and prepared objects with the DECLARE, OPEN, FETCH, CLOSE and FREE statements.
For some of these Dynamic SQL statements, the SPL syntax is a subset of the existing ESQL/C syntax, but character expressions that evaluate to SQL statement text are now supported by PREPARE and EXECUTE IMMEDIATE statements in SPL routines. In addition, Boolean conditions in SPL statements can specify SQLCODE as an expression that returns the SQLCA status from the most recently executed SQL statement.
This feature increases the flexibility of UDRs written in the SPL language, and simplifies the migration to IDS of stored procedures and applications written for other database servers that support Dynamic SQL operations. This feature is documented in the Guide to SQL: Syntax.

al91206 · #4 (**permalink**) 03-19-09, 19:02

Thanks for the info - I'll be checking the guide and get back to everyone if we get this figured out.

Internet Services

Web Development

Digital Marketing

Consumer Tech