Informix How To

Informix Application Contents – Part 2

Before reading this, please read the first part of this article series to make sure you have the required knowledge to continue. Thank you and we hope this article helps you understand the source code better.

I. Behind the Scenes

Here we will explain what is in the code and what each class contains. We won’t go over all the code, as that would probably require a whole book, but we will introduce each class so you have a simple understanding of their functionality. That way, in case you want to change something specific, you know where to look.

-Under the TBXML Folder

Here you will find two classes (NSDataAdditions and TBXML). This is the open source code we talked about in the first part of this article series. We didn’t change anything within these two classes and you shouldn’t either. We used it as it was provided.

These two classes are what we use to parse through the XML file we receive from the web service. They contain different sets of functions that allow us to easily parse through the XML file and grab the desired information.

-Under the Classes Folder

This is, as you might have guessed, the most extensive folder. This will take a while but we will explain all the classes so you can understand their functionality.

CustomGUIAppDelegate(.h, .m)

Assuming you have some knowledge in Objective-C, you will know that this is where all the main functions occur. This is where the views are loaded when the application launches and where everything else is closed when the application closes.

Inside this code not only do we load the views (which we will explain in detail later) but we also create our managedObjectModels and managedObjectContexts. We have actually created one of them dynamically based on what is grabbed from the database and not with an xcdatamodel, but you will find that it is not that complicated to create them programmatically. On the other hand, if you have created these programmatically before, then this won’t be new to you except for the fact that we actually use two managedObjectModels and two managedObjectContexts (as well as two PersistentStoreCoordinators) which might seem a bit odd. Why do we do this? Pretty simple. In the first one (the one with the xcdatamodel), we store everything regarding the table content. That is, we store the information about the table in the database (i.e. if we are using a customer table, we store the column names, column types, etc). This managedObjectModel has a relationship attribute to another table called “attributes”. This is a to-many relationship and we do this so we can actually store as many attributes for a row as we want. Since it is all under the same database, some of the tables have more attributes (columns) than the other, and if we didn’t have a to-many relationship and instead we had a specific amount of attributes assigned, then we could run short of attributes. But you might be asking yourself, what happens if there is no connection to the database? Good question. That is where the second managedObjectModel comes into play.

In the second managedObjectModel (which we created programmatically), we store the information regarding the table itself. That is, we store the column names, the amount of columns and everything regarding the table. That way, if the application launches and there is no connection (therefore there is no way to get the information about the table regarding the number of columns, column names, column types, etc.) we can use this managedObjectModel determine the number of columns, column names etc. from the data that was already stored in Core Data, so we can once again create the first managedObjectModel.

Take note, that in order for the application to do this, it must have connected to the database at least once in the lifetime of the application, or else it would just be impossible to get this information.

CDViewController (.h, .m)

This is where everything related to Core Data happens. Here, not only do we have the code for the Core Data view itself (that is, the view that shows what we have in Core Data) but we also have the code to add and delete information from Core Data as well.

ColNamesAndTable(.h, .m)

As the name suggests, we use this table to store the column names (and information about the column such as data type and length) and the tableName. That way we can later find which tableName we are currently using and based on that we can determine which are the columns that are applicable to that tableName.

We also have a method that returns the number of columns the table contains. We use this method to return that value to the AppDelegate so we can use it for loops (and other operations) in other classes.

dbViewController (.h, .m)

This class simply takes care of the Informix database view. Basically, this class gathers the information from the Informix database and displays it to the user.

GenericTable(.h, .m)

This class is pretty simple and there is really nothing to it. It only contains a NSMutableArray called attributes that we use to store the attributes of a specific element. We did this in a new class as a way to keep things a little bit more organized but this array could have just as well been added to another class and this class wouldn’t be necessary.

PickTableName (.h, .m)

This class controls the first view the user sees. What we have here is a pickerView, which is the little scrolling wheel that is familiar to the iPhone users. There is a method that gathers the table names from the database (using one of the generic web services we have created) and stores them in an NSMutableArray. This NSMutableArray is later used to display the table names in the pickerView. Using the code provided by Apple already, we can easily know which table is being selected from the pickerView and we store that as theTableName (the variable from the appDelegate).

SyncViewController (.h, .m)

This controls the syncing view but more than that, it controls the syncing itself. It has two methods for the synchronization. It has one for Core Data, where it verifies if the row is already in Core Data, and if it is not, then it inserts it (by calling on a method from CDViewController).

The other method takes care of syncing from Core Data into Informix. It checks if there are any new rows in Core Data. If there are new rows, it inserts them into the Informix database. Then it checks if any of the rows have been updated in Core Data, if they have been, it updates it in the Informix database. And last, but not least, it checks if any of the rows have been deleted, if they are, it deletes them from the Informix database.

TextFieldViewControler (.h, .m)

This class takes care of the insert view. Apart from that, it also has a few methods that work with the SyncViewController class. Here you will find the methods that take care of adding new rows into Informix, as well as the method that updates and deletes rows in the Informix database.

UpdateCoreDataViewController (.h, .m)

This class takes care of the view when the user clicks on an element in the Core Data view. What it displays is editable text fields so the user can edit that data that specific row already contains. It also takes care of the unique key. That is, it detects which column is the unique key and it doesn’t make it editable, because since it is unique, it should be editable by only a database administrator or some privileged users.

UniqueKeyViewController (.h, .m)

This class functions very similarly to the PickTableName class. It displays a pickerView and the information that is displayed in the pickerView is the column names. The column names are gathered from the Informix catalog table via the web service and stored in a NSMutableArray. Then based on the code provided by Apple, we can determine which column the user selected and we set it as the unique key. We also assign this value to the variable uniqueKey from the appDelegate. That way we can use that unique key in other classes for further operations.

ViewDBDetails (.h, .m)

This class controls what the user sees when they click on a row in the database view. Basically it just displays (in a non-editable way) all the data that the row contains. It looks a lot like the UpdateCoreDataViewController except the fields are not editable.

-Under the Other Sources Folder

We didn’t modify these classes at all, and you shouldn’t have to do so either.

-Under the Resources Folder

Attributes(.h, .m)

This is the class we use to store the attributes information. We store the attributeName (which is just the column name) and the attributeValue.

Event (.h, .m)

This is the class that contains the “attributes” for the Informix Database information. It is the one we use as reference for Core Data, in order to store the information from the Informix Database. There are also two internal attributes defined: isNew and edited. We use these attributes to determine whether the row in Core Data was added from the phone (therefore it is new) and if it was updated. We then use these values to know what to do when we sync with the Informix Database.

We also have an attributes relationship, which we connect to another table so we can store the attributes themselves. This is, as previously mentioned, a to-many relationship.

Details (.h, .m)

This class is similar to the Event class, except we use this class as a reference for the table details. This is the class we use to store the columns names, number of columns, column type, and table name.

CustomGUI (.xcdatamodel)

This is the data model we use to store the content within the table.

.

.

PickTableName (.xib) and UniqueKeyViewController (.xib)

These files are views made with Interface Builder. All they contain is a pickerView and the connection to its respective methods so it knows what to print within the pickerView. These methods are taken from their respective classes (PickTableName with PickTableName.h and .m, and UniqueKeyViewController with UniqueViewController.h and .m).

-Misc

libz.dylib

As you can see, this package has been added and it doesn’t belong to any of the previously mentioned folders. We need this package so the TBXML code can actually work. Although the package will already be there once you have downloaded our source code, here is how to add it to your project in case you want to start your own project from scratch:

1. Expand the “Targets”.

2. Right click on the file that appears, it should be the name of your project.

3. Then click on Add > Existing Frameworks…

4. In the new window, there is a + sign on the left bottom corner. Click on it.

5. Scroll down to the very bottom and you will find libz.dylib.

6. Select it and click add.

7. Then close the window and it should have been added.

II. The Views

.

.

1.Pick the table

This is the first view the user has. Here they have a pickerView that will let them choose whichever table they desire to work with.
.
.
.

.

.

.

2.Pick The Unique Column

This view comes right after the user has selected the desired table. Here the pickerView will show the columns that belong to that specific table. Based on that, the user needs to select the column that serves as the unique key. Note: this view only comes up if the table doesn’t have a primary key constraint. If it does, the application automatically selects the Unique key.

.
.

.Below we will see the rest of the views

.

1. The first thing the user will see once he/she has selected the unique key is the database view. Here they will see the rows of the selected table that are in the Informix database. They can click on whichever element (row in the table) they desire and this will take them to a new view that will display the column names and the data in the row (it will display all the columns/attributes and their corresponding values).

.

.

.

2. The second view the user will have available (the second tab at the bottom of the screen) is the insert view. In this view the user can insert a new element (a table row). This new element can either be inserted directly to Informix database or it can be inserted to Core Data. Note: this view is generated once the user has chosen the table and the unique key. Based on that information, the application gathers the column names and generates the insert view.

.

.

3. The third view (the third tab at the bottom of the screen) is for the Core Data view. Here the user can see the data that has already been stored in Core Data. It is pretty similar to the Database view, except this one contains the Core Data view instead. The user can also click on whichever element he/she desires. This time, the view that comes up, lets the user edit every single attribute except for the unique key attribute. All the user has to do is just edit the data. The changes will be stored in Core Data automatically. By going back to the Core Data view the user will notice the changes.

.

4. The fourth and last view (the fourth and last tab) contains the sync buttons. These two buttons let the user manually sync the new information from either Informix to Core Data or from Core Data to Informix. It is worth noting that once the user opens the application for the first time, all the information from Informix is synced into Core Data and every time the user closes the application, all the information from Core Data will be synced into Informix (that is, if there is something new to sync). Then after the application is opened again, the application checks if there are any changes from Core Data that weren’t saved the last time and it syncs them into Informix and then it checks if there is something new from Informix and syncs it into Core Data. Then the process just keeps repeating itself with every execution and closing of the application.

By now you must be eager to get your hands on the source code. However, we have one more article in this three-part series. The source code can be downloaded here.

Add comment September 13, 2011

<?php
# Input SQL stmt - need to receive from the app via POST
$sql = $_POST['sql_stmt'];
$table = $_POST['tableName'];

# setup some variables for our connection.
# change the following for your environment.
$host = HOSTNAME; #Hostname of where Informix is running.
$port = PORTNUM;  # port # that Informix is listening on.
$server = SERVERNAME;  # The INFORMIXSERVER.
$database = DATABASENAME; # Name of the stores_demo database.
$protocol = PROTOCOLNUM;  # Protocol for the connection.
$user = USERNAME;  # Username to connect as.
$password = PASSWORD; # Password for the user.

#setup our ‘dsn’
$dsn  = "informix:host={$host};service={$port}";
$dsn .= ";database={$database};protocol={$protocol}";
$dsn .= ";server={$server}";

# connect to the database.
$db = new PDO($dsn,$user,$password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

# perform the query.
#$count = $db->exec($sql); #this is what we use when we didn't deal with error

# perform the query and if there is an error, return it.
try {
	$count = $db->exec($sql);
	}
catch (PDOException $e) {
	$thereWasAnEror = 'YES';
		echo $thereWasAnError;

		#create a simplexml.
		$xml = simplexml_load_string("");

		$errorMessage = 'Error: ' . $e->getMessage();
		$errorMessage = strrev($errorMessage);

                #we are deleting the last parenthesis which contains unecessary info.
		$errorMessage = preg_replace("/\)[^\)]+\(/","",$errorMessage, 1);

		$errorMessage = strrev($errorMessage);

		$node = $xml->addChild("$table");
		$node->addChild('ThereWasAnError', 'yes');
		$node->addChild('ErrorMessage',"$errorMessage");

		#display the xml;
		print $xml->asXML();

		exit();
	}

# create the select statement to verify if the data was inserted
$sql = "SELECT * FROM $table";

# perform the query
$stmt = $db->query($sql);

# fetch all the data.
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

#close the query.
$stmt->closeCursor();

# we are going to output this in an xml format so
# lets tell the browser to expect xml
header("Content-Type: text/xml");

#create a simplexml.
$xml = simplexml_load_string("");

# loop thru our records adding each one to our xml.
foreach ($rows as $key => $value)
{
        $node = $xml->addChild("$table");
        $node->addChild('ThereWasAnError', 'no');

        foreach ($value as $k => $v)
        {
                #fix up any special characters.
                $v = htmlentities($v);
                $node->addChild($k,$v);
        }
}

#display the xml;
print $xml->asXML();

?>

<?php
# Input SQL stmt - need to receive from the app via POST
$sql = $_POST['sql_stmt'];
$table = $_POST['tableName'];

if($table == NULL)
{
$table = 'tablename';
}
# setup some variables for our connection.
# change the following for your environment.
$host = HOSTNAME; #Hostname of where Informix is running.
$port = PORTNUM;  # port # that Informix is listening on.
$server = SERVERNAME;  # The INFORMIXSERVER.
$database = DATABASENAME; # Name of the stores_demo database.
$protocol = PROTOCOLNUM;  # Protocol for the connection.
$user = USERNAME;  # Username to connect as.
$password = PASSWORD; # Password for the user.

#setup our ‘dsn’
$dsn  = "informix:host={$host};service={$port}";
$dsn .= ";database={$database};protocol={$protocol}";
$dsn .= ";server={$server}";

# connect to the database.
$db = new PDO($dsn,$user,$password);

# perform the query.
$count = $db->exec($sql);
# perform the query
 $stmt = $db->query($sql);

# fetch all the data.
 $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

#close the query.
 $stmt->closeCursor();

# we are going to output this in an xml format so
 # lets tell the browser to expect xml
 header("Content-Type: text/xml");

#create a simplexml.
 $xml = simplexml_load_string("");

# loop thru our records adding each one to our xml.
 foreach ($rows as $key => $value)
 {
 $node = $xml->addChild("$table");
 foreach ($value as $k => $v)
 {
 #fix up any special characters.
 $v = htmlentities($v);
 $node->addChild($k,$v);
 }
 }

#display the xml;
 print $xml->asXML();

?>

<?php

# Input variables - need to receive from the app via POST
$customer_num = $_POST['customer_num'];
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$company = $_POST['company'];
$address1 = $_POST['address1'];
$address2 = $_POST['address2'];
$city = $_POST['city'];
$state = $_POST['state'];
$zipcode = $_POST['zipcode'];
$phone = $_POST['phone'];

# setup some variables for our connection.
# change the following for your environment.
$host = HOSTNAME; #Hostname of where Informix is running.
$port = PORTNUM;  # port # that Informix is listening on.
$server = SERVER;  # The INFORMIXSERVER.
$database = stores_demo; # Name of the database.
$protocol = PROTOCOL;  # Protocol for the connection.
$user = USER;  # Username to connect as.
$password = PASSWORD; # Password for the user.

#setup our ‘dsn’
$dsn  = "informix:host={$host};service={$port}";
$dsn .= ";database={$database};protocol={$protocol}";
$dsn .= ";server={$server}";

# connect to the database.
$db = new PDO($dsn,$user,$password);

# create the insert statement
$sql = "INSERT INTO customer VALUES ($customer_num, '$fname', '$lname', '$company', '$address1', '$address2', '$city', '$state', $zipcode, '$phone')";

# perform the insert stmt.
$count = $db->exec($sql);

# create the select statement to verify if the data was inserted
$sql = "SELECT * FROM customer WHERE customer_num = $customer_num";

# perform the query
$stmt = $db->query($sql);

# fetch all the data.
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

#close the query.
$stmt->closeCursor();

# we are going to output this in an xml format so
# lets tell the browser to expect xml
header("Content-Type: text/xml");

#create a simplexml.
$xml = simplexml_load_string("<?xml version='1.0' encoding='utf-8'?><cust/>");

# loop thru our records adding each one to our xml.
foreach ($rows as $key => $value)
{
     $node = $xml->addChild("customer");
     foreach ($value as $k => $v)
     {
           #fix up any special characters.
           $v = htmlentities($v);
           $node->addChild($k,$v);
     }
}

#display the xml;
print $xml->asXML();
?>

function addRecordSubmit(event)
{
   var postURL = "http://localhost:8080/custdemoapp/ins_service.php";
   var onloadHandler = function() { xmlLoaded(xmlRequest); };	// The function to call when the feed is loaded;
   //currently calls the XMLHttpRequest load snippet

   // Customer number
   var cust_num = document.getElementById("textField");	// replace with ID of text field
   // Text field code
   cust_num = cust_num.value;

   var fname = document.getElementById("textField1");
   fname = fname.value;
   var lname = document.getElementById("textField2");
   lname = lname.value;
   var company = document.getElementById("textField3");
   company = company.value;
   var addr1 = document.getElementById("textField4");
   addr1 = addr1.value;
   var addr2 = document.getElementById("textField5");
   addr2 = addr2.value;
   var city = document.getElementById("textField6");
   city = city.value;
   var state = document.getElementById("textField7");
   state = state.value;
   var zip = document.getElementById("textField8");
   zip = zip.value;
   var phone = document.getElementById("textField9");
   phone = phone.value;

   var paramsToSend = "customer_num="+cust_num+"&fname="+
        fname+"&lname="+lname+"&company="+company+
        "&address1="+addr1+"&address2="+addr2+"&city="+
        city+"&state="+state+"&zipcode="+zip+"&phone="+
        phone;

   // XMLHttpRequest setup code
   var xmlRequest = new XMLHttpRequest();
   xmlRequest.onload = onloadHandler;
   xmlRequest.open("POST", postURL, true);
   xmlRequest.setRequestHeader("Cache-Control", "no-cache");
   xmlRequest.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
   xmlRequest.send(paramsToSend);
}

// Called when an XMLHttpRequest loads a feed; works with the XMLHttpRequest setup snippet
function xmlLoaded(xmlRequest)
{
   if (xmlRequest.status == 200) {
      // Parse and interpret results
      // XML results found in xmlRequest.responseXML
      // Text results found in xmlRequest.responseText
   }
   else {
      alert("Error fetching data: HTTP status " + xmlRequest.status);
   }
}

function  openForm(event)
{
   // Insert Code Here
   // Set the current view of a StackLayout
   var stackLayout = document.getElementById('stackLayout').object;

   stackLayout.setCurrentView('addRecordForm'); // Replace with id of new view (or the view element itself)
}

informix@yourserver>touch /path/to/space
informix@yourserver>chmod 660 /path/to/space
informix@yourserver>onspaces -c -S sbspace -p /path/to/space -o 0 -s 10000

informix@yourserver>vi $INFORMIXDIR/etc/$ONCONFIG
#Add 'sbspace' to the line below
SBSPACENAME   sbspace
#Bounce the server for the change to take effect
informix@yourserver>onmode -ky
infomrix@yourserver>oninit

informix@yourserver>dbaccess - -
>DATABASE stores_demo;
>CREATE TABLE styelsheet (style CLOB);
>INSERT INTO stylesheet (style) VALUES ( FILETOCLOB("/path/to/xml2json.xslt", "client"));
>CLOSE DATABASE;
CTRL-C

informix@yourserver>dbaccess - -
>DATABASE stores_demo;
> create temp table xmltable (xml CLOB, xsl CLOB);
> INSERT INTO xmltable  (xml) SELECT genxmlelemclob(customer, "customer")::clob from customer where customer_num=108;
> update xmltable set xsl = (SELECT * FROM stylesheet);
> SELECT xsltransform(xml, xsl) from xmltable;
(expression)  {"customer":{"row":{"customer_num":108,"fname":"Donald         ",
              "lname":"Quinn          ","company":"Quinn's Sports      ","addre
              ss1":"587 Alvarado        ","city":"Redwood City   ","state":"CA"
              ,"zipcode":94063,"phone":"415-544-8729      "}}}
>CLOSE DATABASE;
CTRL-C

if ( `test x${TWITTERID} != x` ) then
    if ( `test x${TWITTERPASS} != x` ) then
          curl --basic --user $TWITTERID:$TWITTERPASS \
          --data status="`cat $MAILBODY`"\

http://twitter.com/statuses/update.xml

   else
         echo "TWITTER PASSWORD not set"
    fi
else
         echo "TWITTER ID not set"
fi

if ( `test x${TWITTERID} != x` ) then</span>
   if ( `test x${TWITTERPASS} != x` ) then
       if ( `test $EVENT_SEVERITY -ge 3`) then
                  curl --basic --user $TWITTERID:$TWITTERPASS \
                  --data status="`cat $MAILBODY`"\

http://twitter.com/statuses/update.xml

        fi
   else
      echo "TWITTER PASSWORD not set"
   fi
else
   echo "TWITTER ID not set"
fi

<?php
# setup some variables for our connection.
# change the following for your environment.
$host = HOSTNAME; #Hostname of where Informix is running.
$port = PORTNUM;  # port # that Informix is listening on.
$server = SERVER;  # The INFORMIXSERVER.
$database = stores; # Name of the stores_demo database.
$protocol = PROTOCOL;  # Protocol for the connection.
$user = USERNAME;  # Username to connect as.
$password = PASSWORD; # Password for the user.

#setup our ‘dsn’
$dsn  = "informix:host={$host};service={$port}";
$dsn .= ";database={$database};protocol={$protocol}";
$dsn .= ";server={$server}";

# connect to the database.
$db = new PDO($dsn,$user,$password);

# create the select statement
$sql = "SELECT * FROM customer";

# perform the query.
$stmt = $db->query($sql);

# fetch all the data.
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

#close the query.
$stmt->closeCursor();

# we are going to output this in an xml format so
# lets tell the browser to expect xml
header("Content-Type: text/xml");

#create a simplexml.
$xml = simplexml_load_string("<?xml version='1.0' encoding='utf-8'?><cust/>");

# loop thru our records adding each one to our xml.
foreach ($rows as $key => $value)
{
        $node = $xml->addChild("customer");
        foreach ($value as $k >= $v )
        {
                #fix up any special characters.
                $v = htmlentities($v);
                $node->addChild($k,$v);
        }
}

#display the xml;
print $xml->asXML();
?>