Save 
Join IIUG
 for   
 

Informix News
01 Feb 13 - IBM Data Magazine - Are your database backups safe? Lester Knutsen (IBM Champion) writes about database back up safety using "archecker"... Read
14 Nov 12 - IBM - IBM's Big Data For Smart Grid Goes Live In Texas... Read
3 Oct 12 - The Financial - IBM and TransWorks Collaborate to Help Louisiana-Pacific Corporation Achieve Supply Chain Efficiency... Read
28 Aug 12 - techCLOUD9 - Splunk kicks up a SaaS Storm... Read
10 Aug 12 - businessCLOUD9 - Is this the other half of Cloud monitoring?... Read
3 Aug 12 - IBM data management - Supercharging the data warehouse while keeping costs down IBM Informix Warehouse Accelerator (IWA) delivers superior performance for in-memory analytics processing... Read
2 Aug 12 - channelbiz - Oninit Group launches Pay Per Pulse cloud-based service... Read
28 May 12 - Bloor - David Norfolk on the recent Informix benchmark "pretty impressive results"... Read
23 May 12 - DBTA - Informix Genero: A Way to Modernize Informix 4GL Applications... Read
9 Apr 12 - Mastering Data Management - Upping the Informix Ante: Advanced Data Tools... Read
22 Mar 12 - developerWorks - Optimizing Informix database access... Read
14 Mar 12 - BernieSpang.com - International Informix User Group set to meet in San Diego... Read
1 Mar 12 - IBM Data Management - IIUG Heads West for 2012 - Get ready for sun and sand in San Diego... Read
1 Mar 12 - IBM Data Management - Running Informix on Solid-State Drives.Speed Up Database Access... Read
26 Feb 12 - BernieSpan.com - Better results, lower cost for a broad set of new IBM clients and partners... Read
24 Feb 12 - developerWorks - Informix Warehouse Accelerator: Continuous Acceleration during Data Refresh... Read
6 Feb 12 - PRLOG - Informix port delivers unlimited database scalability for popular SaaS application ... Read
2 Feb 12 - developerWorks - Loading data with the IBM Informix TimeSeries Plug-in for Data Studio... Read
1 Feb 12 - developerWorks - 100 Tech Tips, #47: Log-in to Fix Central... Read
13 Jan 12 - MC Press online - Informix Dynamic Server Entices New Users with Free Production Edition ... Read
11 Jan 12 - Computerworld - Ecologic Analytics and Landis+Gyr -- Suitors Decide to Tie the Knot... Read
9 Jan 12 - planetIDS.com - DNS impact on Informix / Impacto do DNS no Informix... Read
8 Sep 11 - TMCnet.com - IBM Offers Database Solution to Enable Smart Meter Data Capture... Read
1 Aug 11 - IBM Data Management Magazine - IIUG user view: Happy 10th anniversary to IBM and Informix... Read
8 Jul 11 - Database Trends and Applications - Managing Time Series Data with Informix... Read
31 May 11 - Smart Grid - The meter data management pitfall utilities are overlooking... Read
27 May 11 - IBM Data Management Magazine - IIUG user view: Big data, big time ( Series data, warehouse acceleration, and 4GLs )... Read
16 May 11 - Business Wire - HiT Software Announces DBMoto for Enterprise Integration, Adds Informix. Log-based Change Data Capture... Read
21 Mar 11 - Yahoo! Finance - IBM and Cable&Wireless Worldwide Announce UK Smart Energy Cloud... Read
14 Mar 11 - MarketWatch - Fuzzy Logix and IBM Unveil In-Database Analytics for IBM Informix... Read
11 Mar 11 - InvestorPlace - It's Time to Give IBM Props: How many tech stocks are up 53% since the dot-com boom?... Read
9 Mar 11 - DBTA - Database Administration and the Goal of Diminishing Downtime... Read
2 Feb 11 - DBTAs - Informix 11.7 Flexible Grid Provides a Different Way of Looking at Database Servers... Read
27 Jan 11 - exactsolutions - Exact to Add Informix Support to Database Replay, SQL Monitoring Solutions... Read
25 Jan 11 - PR Newswire - Bank of China in the UK Works With IBM to Become a Smarter, Greener Bank... Read
12 Oct 10 - Database Trends and Applications - Informix 11.7: The Beginning of the Next Decade of IBM Informix... Read
20 Sep 10 - planetIDS.com - ITG analyst paper: Cost/Benefit case for IBM Informix as compared to Microsoft SQL Server... Read
20 Jul 10 - IBM Announcements - IBM Informix Choice Edition V11.50 helps deploy low-cost scalable and reliable solutions for Apple Macintosh and Microsoft Windows... Read
20 Jul 10 - IBM Announcements - Software withdrawal: Elite Support for Informix Ultimate-C Edition... Read
24 May 10 - eWeek Europe - IBM Supplies Database Tech For EU Smart Grid... Read
23 May 10 - SiliconIndia - IBM's smart metering system allows wise use of energy... Read
21 May 10 - CNET - IBM to help people monitor energy use... Read
20 May 10 - ebiz - IBM Teams With Hildebrand To Bring Smart Metering To Homes Across Britain... Read
19 May 10 - The New Blog Times - Misurare il consumo energetico: DEHEMS è pronto... Read
19 May 10 - ZDNet - IBM software in your home? Pact enables five-city smart meter pilot in Europe... Read
17 March 10 - ZDNet (blog) David Morgenstern - TCO: New research finds Macs in the enterprise easier, cheaper to manage than... Read
17 March 2010 - Virtualization Review - ...key components of Big Blue's platform to the commercial cloud such as its WebSphere suite of application ser vers and its DB2 and Informix databases... Read
10 February 2010 - The Wall Street Journal - International Business Machines is expanding an initiative to win over students and professors on its products. How do they lure the college crowd?... Read


End of Support Dates

IIUG on Facebook IIUG on Twitter


Insider

IIUG Insider (Issue #125)
November 2010

Other Issues

Highlights: The IBM Support Portal

Welcome to the International Informix Users Group (IIUG) Insider! Designed for IIUG members and Informix user group leaders, this publication contains timely and relevant information for the IBM Informix community.

Contents:

 

Editorial Back to top

I would like to invite you to find out everything there is to know about the Panther. No, this is not a recommendation to watch the National Geographic channel. The Panther I am referring to is the new Informix 11.7 released last month. And the best place to find about it will be the IIUG Informix Conference in Kansas May 15-18, 2011. Make sure you don't miss this opportunity. Combine a unique educational experience, meeting friends and colleagues and most important make a statement: Informix is growing.

After the big hype (you are invited to read the news section in the Insider) we are all waiting to see Informix gaining additional market share. I believe the load of new exciting features, the new pricing model and the efforts in sales and marketing will make it happen.

For more information and to register, go to http://www.iiug.org/conf.

If you have success stories you can share with us please let me know.

Gary Ben-Israel
IIUG Insider Editor


Highlights Back to top

The IBM Support Portal

The IBM Support Portal is a unified, centralized view of all technical support tools and information for all IBM systems, software, and services. You can tailor the portal pages to suit your needs, focusing on the products you care about and organizing the pages to reflect the way you work. The goal is to simplify your online support experience, reduce the time it takes for you to solve problems, and alert you to information that helps you avoid problems.

Whether you need to open or check on a Service Request (PMR), find a fixpack, research a problem, plan an installation, or check on the latest Flashes for your products, the Support Portal can help. You only need one bookmark (http://www.ibm.com/supportportal) to find it all.

Here's how you can explore some of the portal's key features:

  • It's easy to get started. Just go to the IBM Support Portal, select a product and go to the page for the task you are interested in. Information in the portal will focus on your selected product. You can add (or delete) products from your product list at any time in the Choose our products section on the left. When you do, the content of the portal pages changes to reflect the products in your list. To quickly focus on a product in your list without changing the list, simply uncheck the other products.
  • Enter your search in the support search box in the right column to get results focused on technical support for your products. On the results page, use the search filters to focus the search results even more. You can filter by document type, task, and if you have only one product selected, version. The filters help you quickly narrow results to the information you need.
  • Sign in so you can customize your pages. Add, delete, and rearrange the portlets in the center of the page, setting them up with exactly the resources you need. The drag-and-drop interface is easy to use.
  • Once signed in, you can see your subscribed notifications in the Notifications portlet on the Overview page. Subscribe to different types of notifications for your products and edit your notification settings via the link in the portlet. See helpful information before you know you need it.
  • Find the fixes you need on the Downloads page. Links in the Downloads and fixes portlet take you right to the list of fixes for your products.
  • If you use the Service Request tool, go to the Open Service Request page to see your draft and open PMRs. If you're signed in, you'll see them listed right in the portlet. Click a PMR to update it or click the Open link to open a new PMR.

To stay up-to-date and find out more about the IBM Support Portal and other IBM Electronic Support tools:

With each new release, IBM continues to improve the Support Portal, so be sure and use the feedback link in the right column of the portal to suggest the improvements and enhancements you'd like to see.


Conference corner Back to top

2011 IIUG Conference Update

Registration for the 2011 IIUG Informix Conference is open. Join us May 15 - 18 in Overland Park (Kansas City), KS. The 2011 conference will include Sunday in-depth tutorials, plus 3 full days of conference seminars with over 80 topics to choose from. This conference will introduce several new speakers, offering new and relevant topics related to using IBM Informix to meet your training needs.

Sign up by the Early Bird date of January 31, 2011 and receive a $125 discount off the price of registration. IIUG members can save an additional $100. Make the most of your $250 savings, and get more out of the conference, by applying your savings to one or more of the tutorials offered on Sunday May 15. One half-day tutorial is $150 or you can attend two half-day tutorials or one full-day tutorial for $250. All Hands on Labs are included in the conference price.

Reinforce your professional skills and distinguish yourself in today's competitive environment by becoming IBM Certified. IBM Informix certification testing will be available again during this year's conference at no cost to attendees. Test your knowledge and become certified in areas of Optimization, Tuning, Administration and 4GL programming. Test in one or more disciplines while attending the conference. The latest 11.70 certification exam will be available for the conference.

The Conference, including seminar content, tutorials, certification testing and the opportunity to speak with Informix-specific professionals, is definitely a bargain when considering where to invest your 2011 training dollars.

Hope to see you at the conference!

For more information and to register, go to http://www.iiug.org/conf


Education Back to top

IBM

Improve your Information Management skills for less with the latest discounts on the IBM Education Pack
Don't play complicated games with your training budget. Saving is always simple with the IBM Education Pack - online account. And for a limited time, it's an even better deal, with savings of 15% when you buy an Ed Pack worth $25,000 or more. Train your entire team for less! This special promotional discount expires January 23, 2011.

Learn more!
http://www.ibm.com/training/us/savings/edpack

Complimentary Information Management Training Plans!
One of the most important factors in determining an IT project.s success is the team.s skill level. As you plan and budget for 2011, make sure your team has the skills to drive your projects. success.

To build an accurate budget, consider: Which team members need training? What kind of training? How soon? Once your needs are determined, IBM offers complimentary training plans to match.

Get started and request your complimentary training plan today.Simply complete the online request form and Information Management Training experts will work with you to develop a custom training plan for your entire team that addresses:

  • Training recommendations based on business roles, products and project timelines
  • Scheduling recommendations mapped to your project timelines
  • Cost-saving strategies to stretch your training budget

Learn more!
http://www.ibm.com/training/plan


Developer corner Back to top

New in DeveloperWorks

Understand the Informix Server V11.7 defragmenter
http://www.iiug.org/url/defrag.html
Starting with IBM Informix, Version 11.70.UC1, you can defragment a table, a fragment, or an index, including system catalogs. This reduces the number of extents in which the data in your partition resides. In this article, learn what the defragmenter is, understand some considerations for its use, and see usage examples.
18 Nov 2010

Understanding Informix 11.70 features with upgrade impact
http://www.iiug.org/dmmag_2010.html
GIBM Informix, Version 11.70.xC1 has an abundance of new features, and this article aims to help you achieve a smooth upgrade to Informix 11.70.xC1 and efficiently use these new features. In this article, explore a list of the features that have upgrade impact, and get a brief overview of each feature, with information about how to use the features after upgrade, information about reversion messages (in case you want to revert to your older version), and information about any new onconfig parameters and system catalog changes that were added for the new features.
11 Nov 2010

Online Exclusive: The Beginning of the Next Decade of IBM Informix
http://www.iiug.org/dmmag_2010_2.html
The story behind Informix 11.7, the new possibilities it brings, and what it will mean in the years to come.
03 Nov 2010

For more information go to DeveloperWorks - Informix: http://www.iiug.org/url/devworks.html


Informix news Back to top

Breaking News - IBM Informix 11.7 Now Generally Available
DBTA - October 12
http://www.iiug.org/url/dbta_12oct10.html

Informix 11.7: The Beginning of the Next Decade of IBM Informix
DBTA - October 12
http://www.iiug.org/url/dbta_12oct10_2.html

IBM Helps Organizations Boost Business Performance With New Informix Database Software
The Wall Street Journal - October 13
http://online.wsj.com/article/PR-CO-20101013-904724.html

IBM Helps Organizations Boost Business Performance With New Informix Database Software
REUTERS - October 13
http://www.reuters.com/article/idUS135238+13-Oct-2010+PRN20101013

IBM Unveils Informix Database Software to Help Companies Boost Performance at a Lower Cost
ebizq - October 13
http://www.ebizq.net/news/13111.html

IBM Unveils New Informix Database Software
RFP Connect - October 13
http://rfpconnect.com/news/2010/10/13/ibm-unveils-new-informix-database-software%5C

IBM Helps Organizations Boost Business Performance With New Informix Database Software
PRNewswire - October 13
http://www.iiug.org/url/prn_13oct10.html

IBM Helps Organizations Boost Business Performance With New Informix Database Software
IBM Press Room - October 13
http://www-03.ibm.com/press/us/en/pressrelease/32711.wss

IBM Updates Informix Database for Performance
eWeek - October 14
http://www.iiug.org/eweek_14oct.html

IBM Updates Informix Database for Performance
ITNewsCast - October 14
http://itnewscast.com/odds-sods/ibm-updates-informix-database-performance

IBM Acquires PSS Systems, Updates Informix
EContent - October 14
http://www.iiug.org/url/econtent_13oct10.html

Informix 7.11 improved replication and warehouse functions (In German)
heise online - October 14
http://www.iiug.org/url/heise_14oct10.html

With the new IBM Informix database, companies can increase their business results (In German)
Pressrelations - October 14
http://www.iiug.org/url/pr14oct10.html

IBM has introduced Informix 11.7 software (In Russian)
Cyber Security RU - October 14
http://www.cybersecurity.ru/data/105391.html

A New Appreciation for Database Performance
ITBusinessEdge - October 15
http://www.iiug.org/url/itbe_15oct10.html

IBM Updates Informix Database for Performance
Virtual Technology Leasing - October 15
http://www.iiug.org/url/vtl_15oct10.html

Informix Database Updated For Performance
IT espresso - October 15
http://www.itespresso.de/2010/10/15/informix-database-updated-for-performance/

IBM Informix is available in a new version 11.7 (In German)
Database Pro - October 15
http://www.databasepro.de/Aktuell/News/IBM-Informix-in-neuer-Version-11.7-verfuegbar"

IBM Updates Informix Database for Performance
Database-Benchmark - October 16
http://database-benchmark.com/?s=informix&x=10&y=8

IBM optimizes performance in Informix 11.7 (In German)
SearchDataCenter - October 18
http://www.iiug.org/url/sdc_18oct10.html

IBM helps organizations improve business performance with the new Informix release (In Russian)
Newsdesk@pcmag.ru - October 18
http://www.newsdesk.pcmag.ru/node/29253

IBM updates Informix database for performance
ICT magazine - October 14
http://www.ictmag.info/politics/ibm-updates-informix-database-for-performance/


Informix events Back to top

IBM and Informa organize an Informix Bootcamp version 11.7 on December 1st and 2nd at IBM in Brussels

If you want to join: don't hesitate any longer and please register as soon as possible:

https://www-304.ibm.com/isv/spc/events/enroll.jsp?eventloc=BL01121017

Note that the bootcamp is 2 days: December 1-2 and not until December 3 as mentioned on the website - sorry.

During 2 days, you and your team will have a chance to learn about the latest enhancements around the Informix database engine and will gain the possibility to self proof it through a set of hands-on exercises that are part of the agenda.

During this bootcamp, you'll get to hear from experts about topics such as:

  • Informix Embeddability features
  • Administration and performance tuning
  • OpenAdmin Tool
  • Continuous Availability
  • Data compression

Agenda:

Day 1
09:00-09:15Welcome
09:15-10:00Review Architecture / Database Administration as of 11.50
10:30-11:00Storage Optimization (compression)
11:00-12:00Feature Spotlight 11.50
12:00-13:00Lunch
13:00-13:1511.70 Briefing
13:15-13:40Installer Enhancements
14:00-14:30Fragmentation Strategies
15:00Lab Time
 
Day 2
09:00-10:00Flexible Grid
10:00-10:15Break
10:15-11:00Deployment Utility
11:00-12:00Security
12:00-13:00Lunch
13:00-14:00Storage Provisioning
15:00Informix Certifications (555 Fundamentals, etc. free of charge)

Met vriendelijke groet,
Mirjam Jongenelen - van Agtmaal


Support corner Back to top

Advanced DataTools Informix Remote DBA/Support Offering

Remote Technical and DBA support from Advanced DataTools helps small, medium and enterprise installations maximize their IBM Informix database performance and uptime while minimizing costs and overhead. We can help resolve and/or prevent the worst technical problems you could imagine!

Advanced DataTools' staff of highly experienced, certified IBM Informix consultants are well trained in all levels of database performance and optimization. This allows us to offer our customers IBM maintenance and support contracts on most versions of Informix Dynamic Server, on any platform/operating system.

Top experts in the field, Art Kagel and Lester Knutsen, manage and support our team of certified IBM Informix consultants. Art and Lester each have over 28 years of Informix DBA experience and both have received the IIUG Directors Award, and the Inaugural Data Champion award by IBM.

For more information: http://www.advancedatatools.com/Solutions/Support.html

Java program to allow simple SQL through an IBM JDBC driver

This simple java program, jsql, was initially written to provide a method to execute SQL statements against a database server in the same way as you can do with the Informix 'dbaccess' or DB2 'db2cli'.

Because JDBC is the standard way to operate with a relational database from a Java application, this tool can be used to run sql statements against an Informix , DB2 or Solid database - in fact any database that has its own JDBC driver.

SQL statements, such as SELECT or UPDATE, can be passed in via an sql file ( or from the command line ) and the results are reported back in a similar fashion to the Informix dbaccess utility.

For example - sending in the following sql in a file called 'test.sql'...


- - - test.sql - - -
SELECT FIRST 3 customer_num,fname FROM customer;
- - - - - - - - - -

...via the jsql program...


java jsql com.informix.jdbc.IfxDriver 'jdbc:informix-sqli://mymachine:9089/stores_dem
o:informixserver=myol1150;user=informix;password=xxxxxxxx;' test.sql

...will return...


 customer_num fname

         101 Ludwig
         102 Carole
         103 Philip

3 row(s) retrieved.

The jsql tool also accepts multiple statements, so it can be used to run something like...


- - - test.sql - - -
create table t1( c1 char(20) ) ;
insert into t1 values ("test data" );
select * from t1;
update t1 set c1 = "test data updated";
select * from t1;
drop table t1;
- - - - - - - - - - -

...when the jsql program is run...


$ java jsql com.informix.jdbc.IfxDriver 'jdbc:informix-sqli://mymachine:9089/stores_d
emo:informixserver=myol1150;user=informix;password=xxxxxxxx;' test.sql

...the output will be...


0 row(s) affected.
1 row(s) affected.

c1
test data

1 row(s) retrieved.
1 row(s) affected.

c1
test data updated

1 row(s) retrieved.
0 row(s) affected.

As mentioned earlier, this tool can be used with any JDBC driver. Its has been tested to a lesser or greater extend with the following JDBC drivers connecting to their respective databases : Indeed the com.ibm.db2.jcc.DB2Driver can be used to connect to an Informix database via the drda protocol.

com.informix.jdbc.IfxDriver against an Informix server
com.ibm.db2.jcc.DB2Driver against Informix and DB2 servers
solid.jdbc.SolidDriver against Solid DB

To be able to compile and use the jsql tool, make sure that you have your CLASSPATH set to the JDBC driver jar file(s) you wish to use - and also to the directory containing the jsql.class and that you have a suitable JVM in your environment.

Compiling is as simple as...


javac jsql.java

To execute an SQL statement, just pass the connection string and the SQL query (or the name of a text file with the SQL statements)


java jsql com.informix.jdbc.IfxDriver 'jdbc:informix-sqli://mymachine:9089/...' 'CREA
TE TABLE t1 (c1 INT)'

In simple terms the code will:

      Take the input parameters
      Reads the sql from the supplied sql file - ( or from the command line )
      Loads the supplied jdbc driver
      Creates a connection to the database using the supplied URL
      Executes the supplied sql ( dependant on type ( select , insert, delete , update , DDL ) )

Before the execution of the SQL statements, the jsql tool retrieves MetaData from the database to obtain the column name and length information so that it can correctly print out the results of any SQL to the screen.

The jsql tool can be used as an example of how to perform basic operations with a JDBC driver, the code can be extended to allow for more complex sql if required.

You can find more information on working with the JDBC Drivers for an Informix database in a recently published IBM redbook 'IBM Informix Developer's Handbook' (SG24-7884-00):

http://www.redbooks.ibm.com/abstracts/sg247884.html?Open


----------------------------------jsql.java-------------------------------------------

import java.sql.*;
import java.util.*;
import java.util.regex.*;
import java.io.*;

public class jsql {

   private Connection conn;

   public static void main( String [] argv ) {
           String p_driver  = null ;
           String p_url  = null  ;
           String p_some_sql = null   ;

           if ( argv.length == 1 )  // user sent in  just the sql file  / statement
           {
                  p_some_sql = argv[0] ;
                  p_driver = System.getenv("DRIVER");
                  p_url = System.getenv("URL");
           }
           if ( argv.length == 3 )  // user sent in both URL and DRIVER so try to use
them - takes precedece over ENV VARS above
           {
               p_driver  = argv[0] ;
               p_url  = argv[1] ;
               p_some_sql  = argv[2] ;
           }

           if ( (   ( p_driver == null ) || ( p_url == null ) )  ||  ( ( argv.length !
= 3 ) && ( argv.length != 1 )  )  )
           {
               System.out.println("java jsql   ( make sure
env vars URL and DRIVER exist in environment");
               System.out.println(" OR " ) ;
               System.out.println("java jsql     ");
               System.out.println("");
               System.out.println("Examples");
               System.out.println("DRIVER can be set to one of:");
               System.out.println("com.informix.jdbc.IfxDriver");
               System.out.println("com.ibm.db2.jcc.DB2Driver");
               System.out.println("solid.jdbc.SolidDriver");
               System.out.println("");
               System.out.println("URL could be set simlar to below - Supply the corre
ct password and other connection details as needed:");
               System.out.println("'jdbc:informix-sqli://mymachine:9089/stores_demo:in
formixserver=myol1150;user=informix;password=xxxxxxxx;'");
               System.out.println("'jdbc:db2://mymachine.bedfont.uk.ibm.com:9090/store
s_demo:user=informix;password=xxxxxxxx;'");
               System.out.println("'jdbc:db2://mymachine.torolab.ibm.com:50002/mydb:us
er=holmesg1;password=xxxxxxxx;'");
               System.out.println("'jdbc:solid://mymachine.bedfont.uk.ibm.com:1315/dba
/xxx'");
               System.out.println("");

               System.exit(1);
           }

       jsql myjsql = new jsql(  p_driver , p_url );

       String l_sql_stmt ="" ;
       if (p_some_sql.matches("(?i).*\\.sql.*" ) )
       {
          //System.out.println ("sql file");
          // lets read that file and send the sql(s) through ....
          String l_sql_file = p_some_sql ;

        try
        {
              // Open the file that is the first command line parameter
              FileInputStream fstream = new FileInputStream(l_sql_file);

              // Get the object of DataInputStream
              DataInputStream in = new DataInputStream(fstream);
              BufferedReader br = new BufferedReader(new InputStreamReader(in));
              String strLine;

              //Read File Line By Line
              while ((strLine = br.readLine()) != null)   {

                // Print the content on the console
                //System.out.println (strLine);
                // lets cater for some double dash comments anywhere on the line
                String[] line_in = strLine.split("--",2);

                //  join the line to the sql stmt and add a line feed so that the inp
ut data looks the same
                //  using a space to join rather than lf means that "matches" works a
gainst the full text
                //  line feeds mean that matches fails - so I reverted back to just a
dding a space

                //l_sql_stmt = l_sql_stmt + "\r\n" +  line_in[0];
                l_sql_stmt = l_sql_stmt + " " +  line_in[0];

                //if (line_in[0].matches("(?i).* into .*" ) ) // imagine select * fro
m customer into temp t1
                    //System.out.println ("MATCHED INTO" );

              }
              //Close the input stream
              in.close();
      }
      catch (Exception e){//Catch exception if any
                System.err.println("Error: " + e.getMessage());
      }

     //System.out.println ( "FULL STRING IS :"  + l_sql_stmt);

          // now we have the string - lets  break at each semicolon and send each stm
t thro

          //String l_no_lf = l_sql_stmt.replaceAll("[\r\n]", " ")  ;

          while (!l_sql_stmt.equals(""))
          {
             //String[] sqls = l_no_lf.split(";",2);
             String[] sqls = l_sql_stmt.split(";",2);
             myjsql.do_sql(sqls[0]);

             if (  ( sqls.length == 1 ) ||  (sqls[1].trim().equals ("") ) )    // onl
y 1 element left  - then its the last one
                                                                               // or
the second element is just space
                 break ;                      // break out of this loop
             l_sql_stmt=sqls[1];
          }

       }
       else // its just a singleton whole piece of sql sent in - so
          myjsql.do_sql( p_some_sql );

   }

   public jsql(String p_driver , String p_url) {

        try
           {
           Class.forName(p_driver);
           }
       catch (Exception e)
           {
           System.out.println("FAILED: failed to load JDBC driver." + p_driver);
           }

       try
           {
           conn = DriverManager.getConnection(p_url);
           }
       catch (SQLException e)
           {
           System.out.println("FAILED: failed to connect!");
           }

   }

   public void close() {

       try {
           conn.close();
       }
       catch ( SQLException e ) {
           e.printStackTrace();
           e.getMessage();
       }
   }

  public static String padRight(String s, int n) {
       return String.format("%1$-" + n + "s", s);
  }

 public static String padLeft(String s, int n) {
   return String.format("%1$#" + n + "s", s);
  }

 public static String FormatString( String s_col_name , int disp_size , int coltype
, String s ) {
   String retstr ;
   int max_col_width ;

         if ( disp_size >= s_col_name.length())
             max_col_width = disp_size ;
         else
             max_col_width = s_col_name.length() ;

         // if its a character - then pad right - otherwise pad left
         switch ( coltype )
         {
             case  1 :
             case  12 :
             case  -1 :
             case  -4 :
                if ( s != null)
                   retstr=padRight(s, max_col_width) + " " ;
                else
                   retstr=padRight("", max_col_width) + " " ;
                break ;

             default :
                   if (s != null)
                       retstr=padLeft(s, max_col_width) + " " ;
                   else
                       retstr=padLeft("", max_col_width) + " " ;
         }

   return retstr ;
  }


 private static String readFileAsString(String filePath) throws java.io.IOException
   {
       byte[] buffer = new byte[(int) new File(filePath).length()];
       BufferedInputStream f = new BufferedInputStream(new FileInputStream(filePath))
;
       f.read(buffer);
       return new String(buffer);
   }


   public void do_sql(String p_stmt ) {

   try {
          //System.out.println("SQL STATEMENT :" + "\r\n" +  p_stmt) ;
          //System.out.println("") ;
          //System.out.println("SQL STATEMENT : " +  p_stmt) ;
          System.out.println("") ;

          String l_user_string ="";
          int l_rowcount = 0 ;
          // ------------------------------------
          // select statement
          // ------------------------------------
          if (    (p_stmt.trim().substring(0,6).toUpperCase().equals("SELECT") )
               && (!p_stmt.matches("(?i).* into .*" ) ) // imagine select * from cust
omer into temp t1
             )
          {

            PreparedStatement p_pmr_base = conn.prepareStatement(p_stmt);
            p_pmr_base.setQueryTimeout(4);

             ResultSet rset = p_pmr_base.executeQuery();
             ResultSetMetaData rmData = rset.getMetaData();
             int col_count = rmData.getColumnCount();

             // display column  headers
             l_user_string="";
             int l_row_size=0;
             int max_col_len=0;

             // work out a rough row size
             for (int i = 1; i <= col_count ; ++i) // number of columns returned  - c
olumns in that table
             {
                  l_row_size = l_row_size + rmData.getColumnDisplaySize(i); // length
of display DATA across screen
                  if ( rmData.getColumnName(i).length() > max_col_len )  // longest c
olumn name  length ( for use
                                                                         // when disp
laying 1 column per line )
                      max_col_len = rmData.getColumnName(i).length()  ;
             }

             if ( l_row_size >=80 ) // display one column per line
             {
                System.out.println("") ;
                l_rowcount = 0 ;
                while ( rset.next() ) {
                        l_rowcount = l_rowcount + 1 ;
                        for (int i = 1; i <= col_count ; ++i) // number of columns re
turned  - columns in that table
                        {
                            l_user_string=FormatString(rmData.getColumnName(i),max_co
l_len,1,rmData.getColumnName(i));
                            l_user_string=l_user_string+" "+FormatString(rmData.getCo
lumnName(i),max_col_len,1,rset.getString(i));

                            // print colname and data
                            System.out.println(l_user_string) ;
                         }
                         System.out.println("") ;
                }
             }
             else   // display columns across the page
             {
                 for (int i = 1; i <= col_count ; ++i) // number of columns returned
- columns in that table
                 {
                      l_user_string=l_user_string+FormatString( rmData.getColumnName(

i) , rmData.getColumnDisplaySize(i) , rmData.getColumnType(i), rmData.getColumnName(i
)) ;
                  }
                  System.out.println(l_user_string) ;

                  // now print the whole row DATA
                  System.out.println("") ;


                  l_rowcount = 0;
                  while ( rset.next() ) {
                            l_rowcount = l_rowcount + 1 ;
                            // get row info for each column
                            l_user_string="";
                            for (int i = 1; i <= col_count ; ++i) // number of column
s returned  - columns in that table
                            {
                               l_user_string=l_user_string+FormatString( rmData.getCo
lumnName(i) , rmData.getColumnDisplaySize(i) , rmData.getColumnType(i),rset.getString
(i)) ;

                             }
                             // print the whole row
                             System.out.println(l_user_string) ;
                  }
              }
              System.out.println("") ;
              System.out.println(l_rowcount + " row(s) retrieved.") ;
              rset.close();
              rset = null;
          }


          // ------------------------------------
          // insert / delete / update /DDL  statement
          // ------------------------------------
          else
          {
           int l_rows = 0 ;
           String stmt_ins = p_stmt;
           PreparedStatement p_ins  = conn.prepareStatement(stmt_ins);
           l_rows = p_ins.executeUpdate();
           System.out.println( l_rows + " row(s) affected.") ;
          }



       } // try

       catch ( SQLException sqle ) {
          sqle.printStackTrace();
          System.err.println(sqle.getMessage());
       }

   }

}


----------------------------------jsql.java-------------------------------------------

Greg Holmes

Technical Support
Information Management
IBM Software Group


Informix resources Back to top

Informix Blogs and things  
 
Calendar of events Back to top

December - 2010
Date Event Location Contact
9 China Informix User Group Meeting Guang Xi, China  
9 Chicago Informix User Group Meeting Chicago, Illinois Rob Beal
May - 2011
Date Event Location Contact
10-12 2011 IDS 11.7 Bootcamp Kuala Lumpur, Malaysia Fong Lee Yong
15-18 2011 IIUG Informix Conference Overland Park, Kansas Cindy Lichtenauer

Useful links Back to top

http://www.iiug.org/quicklinks.html


Closing and credits Back to top

The International Informix Users Group (IIUG) is an organization designed to enhance communications between its worldwide user community and IBM. The IIUG's membership database now exceeds 25,000 entries and enjoys the support and commitment of IBM's Data Management division. Key programs include local user groups and special interest groups, which we promote and assist from launch through growth.

Sources:  IIUG Board of Directors
IBM Corp.
Editors:  Gary Ben-Israel

For comments, please send an email to gary@iiug.org.