Multilines with 7.2-Dinosaur

Hi there, I desperately need your help. I am just trying to insert text with multilines to a char-field. We still use the very outdated version 7.2 of informix. There must be a way, shouldn't it? - Tried to insert it right away, even escaped does not work. - I found the IFX_ALLOW_NEWLINE-Option on the net. But our server does not seem to know that one. - I tried to create Host-Variables from php (blobs and char-objects called there). This could work, but did not with char-fields (error -608). Do these techniques only apply to blob- and text-fields? I am really, really getting mad!! How could people live without newlines in 1996?? This should be soooooooo simple! Plase help as I still do not believe you cannot insert newlines to a database. Even if it's 10 years old. Thank you veeery much! Yours depressed, Thomas

tom wrote: > Hi there, > > I desperately need your help. > > I am just trying to insert text with multilines to a char-field. We > still use the very outdated version 7.2 of informix. > > There must be a way, shouldn't it? > > - Tried to insert it right away, even escaped does not work. > > - I found the IFX_ALLOW_NEWLINE-Option on the net. But our server does > not seem to know that one. > > - I tried to create Host-Variables from php (blobs and char-objects > called there). This could work, but did not with char-fields (error > -608). Do these techniques only apply to blob- and text-fields? > > > I am really, really getting mad!! How could people live without newlines > in 1996?? This should be soooooooo simple! > > Plase help as I still do not believe you cannot insert newlines to a > database. Even if it's 10 years old. > > Thank you veeery much! The problem is NOT the IDS version. It is the tools you are trying to use to insert the text containing newlines. If you use a host language interface (ie: ESQL/C, ODBC, Perl DBD/DBI, JDBC, etc.) you will have no problems inserting or retrieving a string that contains newlines into a CHARACTER type column! I do it all the time. The problem is that dbaccess, dbload, dbimport have a problem which has partly to do with the delimited file format that they use for file based input and the assumption that dbaccess makes for interactive input that newlines indicate a break in the data and so are not allowed in quoted strings. You could probably use Jonathan Leffler's sqlcmd utility to insert strings containing newlines using the CSV file format (haven't tried it myself - Jonathan, comments?). Art S. Kagel

Art S. Kagel wrote: > tom wrote: >> I am just trying to insert text with multilines to a char-field. We >> still use the very outdated version 7.2 of informix. The first advice would be upgrade, of course. >> There must be a way, shouldn't it? >> >> - Tried to insert it right away, even escaped does not work. If you mean by including a newline in the string literal in a direct INSERT statement, then yes, that doesn't work. >> - I found the IFX_ALLOW_NEWLINE-Option on the net. But our server does >> not seem to know that one. It is more recent than your server - hence the suggestion to upgrade. >> - I tried to create Host-Variables from php (blobs and char-objects >> called there). This could work, but did not with char-fields (error >> -608). Do these techniques only apply to blob- and text-fields? Which version of PHP? Are you using the PDO driver? (I'm going to guess you're using PHP 4 (possibly even 3) and the driver supplied with that. Said driver did not support host variables properly. Time to upgrade to PHP 5.x and the PDO driver.) >> I am really, really getting mad!! How could people live without >> newlines in 1996?? This should be soooooooo simple! We didn't have to. It is simple - you just need to use the right tools. >> Please help as I still do not believe you cannot insert newlines to a >> database. Even if it's 10 years old. Good; you believe correctly. > The problem is NOT the IDS version. It is the tools you are trying to > use to insert the text containing newlines. If you use a host language > interface (ie: ESQL/C, ODBC, Perl DBD/DBI, JDBC, etc.) you will have no > problems inserting or retrieving a string that contains newlines into a > CHARACTER type column! I do it all the time. I agree with this. > The problem is that dbaccess, dbload, dbimport have a problem which has > partly to do with the delimited file format that they use for file based > input and the assumption that dbaccess makes for interactive input that > newlines indicate a break in the data and so are not allowed in quoted > strings. I don't entirely agree with this... The LOAD format files (documented in the text file unload.format in the SQLCMD source code) certainly permits newlines in data, escaping the newline with a backslash; this covers the LOAD and UNLOAD statements in DB-Access, DB-Load, and the DB-Export and DB-Import pair. > You could probably use Jonathan Leffler's sqlcmd utility to insert > strings containing newlines using the CSV file format (haven't tried it > myself - Jonathan, comments?). If I'm reading my code correctly (readload.c), only if the newlines in the CSV fields are escaped with a backslash (by default) character. SQLCMD reads whole records first (each of which is an integral number of lines) and then tries to divvy things up into fields. This isn't as flexible or efficient as it could be, but it does give great predictability. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Hi, thanks for your replys Jonathan and Art. Jonathan Leffler wrote: > Art S. Kagel wrote: > >> tom wrote: >> >>> I am just trying to insert text with multilines to a char-field. We >>> still use the very outdated version 7.2 of informix. > > > The first advice would be upgrade, of course. I would, but WE cannot. It's quite a hassle, but the incompetent people who make the decisions are afraid of upgrading or changing the database. So we all have to stick with this, for the last years ;-( I am new to this business, so I get very frustated. > >>> There must be a way, shouldn't it? >>> >>> - Tried to insert it right away, even escaped does not work. > > > If you mean by including a newline in the string literal in a direct > INSERT statement, then yes, that doesn't work. thats a great pity. But one _could_ handle this. > >>> - I found the IFX_ALLOW_NEWLINE-Option on the net. But our server >>> does not seem to know that one. > > > It is more recent than your server - hence the suggestion to upgrade. unfortunately no upgrades ;-( > >>> - I tried to create Host-Variables from php (blobs and char-objects >>> called there). This could work, but did not with char-fields (error >>> -608). Do these techniques only apply to blob- and text-fields? > > > Which version of PHP? Are you using the PDO driver? > (I'm going to guess you're using PHP 4 (possibly even 3) and the driver > supplied with that. Said driver did not support host variables > properly. Time to upgrade to PHP 5.x and the PDO driver.) > Well, its fortunately php4. The same people who are afraid of upgrading the database are afraid of upgrading to php5. Quite stupid. I am an expert in this. It should be quite harmless to upgrade, but people are just afraid, i think. phpinfo tells me we use this: ESQL/C Version 9.53 I don't know if PDO is used. >>> I am really, really getting mad!! How could people live without >>> newlines in 1996?? This should be soooooooo simple! > > > We didn't have to. It is simple - you just need to use the right tools. I want these, but no chance I guess. Till now, people here replaced newlines with a token when inserting and rereplaced it when they read the data again. embarrassing, isn't it? But actually this seems to be the only way to do it. > >>> Please help as I still do not believe you cannot insert newlines to a >>> database. Even if it's 10 years old. > > > Good; you believe correctly. Good to know ;-) > >> The problem is NOT the IDS version. It is the tools you are trying to >> use to insert the text containing newlines. If you use a host >> language interface (ie: ESQL/C, ODBC, Perl DBD/DBI, JDBC, etc.) you >> will have no problems inserting or retrieving a string that contains >> newlines into a CHARACTER type column! I do it all the time. Our PHP4 uses ESQL/C Version 9.53. But no way of inserting host-variables, i guess. I tried it, but didn't work. I even cannot create any Text-Column to doublecheck if it's a Char-Column-Issue. create table root.cake_posts ( id SERIAL, title CHAR(50), body TEXT DEFAULT NULL null, created DATETIME YEAR TO SECOND, modified DATETIME YEAR TO SECOND ) should work, shouldn't it? I also tried all combinations with NULL, without, with default value and without. The server keeps telling me "Error: Syntax disallowed in this database server." So I can't even check if it would be possible with text-columns. > > > I agree with this. > >> The problem is that dbaccess, dbload, dbimport have a problem which >> has partly to do with the delimited file format that they use for file >> based input and the assumption that dbaccess makes for interactive >> input that newlines indicate a break in the data and so are not >> allowed in quoted strings. > > > I don't entirely agree with this... The LOAD format files (documented > in the text file unload.format in the SQLCMD source code) certainly > permits newlines in data, escaping the newline with a backslash; this > covers the LOAD and UNLOAD statements in DB-Access, DB-Load, and the > DB-Export and DB-Import pair. > >> You could probably use Jonathan Leffler's sqlcmd utility to insert >> strings containing newlines using the CSV file format (haven't tried >> it myself - Jonathan, comments?). > > > If I'm reading my code correctly (readload.c), only if the newlines in > the CSV fields are escaped with a backslash (by default) character. > SQLCMD reads whole records first (each of which is an integral number of > lines) and then tries to divvy things up into fields. This isn't as > flexible or efficient as it could be, but it does give great > predictability. > I need to do it in php. I am writing a database-abstraction layer for a php-framework. Well, I guess I have to live without newlines in the layer and implement the Replacement-strategy in some upper Model-Class. Thanks again. Other solutions are, of course, welcome. bye, Thomas