Обсуждение: Update+ Prepared Statement Error.

Hi all,
Im trying to write a PreparedStatement , to pass an update query as below
.

    query = "UPDATE db_consumer SET
WENEXA_ID=?,RR_NO=?,CONSUMER_NAME=?,RESIDING_VILLAGE=?,CONTACT_NO=?,CONTACT_PERSON=?,REP_DATE=?,STATUS_ID=?
WHERE CONSUMER_ID=?";
                  stmt = con.prepareStatement(query);
                                    stmt.setString(1, bean.getWenexa_id());
                          stmt.setString(2, bean.getRr_number());
                  stmt.setString(3, bean.getConsumer_name());
                  stmt.setString(4, bean.getResiding_village());
                  stmt.setString(5, bean.getContact_no());
                  stmt.setString(6, bean.getContact_person());
                  if (bean.getRep_date() == null || bean.getRep_date() == "") {
                        bean.setRep_date(null);
                    }
                    if (bean.getRep_date() != null) {
                        System.out.println("DAte before Insert" + bean.getRep_date());
                        dtd2 = df2.parse(bean.getRep_date());
                        sqlDate1 = new java.sql.Date(dtd2.getTime());
                    }


                  stmt.setDate(7, sqlDate1);
                  stmt.setInt(8, bean.getStatus());
                  stmt.setInt(9, bean.getConsumer_id());

                  System.out.println(stmt.toString());
                  stmt.executeUpdate();

However, I get an error as below.
org.postgresql.util.PSQLException: No value specified for parameter 1.
    at
org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:178)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:246)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
    at
com.enzen.cis.dao.DAOConsumerAddEdit.updateDB(DAOConsumerAddEdit.java:253)

I'm not being able to figure out why this is happening . However, when I try
to run the query printed by the toString() method  at the database, it works
perfectly . Am I missing something in the syntax?
Thank you for your time.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Update-Prepared-Statement-Error-tp4725545p4725545.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On 23/08/11 06:34, JavaNoobie wrote:
> Hi all,
> Im trying to write a PreparedStatement , to pass an update query as below
> .
>
>     query = "UPDATE db_consumer SET
> WENEXA_ID=?,RR_NO=?,CONSUMER_NAME=?,RESIDING_VILLAGE=?,CONTACT_NO=?,CONTACT_PERSON=?,REP_DATE=?,STATUS_ID=?
> WHERE CONSUMER_ID=?";
>                   stmt = con.prepareStatement(query);
>                                     stmt.setString(1, bean.getWenexa_id());

The bean.getWenexa_id() call returns null?

>                           stmt.setString(2, bean.getRr_number());
>                   stmt.setString(3, bean.getConsumer_name());
>                   stmt.setString(4, bean.getResiding_village());
>                   stmt.setString(5, bean.getContact_no());
>                   stmt.setString(6, bean.getContact_person());
>                   if (bean.getRep_date() == null || bean.getRep_date() == "") {
>                         bean.setRep_date(null);
>                     }

The check and set of null here is redundant, and you are comparing the
identity of the rep_date string with the empty string. Unless the
rep_date string is interned this will most likely not work as you
expect. The clause should be:

if ("".equals(bean.getRep_date())) {
     bean.setRep_date(null);
}

Although I have to wonder why are you representing a date in your bean
as a String rather than a Date. If it was a Date, then the previous
clause would be unnecessary and the next clause could be:

if (bean.getRep_date() != null) {
     stmt.setDate(7, new java.sql.Date(bean.getRep_date().getTime()));
} else {
     stmt.setNull(7, Types.DATE);
}

That would save you the cost of parsing a date from a string.

>                     if (bean.getRep_date() != null) {
>                         System.out.println("DAte before Insert" + bean.getRep_date());
>                         dtd2 = df2.parse(bean.getRep_date());
>                         sqlDate1 = new java.sql.Date(dtd2.getTime());
>                     }
>
>
>                   stmt.setDate(7, sqlDate1);
>                   stmt.setInt(8, bean.getStatus());
>                   stmt.setInt(9, bean.getConsumer_id());
>
>                   System.out.println(stmt.toString());

The toString() call is redundant, an implicit conversion  will occur
without it.

>                   stmt.executeUpdate();
>
> However, I get an error as below.
> org.postgresql.util.PSQLException: No value specified for parameter 1.
>     at
> org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:178)
>     at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:246)
>     at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
>     at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
>     at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
>     at
> com.enzen.cis.dao.DAOConsumerAddEdit.updateDB(DAOConsumerAddEdit.java:253)
>
> I'm not being able to figure out why this is happening . However, when I try
> to run the query printed by the toString() method  at the database, it works
> perfectly . Am I missing something in the syntax?
> Thank you for your time.
>

Solved the problem . Thanks

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Update-Prepared-Statement-Error-tp4725545p4726227.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.