Error Database Query To Locate Invalid Cr Characters

[techjohnny]

We recently used a tool to automatically update a field, unfortunately the person used copy and paste to update a description field for several thousand items. Now the description field has a character which is a return character that is showing up as a square in our application.

Now whenever we try and view any reports in Crystal that contain this character it reports an invalid hexadecimal character.

I want to run a query against the database that will display all items with this character in the description, so can somebody help?

Update: I run this query using Procedure Editor "select PartDescription from Part where partnum = ".002 3M TAPE" it returns:
** Invalid character unit value 1001, Changed to 320 (4132)
** FILL-IN Description will not fit in FRAME in PROGRAM. (4028)

Thanks.

 

[TheMadDBA]

Using SQL in the procedure editor is a recipe for disaster.... the 4GL solution

for each part where part.partnum =  ".002 3M TAPE":
  display part.partdescription view-as editor size 50 by 20 with no-box.
end.

If you know the ascii value of the problem character you can just do this.... (substitute the 11 in CHR(11) for the actual ascii code/codes you are having issues with). Only records that actually have the character to replace will be updated, Progress will discard the updates for assigns that replace the current value with the current value.

for each part:
  assign part.partdescription = REPLACE(part.partdescription,CHR(11),"").
end.

 

[techjohnny]

Using SQL in the procedure editor is a recipe for disaster.... the 4GL solution

for each part where part.partnum =  ".002 3M TAPE":
  display part.partdescription view-as editor size 50 by 20 with no-box.
end.

If you know the ascii value of the problem character you can just do this.... (substitute the 11 in CHR(11) for the actual ascii code/codes you are having issues with). Only records that actually have the character to replace will be updated, Progress will discard the updates for assigns that replace the current value with the current value.

for each part:
  assign part.partdescription = REPLACE(part.partdescription,CHR(11),"").
end.

Thank you. That definitely helps. What's the proper syntax to use MATCH in a query for hexadecimal value 0x1E in the partdescription field? I'm trying matches CHR(0x1E), but it does find anything. I've already tried wildcards, but not sure if enclosing in quotes is required and how that will affect the pattern matches.

 

[TheMadDBA]

What ascii number depends on the code page of your database and client.... Hex 0x1E is usually CHR(30).

for each part where part.partdescription matches "*" + CHR(30) + "*" :

 

[techjohnny]

Thanks! That is the CHR value I was looking for in the database. Unfortunately our database now has several thousands entries with that CHR, which is now crashing Crystal Reports when trying to view reports.

It also looks like our version of Progress will not allow us to update the database from the Procedure Editor. Will we need another version of OpenEdge to make this update workable?

Thanks.

 

[TheMadDBA]

That depends on whether the license itself is preventing updates or that is controlled by the startup settings. You would need to post the output of a showcfg (except the serial numbers and control codes) to determine that.

Since it seems you have SQL update access you can always use replace the characters with a SQL-92 update statement. REPLACE has the same syntax on the SQL side and you would need to use CHR for native access or CHAR for ODBC access.

 

[techjohnny]

That depends on whether the license itself is preventing updates or that is controlled by the startup settings. You would need to post the output of a showcfg (except the serial numbers and control codes) to determine that.

Since it seems you have SQL update access you can always use replace the characters with a SQL-92 update statement. REPLACE has the same syntax on the SQL side and you would need to use CHR for native access or CHAR for ODBC access.

Okay, the showcfg is outputting all the licenses and serial numbers attached to this version. What should I be looking for in this output?

 

[TheMadDBA]

The product names. Probably better to just post the names since PSC has quite a few different names for development products.

 

[techjohnny]

Configuration File: c:\epicor\oe101b\PROGRESS.CFG

Company Name: Microsoft

Product Name: Client Networking
Installation Date: Wed Feb 26 15:52:13 2014
User Limit: 100
Expiration Date: None
Version Number: 10.1B
Machine Class: KB
Port Number: 31

Product Name: OE Enterprise RDBMS
Installation Date: Wed Feb 26 15:52:13 2014
User Limit: 100
Expiration Date: None
Version Number: 10.1B
Machine Class: KB
Port Number: 31

Product Name: OE DataServer for Oracle
Installation Date: Wed Feb 26 15:52:13 2014
User Limit: 100
Expiration Date: None
Version Number: 10.1B
Machine Class: KB
Port Number: 31

Product Name: OE DataServer MS SQL Svr
Installation Date: Wed Feb 26 15:52:13 2014
User Limit: 100
Expiration Date: None
Version Number: 10.1B
Machine Class: KB
Port Number: 31

Product Name: OE Application Svr Ent
Installation Date: Wed Feb 26 15:52:13 2014
User Limit: 100
Expiration Date: None
Version Number: 10.1B
Machine Class: KB
Port Number: 31

Product Name: Query/RESULTS
Installation Date: Wed Feb 26 15:52:13 2014
User Limit: 100
Expiration Date: None
Version Number: 10.1B
Machine Class: KB
Port Number: 31

Product Name: NameServer Load Balance
Installation Date: Wed Feb 26 15:52:13 2014
User Limit: 100
Expiration Date: None
Version Number: 10.1B
Machine Class: KB
Port Number: 31

 

[TheMadDBA]

Well that certainly is an odd configuration. Enterprise db, sql server and oracle all at the same time

Query/results means enforced read only except through pre compiled code.

Which database are these columns actually in? Progress or one of the dataservers?

 

[techjohnny]

They are located in a Progress DB.

We have a tool called DMT that can update, but we would need to output the partdescription field in one column, and since some of these part descriptions have several rows, we cannot output into a text file.

 

[techjohnny]

Well that certainly is an odd configuration. Enterprise db, sql server and oracle all at the same time

Query/results means enforced read only except through pre compiled code.

Which database are these columns actually in? Progress or one of the dataservers?

If I can output the partnum and partdescription into a CSV file, our DMT tool can run the updates to strip the record separater, but the SQL query still don't like the long part descriptions.

 

[techjohnny]

This query will work, but still receiving an error that the shared variable localizationmanager variable has not been created.

For each Part where ( Part.PartDescription MATCHES "*" + CHR(30) + "*") no-lock :
DEFINE VARIABLE cNewDesc AS CHARACTER NO-UNDO.
cNewDesc = replace(Part.PartDescription, "*" + CHR(30) + "*", "").
Run lib\UpdateTableBuffer.p(input BUFFER Part:HANDLE, "PartDescription", cNewDesc).
End.

 

[TheMadDBA]

You would need to find the Epicor documentation about which include files or variables need to be defined. The names and data types must match and Epicor might be depending on certain values to be set properly.

BTW - They should usually be defined outside of the FOR EACH loop.

What was the SQL query you ran and what was the error?

 

[techjohnny]

You would need to find the Epicor documentation about which include files or variables need to be defined. The names and data types must match and Epicor might be depending on certain values to be set properly.

BTW - They should usually be defined outside of the FOR EACH loop.

What was the SQL query you ran and what was the error?

Thanks for the help. We ended up exporting part,partdescription to Excel using the substitute formula to replace CHR(30) and finally upload using the DMT tool.

Appreciate your time and help.

 

[RealHeavyDude]

There is another way to find out which records contain certain character. Mostly the utility to convert the database character set is to convert, but, it also contains the charscan option ( see the documentation ). It will generate a report that holds the RECID, table name and field name.

proutil db-name -C convchar [ analyze | charscan | convert ]
[ codepage ] [ character-list ]

Maybe that can help you.

Heavy Regards, RealHeavyDude.