FWD » Core Development » Database

I used H2 permanent database. No records are found in FWD.

create guest. guest.first-name = "John". guest.last-name = "Smith". release guest.

for each guest where guest.first-name + " " + guest.last-name = "John Smith" no-lock:
    message "found".
end.

This is because of the H2's dialect automatic case-insensitive assumption in P2JH2Dialect; I've used a JDBC URL like this dbc:h2:../db/hotel;DB_CLOSE_DELAY=-1;MV_STORE=FALSE;RTRIM=TRUE;DEFAULT_IGNORE_CASE=TRUE;" and this patch so that the login works:

=== modified file 'src/com/goldencode/p2j/persist/ConnectionManager.java'
--- old/src/com/goldencode/p2j/persist/ConnectionManager.java   2025-11-05 15:01:44 +0000
+++ new/src/com/goldencode/p2j/persist/ConnectionManager.java   2025-11-24 08:14:54 +0000
@@ -2228,14 +2228,27 @@
       sb.append(MetadataSecurityOps.META_TABLE_USER);
       sb.append( " WHERE ");
       sb.append(pccUserid);
-      sb.append(" = upper(");
+      sb.append(" = ");
+      if (!dialect.isAutoCi())
+      {
+         sb.append("upper(");
+      }
       dialect.addRtrimmedExpression("?", sb);
-      sb.append(") AND ");
+      if (!dialect.isAutoCi())
+      {
+         sb.append(")");
+      }
+      sb.append(" AND ");
       sb.append(pccPassword);
       sb.append(" = ?");
       String query = sb.toString();
       try
       {
+         if (dialect.isAutoCi())
+         {
+            encPassword = encPassword.toUpperCase();
+         }
+         
          String[] paramList = new String[] { userid, encPassword };
          Object ufo = persistence.getSingleSQLResult(query, Persistence.META_CTX, paramList);

With this the report in #10914 works (i.e. it gets the file to be downloaded in embedded mode).

I am OK with the changes in #10920-3. This piece of code is a backdoor into the persistence and wasn't handled properly when DEFAULT_IGNORE_CASE was introduced - we mostly considered FqlPreprocessor.
I looked for other usages of getSingleSQLResult, but this seemed to be the only one that had string fields/parameters used.

Alex, as a side note: this query does not return records in H2: select * from meta_user where userid = rtrim('HOTEL');.

The JDBC URL has the DEFAULT_IGNORE_CASE=TRUE. It may be that rtrim returns a case-sensitive value. I can't tell if this is something of concern for our _temp and meta H2 in-memory databases.

Is RTRIM=TRUE; set? H2 also knows to compare the values of strings with rtrim automatically.

PS: I see dialect.addRtrimmedExpression("?", sb);. This can be wrapped with dialect.isAutoRtrim.

Is not about a query from FWD, just a direct SELECT I noticed. Yes, RTRIM=TRUE is in the JDBC URL.

Is not about a query from FWD, just a direct SELECT I noticed. Yes, RTRIM=TRUE is in the JDBC URL.

Right. The code is:

case RTRIM:
            result = ValueString.get(StringUtils.trim(v0.getString(), false, true, v1 == null ? " " : v1.getString()),
                    database);

This shows that the returned string is case sensitive. This is indeed a bug. I will fix it now.