Thursday Aug 16, 2007

SqlException: At least one parameter to the current statement is uninitialized

When you do the Using Databound Components to Access a Database tutorial, you learn how to use the Query Editor on the rowset to build the SQL statement to retrieve the desired set of data. You also learn that for every ? (parameter) in your query, you need to call setObject() on the rowset before the query is executed. Take, for example, the following query.

SELECT ALL TRAVEL.TRIP.TRIPID, 
           TRAVEL.TRIP.PERSONID, 
           TRAVEL.TRIP.DEPDATE 
FROM TRAVEL.TRIP
WHERE TRAVEL.TRIP.PERSONID = ?
   AND TRAVEL.TRIP.TRIPTYPEID = ? 
This query has two parameters, PERSONID and TRIPTYPEID. What this means is that you have to provide values for these two parameters before the rendering phase. You typically do this in the prerender() method, as shown in the following code example.
public void prerender() {
    if ( personIdDD.getSelected() == null ) {
        try {
          personDataProvider.cursorFirst();
          getSessionBean1().getTripRowSet().setObject(
            1, personDataProvider.getValue("PERSON.PERSONID"));
          getSessionBean1().getTripRowSet().setObject(
            2, "4");
          tripDataProvider.refresh();
        } catch (Exception e) {
          error("Cannot switch to person " +
              personDataProvider.getValue("PERSON.PERSONID"));
            log("Cannot switch to person " +
              personDataProvider.getValue("PERSON.PERSONID"), e);
        }
    }
}

If you forget to set the parameters before the rendering phase, you usually get the following error.

SqlException: At least one parameter to the current statement is uninitialized.

You will also get an error like this if you do not set all the parameters. Such as only calling setObject(1, somevalue) when you have two parameters in the query.

Another common error is to forget to edit the query to add the parameters. If you call setObject(1, somevalue), but your query has no parameters (no ?), you might get an error like the following.

java.lang.NullPointerException
at org.apache.derby.client.am.PreparedStatement.checkForValidParameterIndex(Unknown Source)

Similarly, if you call setObject(2, somevalue) when your query has only one parameter, the server will emit something like the following message.

SqlException: The parameter position '2' is out of range.  
The number of parameters for this prepared  statement is '1'
About

divas

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today