• 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

    • keep in mind some things like chrome look at this setting to disable some animations in browsers... its an accessibility thing
    • I always disable animations in Windows using sysdm.cpl and yes, it feels faster. I have also reduced animations in my android phone using dev options.
    • Neowin's servers (hosted in the UK).
    • MacOS isn't Linux, but Proton is modified WINE, and WINE runs on MacOS. They would just need to add something like Box86 or Rosetta support.
    • PicPick 7.4.0 by Razvan Serea PicPick is user friendly and full of features for creating your image, suitable for software developers, graphic designers and the home user. It is an all-in-one program that provides a full-featured screen capture tool, intuitive image editor, color picker, color palette, pixel ruler, protractor, crosshair and even whiteboard. It not only has everything that you need, but it loads fast, and sits quietly in the system tray until needed. This software is provided as freeware for personal use only. In this case, you are granted the right to use this program free of charge. Otherwise, you need to pay for a license for commercial use. PicPick key features: For All Windows (Fully support Windows 11, 10, 8.1, 8, 7, Vista and XP both 32-bit and 64-bit) Multi-language is supported. (MORE 28+) All functions are fully supported on a dual screen environment. No Registry, No access to System folder (you can copy these files to portable USB) Screen Capture Auto-scroll, dual monitors and sound effect are supported Various output to File, Printer, Office programs, External program Sharing to FTP, Web, E-mail, Facebook and Twitter are supported as well Full Screen Active Window Window Control Scrolling Window Region, Fixed Region FreeHand Repeat Last Capture Image Editor Intuitive User Interface Windows Ribbon style Standard drawing, shapes, arrows, lines, text, and etc. Blur, sharpen, hue, contrast, brightness, pixelate, rotate, flip, frame effect and etc. Color Picker and Color Palette various color code type (RGB, HTML, C++, Delphi) Photoshop style RGB/HSV conversion is supported. Pick and Save your favorite color! Screen Pixel Ruler Horizontal and vertical orientation various units (Pixels, Inches, Centimeters) DPI setting (72, 96, 120, 300) colorful gradient skins You don't have to install any other screen ruler softwares. Screen Magnifier Zoom 2x to 10x option Stay on top, smooth display, and sizeable window Screen Protractor Have you seen any screen protractor function in other software? Screen Crosshair For aligning objects in graphics or design applications For calculating relative coordinates on screen Some prefer to use this tool than a pixel-ruler. Whiteboard For giving a presentation or just drawing something on screen PicPick 7.4.0 changelog: Added support for saving in WebP file format Added horizontal scrolling in the Image Editor with Shift and mouse wheel Fixed control capture failing to detect specific windows Fixed focus loss during delayed active-window captures. Download: PicPick 7.4.0 | 74.9 MB (Free for personal use only) Download: Portable PicPick 7.4.0 | 73.3 MB View: PicPick Home page | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
  • Recent Achievements

  • Popular Contributors

    1. 1
      +primortal
      651
    2. 2
      Michael Scrip
      224
    3. 3
      ATLien_0
      222
    4. 4
      Xenon
      146
    5. 5
      +FloatingFatMan
      142
  • Tell a friend

    Love Neowin? Tell a friend!