Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006

    Unanswered: Fetch value of select into korn shell variable

    I am writing a korn shell script in which I need to read some field values from an Informix database. I execute the query using isql $DBNAME <<. loading the appropriate modules for the informix database. Can I get the select query value directly into a shell variable? For example, is this possible:
    #load db specific modules
    typeset SOMEVARIABLE
    isql $DBNAME <<.
    select field_name INTO SOMEVARIABLE from table_name where record_id = 1;
    #now use SOMEVARIABLE which contains the value that would have had been returned.
    Don't need to worry about multiple values being returned from the query. Any modifications that can help do this? Any other simpler methods to achieve something like this? I need to fetch around 5 values from the same table depending upon different where clause conditions and use them with another informix database (with different module loads - on another server)

    Or, is there a way to query a different informix server from another server? Like in case of MS Sql Server 2000, there is something like OPENDATASOURCE() function that helps with this. We can call it from stored proc on one server to query a db on another server giving the connection parameters.

    Thanks to all in advance. Appreciate any help.

  2. #2
    Join Date
    Apr 2003
    Phoenix, AZ
    There's actually a couple of ways to accomplish this - a lot depends upon the number of values you wish and the data type.

    Example one:

    VERSION=`dbaccess sysmaster 2>/dev/null << !eof
    output to pipe "awk '/[0-9]/'" without headings
    select dbinfo('version','major') from systables where tabid = 1;
    !eof `

    Example two:

    If you wish multiple values (ie columns, not rows) returned I'd be inclined to do an "unload to file" and then use a shell READ to pull in the values into variables. For example:

    $INFORMIXDIR/bin/dbaccess sysmaster <<!eof > /dev/null 2>&1
    unload to ${FILENAME}
    name -- dbspace name truncated
    , count(*)
    , sum(c.chksize/(c.pagesize/2048)) -- pages allocated
    , sum((c.chksize - c.nfree)/(c.pagesize/2048)) -- Pages used
    , sum(c.nfree/(c.pagesize/2048)) -- Pages free
    , round((sum(c.nfree) / sum(c.chksize)) * 100, 2) -- percent free
    from sysdbspaces d, syschunks c
    where d.dbsnum = c.dbsnum
    group by 1
    order by 1;
    Fred Prose

  3. #3
    Join Date
    Feb 2007
    nice code!
    thanks to share

Posting Permissions

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