HOWTO: Create Date Parameters with ADO in Java

• Microsoft Visual J++, version 6.0
• ActiveX Data Objects (ADO), versions 2.0, 2.1

SUMMARY

This article describes how to create date and time parameters for use in WFC ADO parameterized queries in Visual J++ 6.0. The WFC ADO Command object allows the creation of Parameter objects to provide parameter information to SQL queries when they are executed. To create a properly initialized date parameter, create a com.ms.com.Variant object initialized by passing in a com.ms.wfc.app.Time.toDouble() value.

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:

   
   Connection con;
   Command cm;
   Recordset rs;
   Parameter startDate, endDate;
   Time date;
   Variant varDate;
   
   try 
   {
      con = new Connection();
      con.setConnectionString(
         "Provider=SQLOLEDB;User ID=sa;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.

Additional query words:

Keywords          : kbADO kbADO200 kbADO210 kbDatabase kbVJ kbVJ600 kbGrpVCDB 
Version           : WINDOWS:2.0,2.1,6.0
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: May 14, 1999