SQuirreL SQL Client

SQuirreL SQL Client is a graphical Java program that will allow you to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands etc, SQuirreL's functionality can be extended through the use of plugins.

SQuirreL SQL is open-source software. It provides an editor that offers code completion and syntax highlighting for standard SQL. It also provides a plugin architecture that allows plugin writers to modify much of the application's behavior to provide database-specific functionality or features that are database-independent. 

The SQuirreL client leverages a database's JDBC driver to facilitate conversation with a database. Officially, the client supports a myriad of database products, including the major players: IBM DB2, Oracle, Microsoft SQL Server, Informix, Sybase, and MySQL. If there is a JDBC 2.0 available driver for your database, you can register the database manually with the client.

The ability to interact with databases of different vendors via a single interface is one of the key offerings of SQuirreL. As it is quite likely that the databases you are dealing with will not reside on the same physical machine, SQuirreL client has built in support for using HTTP proxies and SOCKS proxies to interact with databases that might reside somewhere on the Internet.

Supported Database.
· Axion Java RDBMS.
· Apache Derby
· Daffodil (One$DB)
· Fujitsu Siemens SESAM/SQL-Server with the SESAM/SQL JDBC driver
· Firebird with the JayBird JCA/JDBC Driver
· FrontBase
· Hypersonic SQL
· H2 (DBMS)
· Informix
· IBM DB2 for Linux, OS/400 and Windows
· InstantDB
· Ingres
· Mckoi SQL Database
· InterBase
· Microsoft Access with the JDBC/ODBC bridge.
· Microsoft SQL Server
· Mimer SQL
· MySQL
· Oracle Database 8i, 9i, 10g, 11g
· Netezza
· Pointbase
· SAPDB
· PostgreSQL 7.1.3 and Higher
· Sybase
· Sunopsis XML Driver (JDBC Edition)
· Teradata Warehouse
· Vertica Analytic Database.
· ThinkSQL RDBMS

Features:
· Object Tree allows for browsing database objects.
· The SQL Editor is based on RSyntaxTextArea by fifesoft.com to provide syntax highlighting. It can be used to open, create, save and execute files containing SQL statements.
· Supports simultaneous sessions with multiple databases.
· Graph capabilities to create charts showing table relationships.  

Installing SQuirreL SQL:
From command prompt run the bellow command:

Java –jar file path/Downloaded Squirrel jar file name.jar

Once you run the jar file you will be prompted with the installation screen, keep press Next button on all the screens till you reach the bellow shown screen.

Select "Optional Plugin - DB2" when you are prompted.

Running the Application:

When you start the application, you will get the splash screen and then you will see the application screen. In the application screen, click on the Drivers tab on the left.

To set up the driver, select the JTOpen(AS/400) driver and then click on the pencil icon in the tool bar directly above the driver list.
On the Change Driver Window, click on the Extra Class Path tab. Then click on the Add button and navigate into the folder where you downloaded your JTOpen jar file.

(Note: You need to download the JTOpen from http://jt400.sourceforge.net/)

Once the driver is set up, you need to configure the application for your IBM i. Click on the Aliases tab on the left of the application screen. Then click on the blue plus sign (+) in the tool bar above the empty list box to add a new alias.

· In URL box follow the bellow standard to setup a connection with IBM i.

         o jdbc:as400://IBM i Name/Test Library

· Select Driver as JTOpen(AS/400).

· Give the User Name and password.

XMLi for i

The XMLi package offers two different methods for generating XML.

XMLi1 enables the building of XML within RPG using procedure calls. Data can be written to your program variables or pointers.

XMLi2 provides an XML-based scripting language that makes XML generation easy. Simply write a template of your desired XML/HTML and use imbedded SQL queries to build your data. Pass parameters to the template from your RPG to be used in your SQL queries. Conditional logic is provided by IF and CHOOSE blocks, and looping constructs are provided by DOW/DOU/FOR loops. Looping through SQL queries is provided using FOR-EACH. Data can be written to the IFS, standard output, or passed to a call-back procedure.

In this post we’ll take one simple XML document, and we will generate the same using XMLi1

       <Customers>

     <RecordCount>12</RecordCount>

     <Customer ID="938472">

     <Name>Henning</Name>

     <Address>

     <Street>4859 Elm Ave</Street>

     <City>Dallas</City>

     <State>TX</State>

     <Zip>75217</Zip>

     </Address>

     </Customer>

     </Customer>

In This Example we will Be Using QCUSTCDT - PC Support Customer File from QIWS library,

Beginning at (a) in the following code, we identify the binding directory supplied with XMLi to make it easy for the compiler to locate XMLi's subprocedures. This way a simple CRTSQLRPGI (or CRTBNDRPG if we weren’t using embedded SQL) can be used to compile the program.

(b) includes the XMLi prototypes in the program. 

(c) defines xmlData, the field to hold the generated XML document, followed by xmlFilename, the name of the file to which it will be written.

     (a) H  BNDDIR('XMLILIB/XMLI')  DftActGrp(*no)

     (b)  /include XMLILIB/QRPGLESRC,XMLI_H

     // Declare field definitions for use by SQL

     d  customer    E DS            ExtName('QIWS/QCUSTCDT')

     // Work Variables

     d   recordCount   s        5i 0

     d  endOfData     c             '02000'

     (c) d  xmlData       s    10000a   Inz

     d  xmlFilename   s      128a   Varying

     d  Inz('/Partner400+

     d  /CustomersE1.xml')

In the following logic, the call to xmli_useVariable() at (d) identifies the variable in which XMLi is to build the XML document. You can either specify a variable as we’ve done, or have XMLi provide “managed memory” automatically for you.

We next (e) set the format for the generated XML. The PRETTY option causes the XML to be indented to make it easier to read and is a good option to use when testing. For production purposes, you might want to use SIMPLE (no indentation). Other options are available when using the XMLi2 templating approach.

     /Free

     (d)    xmli_useVariable(xmlData);

     (e)    xmli_setFormat(XML_FORMAT_PRETTY);

     Exec SQL

     select count(*)

     into :recordCount

     from qiws/qcustcdt

     where STATE = 'TX';

Now we begin the process of generating the actual XML. At (f), we call the xmli_openTag() API to generate the <Customers> tag. Then (g) calls xmli_addElement() to generate the complete <RecordCount> nnn </RecordCount> element.

The actual SQL directives (h) set up the cursor so that we can subsequently loop through the result set. The actual loop begins at (i) in the next code example.

       (f)  xmli_openTag('Customers');

     (g)  xmli_addElement('RecordCount': %char(recordCount));

     (h)  Exec SQL

     declare customerCursor cursor for

     select CUSNUM, LSTNAM, STREET, CITY, STATE, ZIPCOD

     from QIWS.QCUSTCDT

     where STATE = 'TX';

     Exec SQL

     open customerCursor;

Now that all the setup is complete, we can loop through the result set and build the body of the XML document. This process begins at (j) where we create the opening tag for the <Customer> complex element. Since the Customer element includes the attribute ID, we need to call xmli_addAttribute() to add the attribute value (k). xmli_addAttribute always adds the attribute to the last element opened, so the sequence is important here.

We then proceed to add all of the individual simple elements before finally at (l) using the xmli_closeTag() API to close out the Address and Customer elements.

Once all rows in the result set have been processed, we can proceed to close the Customers element (m) and then call the API xmli_writeToFileWithVar() to write the XML document that has been built in the xmlData variable to the IFS file named in the xmlFilename variable.

     (i)    DoU SQLSTATE = endOfData;

     Exec SQL

     fetch next

     from customerCursor

     into :CUSNUM, :LSTNAM, :STREET, :CITY,

     :STATE, :ZIPCOD;

     If SQLSTATE <> endOfData;

     (j)        xmli_openTag('Customer');

     (k)        xmli_addAttribute('ID': %char(cusnum));

     xmli_addElement('Name': LSTNAM);

     xmli_openTag('Address');

     xmli_addElement('Street': STREET);

     xmli_addElement('City': CITY);

     xmli_addElement('State': STATE);

     xmli_addElement('Zip': %editc(ZIPCOD:'X'));

     (l)          xmli_closeTag('Address');

     xmli_closeTag('Customer');

     EndIf;

     EndDo;

     Exec SQL

     close customerCursor;

     (m)    xmli_closeTag('Customers');

     xmli_writeToFileWithVar( xmlFilename

     : xmlData

     : XML_ENCODING_UTF8);

That’s all there is to it. Effectively, we use API calls to open tags (xmli_openTag), add attributes to them (xmli_addAttribute), close them (xmli_closeTag) and to write complete elements (xmli_addElement).

WOW for i

Hi folks,

As a first post in this blog i wanted to write about Web Object Wizard Community Edition tool.

There are many tools available in market which helps to build modern web based application using IBM i, but i selected WOW CE because its available for free of charge, and very easy to develop application.

WOW CE 7.0 is the two time GOLD winner as the top IBM i development tool.

WOW CE will allow you to access not only DB2/400, but also you can access Oracle, SQL server, MySQL.

WOW is written in Java, so it can run on any platform. It runs on top of IBM's WebSphere Application Server or Apache's Tomcat.

WOW CE serve as a query or reporting tool.

It allow you to create runtime selection, and in one click you can download the reports without additional programing changes.

The challenge with web based application is you need a knowledge on HTML, CSS, JavaScript, PHP, Java, and Object Orientation. WOW solve this problem by by allowing the programmer to simply specify SQL statements while WOW framework guarantees all needed HTML, CSS, JavaScript related stuffs.

Features

WOW CE also supports real-time access to Oracle, SQL Server, MySQL, and other major databases. Generates industry-standard HTML, CSS, and JavaScript for IBM i, Microsoft Windows, and Linux systems

Requirements: IBM OS/400 V5R1 or later.

You can perform bellow using WOW.

• Data Inquiry

• Data Maintenance

• Business Intelligence

• Dashboards/Graphics

• Customer Self Service

• Business to Business

• Government Data Solutions