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.
