Обсуждение: strange jdbc problem moving to 8.1 database

hi, i've got a test system under fedora 4/ 8.04 postgres that works
fine. I installed 8.1 on a production machine using redhat 9, and weird
errors start happening.

here's the java sql code

      String strSQL = "SELECT mi.menuitemid " +
                      "  FROM menuitem mi"+
                      " INNER JOIN menuitemtypes mit" +
                      "    ON (mi.menuitemid = mit.menuitemid) " +
                      " INNER JOIN menugroupitem mgi " +
                      "    ON (mgi.menuitemid = mit.menuitemid) " +
                      " WHERE mit.usertype = ? " +
                      "   AND mgi.menugroupid = ? " +
                      "   AND mi.menuitemid = ? ";

      pstmt = livedb.prepareStatement(strSQL);
      pstmt.setString(1, strUserType);
      pstmt.setString(2, strMenuGroup);
      pstmt.setString(3, strLookupFunction);

      rst = pstmt.executeQuery();

      if (rst.next()){
         blnFound = true;
      }


this will produce true in 8.04 but not 8.1. There are other (similar)
statements against these tables that have stopped working.
the tables are defines as follows

                   Table "public.menuitem"
   Column    |         Type          |       Modifiers
-------------+-----------------------+------------------------
 menuitemid  | character varying(20) | not null
 description | character varying(30) | not null
 url         | character varying(50) | not null
 menuprog    | boolean               | not null default false

          Table "public.menuitemtypes"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 usertype   | character varying(10) | not null
 menuitemid | character varying(20) | not null

               Table "public.menugroupitem"
   Column    |         Type          |     Modifiers
-------------+-----------------------+--------------------
 menugroupid | character varying(8)  | not null
 menuitemid  | character varying(20) | not null
 sequence    | integer               | not null default 1
 parentmenu  | character varying(8)  | not null

jdbc string parameter values
usertype='ADMIN'
menugroup='ADMIN'
lookupfunction='HOME'

i've checked the gotcha list for 8.1 and tried the insert_missing_from
idea with no change.
i swapped out the default 7.4 jdbc driver for the 8.1 jdbc3driver with
no change.

google reveals no obvious duhh why didn't i think of that stuff.

i have cut and pasted the code into psql and it returns one row, yet
the log data shows that the self same string data returns false in the
jdbc code. i've selected the values from the tables concatenated
between characters to ensure the key values (appear) are identical.

anybody got an idea why it stopped working?

On Fri, 11 Nov 2005 kevin@mtel.co.uk wrote:

> hi, i've got a test system under fedora 4/ 8.04 postgres that works
> fine. I installed 8.1 on a production machine using redhat 9, and weird
> errors start happening.
>
> this will produce true in 8.04 but not 8.1. There are other (similar)
> statements against these tables that have stopped working.
> the tables are defines as follows
>

That's certainly a strange one.  I see no reason for plain string
comparisons to fail randomly.  My only guess would be some sort of
locale/encoding issue.  Are both databases using the same locale and
encoding?  What do you get from "SHOW lc_collate;" and "SHOW
server_encoding;" on each?  This seems especially bizarre given that it
works from psql (implying something might be wrong converting from
client_encoding to the server_encoding) and also that you have such
vanilla values for these parameters ("ADMIN" and such, not unusual
characters from distant parts of the world.)

Kris Jurka

duhh,
sorry guys.

This one took me a long time to find. I changed the original table that
supplies these fields from char(10) to char varying (10) in the test
system and have made a demo area from the live system still with the
char(10) fields.
the data is actually
'ADMIN     ' not
'ADMIN'
simply dump to sql, alter tabledef to varying, reload and to set field
= trim(field)

noooow it works.
thanks to Kris Jurka

hmm,

on both machines the lc_colate returns
 lc_collate
------------
 C

and server_encoding returns
 server_encoding
-----------------
 SQL_ASCII

these values were set up because the default encodinig was giving silly
results for string ordering of (numeric) string fields.