CFStoredproc syntax help, please?

Report · Jan 21, 2011

I need to use cfstoredproc tags to call a str. proc. written in Sybase. This str. proc. requires 4 parameters to be passed in and will returns 4 variables back

So I wrote this (see the cfstoresproc script below )

I need to use the return results in my SQL insert statement. Please help!

The following is the declaration on the Sybase str. proc. site, just to show you the relation between the two:

CREATE PROC EmpStat

@FNAME   VARCHAR(10) ,
@LName   VARCHAR(15) ,
@MName   VARCHAR(15) ,
@Gender   VARCHAR(1) ,

@DATA1 VARCHAR(40) out,

@Deleted User    VARCHAR(40) out,
@Data3    VARCHAR(40) out,
@Data4    VARCHAR(6) out

AS .......etc

<cfprocresult name="Data1"

<cfprocresult name="Data2"

<cfprocresult name="Data3"

<cfprocresult name="Data4"

</cfstoredproc>

Or should I write it this way, what are the differences?

<cfprocresult name="Data1"

<cfprocresult name="Data2"

<cfprocresult name="Data3"

<cfprocresult name="Data4"

</cfstoredproc>

Report · Jan 21, 2011

The second option is closest: dbvarname is deprecated to the point of obsolescence, and the docs say it culd cause unexpected behaviour. So you should never use them.

But you don't need the <cfprocresult> tags. The output params from the proc will populate the OUT <cfprocparam> variables.

--

Adam

Report · Jan 21, 2011

If the second script is the suggested approach then I'm assuming the order of these "IN" variables should be the same as those listed in Sybase str. proc. ?

And if cfprocresult is not necessary, than I can just use Data1 to 4 in my insert, such as:

Insert into column1, column2, column3, column4 Values ('#Data1#', '#Data2#', '#Data3#', '#Data4#') ?

Report · Jan 21, 2011

Yup, on both counts.

--

Adam

Report · Jan 21, 2011

Aother approach is to use a cfquery tag to run your stored procedure. The performance will be the same, but you will end up with less code and more readable code.

Report · Jan 21, 2011

Hi Dan,

I've never used cfquery to call str. proc., if I use this method I'm not sure how to write for the return result. In this case Data1,Data2,Data3,Data4

EXEC MyStrProc '#FName#', '#LName#', '#MName#', '#Gender#' .... this are the parameter oassed into the str. proc. but how to say I need to get Data1,2,3,and Data4 back from str, proc?

</CFQUERY>

Report · Jan 21, 2011

The performance isn't the same on a high traffic site, plus a <cfquery> tag will only work if the procs return a single recordset. So it's no good for procs that return multiple record sets, or any sort of return value other than recordsets.

Our DBA also baulked at calling a proc that way, and muttered something unintelligible about my parentage when I tried it. He's a nice bloke, so I guess there was something he didn't like about it.

I just want ot know how he found out about my parentage!

😉

--

Adam

Report · Jan 21, 2011

Thank you everyone for the valueable suggestions!! Thank you!!

Report · Jan 22, 2011

No doubt you tested this. How much of a difference did you see? I had done a very simple test on our developement server, which is very low traffic, and noticed no difference at all.

Report · Jan 22, 2011

Nope. However I recognise I am just a CF dev who knows a bit about DBs

(so: a) just enough to be dangerous; b) like most CF developers). On

the other hand our DBA's entire career has been based on developing

with and managing SQL Server DBs. If he says something: I believe it.

I'd be a muppet not to.

Equally, if the bods who came up with JDBC came up with specific ways

to execute SQL statements, and another way to execute procs... and so

does CF... and so does the DB... there'll be a reason for that, isn't

there?

Off the top of my head, using skips that first

bit. Also, if it's the first time you call that proc via that SQL

string, then the DB will need to compile the statement first, too.

And if you're not binding your params (*)... it'll need to compile it

every time you call the proc. So just not a very clever way of

going about things, if you ask me. Just because one can do

something, doesn't mean one should do something.

--

Adam

(*) and obviously that only means IN params, as I can't see how one

would bind OUT params in a statement? That said, I've never

tried.

Report · Jan 22, 2011

There's nothing inherently wrong with executing a stored procedure from a query. JDBC (and ODBC, and ADODB, etc, etc) provide separate mechanisms for calling stored procedures because there are some things that you can't effectively do - the things you listed - but if you're not doing those things, there is no performance cost or functionality loss from calling the SP from a query. There have been issues with specific data access layer and database combinations that might prevent you from calling an SP from a query - Oracle and ODBC come to mind, where you couldn't get a recordset from an SP called through a query. But if you don't have those issues, there's no problem. With SQL Server specifically, there are no problems.

We've done this with some large, high-volume applications, with no degradation of performance and functionality. As you point out, you're responsible for building prepared statements if you do this; using CFSTOREDPROC eliminates the need to do that. But you're responsible for building prepared statements for queries that don't call SPs.

And while your DBA is probably a smart guy - he was able to identify your parentage after all - I've been told some pretty dumb things by DBAs before. DBAs don't necessarily know that much about application development, data access layers like ODBC/JDBC/etc. The MS database certification exams don't really cover data access layers in any depth. So, trust but verify if you know what I mean.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Dave Watts, Eidolon LLC

Report · Jan 22, 2011

For fvck's sake: nice mangling of my posting, Jive Software.

The Jive Software dev team should be banned from writing code. And go back to flipping burgers or whatever it is it suggests they are qualified to do on whatever diplomas they have. What an embarrassment for Jive Software. And Adobe for running this crap.

Anyway.

What I meant to say (and, indeed, have in my Sent Items...) was this:

{quote}

Off the top of my head, using CFQUERY to execute a proc is going to
require to perform slightly more work to determine what to run: it's
got to parse the SQL string you pass in, work out it's a proc you want
to run, then run the proc. Calling CFSTOREDPROC skips that first
bit. Also, if it's the first time you call that proc via that SQL
string, then the DB will need to compile the statement first, too.
And if you're not binding your params (*)... it'll need to compile it
*every time* you call the proc. So just not a very clever way of
going about things, if you ask me. Just because one *can

{quote}

It makes slightly more sense that way. I can kinda understand that it's a common newbie trap to not escape angle brackets so the CFQUERY and CFSTROEDPROC references (which I have subsequently changed to avoid yet another truncation) would get trimmed, but I cannot see how they managed to trim out an entire sentence! Muppets.

--

Adam

Report · Jan 22, 2011

Silly me forgot how crap this forum software is

I am normally more forgiving .. but it truly is awful. What is the point of allowing responses via email if the only way to post an intelligible response is via the web application.

Report · Jan 22, 2011

I'm usually more forgiving too. And if this was some random forum thing that some blogger had knocked together in their spare time,it'd be more than adequate.

But this is Adobe's website. 75th most significant website on the 'net (according to Alexa, anyhow).

It's embarrassing.

--
Adam

Report · Jan 22, 2011

It's embarrassing.

Very.