Listing 1: UPDATEBLOB and SELECTBLOB in embedded SQL

long ll_serial
blob lb_pic_in
blob lb_pic_out
long ll_stock_num
string ls_manu_code

//... code to initialize lb_pic_in, ll_stock_num,
// and ls_manu_code from some external source

// insert new row leaving BYTE column null
INSERT INTO catalog (stock_num, manu_code) 
  VALUES (:ll_stock_num, :ls_manu_code) USING SQLCA;
IF SQLCA.SQLCode = 0 THEN

  // get new ID of row for subsequent update
  ll_serial = Long(SQLCA.SQLReturnData)

  // set blob column using data retrieve 
  // externally, perhaps read from image file
  UPDATEBLOB catalog SET cat_picture = :lb_pic_in
    WHERE catalog_num = :ll_serial USING SQLCA;

  // use SQLNRows to determine if blob was 
  // successfully updated
  IF SQLCA.SQLNRows <> 1 THEN
    MessageBox("Update Failed", &
      "Could not update record: " + &
      SQLCA.SQLErrText, StopSign!)
  END IF
END IF

// retrieve the blob
SELECTBLOB cat_picture INTO :lb_pic_out
  FROM catalog 
  WHERE catalog_num = :ll_serial USING SQLCA;

// if blob was successfully retrieved, display 
// in picture control within current window
IF SQLCA.SQLNRows = 1 THEN
  p_catalog.setPicture(lb_pic_out)
END IF

Listing 2:  Retrieving Contents of an LVARCHAR Column

string  ls_desc
integer li_length
// retrieve item_desc LVARCHAR column 
// and its length given a catalog_num
DECLARE c CURSOR FOR SELECT 
  CAST(item_desc as CHAR(2048)), 
  LENGTH(item_desc)
  FROM catalog 
  WHERE catalog_num = :al_catnum;
OPEN c;
IF SQLCA.SQLCODE = 1 THEN
  FETCH c into :ls_desc, :li_length;
  // since item_desc was cast to CHAR(2048) we need 
  // to trim off the 'extra' characters that were not 
  // stored with the original column value
  ls_desc = Left(ls_desc, li_length)
  CLOSE c;
END IF

Listing 3: IBM Informix SPL Function

CREATE FUNCTION listordersbystate (state_abbr CHAR(2)) 
  RETURNING CHAR(15), CHAR(15), CHAR(15), INT, DATE;
DEFINE lname_out CHAR(15);
DEFINE fname_out CHAR(15);
DEFINE city_out  CHAR(15);
DEFINE onum_out  INT;
DEFINE date_out  DATE;
FOREACH
SELECT customer.lname, customer.fname, customer.city, 
  orders.order_num, orders.paid_date
  INTO lname_out, fname_out, city_out, onum_out, date_out
  FROM customer, orders  
  WHERE (orders.customer_num = customer.customer_num) and  
        (customer.state = state_abbr)
  ORDER BY customer.lname ASC,  customer.fname ASC
  RETURN lname_out, fname_out, city_out, onum_out, date_out 
  WITH RESUME;
END FOREACH
END FUNCTION

Listing 4: Calling a UDR Via Embedded SQL

string ls_lastName, ls_firstName, ls_city
long   ll_orderNum
date   ld_paidDate
string ls_listItem

// declare and execute procedure
DECLARE p PROCEDURE FOR 
  listOrdersByState(:as_stateAbbr) 
  USING SQLCA;
EXECUTE p;

// get first row
FETCH p INTO :ls_lastName, :ls_firstName, 
  :ls_city, :ll_orderNum, :ld_paidDate;

// if SQLCode=0 we haven't errored 
// or reached end of result set
DO WHILE SQLCA.SQLCode = 0 

  // filter out anything older than 30 days
  IF ld_paidDate >= RelativeDate(today(), -30) THEN

    // format item for list view
    ls_listItem  = Trim(ls_lastName) + &
      ", " +Trim(ls_firstName) + "~t"
    ls_listItem += String(ll_orderNum) + "~t"
    ls_listItem += String(ld_paidDate)

    // add item to the list view
    lv_orders.AddItem(ls_listItem, 0)
  END IF

  // get data from next row
  FETCH p INTO :ls_lastName, :ls_firstName, 
    :ls_city, :ll_orderNum, :ld_paidDate;
LOOP

// always close the cursor
CLOSE p;

Listing 5a: OJSyntax='PB' - PB (IBM Informix-Native) Outer Join Syntax

SELECT customer.customer_num  customer.fname,
  customer.lname, count(order_num)
FROM OUTER orders, customer
WHERE ( customer.customer_num  = orders.customer_num)
GROUP BY customer.fname, customer.lname,
customer.customer_num

Listing 5b: OJSyntax='ANSI' - ANSI Outer Join Syntax

SELECT customer.customer_num, customer.fname, 
  customer.lname, count(order_num)
FROM customer LEFT OUTER JOIN orders ON
  customer.customer_num = orders.customer_num
GROUP BY customer.fname, customer.lname,
customer.customer_num