Re: Imbedding Special Characters in SQL

"William H. Phinizy" <kb6hk@gte.net> wrote: :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. We put nothing but printer controls in a column. That's different. They are basically font change codes for the different types of printers. And yes of course cr/lf is a kind of printer control characters. : :> 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. I would *not* put in proxy characters. I would *think* of putting each part in a separate column or row and let the application insert the cr/lf. However your design may be very good for your purposes. :> 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? ;-) No. But it's a little strange. May be the quoted string gets converted to a "variable" somewhere in the communication path (by the ODBC driver or whatever) instead of beeing sent to the engine as is? Or more likely they may allow cr/lf in quoted strings, something I doubt very much is ANSI standard but would have to check on that. :> 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. Hope it works out, and it should work equally well with the other engines. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org