Program with Informix .NET extensions

Document options Document options requiring JavaScript are not displayed Discuss Rate this page Help us improve this content

Level: Intermediate

Kanchana Padmanabhan (kanchana@us.ibm.com), Software Developer, IBM

17 Mar 2005

Some Informix data types cannot be loaded into .NET built-in types without losing precision, functionality, or both. Discover how to avoid compromising your data types, and find out how to use the following Informix data types included in the IBM® Informix® .NET provider: IfxBlob, IfxClob, IfxMonthSpan, IfxTimeSpan, IfxDateTime, and IfxDecimal.

Overview

The Informix .NET provider version 2.90.TC1 and later includes classes and structures to hold these Informix data types. This article explains when and how you should use these data types.

The data types discussed here are:

• IfxBlob
• IfxClob
• IfxMonthSpan
• IfxTimeSpan
• IfxDateTime
• IfxDecimal

Back to top

IfxBlob and IfxClob

The BLOB and CLOB Informix data types support random access to the data. This random access feature allows you to seek and read through the smart large object as if it were an operating system file. If you use .NET built-in types (such as Byte or String) to hold BLOB and CLOB data you can still read the data but you cannot access the data randomly. Use the classes IfxBlob and IfxClob when you want random access to the data.

The IfxBlob and IfxClob classes hide the details of the LO-specification structure, LO file descriptor, smart large object locator, LO-status structure, and other complications usually associated with working with BLOB and CLOB data types. You can use the functionality of the structures through various properties and methods of the class without having to understand the structures. You do, however, still have access to the smart large object locator.

Reading BLOB data using IfxBlob

You need to do these steps to read a BLOB data using an IfxBlob object:

• Using an IfxConnection object, connect to a database.
• Using an IfxCommand object, execute a query that selects a BLOB column. This will create an IfxDataReader object.
• Call GetIfxBlob on the IfxDataReader. This will return an IfxBlob object.
• Open the IfxBlob object for reading.
• Read data from the IfxBlob object into a byte buffer.
• Close the BLOB when done with reading.

Listing 1 is a code snippet that demonstrates how to select data from a BLOB column using IfxBlob.

				
.
.
.
using IBM.Data.Informix;
.
.
.
//Declare and initialize variables
IfxBlob myBlobVal;
byte[] myByteBuff = new  byte[5000];
Int64 dataLenRead;

//Establish a database connection. Execute a query that selects a BLOB column
myIfxConn.Open();
myCmd = myIfxConn.CreateCommand();
myCmd.CommandText = "SELECT blobCol FROM blobTab";
myDR = myCmd.ExecuteReader();

//Travel through the DataReader
while(myDR.Read())
{
    if( !myDR.IsDBNull(0) )
    {
        myBlobVal = myDR.GetIfxBlob(1); //Get the IfxBlob object
        myBlobVal.Open(IfxSmartLOBOpenMode.ReadOnly); //Open the BLOB for read-only access
        do
        {
            dataLenRead = myBlobVal.Read(
                             myByteBuff,		//buffer to receive data
                             0,				//offset within buffer
                             myByteBuff.Length,		//length of the buffer
                             0,				//offset within the BLOB
                             IfxSmartLOBWhence.Current 	//Position from where to read the BLOB
            );
        }while(dataLenRead != 0);
        myBlobVal.Close();
     }
}
.
.
.

Creating and inserting a CLOB column on a database using IfxClob

You need to do these steps to create a CLOB column on a database using an IfxClob object:

• Using an IfxConnection object, establish a connection to the database.
• Create an IfxClob object by calling the GetIfxClob method on the IfxConnection object.
• Assign appropriate values to the properties in the IfxClob object.
• Open the IfxClob object for writing into.
• Write data into the IfxClob object.
• Close the IfxClob object.
• Create an IfxCommand object for doing a parameterized insert of the CLOB value into the database.
• Bind the IfxClob object as a parameter.
• Execute the insert query.

Listing 2 is a code snippet that demonstrates how to insert a new CLOB column using IfxClob.

				
.
.
.
using IBM.Data.Informix;
.
.
.
//Declare and initialize variables
IfxClob myClobVal;
char[] myCharBuff = new char(5000);
Int64 dataLenRead, dataLenWritten;

myIfxConn.Open();

//Get an IfxClob object
clobColVal = myIfxConn.GetIfxClob();

//Set appropriate values for the properties
myClobVal.EstimatedSize = 49000;
myClobVal.ExtentSize = 100;
myClobVal.Flags =  (int)IfxSmartLOBCreateTimeFlags.KeepAccessTime
                        | (int)IfxSmartLOBCreateTimeFlags.NoLog;
myClobVal.MaxBytes = 45000;
myClobVal.SBSpace = "sbspace1";

//Open the CLOB for writing
myClobVal.Open(IfxSmartLOBOpenMode.ReadWrite);

strmRdr = new StreamReader("clob.data");
do
{
    //Read data from some file
    dataLenRead = strmRdr.Read(clobDataBuff, 0, clobDataBuff.Length);
    dataLenWritten = myClobVal.Write(
                         clobDataBuff,			//buffer that holds data to be written
                         0,				//offset within the buffer
                         dataLenRead,			//number of bytes to be written
                         0,				//offset within the CLOB
                         IfxSmartLOBWhence.Current	//Position within the CLOB
    );
}while(dataLenRead != 0 && dataLenWritten !=0);

strmRdr.Close();
myClobVal.Close();

myIfxCmd = myIfxConn.CreateCommand();
myIfxCmd.CommandText = "INSERT INTO clobTab(clobCol) VALUES(?)";

//Bind the IfxClob value and execute the insert query
clobParam = new IfxParameter(null, myClobVal);
myIfxCmd.Parameters.Add(clobParam);
myIfxCmd.ExecuteNonQuery();

Storing and reusing smart large object locators

Smart large object locators do not change from connection to connection. This allows you to store them and use them during later sessions. If you have the smart large object locator of a BLOB or CLOB you can access the data without executing any SQL commands.

Listing 3 is a code snippet that demonstrate how to store the locator of a CLOB column outside the database and then access the CLOB data through a connection

				
.
.
.
using IBM.Data.Informix;
.
.
.
//Get an IfxClob object from a DataReader
myClobVal = myIfxDR.GetIfxClob(colnumber);

//Get the IfxSmartLOBLocator object
IfxSmartLOBLocator mySBLocator = myClobVal.GetLocator();

//Get the locator as a byte array
byte[] myBytes = mySBLocator.ToBytes();
//This byte array can now be stored in a file for later use.
.
.
.
//Create an IfxSmartLOBLocator object with the byte array stored earlier
IfxSmartLOBLocator myNewSBLocator = new IfxSmartLOBLocator(myBytes);

//Instantiate an IfxClob object passing the constructor a connection 
//and IfxSmartLOBLocator object
myNewClobVal = new IfxClob(myIfxConn, myNewSBLocator);

//The IfxClob object is now ready for reads
myNewClobVal.Open(IfxSmartLOBOpenMode.ReadOnly);
myNewClobVal.Read(...);
.
.
.
myNewClobVal.Close();

Back to top

IfxMonthSpan, IfxTimeSpan

The Informix data type INTERVAL represents a span of time with positive or negative value. Informix INTERVAL values differ in their precision based on the start and end time units. The .NET TimeSpan built-in type cannot hold the Informix INTERVAL YEAR TO MONTH type. Also, TimeSpan has a fixed precision of day to 10^-7 seconds so any INTERVAL value stored in one will take on that precision.

The IfxMonthSpan and IfxTimeSpan structures overcome these problems. IfxMonthSpan represents a time interval in months. Use it to store Informix INTERVAL types that include the time units year, month or both. IfxTimeSpan represents a time interval in fractional seconds. Use it to hold Informix INTERVAL types that include only time units in the range of day to fractional seconds.

Inserting INTERVAL values using IfxMonthSpan and IfxTimeSpan

You need to do these steps to insert INTERVAL data using IfxMonthSpan and IfxTimeSpan objects:

• Create an IfxCommand object for executing a query that inserts INTERVAL values.
• Create IfxMonthSpan and IfxTimeSpan objects and assign appropriate values.
• Bind the IfxMonthSpan and IfxTimeSpan objects as parameters.
• Execute the command.

Listing 4 is a code snippet that demonstrates inserting IfxMonthSpan and IfxTimeSpan values into a database.

				
.
.
.
using IBM.Data.Informix;
.
.
.
//Declare and initialize variables
IfxMonthSpan y2yMS, y2mMS;
IfxTimeSpan d2hTS, h2f5TS;
IfxParameter y2yParam, y2mParam, d2hParam, h2f5Param;

myIfxCmd.CommandText = "INSERT INTO invTab(y2y, y2m, d2h, h2f5) VALUES (?, ?, ?, ?)";

//Create the IfxMonthSpan and IfxTimeSpan objects
y2yMS = IfxMonthSpan.Parse("689", IfxTimeUnit.Year, IfxTimeUnit.Year);
y2mMS = new IfxMonthSpan(10,3);
d2hTS = new IfxTimeSpan(20, 10, 23, IfxTimeUnit.Hour);
h2f5TS = IfxTimeSpan.Parse("100:30:25.00894", IfxTimeUnit.Hour, IfxTimeUnit.Fraction5);

//Bind the IfxMonthSpan and IfxTimeSpan objects as parameters
y2yParam = new IfxParameter(null, y2yMS);
y2mParam = new IfxParameter(null, y2mMS);
d2hParam = new IfxParameter(null, d2hTS);
h2f5Param = new IfxParameter(null, h2f5TS);
myIfxCmd.Parameters.Add(y2yParam);
myIfxCmd.Parameters.Add(y2mParam);
myIfxCmd.Parameters.Add(d2hParam);
myIfxCmd.Parameters.Add(h2f5Param);

//Execute the insert query
myIfxCmd.ExecuteNonQuery();

Selecting INTERVAL values using IfxMonthSpan and IfxTimeSpan

You need to do these steps to select INTERVAL data using IfxMonthSpan and IfxTimeSpan objects

• Using an IfxCommand object, execute a query that selects INTERVAL year through month and INTERVAL day through fractional seconds values. This will return an IfxDataReader object.
• Call GetIfxMonthSpan method on the DataReader object to get an IfxMonthSpan object.
• Call GetIfxTimeSpan method on the DataReader object to get an IfxTimeSpan object.

Listing 5 is a code snippet that demonstrates retrieving an INTERVAL value from a database.

				
.
.
.
using IBM.Data.Informix;
.
.
.
//Declare and initialize variables
IfxMonthSpan y2mMS;
IfxTimeSpan h2f5TS;

//Execute a query that selects INTERVAL values
myIfxCmd.CommandText = "SELECT y2m, h2f5 FROM invTab;
myIfxDR = myIfxCmd.ExecuteReader();

while(myIfxDR.Read())
{
    if( !myIfxDR.IsDBNull(0) )
    {
        y2mMS = myIfxDR.GetIfxMonthSpan(0); //Get the IfxMonthSpan object
    }
    
    if( !myIfxDR.IsDBNull(1) )
    {
        h2f5TS = myIfDR.GetIfxTimeSpan(1); //Get the IFxTimeSoan object
    }
}

Back to top

IfxDateTime

The Informix data type DATETIME represents a point in time. DATETIME values differ in their precision depending on their start and end time units. The .NET DATETIME built-in type allows only 3 different precisions; Year to Day, Year to Second, and Year to 10^-7 seconds.

Use the IfxDateTime structure to hold the Informix DATETIME data type. The IfxDateTime structure supports all precisions allowed in the Informix DATETIME data type.

Inserting DATETIME values using IfxDateTime

You need to do these steps to insert DATETIME data using IfxDateTime objects:

• Create an IfxCommand object for executing a query that inserts DATETIME values.
• Create IfxDateTime objects and assign appropriate values.
• Bind the IfxDateTime objects as parameters.
• Execute the command.

Listing 6 is a code snippet that demonstrates inserting IfxDateTime values into a database.

				
.
.
.
using IBM.Data.Informix;
.
.
.
//Declare and initialize variables
IfxDateTime y2mDT, h2sDT, s2f3DT, y2fDT;
IfxParameter y2mParam, h2sParam, s2f3Param, y2fParam;

myIfxCmd.CommandText = "INSERT INTO dtTab(y2m, h2s, s2f3, y2f ) VALUES (?, ?, ?, ?);

//Create the IfxDateTime objects
y2mDT = new IfxDateTime(1999, 12, IfxTimeUnit.Month);
h2sDT = IfxDateTime.Parse("12:35:45", IfxTimeUnit.Hour, IfxTimeUnit.Second);
s2f3DT = IfxDateTime(34562998);
y2fDT = DateTime.Now;

//Bind the IfxDateTime objects as parameters
y2mParam = new IfxParameter(null, y2mDT);
h2sParam = new IfxParameter(null, h2sDT);
s2f3Param = new IfxParameter(null, s2f3DT);
y2fParam = new IfxParameter(null, y2fDT);;
myIfxCmd.Parameters.Add(y2mParam);
myIfxCmd.Parameters.Add(h2sParam);
myIfxCmd.Parameters.Add(s2f3Param);
myIfxCmd.Parameters.Add(y2fParam);

//Execute the insert query
myIfxCmd.ExecuteNonQuery();

Selecting DATETME values using IfxDateTime

You need to do these steps to select DATETIME data using IfxDateTime objects:

• Using an IfxCommand object, execute a query that selects DATETIME values. This will return an IfxDataReader object.
• Call GetIfxDateTime method on the DataReader object to get an IfxDateTime object.

Listing 7 is a code snippet that demonstrates retrieving a DATEIME value from a database.

				
.
.
.
using IBM.Data.Informix;
.
.
.
//Declare and initialize variables
IfxDateTime h2sDT, y2fDT;

//Execute a query that selects DATETIME values
myIfxCmd.CommandText = "SELECT h2s, y2f FROM dtTab;
myIfxDR = myIfxCmd.ExecuteReader();

while(myIfxDR.Read())
{
    if( !myIfxDR.IsDBNull(0) )
    {
        h2sDT = myIfxDR.GetIfxDateTime(0); //Get the IfxDataTime object
    }
    if( !myIfxDR.IsDBNull(1) )
    {
        y2fDT = myIfDR.GetIfxDateTime(1);  //Get the IfxDataTime object
    }
}

IfxDecimal

Informix DECIMAL values can either be floating-point or fixed-point numbers. The maximum precision supported by either type is 32. There are two reasons why the .NET decimal built-in type cannot hold Informix DECIMAL values. Firstly, the maximum precision of .NET decimal built-in type is 29 as against 32 in the Informix DECIMAL type. Seconly, .NET decimal built-in type always has fixed precision as against Informix DECIMAL type that can be floating-point.

Use the IfxDecimal structure to hold the Informix DECIMAL data type. The IfxDecimal structure supports both floating-point and fixed-point decimals. The maximum precision allowed in IfxDecimal is 32.

Inserting DECIMAL values using IfxDecimal

You need to do these steps to insert DECIMAL data using IfxDecimal objects:

• Create an IfxCommand object for executing a query that inserts DECIMAL values.
• Create IfxDecimal objects and assign appropriate values.
• Bind the IfxDecimal objects as parameters.
• Execute the command.

Listing 8 is a code snippet that demonstrates inserting IfxDecimal values into a database.

				
.
.
.
using IBM.Data.Informix;
.
.
.
//Declare and initialize variables
IfxDecimal dec31, dec20_5;
IfxParameter dec31Param, dec20_5Param;

myIfxCmd.CommandText = "INSERT INTO decTab(dec31, dec20_5 ) VALUES (?, ?);

//Create the IfxDecimal objects
dec31 = new IfxDecimal(43234532452534243.56454565652);
dec20_5 = IfxDecimal.Parse("83345623984587.15245");

//Bind the IfxDecimal objects as parameters
dec31Param = new IfxParameter(null, dec31);
dec20_5Param = new IfxParameter(null, dec20_5);
myIfxCmd.Parameters.Add(dec31Param);
myIfxCmd.Parameters.Add(dec20_5Param);

//Execute the insert query
myIfxCmd.ExecuteNonQuery();

Selecting DECIMAL values using IfxDecimal

You need to do these steps to select DECIMAL data using IfxDecimal objects:

• Using an IfxCommand object, execute a query that selects an DECIMAL values. This will return an IfxDataReader object.
• Call GetIfxDecimal method on the DataReader object to get an IfxDecimal object.

Listing 9 is a code snippet that demonstrates retrieving a DECIMAL value from a database.

				
.
.
.
using IBM.Data.Informix;
.
.
.
//Declare and initialize variables
IfxDateTime dec31, dec20_5;

//Execute a query that selects DECIMAL values
myIfxCmd.CommandText = "SELECT dec31, dec20_5 FROM decTab";
myIfxDR = myIfxCmd.ExecuteReader();

while(myIfxDR.Read())
{
    if( !myIfxDR.IsDBNull(0) )
    {
        dec31 = myIfxDR.GetIfxDecimal(0); //Get the IfxDecimal object
    }
    if( !myIfxDR.IsDBNull(1) )
    {
        dec20_5 = myIfDR.GetIfxDecimal(1); //Get the IfxDecimal object
    }
}

Arithmetic operations

All data types below can be used for arithmetic operations. Arithmetic operators such as '+' (addition), '-' (subtraction), '*' (multiplication), and '/' (division) are overloaded for these structures.

• IfxMonthSpan
• IfxTimeSpan
• IfxDateTime
• IfxDecimal

Example calculations:

				
IfxDateTime entryTime = IfxDateTime.Parse("12:0:30", IfxTimeUnit.Hour, IfxTimeUnit.Seconds);
IfxTimeSpan timeElapsed = IfxDateTime.Now - entryTime;

				
IfxTimeSpan ts1 = IfxTimeSpan.Parse("9.01", IfxTimeUnit.Seconds, IfxTimeUnit.Fraction2);
IfxTimeSpan ts2 = IfxTimeSpan.Parse("10.5", IfxTimeUnit.Seconds, IfxTimeUnit.Fraction2);
IfxTimeSpan diff = ts2 - ts1;

				
IfxMonthSpan johnAge = new IfxMonthSpan(66, 7);
IfxMonthSpan paulAge = new IfxMonthSpan(54, 11);
IfxMonthSpan diffAge = johnAge - paulAge;

				
IfxDecimal dec1 = IfxDecimal.Parse("3214.32)"
IfxDecimal dec2 = IfxDecimal.Parse("6532.0");
IfxDecimal dec3 = dec1 / dec2;

Logical operators

All data types below can be used to carry out logical operations. Logical operators like '=' (equal to), '!=' (not equal to), '<' (less than), '>' (greater than), '<=' (less than or equal to), '>=' (greater than or equal to) are overloaded for these structures.

• IfxMonthSpan
• IfxTimeSpan
• IfxDateTime
• IfxDecimal

Example calculations:

				
IfxTimeSpan johnTimeTaken = new IfxTimeSpan(9, 1, IfxTimeUnit.Fraction5);
IfxDateTime leeTimeTaken= IfxTimeSpan.Parse("10.3", IfxTimeUnit.Seconds, IfxTimeUnit.Fraction5);
if(johnTimeTaken > leeTimeTaken)
{
     Console.WriteLine("Johnson runs faster");
}
if(leeTimeTaken > johnTimeTaken )
{
    Console.WriteLine("Lee runs faster");
}

				
IfxDateTime johnDOB = IfxDateTime.Parse("1976-05-12", IfxTimeUnit.Year, IfxTimeUnit.Day);
IfxDateTime paulDOB = IfxDateTime.Parse("1972-07-17", IfxTimeUnit.Year, IfxTimeUnit.Day);
if(johnDOB > paulDOB)
{
     Console.WriteLine("John is older");
}
if(paulDOB > johnDOB)
{
    Console.WriteLine("Paul is older");
}

Back to top

IsNull method

All the data types below implement the IsNull method of the INullable interface.

• IfxBlob
• IfxClob
• IfxMonthSpan
• IfxTimeSpan
• IfxDateTime
• IfxDecimal

Back to top

IComparable interface

All the data types below implement the CompareTo method of the IComparable interface. Generic sorting algorithms that work on IComparable objects can also be used to sort arrays of these data types.

• IfxMonthSpan
• IfxTimeSpan
• IfxDateTime
• IfxDecimal

Resources

• Participate in the discussion forum.
  
  
• IBM Informix .NET Provider Reference Guide : Get detailed information about the data types extensions.
  
  
• developerworks DB2, Informix technical resource
  

About the author

		Kanchana Padmanabhan has been working on Informix Client-side tools for the past 6 years. Her major focus has been on Microsoft APIs like ODBC, Ole DB and .NET. For the past 2 years, she has been playing an active role in the .NET design and development team.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top