Pervasive PSQL Control Centre更新254-VB Currency字段异常求助

Let’s break down what’s happening here and how to fix this frustrating quirk:

1. First, Fix the Critical Syntax Error in Your Query #

Your original WHERE clause has a mistake: wrapping Posting in single quotes treats it as a string literal, not a column name. That means your query wasn’t actually targeting any rows (unless you had a row where the string 'Posting' equals 215288, which is impossible). Correct this by using double quotes for the column name:

Update TABLE set "remBal" = 1000000 where "Posting" = 215288;

2. Understand Why the Value is Getting Corrupted #

VB Currency (type 254 in Pervasive) is stored as a 64-bit integer that represents the actual value multiplied by 10000 (to handle 4 decimal places without floating-point errors). When you enter 100.00 directly in PCC’s SQL editor, it doesn’t automatically convert this float to the correct integer format for the Currency type. Instead, it’s interpreting the float’s binary data as the raw integer storage value, which leads to that massive, incorrect number you’re seeing.

3. Reliable Methods to Update the Field #

You have two straightforward, working options:

• Use the integer equivalent: Since 100.00 equals 100 * 10000, pass the integer 1000000 directly (this matches how VB Currency stores the value under the hood):

  Update TABLE set "remBal" = 1000000 where "Posting" = 215288;
  

• Use explicit casting: If you prefer working with decimal values, use CAST to convert the float to the Currency type (Pervasive v10.30 supports this for VB Currency):

  Update TABLE set "remBal" = CAST(100.00 AS CURRENCY) where "Posting" = 215288;
  

4. Why VAccess Works But PCC Doesn’t #

VAccess controls are built specifically to integrate with VB and Pervasive’s data types. They automatically handle the conversion between human-readable decimal values and the raw integer storage format of VB Currency. PCC’s SQL editor, however, treats values as raw literals unless you explicitly cast them, so it skips that critical conversion step.

内容的提问来源于stack exchange，提问作者Doug