Обсуждение: Updating a chkpass column in postgres v8.1.21
I am using the JDBC prepareStatement object to build a SQL statement that should update a users password in a postgres v8.1.21 database. (Based on what I have read, chkpass became a “contrib module” starting at 8.3, but I have to use an older version of postgres.) Here is my java code/snippet:
// Build an SQL statement with caller's parameters
private static final String m_defaultPass = "changeme";
stmtStr = "UPDATE users SET pwd_date=now(), pwd=? " + "WHERE user_id=?;";
stmt = con.prepareStatement(stmtStr);
stmt.setString(1, m_defaultPass);
stmt.setString(2, userId);
(userId is a String that is passed into the method)
The password column in the database is defined as type chkpass.
userdb=# \d users
Table "public.users"
Column | Type | Modifiers
-------------------+-----------------------------+-------------------------------------------------------------------
user_pk | integer | not null default nextval(('"users_user_pk_seq"'::text)::regclass)
user_id | character varying(10) | not null
pwd | chkpass | not null
:
chkpass is a user created type, and is defined as follows:
CREATE TYPE chkpass (
INTERNALLENGTH = 16,
INPUT = chkpass_in,
OUTPUT = chkpass_out,
ALIGNMENT = int4,
STORAGE = plain
);
When the above java snippet is executed, I get the following error:
column "pwd" is of type chkpass but expression is of type character varying.
How do I modify my SQL statement to get this to work properly?
Thanks in advance!
Howard
"Nelson, Howard C" <howard.c.nelson@lmco.com> writes:
> I am using the JDBC prepareStatement object to build a SQL statement that should update a users password in a
postgresv8.1.21 database. (Based on what I have read, chkpass became a "contrib module" starting at 8.3, but I have to
usean older version of postgres.) Here is my java code/snippet:
No, it's been in contrib a long time...
> // Build an SQL statement with caller's parameters
> private static final String m_defaultPass = "changeme";
> stmtStr = "UPDATE users SET pwd_date=now(), pwd=? " + "WHERE user_id=?;";
> stmt = con.prepareStatement(stmtStr);
> stmt.setString(1, m_defaultPass);
> stmt.setString(2, userId);
This is telling JDBC that parameter 1 is a string (hence, character
varying); but there is no cast from varchar to chkpass (hence, fail).
It might work to use setObject not setString there, but not sure because
I'm not a Java person. You might get more help on that from the
pgsql-jdbc list.
In more recent PG versions you could definitely fix it with an explicit
cast in the query, ie "SET pwd=?::chkpass", but I think that will not
work in 8.1.
BTW, 8.1 has been out of support for awhile. You really need to think
about upgrading.
regards, tom lane