Try to work hard !

Many times users are only interested in a subset of the results returned from an SQL query. DB2 provides a mechanism for limiting the records returned from an SQL query with the FETCH FIRST command. Using the FETCH FIRST syntax improves the performance of queries when not all results are required. Listed below are some examples explaining how to use FETCH FIRST.

Example 1: Returning the first 100 rows from a table called employee: 

select * from employee fetch first 100 rows only 

Example 2: Using the fetch first syntax with an order by clause 

select * from employee order by salary desc fetch first 10 rows only

Comment +0

 DB2 Version 9.5 for Linux, UNIX, and Windows

After executing a statement that returns one or more result sets, use one of the functions available in the ibm_db API to iterate through the returned rows of each result set. If your result set includes columns with extremely large data (such as a column defined with a BLOB or CLOB data type), you might want to retrieve the data on a column-by-column basis to avoid using too much memory in your Python process.

Before you begin

You must have a connection resource returned by either the ibm_db.exec_immediate() or ibm_db.execute() function that has one or more associated result sets.

About this task

This procedure fetches rows or columns from result sets.

Procedure

  1. Call one of the following functions to fetch a row from the result set:
    Table 1. ibm_db fetch functions
    FunctionDescription
    ibm_db.fetch_tuple()Returns a tuple, indexed by column position, representing a row in a result set. The columns are 0-indexed.
    ibm_db.fetch_assoc()Returns a dictionary, indexed by column name, representing a row in a result set.
    ibm_db.fetch_both()Returns a dictionary, indexed by both column name and position, representing a row in a result set.
    ibm_db.fetch_row()Sets the result set pointer to the next row or requested row. Use this function to iterate through a result set.
    These functions accept the following arguments:
    stmt
    A valid statement resource.
    row_number
    The number of the row that you want to retrieve from the result set. Specify a value for this optional parameter if you requested a scrollable cursor when you called the ibm_db.exec_immediate() or ibm_db.prepare() function. With the default forward-only cursor, each call to a fetch method returns the next row in the result set.
  2. Optional: If you called the ibm_db.fetch_row() function, for each iteration over the result set, call the ibm_db.result() function to retrieve the value from the specified column. You can specify the column by either passing an integer that represents the position of the column in the row (starting with 0), or a string that represents the name of column.
  3. Continue fetching rows until the fetch method returns False, which indicates that you have reached the end of the result set.

    For more information about the ibm_db API, see http://code.google.com/p/ibm-db/wiki/APIs.

Example

Example 1: Fetch rows from a result set by calling the ibm_db.fetch_both() function.

import ibm_db

conn = ibm_db.connect( "dsn=name", "username", "password" )
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
    print "The ID is : ",  dictionary["EMPNO"]
    print "The Name is : ", dictionary[1]
    dictionary = ibm_db.fetch_both(stmt)Copy

Example 2: Fetch rows from a result set by calling the ibm_db.fetch_tuple() function.

import ibm_db

conn = ibm_db.connect( "dsn=name", "username", "password" )
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
tuple = ibm_db.fetch_tuple(stmt)
while tuple != False:
    print "The ID is : ", tuple[0]
    print "The name is : ", tuple[1]
    tuple = ibm_db.fetch_tuple(stmt)Copy

Example 3: Fetch rows from a result set by calling the ibm_db.fetch_assoc() function.

import ibm_db

conn = ibm_db.connect( "dsn=name", "username", "password" )
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_assoc(stmt)
while dictionary != False:
    print "The ID is : ", dictionary["EMPNO"]
    print "The name is : ", dictionary["FIRSTNME"]
    dictionary = ibm_db.fetch_assoc(stmt)Copy

Example 4: Fetch columns from a result set

import ibm_db

conn = ibm_db.connect( "dsn=name", "username", "password" )
sql = "SELECT * FROM EMPLOYEE
stmt = ibm_db.exec_immediate(conn, sql)
while ibm_db.fetch_row(stmt) != False:
    print "The Employee number is : ",  ibm_db.result(stmt, 0)
    print "The Name is : ", ibm_db.result(stmt, "NAME") Copy

What to do next

When you are ready to close the connection to the database, call the ibm_db.close() function. If you attempt to close a persistent connection created with ibm_db.pconnect(), the close request returns TRUE, and the persistent IBM® Data Server client connection remains available for the next caller.


Comment +0

Returning File as response to REST Request

8 Feb 2016
Returning File as response to REST Request

Introduction

This article demonstrates how to get a file as response to a REST request. I have posted my workng solution here for reference. This would help general user to write the code to implement this functionality.

Background

I was assigned a project to create a Web API which is used by SharePoint Link-To-Document, to return a file from a network drive which is different from the SharePoint Server machine. The user's requirement was that the file should be returned to user which he/she click on L-To-D  on a SharePoint site. As I struggled to find a working code the purpose, so, I decided to share my sample code for the reference of other developers.

Using the code

Here we see the step by step approach to create a Web API service which return files as response. During this demo project I will be using Visual Studio (VS) 2012 as IDE.

Step 1: In VS IDE, click on Add New Project. Select Web from Templates and then select Visual Studio 2012 under Web. Select ASP.Net MVC 4 Web Application from types of application (as shown below). Give Name of the solution as ReturnFile and click on OK. 

Step 2: Once you click on OK you will be prompted will another pop-up. In the second pop-up select template as Web API and click on OK. VS will take a moment to create a solution for you and your FileService solution will be ready.

Step 3: Right Click on controller folder and select Add -> Controller. Give Name of the controller as FileController, and click OK. FileController.cs will be added to your Controllers folder.

Step 4: Open FileController.cs and and delete the default methods inside class FileController.cs. Now add a method GetFile, which will called from the REST request.

public HttpResponseMessage GetFile(string networkPath, string siteCollection, string library, 
          string folder, string fileName, string fileExtension)
{
     string filePath = string.Format(@"{0}\{1}\{2}\{3}\{4}.{5}", networkPath, siteCollection, library, 
                           folder, fileName, fileExtension);
     string file = string.Format("{0}.{1}", fileName, fileExtension);

     return DownloadFile(filePath, file);
}

The above method acts as a wrapper, formats the strings and calls another method which do processing as returns file stream in Message Response.

Step 5: Create method DownloadFile to search, process and return file in HttpMessageResponse.

private HttpResponseMessage DownloadFile(string downloadFilePath, string fileName)
{
    try
    {
          //Check if the file exists. If the file doesn't exist, throw a file not found exception
          if (!System.IO.File.Exists(downloadFilePath))
          {
                throw new HttpResponseException(HttpStatusCode.NotFound);
          }

          //Copy the source file stream to MemoryStream and close the file stream
          MemoryStream responseStream = new MemoryStream();
          Stream fileStream = System.IO.File.Open(downloadFilePath, FileMode.Open);

          fileStream.CopyTo(responseStream);
          fileStream.Close();
          responseStream.Position = 0;

          HttpResponseMessage response = new HttpResponseMessage();
          response.StatusCode = HttpStatusCode.OK;

          //Write the memory stream to HttpResponseMessage content
          response.Content = new StreamContent(responseStream);
          string contentDisposition = string.Concat("attachment; filename=", fileName);
          response.Content.Headers.ContentDisposition = 
                        ContentDispositionHeaderValue.Parse(contentDisposition);
          return response;
      }
      catch
      {
          throw new HttpResponseException(HttpStatusCode.InternalServerError);
      }
}

Points of Interest

The code above work for the file type like pdf, doc, mov, mp3, PNG, TIF, JPEG, etc. We don't need to write a separate logic based on filetype.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Comment +0