Textedit - Varchar and \n in construct

[Stephen T.]

If you have a textedit that allows for returns in the field - how do you then search for for values in that field that contain the \n?

IE I have a construct by name into my string field, that string field is then appended to an overall prepared/declared cursor. The prepare fails with a 282 as the string now contains embedded \n characters. I have tried changing the \n to escape it before preparing the cusror, changeing to \xoa but inevitably I get something like:
(SELECT mess.* FROM MDIMessage AS mess WHERE m_mess.messagetext='line 1\x0aline 2\x0aline 3' ORDER BY messageType)

translate: 0 SELECT mess.* FROM MDIMessage AS mess WHERE m_mess.messagetext='line 1
line 2
line 3' ORDER BY messageType
translate: 1 SELECT mess.* FROM MDIMessage AS mess WHERE m_mess.messagetext='line 1
line 2
line 3' ORDER BY messageType
PREPARE
  STATUS:-282
  NAME_fgl:mMDIMessage.p_read_mess
  NAME_sql:cu18
  COMMAND:SELECT mess.* FROM MDIMessage AS mess WHERE m_mess.messagetext='line 1

So, the display is from the code before the prepare and the other output is from SQLDEBUG.

Any ideas?

[Sebastien F.]

Steve,

First we need to identify what's the way to specify \n in an SQL string constant. This is probably vendor specific...

Then we can make CONSTRUCT replace \n by the SQL equivalent, according to the database type.

I will have a look at this.

Seb

[Sebastien F.]

Steve,

What version of FGL are you using?

I had a look at the code and actually since build 966.40 the CONSTRUCT will generate an "Error in field" message if user enters a NL character during a construct:

build 966.40
bug 8166: CONSTRUCT should raise error when invalid chars like LF are used.

So we faced this issue before and solved it by denying NL chars input during a CONSTRUCT...
Maybe that's the best way (do user really enter search criteria with NL???)

May I suggest that you move to version 2.11 in a first time?

Seb

[Reuben B.]

Steve,

What version of FGL are you using?

I had a look at the code and actually since build 966.40 the CONSTRUCT will generate an "Error in field" message if user enters a NL character during a construct:

build 966.40
bug 8166: CONSTRUCT should raise error when invalid chars like LF are used.

So we faced this issue before and solved it by denying NL chars input during a CONSTRUCT...
Maybe that's the best way (do user really enter search criteria with NL???)

May I suggest that you move to version 2.11 in a first time?

Seb

Seb,

I reported 8166 February last year.  From memory the users don't intentionally enter a NL in the search criteria.  The TEXTEDIT was the last field, they pressed ENTER thinking it would accept the dialog, when nothing happend, they'd use the mouse to click OK or TAB there way out of the TEXTEDIT, but in doing so they'd leave the NL in there. 

Reuben

[Sebastien F.]

I made some tests with the different databases we support:

Just found that Informix IDS 11 has a config parameter ALLOW_NEWLINE or EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('t'), to allow NL characters in SQL strings.

Following databases (latest versions) accept NL in the SQL strings by default:

Oracle, PostgreSQL, MySQL, DB2, SQL Server, Genero DB.

It would not be a big deal to reject the fix for 8166 and allow NL characters during CONSTRUCT, but then we may face the same error -282 again with older Informix databases... and I don't want to introduce a new FGLPROFILE entry to handle that...

Is there a real need to allow search for data with NL characters?

Seb

[Stephen T.]

Seb,
IMHO if you allow characters to be put into a field, then you have to allow to search on that character. Or else what is the 'rule'?
What I mean is - assume someone has entered an address into a location field - they put in:
My address \n
In my street\n
etc
If I then search for My address In My street - would you expect it to find it?

You also say that PostgreSql caters for newlines in the SQL - but how do you get that into a 'string' to build the prepared/declared cursor?

[Sebastien F.]

To specify a NL character in SQL command interpreters like dbaccess or sqlplus, you just go to the new line with RETURN, while with MySQL you can use \n as on UNIX.... So it's vendor specific...

Actually I checked if the databases support NL in SQL string with following sample program:

MAIN
  DEFINE cond STRING, cnt INTEGER

  --DATABASE test1 EXECUTE IMMEDIATE "EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('t')"
  --CONNECT TO "orc2fox+driver='dbmoraA2x'" USER "orauser" USING "fourjs"
  --CONNECT TO "test1+driver='dbmpgs83x'" USER "pgsuser" USING "fourjs"
  --CONNECT TO "test1+driver='dbmmys51x'" USER "mysuser" USING "fourjs"
  --CONNECT TO "test1+driver='dbmdb28x'" USER "db2user" USING "fourjs"
  --CONNECT TO "ads_361+driver='dbmads3x'" USER "adsuser" USING "fourjs"
  CONNECT TO "freetds_msvtest1_cobra+driver='dbmftm90'" USER "msvuser" USING "fourjs"

WHENEVER ERROR CONTINUE
  DROP TABLE tt1
WHENEVER ERROR STOP
  CREATE TABLE tt1 ( k INT, col1 VARCHAR(100) )

  LET cond = "INSERT INTO tt1 VALUES( 1, 'abc", ASCII(10), "def' )"
  EXECUTE IMMEDIATE cond

  LET cond = "SELECT COUNT(*) FROM tt1 WHERE col1 = 'abc", ASCII(10), "def'"
  PREPARE s1 FROM cond
  EXECUTE s1 INTO cnt
  DISPLAY cnt

END MAIN

[Sebastien F.]

Seb,
IMHO if you allow characters to be put into a field, then you have to allow to search on that character. Or else what is the 'rule'?
What I mean is - assume someone has entered an address into a location field - they put in:
My address \n
In my street\n
etc
If I then search for My address In My street - would you expect it to find it?

I think one would naturally search with the criteria "*My address*" or "*in my street*" ... or "*My address*in my street*" ...

I mean, how can you be sure that an address was entered with a \n between "My address" and "In my street"?

So if you enter the search criteria:

My address\n
In my street*

... and you find no rows you conclude that there are no rows with that address?

But I agree with you, I am now 50/50 to re-introduce this behavior...

I am just scared about raising again -282 errors with Informix versions older that 9.20, where ALLOW_NEWLINE was introduced, or with >=9.20 IDS versions not configured with that option...

Seb

[Stephen T.]

It's one of those odd ones Seb - but I still think (IMHO) that if you allow a field to contain a specific character, you have to allow the user to search for it. Obvioulsy if we diidn't want N/L chars in a textedit, we can disable that anyway - but one of the benefits of a textedit field is that you can format it to a degree with embedded N/L's. I'm not saying that users WILL then want to search using that char, just that they may want to (and we, like Reuben, have had instances of users inadvertantly leaving a N/L in a text edit field and then trying to search.
The problem is you never know what a user will end up using the textedit field for, and you cannot then know what they will want to search for.

[Sebastien F.]

Yes, I agree but if we change the behavior back to allow NLs in CONSTRUCT, someone may complain again because of error -282 (bug 8166 reported by Reuben)...  and I don't want to re-introduce this "bug" ...

Should we have a new FGLPROFILE entry to drive this behavior (default denies NLs in CONSTRUCT)?

   Dialog.constructAllowsNewLine

I doubt there is a smart way to detect if a database supports NLs in SQL Strings...

But we want to minimize the number of FGLPROFILE entries because it results in too much combinations that are quite impossible to QA.

What a dilemma!

[Sebastien F.]

I think NL usage in SQL strings is standard SQL, so it makes sense to support that in CONSTRUCT, even if it re-introduces bug 8166.
For 8166, we can consider that Informix should be configured with ALLOW_NEWLINE = 1.
If someone complains about that, we can introduce and FGLPROFILE entry to control this behavior.
Seb

[Reuben B.]

Seb,

Perhaps the behaviour of the CONSTRUCT ought to be altered so that it treats trailing NL the same as trailing spaces and ignores them.

If in an EDIT field in a  CONSTRUCT you type A[space][enter], the where clause is field='A', the trailing space is removed.  So if in a TEXTEDIT you type A[enter][click OK] then the where clause should be field='A' rather than field='A\n'.  Embedded NL would still be OK, so if you type A[enter]B[enter][click OK] then the where clause would be field = 'A\nB' rather than field = 'A\nB\n'

Reuben

[Sebastien F.]

Seb,

Perhaps the behaviour of the CONSTRUCT ought to be altered so that it treats trailing NL the same as trailing spaces and ignores them.

If in an EDIT field in a  CONSTRUCT you type A[space][enter], the where clause is field='A', the trailing space is removed.  So if in a TEXTEDIT you type A[enter][click OK] then the where clause should be field='A' rather than field='A\n'.  Embedded NL would still be OK, so if you type A[enter]B[enter][click OK] then the where clause would be field = 'A\nB' rather than field = 'A\nB\n'

Reuben

Yes, I thought of that...
Actually right trim occurs also in normal INPUT... When you input 'A[space]' you get a 'A' in the variable.
But your suggestion does not match the request from Steve:
If you can enter 'A[NL]' in the database, it must be possible to search for 'A[NL]' ...
To me it's legal to insert 'A[NL]' in the database...

Seb