Microsoft KB Archive/227202

= How To Create Date Parameters with ADO in Java =

Article ID: 227202

Article Last Modified on 7/1/2004

-

APPLIES TO


 * Microsoft Visual J++ 6.0 Standard Edition
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q227202



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
 Create a new console application using Visual J++ 6.0.  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.*;   Add the following code to the main function in Class1.java:

Note You must change the User ID= value and the 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= ;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 to exit."); try { System.in.read; } catch (Exception e) {};  Run the code from the Visual J++ debugger and observe the results in the jview console window.

Keywords: kbhowto kbdatabase KB227202

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.