Re: Imbedding Special Characters in SQL

firstly, Nils, thank you for your response. Nils Myklebust wrote: > > 1. cr/lf are technically invalid characters in a char column so if you > do insert these it's on your own risk. (We do insert other "illegal" > characters in special types of data used for printer control codes and > that works fine. We wouldn't dream of doing it in a case like yours > though.) ..our insertion of cr/lf characters is *precisely* the same as your insertion of special characters "used for printer control codes". We are using these characters to allow enter line breaks in advertising copy. I was always under the impression that cr/lf were "primitive" printer control characters, anyway. > 2. Putting cr/lf in a name column like you intend to do looks to me to > be very bad database design, but there may of course be valid reasons > for it. Normaly I would however look into adding such things upon > retrieval. Is there a spesific reason why you need them in the > database? ..it is not a bad database design (see above for reasons). I should think programmatically inserting proxy characters for a cr/lf sequence and then decoding them upon retrieval would be *much* more arduous than a method of encoding them in the first place. > 3. When you use SQLExecDirect the sql statement is sent as is to the > database engine. At its end the cr/lf is viewed as a new line of > course, and if you check the SQL syntax (from Informix or the ANSI > standard) your statement is illegal. A quoted string can't cross a > newline, although it doesn't look to bad when you see it as you have > written it. ..I am puzzled why our application -- where the native quoted string contains \\n\\r characters -- works with Watsom, Sybase, and SQL Server, and *not* Informix? Bad database design? ;-) > 4. The solution is to prepare/declare an insert statement or cursor > using a variable for the values part of your statement. This variable > can than contain the string you want to insert, including the cr/lf > characters. > ..thank you, your advice corroborates the approach we derived from reverse-engineering what MS Access did.