Yeah, things start to get funny when you surpass the varchar limit (typically 4000 chars) and head into LOB territory. Maybe it had something to do with that.
I've never used such a large query in a Set Fields, so I don't think I've ever seen this issue. -charlie On Thu, Feb 20, 2014 at 3:21 PM, Joe D'Souza <[email protected]> wrote: > ** > > I've once hit a limitation wherein a long SQL command (which ran perfectly > on the SQL client) didn't work within the SQL Set Fields action and > returned an error (do not recall what error as this was many years ago). So > since then I have tried to limit my use of SQL in Set Fields only if the > number of fields you are returning back are few. I do not know if this > limitation is now not an issue. If I recall right, I hit this limitation in > a very early release of 7.0 and I recall testing it on 6.3 and found that > to be true on that version too. > > > > If I recall right, the limitation was not because of the number of fields > being returned, but the actual string length of the SQL statement. > > > > Joe > > > ------------------------------ > > *From:* Action Request System discussion list(ARSList) [mailto: > [email protected]] *On Behalf Of *Charlie Lotridge > *Sent:* Thursday, February 20, 2014 3:25 PM > > *To:* [email protected] > *Subject:* Re: FYI...SQL in Set Fields MUST begin with "SELECT" > > > > ** > > Interesting, thanks for this info. > > > > I suppose this could be what's going on, but I'd think it would be more > appropriate for ARS to error if the SQL violates some rules, rather than > operate as if the query returned no results. It's misleading. Also, the > documentation mentions nothing about any such rules. > > > > That Jaspersoft doc mentions that the SQL "should start with SELECT" and > "cannot > have comments". And while Remedy seems to be adhering to the first part of > this, it's not adhering to the second: I was able to insert comments both > as separate lines, and at the end of lines, and it works correctly. > > > > -charlie > > > > On Thu, Feb 20, 2014 at 12:12 PM, Janie Sprenger <[email protected]> > wrote: > > ** > > I think there are security requirements for web applications and one of > the requirements is to prevent SQL injection. Not sure, but perhaps Remedy > is using something of this sort with the midtier. > > > > I ran into something similar with iReports and Jaspersoft when I was > writing an SQL query only mine happened to be with setting a variable to > begin with instead of a Comment. I could run the iReport in the tool but > not on the JasperSoft web client. > > > > You can read more about it here. > > > http://community.jaspersoft.com/wiki/jaspersoft-security-changes-and-configuration > > Janie > > > > On Thu, Feb 20, 2014 at 11:59 AM, Charlie Lotridge <[email protected]> > wrote: > > ** > > I use quite a lot of SQL in my workflow, yet somehow never discovered this > one before. It turns out that if you're using SQL to pull data back for a > Set Fields action, it must begin with the SELECT keyword, or it won't > return any results. > > > > For example, if you have a Set Fields with this SQL: > > > > *SELECT name* > > *FROM arschema* > > > > it'll work fine. But if you insert a comment before it: > > > > *-- Comment* > > *SELECT name* > > *FROM arschema* > > > > or even > > > > */* Comment */ SELECT name* > > *FROM arschema* > > > > the Set Fields will operate as if "No Request Match" (i.e. it'll display > the No Match error, or set the target fields to NULL, depending upon how > you've got it configured). > > > > What's interesting here is that the SQL in these queries is syntactically > correct and they're submitted to the database by ARS without any error. If > you submit the SQL manually (through SQL Plus or SQL Server Management > Studio, etc), it works correctly and returns the expected data. > Apparently, though, Remedy doesn't know how to deal with it if it doesn't > begin with the keyword SELECT. > > > > I only just discovered this because I was attempting to use a query > containing a WITH clause in SQL Server to create a Common Table Expression > to flatten out a recursive data structure. Using the WITH clause, which > MUST be first in the query (and can't be contained in a subquery) is the > only way to do this in a single query. > > > > Of course, the work-around is to create a view containing the CTE, which > is what I ultimately had to do. It's just a less convenient solution. > > > > Anyway, just something interesting I just discovered. > > > > -charlie > > _ARSlist: "Where the Answers Are" and have been for 20 years_ > > > _ARSlist: "Where the Answers Are" and have been for 20 years_ > > > > _ARSlist: "Where the Answers Are" and have been for 20 years_ > _ARSlist: "Where the Answers Are" and have been for 20 years_ > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
