http://coldfusion.com logo
Join Slack
Powered by
this is how it looks like <cfprocparam type="OU...
# lucee
s
this is how it looks like <cfprocparam type="OUT" cfsqltype="INTEGER" dbvarname="@id"> there seems no complete example on how to this in cfdocs or anywhere
z
use threads
and stored procs are tightly tied to the database in use, so mentioning that would also be helpful
s
a stored proc
Copy code
<cfstoredproc procedure="assign">
 <cfprocparam type="IN" cfsqltype="VARCHAR" dbvarname="@itemID" value="#form.ItemId#" null="#!len(form.ItemId)#">
<cfprocparam type="OUT" cfsqltype="INTEGER" dbvarname="@id">
</cfstoredproc>
šŸ¤” 1
z
seriously?
FFS, try and ask a complete question with enough detail that somebody might be able to answer ā€¢ source code for the SP ā€¢ which database you are using
p
What did you get if you run the stored proc on the SQL server or MySQL ( where ever you created it )?
s
@zackster i think i am clear as what i am asking, is there a complete example how the out attribute works if i need to get the last record because in my proc, i am doing return @@identityt()
if you know any examples, share that because i could not find one one which talk about this case, not in cfdocs not in adobe coldfusin docs
there might be something you know which i am not aware of
r
It's not a ColdFusion issue. It's a database issue. Look up how to output a value from a stored procedure for the database engine you are using. Below are the first results doing a Google search. SQL Server - https://www.sqlservertutorial.net/sql-server-stored-procedures/stored-procedure-output-parameters/ MySQL - https://www.mysqltutorial.org/stored-procedures-parameters.aspx
s
if this is the sp
Copy code
CREATE PROCEDURE uspFindProductByModel (
    @model_year SMALLINT,
    @product_count INT OUTPUT
) AS
BEGIN
    SELECT 
        product_name,
        list_price
    FROM
        production.products
    WHERE
        model_year = @model_year;

    SELECT @product_count = @@ROWCOUNT;
END;
how when i am caling the stored proc wth cfstoredproc, its not returning me the value then, i am using out in my case if you se at the code i posted, i have the sp running and giving me data
its from cf end, i can not seem to find why on hell its not returning my the outvalue in the cfdump the results attribute of cfstoredproc
my question is simple, if i run my sp in db, i get the output i am using scope_identity
so even when i use cfquery or cfstoredproc, it is not returing me the id in the dump so i can reuse anywhere
found my own answer
šŸ‘šŸ» 1
cfproc sucks
m
i'm assuming your solution is something like : 
cfprocparam( type="out", cfsqltype="cf_sql_integer", variable="pcount");
then you can reference pcount in your code. But, if you found something different, it could help future people to share your finding.
s
i reverted back to cfquery and just dumped it, it gave me the recordcount variable value
i tried result but that did not worked againset sp
so i called the queryname got the data back
There should be good documentation on cfstored proc, all i see on google is how to call it and no one even adobe explained how out attribute works
m
Copy code
Any one of these should work on lucee with sql server for your 'if this is the sp' question

cfstoredproc( procedure="uspFindProductByModel" ) {
	cfprocparam( value="2022");
	cfprocparam( type="out", variable="pcount");
}
dump(pcount);

cfstoredproc( procedure="uspFindProductByModel" ) {
	cfprocparam( sqltype="integer", value="2022");
	cfprocparam( type="out", sqltype="integer", variable="pcount");
}
dump(pcount);

cfstoredproc( procedure="uspFindProductByModel" ) {
	cfprocparam( cfsqltype="cf_sql_integer", value="2022");
	cfprocparam( type="out", cfsqltype="cf_sql_integer", variable="pcount");
}
dump(pcount);

cfstoredproc( procedure="uspFindProductByModel" ) {
	cfprocparam( cfsqltype="cf_sql_integer", value="2022", dbvarname="@model_year" );
	cfprocparam( type="out", cfsqltype="cf_sql_integer", variable="pcount", dbvarname="@product_count");
}
dump(pcount);
Open in Slack
PreviousNext
Powered by