Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009

    Unanswered: query needs to use dynamic database:table name


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


    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.


    Al in SoCal

  2. #2
    Join Date
    May 2004
    New York
    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'
    dbaccess $i <<!!
    select * from tablename

  3. #3
    Join Date
    Apr 2007
    Jundiai / SP - Brasil
    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

    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.
    Last edited by ceinma; 03-17-09 at 15:17.
    César Inacio Martins
    Jundiai / SP - Brasil - em Português - English (translated by Google).

  4. #4
    Join Date
    Mar 2009

    Thanks for the info

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts