Обсуждение: Could not determine data type of parameter $1

////////////////////////////

Using the version of the jbdc drive (postgresql-8.1dev-400.jdbc3.jar)
instead of (postgresql-8.1-407.jdbc3.jar) the code
below works. I would like to know if the code is valid or if the driver
has a problem. It fails
at
      ResultSet result = prepared.executeQuery();

////////////////////////////

========================
Extract from DataHandler
========================
...
  import java.text.SimpleDateFormat;

  public static SimpleDateFormat dateTimeFormat = new
SimpleDateFormat("yyyyMMddHHmmss");

  public static java.sql.Timestamp dateTime(String value) throws Throwable
  {
    if (value.length() == 8)
      value += "000000";
    java.util.Date date = dateTimeFormat.parse(value);
    return new java.sql.Timestamp(date.getTime());
  }

==================================
Extract from inherited data record
==================================
...
    public String cashier;
    public String terminalId;
    public String actionDateFrom;
    public String actionDateTo;
    public double receiptTotal;
    public double removalTotal;

=========================================
Function or method where the error occurs
=========================================
...
    public boolean pettyCash() throws Throwable
    {
      String statement =
      "select CashierUserid, CashierTerminalId, ?, ?, 0,
sum(CashierTotalRemoved) "
      +_EOL_+ "from ReceiptRemoval "
      +_EOL_+ "where RemovalType = 'P' "
      +_EOL_+ "and CashierUserid = ? "
      +_EOL_+ "and CashierTerminalId = ? "
      +_EOL_+ "and ActionDate > ? "
      +_EOL_+ "and ActionDate <= ? "
      +_EOL_+ "group by CashierUserid, CashierTerminalId "
      ;
      PreparedStatement prepared = connector.prepareStatement(statement);
      prepared.setTimestamp(1, DataHandler.dateTime(actionDateFrom));
      prepared.setTimestamp(2, DataHandler.dateTime(actionDateTo));
      prepared.setString(3, cashier);
      prepared.setString(4, terminalId);
      prepared.setTimestamp(5, DataHandler.dateTime(actionDateFrom));
      prepared.setTimestamp(6, DataHandler.dateTime(actionDateTo));
      ResultSet result = prepared.executeQuery();
      ResultSetMetaData _rsmd_ = result.getMetaData();
      int _columns_ = _rsmd_.getColumnCount();
      if (_columns_ != 6)
        throw new Exception("Columns Read="+_columns_+" != Expected=6");
      if (!result.next())
      {
        result.close();
        prepared.close();
        return false;
      }
      cashier = result.getString(1);
      terminalId = result.getString(2);
      actionDateFrom = DataHandler.dateTime(result.getTimestamp(3));
      actionDateTo = DataHandler.dateTime(result.getTimestamp(4));
      receiptTotal = result.getDouble(5);
      removalTotal = result.getDouble(6);
      result.close();
      prepared.close();
      return true;
    }

///////////////////////////////////

On Fri, 25 Aug 2006, Vincent Risi wrote:

> ////////////////////////////
>
> Using the version of the jbdc drive (postgresql-8.1dev-400.jdbc3.jar)
> instead of (postgresql-8.1-407.jdbc3.jar) the code below works. I would
> like to know if the code is valid or if the driver has a problem. It
> fails at
>
> [
>   PreparedStatement ps = conn.prepareStatement("SELECT ?");
>   ps.setTimestamp(1, new Timestamp(0));
>   ResultSet rs = ps.executeQuery();
> ]
>

When given a timestamp parameter the jdbc driver does not know if it is
going to be used a timestamp with time zone or a timestamp without time
zone.  The driver passes it to the server as an unknown data type letting
the server figure out what to do with it.  Usually you'll have
timestampcol = ? or you're inserting into a timestamp field so the server
can easily determine the true datatype the parameter is being used at.  In
this case since it's just being sent back to the user the server cannot
determine the type and bails out.  You need to help it along with a cast:
"SELECT ?::timestamptz".

Kris Jurka

Kris Jurka <books@ejurka.com> writes:
> When given a timestamp parameter the jdbc driver does not know if it is
> going to be used a timestamp with time zone or a timestamp without time
> zone.  The driver passes it to the server as an unknown data type letting
> the server figure out what to do with it.  Usually you'll have
> timestampcol = ? or you're inserting into a timestamp field so the server
> can easily determine the true datatype the parameter is being used at.  In
> this case since it's just being sent back to the user the server cannot
> determine the type and bails out.  You need to help it along with a cast:
> "SELECT ?::timestamptz".

Hm, I wonder if we are being overly strict about this.  In the
non-prepared-statement scenario, you can do

    SELECT 'foo'

and the system just plays dumb and emits the string again --- it's
perfectly happy to report the datatype as UNKNOWN.  Is it reasonable to
allow the same treatment for parameter symbols, or will client code just
choke anyway if it sees the parameter type come back as UNKNOWN?

            regards, tom lane