Really? When I made a test on 7.6.04 and 8.1 the stored procedure returned a value, though you needed to cast it on some occasions.
https://bmccommunities.jive-mobile.com/#jive-discussion?content=%2Fapi%2Fcore%2Fv2%2Fdiscussions%2F93451 Mobilis in Mobile. > Le 20 févr. 2014 à 21:42, Charlie Lotridge <[email protected]> a écrit : > > ** > 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 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_ > > _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"
