Re: characters to not use in files with sql and rpgle functions

Yes - the statements are dynamic - mostly search programs - with users entering values for any combination of 10-12 differnt fields, and some are =, some "like", and some are ranges. It still manages to perform well on a small i5 and millions of records.
Over the years (been running 11+ years) I've tweaked the code to handle the issues (like searching name O'ROURKE in a dynamic select "like").

To get good matching on records, such as an address
125 LANE ST
125 LANE STREET
125 LANE ST NW
125 LANE ST NORTHWEST
125 LANE ST NW APT #12
125 LANE STREET NW APT. - 12
I standardize the addresses, all US Postal, using the USPS Pub 28 standards, so that records 1 & 2 match, 3 & 4 match, and 5 & 6 match, and all return in a search for "125 LANE".
Am very aware of street names that should not be abbreviated.
It was the customer's requirement for the matches - it is their business to research property related to construction projects, matching imported public records with internal database, and export records related to a customer's order.

Even though this may sound like a very customized app, it was a diff customer who also imports/exports records who squealed about % embedded in an address (some people use in an address to mean "in care of"), and that led to my question this morning.
Given that we may edit a name entry on our own input to not allow parenthesis in a name, because we want to stop this: ROBERT (BOB), my recommend to the customer is to do a mild cleaning of characters in any import, and edit anything that doesn't meet their own input rules.
It's not an option to pass on ROBERT (BOB) to a government database that says "alpha A-Z and space only". They reject the record.
I guess it all depends on what your doing with the data.
Jim Franz
----- Original Message ----- From: "Scott Klement" <midrange-l@xxxxxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Tuesday, August 17, 2010 1:09 PM
Subject: Re: characters to not use in files with sql and rpgle functions

Hi Jim,

Are you building the SQL statements dynamically with concatenation? If
so, why?

Normally, in RPG, you'd use embedded SQL and build your statement as a
static statement. In this case, having a single quote in your "select
where like" wouldn't do any harm!

But (although you haven't said this) I'm assuming you're building the
statement dynamically... because I can't come up with any other reason
why you'd have a problem like that. If that's the case, you can simply
use parameter markers to solve the "special characters" problem. But I
don't know why you'd do that instead of a static statement.

Can you please clarify your situation?



On 8/17/2010 11:30 AM, franz400@xxxxxxxxxxxx wrote:

Let me back up a bit...
I certainly agree proper sql can handle all characters, and
we have some old code around that have had to fix for
example: sql "select where like" and the parm passed for the
search value had single quote in it.
I guess I was looking to see if others had certain characters
they normally strip out and it seems not..
We do have in imports a check to remove characters not on a normal
keyboard, but that is not consistent, therefore trying to build a
standard list.
Jim

there's nothing intrinsic to sql that prohibits certain characters.
therefore to help you, we'd need to know what characters your program(s)
cant handle...