CodeCharge Studio All ASP PHP Perl ColdFusion Java C#.NET VB.NET

Utilize MySQL Enum Field Type

This example shows how to use the MySQL enum type field to provide the list of values for a listbox. In this case, we have a Record form called Tasks which has a status List Box. We use event code to set the values for the List Box to correspond to the values of the statuses field (enum("High","Normal","Low")) in the Tasks database table.

  1. In the Properties window for the Listbox, set the List Source Type property to ListOfValues.
  2. Type any value (e.g. "0;0") into the List Data Source property of the Listbox.
  3. Add the Before Show event to the Record form.
  4. Within the event, add the code below:

ASP

Function Tasks_BeforeShow(Sender) 
 Dim SQL
 Dim RecordSet
 Dim Enumfield
 Dim ArrayEnum
 
  SQL = "SHOW COLUMNS FROM tasks LIKE 'statuses'"
  
' Open the recordser 
  Set RecordSet = DBConnection1.Execute(SQL)
  If DBConnection1.Errors.Count = 0 Then
    If NOT RecordSet.EOF then 
       Enumfield = RecordSet.Fields(1)
    End If 
  ' Close the recordser 
    RecordSet.Close
    Set RecordSet = Nothing
  Else
    Print "SQL Execution Failed."
    DBConnection1.Errors.Clear
  End If 
  
' Parse string from DB
  if Enumfield <> Empty Then
    Enumfield = Replace(Mid(Enumfield,6,len(Enumfield)-6),"'","")
    ArrayEnum = split(Enumfield ,",")
  
    Set Tasks.Status.DataSource = CCCreateDataSource(dsListOfValues, Empty, _
        Array(ArrayEnum,ArrayEnum))
  End if 
  
End Function

PHP

function Tasks_BeforeShow(& $sender) {
global $Tasks;
 
  $SQL = "SHOW COLUMNS FROM tasks LIKE 'statuses'";

// Open the connection
  $db = new clsDBConnection1();
  $db->query($SQL);
  $Result =  $db->next_record();
  if($Result) {
    $Enumfield = $db->f(1); 
  }
  $db->close(); 
  
// Parse string from DB 
  if ($Enumfield != "") {
    $Enumfield = str_replace("'","",substr($Enumfield,6,strlen($Enumfield)-7));
    $ArrayEnum = split(",",$Enumfield);
    foreach ($ArrayEnum as $value) { 
       $values[] = array($value,$value);
    }
    $Tasks->Status->Values = $values;
  }

}

Perl

sub Tasks_BeforeShow() {
 
  $SQL = "SHOW COLUMNS FROM tasks LIKE 'statuses'";

# Open the connection
  $db = clsDBConnection2->new();
  $db->query($SQL);
  $Result = $db->next_record();
  if ($Result) {
    $Enumfield = $db->f(1);
  }
  $db->{sth} = undef;
  $db->{dbh} = undef;
  
# Parse string from DB
  if ($Enumfield ne "") {
    $Enumfield =~ s/enum|\(|\'|\)//ig;
    my @ArrayEnum = split /\,/,$Enumfield ;

    my @values;
    for (my $i= 0; $i <= $#{@ArrayEnum}; $i++  ) {
       $values[$i] = [$ArrayEnum[$i],$ArrayEnum[$i]];
    }
    $Tasks->{Status}->{Values} = \@values;
  }
  
}

Java

//Tasks_BeforeShow
 
  String sql = "SHOW COLUMNS FROM tasks LIKE 'statuses'";

// Open the connection
  JDBCConnection conn = JDBCConnectionFactory.getJDBCConnection("Connection2");
  DbRow row = conn.getOneRow(sql);
  conn.closeConnection();
  String enumField = null;
  if ( row != null ) {
     enumField = (String) row.get(new Integer(1));
  }

  if ( enumField != null ) {
    enumField = StringUtils.replace(enumField.substring(5, enumField.length()-1),"'","");
    Enumeration opts = StringUtils.split(enumField, ',');
    Vector options = new Vector();
    int i = 1;
    com.codecharge.components.List list = e.getRecord().getList("Status");
    while ( opts.hasMoreElements() ) {
      DbRow optRow = new DbRow();
      optRow.put(list.getBoundColumn(),String.valueOf(i++));
      optRow.put(list.getTextColumn(),(String) opts.nextElement());
      options.add(optRow);
    }
    e.getRecord().getControl("Status").setValue(enumField);
    list.setOptions(options.elements());
  } 
  

See Also:

BeforeShow event, How to modify the 'ListOfValues' of a list box


On-line, printable versions and updates