Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

"Item does not pass validation test" in relation with thread144-425622

Status
Not open for further replies.

aHash

Programmer
Aug 22, 2000
78
US
thread144-425622
Another reason for "Item does not pass validation test" error message to occur.
If your column size is bigger in database table but the corresponding column is shorter in your datawindow then this error appears. In my case the DB (ORacle) table column size was 6 characters with 4 characters + two spaces and datawindow had only size of 4 this error came up. I had to trim the column for trailing 2 spaces. Then it worked.
I am writing this as the trailing two spaces was little harder to find if you are new to PB
 
Only intended as an addtional information on how to do stuff (it's just my little opinion):
-----------------------------------------------------------------------------------------
If your database column size is bigger than the size allowed in your column in the datawindow, the normal thing would be to increment the size (limit) of your column in the database, instead of doing trim of every case that gets you into trouble.

The trimming - by script - is unnecessary if you can 'always' trim trailing blanks, which is advisable since I've seen users that "couldn't type anything" since the full field length was already filled up with blanks. Since they don't show (that's why they call them white space), they're normally unwanted anyway.

You can use :

1 - the database settings of Oracle to achieve a 'trimmed' output always, like you can tell your DB administrator to set the "blank_trimming" property to TRUE on your Oracle database/Schema. Seem to remember it might be posible to do so only on certain tables or event certain fields of tables. (don't shoot me if I'm wrong).
2 - you can do a "SET TRIMOUT ON" using 'execute dynamic' (don't remember if this has to be before or after connecting to the database from powerscript).
3 - or your can use dbparm="trimspaces=1" when connecting from powerbuilder, though this one has to be taken into consideration when reading: , especially for the part where they talk about doing an "update" or "delete" that might give 'rows changed between retrieve and update' error message. (that one might be solved using the 'ResetUpdate()' function right after retrieval from the database
----------------:

ODBC database interface Some ODBC drivers, such as SQL Anywhere, trim trailing spaces before the data reaches the fetch buffer—even when TrimSpaces is set to 0.

If your DBMS makes a distinction between Char data with trailing spaces and Char data without trailing spaces when evaluating a WHERE clause expression, you might receive the message Row changed between retrieve and update when your DataWindow object’s update properties are set to “Key and updateable columns.” To prevent this, change your DataWindow object’s update properties. In embedded SQL, you can check Sqlca.Sqlnrows after each update to determine if the update took place. Avoid using Char data columns in the WHERE clause of an UPDATE or DELETE statement when TrimSpaces=1.


regards,
Miguel L.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top