• 0

[Perl/Informix] Automated Data Extraction


Question

I needed a way to automatically extract several tables from a database running Informix so I could DTS them into MS SQL 2k5+. This was an older Informix 7.4 server running Debian 3 sarge and every attempt at connect to this server via ODBC failed so I wrote this script.

#!/usr/bin/perl -X

##################################################################
# Export all tables from an Informix database using iSQL command #
#   by Travis Gutjahr                                            #
#   Version 2.1                                                  #
#   Updated July 14, 2011 11:18 UTC -05:00                       #
##################################################################

### VARIABLES

### Database to extract all tables from
$database = "maindb";

### Folder to extract files to
$backupfolder = "/backup/output";

### Delimiter to use
$delimiter = "?";

### SOURCE CODE - DO NOT EDIT ###

print "Retrieving table list...\n";
$tableinfoexport = `isql $database << EOF
output to pipe "cat" without headings
select tabname from systables where tabid > 99 and tabtype = "T"
EOF`;
print "Table list retrieved!\n\n";

foreach $table (split(/\n/, $tableinfoexport)) {
        if (($table !~ /.*row(s) retrieved\..*/ig) && ($table !~ /^$/)) {
                $table =~ s/^\s+//;
                $table =~ s/\s+$//;
                print "Exporting table: " . $table . "\n";
                $output = `isql $database << EOF
UNLOAD TO '$backupfolder/$table.ifx' DELIMITER '$delimiter' select * from $table
EOF`;
        }
}

close(TABINF);
###

Edited by FuhrerDarqueSyde

3 answers to this question

Recommended Posts

  • 0

Change log:

(7/14/2011)

* Fixed a bug where it would open the tables.txt before it was done being written to, resulting in not all tables being exported. The file tables.txt is no longer being generated and the tables list is now piped to 'cat' and stored within a variable for processing in the code, resulting in less folder clutter.

  • 0

I inherited a complete cpio tape backup of a 'working' system but short of using isql and dbaccess, nothing else i used seemed to connect to the instance including ODBC (at least on windows, tried using multiple tools including the Informix SDK as well as connectivity tools i found in a share on this server). Eventually I decided to just export the data using a delimited format and importing to SQL Server using SSIS/DTS.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Or, they could really turn the table upside down and rename it in BINARY. 00011001
    • I have tried that registry 'trick' from that video already. It doesn't work at all in Win11 for me   Yes it's a 3rd party app... that I am only using because of Microsoft breaking user customization in Windows 11. So yes... the is a Windows 11 issue. As of now to me, Microsoft has made a huge mistake with this in Windows 11.   The current font option in Windows 11 are terrible. They are just missing. People that want to use their own computer the way they want to, they need to avoid 11.
    • It's been an age since I did desktop support, but here goes: You have an issue with Windows 11, which is fair.  You are using a freeware 3rd party app to make modifications to the default Windows 11.  Since an update, this 3rd party application no longer plays nice with Windows 11.  And this is somehow Windows 11's fault? Sorry, not buying that this is a Windows 11 problem... it's a customization issue that has just appeared, but standard 3rd party support.   Clearly you're upset.  You can't make Windows do anything, just like you can't make MacOS run Explorer or Linux run Nintendo games natively.  And I know people are going to say "sure, it's possible..." but those aren't defining elements of the OS.  You can't have animated backgrounds in Windows 11 natively -- so it's trash amirite??? I did quick searches about changing the default fonts and there are ways to do it, and clearly 3rd party freeware apps can do it (basically my guess is they're making registry changes on your behalf) and clearly they're having issue.   You can make your computer do a lot of things, but sometimes you get what you pay for. Did a quick search and don't see an easy option in Windows 10 either.  Some of these links - ironically - are for Windows 10.  They still apply. Here's a video tutorial of how it can be done without a 3rd party:   Same reference here with a bit more detail: https://www.howtogeek.com/716407/how-to-change-the-default-system-font-on-windows-10/ Once the font is chosen, the size can be changed via personalization from my understanding. Hope this helps.
  • Recent Achievements

    • Week One Done
      maimutza earned a badge
      Week One Done
    • Week One Done
      abortretryfail earned a badge
      Week One Done
    • First Post
      Mr bot earned a badge
      First Post
    • First Post
      Bkl211 earned a badge
      First Post
    • One Year In
      Mido gaber earned a badge
      One Year In
  • Popular Contributors

    1. 1
      +primortal
      488
    2. 2
      +FloatingFatMan
      262
    3. 3
      snowy owl
      244
    4. 4
      ATLien_0
      222
    5. 5
      Edouard
      187
  • Tell a friend

    Love Neowin? Tell a friend!