Robert, Suggestion #2 below fixed the issue!
I had tried replacing the quotes with <@SQ> which had not worked, interestingly. You are right that this is CustomSQL, though, and oddly enough I did not even see the Automatic SQL encoding checkbox. Too many years of working with the old Witango 5.5 client, I think. No need to use it, however. Using the new @VAR feature did the trick. I greatly appreciate the help. Thank you! Erik On Feb 8, 2013, at 10:31 AM, Robert Shubert <[email protected]> wrote: > Erik, > > Few things to try: > > 1) instead of typing the ' character, try using the @SQ tag > > 2) the @VAR in 6.2 now has a new array attributes csep= and rsep=. These > allow for placement of separators in the direct string conversion of an array > variable. > > In other words, this should work: <@VAR positionList aprefix='(' asuffix=')' > rprefix= rsuffix= cprefix=<@SQ> csuffix=<@SQ> rsep=','> - this will have the > benefit of only executing one tag. > > 3) I'm assuming that you are doing this in a CustomSQL action - if not, the > other actions automatically convert arrays to IN lists. > > 4) if you are in a CustomSQL action, try toggling the Automatic SQL encoding > checkbox at the bottom of the query window, and then apply your own encoding > (encoding=sql) where needed. > > If none of these ideas help, please email me the TAF at > [email protected] I have a FMP 11 server that I can test with. > > Robert > > -----Original Message----- > From: Erik Gorka [mailto:[email protected]] > Sent: Thursday, February 07, 2013 6:46 PM > To: [email protected] > Subject: TeraScript-Talk: noSQLEncoding config ignored > > We're running Terascribe Server 6.2 and are in the process of upgrading our > FileMaker 10 server which we've been accessing via JDBC to FileMaker 11. > Seems FileMaker 11 is very picky about data typing, which has revealed a > problem where, in some cases, part of the SQL is having its single quotes > doubled, breaking the query. > > As mentioned it is not doing this in all cases, but refuses to let go of this > one. I've tried turning the "noSQLEncoding" configuration to "true", but the > setting is being ignored. In this case, we're creating a list of items for an > "IN" statement based on an array, like this: > > and position in (<@rows array="request$postionList"><@if expr="<@currow> != > 1">, </@if>'<@col 1>'</@rows>) > > This list of positions are numbers, the the field in FileMaker is a text > field. So if I remove the quotes it breaks because the items are not of the > correct type (worked fine for FM 10 but not for 11), but add the quotes and > the resulting sql in the logs looks like this: > > and position in (''05040'', ''24762'', ''24763'', ''72010'') > > There are other quoted parts of the statement above this one, but only this > part is getting the doubled single quote treatment. Is this a bug or am I > doing something wrong? > > Erik Gorka > Reed College > > > > ---------------------------------------- > > To unsubscribe from this list, please send an email to > [email protected] with "unsubscribe terascript-talk" in the body. > > > > > ---------------------------------------- > > To unsubscribe from this list, please send an email to > [email protected] with "unsubscribe terascript-talk" in the body. > ---------------------------------------- To unsubscribe from this list, please send an email to [email protected] with "unsubscribe terascript-talk" in the body.
