LJ, the doc indeed does say that there's no syntax checking, and I did chase it down the rabbit hole insofar as to do a SQL log to confirm that ARS thinks it's submitting the SQL to the DB. Just to make sure, I then cut & pasted that same SQL from the SQL log and into a query window and executed it - it worked just as expected.
Shawn, WRT stored procedures, the doc<https://docs.bmc.com/docs/display/public/ars81/Assigning+values+through+SQL+statements>says "A stored procedure with a Set Fields action executes all its commands but does *not* return a value." If your stored procedure returns a result set you'd like to use in a Set Fields then, again, the solution here would be to wrap the call to the stored procedure in a SQL view and call that. And, again, it's a less convenient solution. On Thu, Feb 20, 2014 at 12:30 PM, LJ LongWing <[email protected]> wrote: > ** > the interesting thing is that what I remember of the docs state that > Remedy does no syntax checking of your SQL to determine if it's accurate, > it trusts you to do that. And because of that, I must wonder if you traced > the SQL all the way to the DB and determine what it's doing with that....to > see if it's executing it or not....not sure what would be found if you went > down that rabbit hole. > > > On Thu, Feb 20, 2014 at 1:25 PM, Charlie Lotridge <[email protected]>wrote: > >> ** >> 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"
