How To Create Date Parameters with ADO in Java (227202)

MORE INFORMATION

The com.ms.wfc.data.Parameter class provides methods such as setInt() and setString() to set the value and type of the Parameter object. The Parameter class does not provide a method to set the type and value to a date or time directly. To properly set the value and type of a Parameter object to a date or time, create a Variant object of type VariantDate and use the setValue(Variant) method of the Parameter class to assign the date value to the parameter.

To create a VariantDate type Variant object, use the com.ms.wfc.app.Time class to store the date and then use the Time.toDouble() method to extract the double value needed to initialize the VariantDate type Variant. The toDouble() method of the Time class returns a properly formatted OLE double value that represents the date.

The following example uses the SQL Server Pubs sample database to demonstrate using a date value with a parameterized SQL SELECT statement. The example opens a connection to a SQL Server using the SQLOLEDB provider, creates two date parameters, and issues a SELECT statement to the database using the parameters. The parameterized SELECT statement returns all the employees hired in 1991.

Step-by-Step Example

1. Create a new console application using Visual J++ 6.0.
2. Add the following imports to the top of the Class1.java source file:

   import com.ms.wfc.data.*;
   import com.ms.wfc.app.*;
   import com.ms.com.*;
   						

3. Add the following code to the main() function in Class1.java:
   
   Note You must change the User ID=<username> value and the password =<strong password> value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

   
   Connection con;
   Command cm;
   Recordset rs;
   Parameter startDate, endDate;
   Time date;
   Variant varDate;
   
   try 
   {
      con = new Connection();
      con.setConnectionString(
         "Provider=SQLOLEDB;User ID=<username>;Password=<strong password>;" +
         "Initial Catalog=Pubs;Data Source=(local)");
      con.open();
   				
      cm = new Command();
      cm.setActiveConnection(con);
      cm.setCommandText("SELECT fname,lname,hire_date " +
                        "FROM employee WHERE " +
                        "hire_date > ? AND hire_date < ?");
      cm.setCommandType(AdoEnums.CommandType.TEXT);
   					
      date = new Time(1990,12,31);
      varDate = new Variant(Variant.VariantDate,date.toDouble());
   
      startDate = new Parameter();
      startDate.setName("Start date");
      startDate.setType(AdoEnums.DataType.DATE);
      startDate.setDirection(AdoEnums.ParameterDirection.INPUT);
      startDate.setValue(varDate);
   					
      date = new Time(1992,1,1);
      varDate = new Variant(Variant.VariantDate,date.toDouble());
   					
      endDate = new Parameter();
      endDate.setName("End date");
      endDate.setType(AdoEnums.DataType.DATE);
      endDate.setDirection(AdoEnums.ParameterDirection.INPUT);
      endDate.setValue(varDate);
   					
      cm.getParameters().append(startDate);
      cm.getParameters().append(endDate);
   				
      rs = new Recordset();
      rs.setCursorType(AdoEnums.CursorType.FORWARDONLY);
      rs.setCursorLocation(AdoEnums.CursorLocation.CLIENT);
      rs.open(cm);
   					
      while (!rs.getEOF())
      {
         System.out.println(
            rs.getField("fname").getString() +
            "\t" +
   	 rs.getField("lname").getString()+
            "\t" +
            rs.getField("hire_date").getTime().formatShortDate());
            rs.moveNext();	
      }
      System.out.println(rs.getRecordCount()+" records found.");
   }
   catch (AdoException adoEX)
   {
      System.out.println(adoEX);	
   }
   
   System.out.println("Press <enter> to exit.");
   try { System.in.read(); } catch (Exception e) {};
   						

4. Run the code from the Visual J++ debugger and observe the results in the jview console window.