Stored Procedures

[jl8789]

First of all, thanks to all who have helped me on this site, I am forever greatful. I am near completion of my project and I couldn't have done it without you, especially you Carl.

I am trying to call a stored proc in Cold Fusion.
Here is my code:
<cfstoredproc procedure = &quot;get_order_dups&quot; datasource=&quot;#datasource#&quot; username=&quot;#username#&quot;
password=&quot;#password#&quot; returnCode = &quot;Yes&quot;>

<!--- cfprocresult tags --->
<cfprocresult name = RS1>
<!--- cfprocparam tags --->
<cfprocparam type = &quot;IN&quot;
CFSQLType = &quot;CF_SQL_VARCHAR&quot;
value = &quot;GH45632&quot; dbVarName = @param1>
<cfprocparam type = &quot;IN&quot;
CFSQLType = &quot;CF_SQL_VARCHAR&quot;
value = &quot;JH43434&quot; dbVarName = @param2>
<cfprocparam type = &quot;IN&quot;
CFSQLType = &quot;CF_SQL_VARCHAR&quot;
value = &quot;B&quot; dbVarName = @param3>

<cfprocparam type = &quot;OUT&quot; CFSQLType = CF_SQL_INTEGER
variable = 11_count dbVarName = @param4>
</cfstoredproc>

<cfoutput>
The output param value: ’#ll_count#’<br>
</cfoutput>

I am using Oracle as the database, and I am receiving this error:

The system has attempted to use an undefined value, which usually indicates a programming error, either in your code or some system code.
Null Pointers are another name for undefined values.

 

[erwino]

there are a few errors in the tags, try this:
<code>

<cfprocresult name = &quot;RS1&quot;>
<cfprocparam type = &quot;IN&quot; CFSQLType = &quot;CF_SQL_VARCHAR&quot;
value = &quot;GH45632&quot; dbVarName = &quot;@param1&quot;>
<cfprocparam type = &quot;IN&quot; CFSQLType = &quot;CF_SQL_VARCHAR&quot;
value = &quot;JH43434&quot; dbVarName = &quot;@param2&quot;>
<cfprocparam type = &quot;IN&quot; CFSQLType = &quot;CF_SQL_VARCHAR&quot;
value = &quot;B&quot; dbVarName = &quot;@param3&quot;>

<cfprocparam type = &quot;OUT&quot; CFSQLType = &quot;CF_SQL_INTEGER&quot;
variable = &quot;11_count&quot; dbVarName = &quot;@param4&quot;>

You need to put some of the var's in quotes.

(to return whole recordsets use:
<cfprocparam type=&quot;OUT&quot; cfsqltype=&quot;CF_SQL_REFCURSOR&quot; dbvarname=&quot;some_name&quot; variable=&quot;cursor&quot; maxrows=-1>
you need to specify a refcursor in your procedure)
</code>

Good luck

Erwin Oosterhoorn
Analyst Programmer,
ice hockey player/fan.

 

[jl8789]

I did this and it works!

<CFSET son = '#UCase(FORM.SON)#'>
<CFSET pon = '#UCase(FORM.PON)#'>
<CFSET sfx = '#UCase(FORM.SFX)#'>

<CFIF bCheckForDups>
<cfstoredproc procedure =&quot;GET_ORDER_DUPS&quot; datasource=&quot;#datasource#&quot; username=&quot;#username#&quot;
password=&quot;#password#&quot;>

<CFIF isDefined(&quot;FORM.SON&quot AND #FORM.SON# is &quot;&quot;>
<cfprocparam type = &quot;IN&quot;
CFSQLType = CF_SQL_VARCHAR
value = &quot;~&quot; dbVarName = @param1>
<CFELSE>
<cfprocparam type = &quot;IN&quot;
CFSQLType = CF_SQL_VARCHAR
value = #son# dbVarName = @param1>
</CFIF>
<CFIF isDefined(&quot;FORM.PON&quot AND #FORM.PON# is &quot;&quot;>
<cfprocparam type = &quot;IN&quot;
CFSQLType = CF_SQL_VARCHAR
value = &quot;~&quot; dbVarName = @param1>
<CFELSE>
<cfprocparam type = &quot;IN&quot;
CFSQLType = CF_SQL_VARCHAR
value = #pon# dbVarName = @param1>
</CFIF>
<CFIF isDefined(&quot;FORM.SFX&quot AND #FORM.SFX# is &quot;&quot;>
<cfprocparam type = &quot;IN&quot;
CFSQLType = CF_SQL_VARCHAR
value = &quot;~&quot; dbVarName = @param1>
<CFELSE>
<cfprocparam type = &quot;IN&quot;
CFSQLType = CF_SQL_VARCHAR
value = #sfx# dbVarName = @param1>
</CFIF>

<cfprocparam type = &quot;OUT&quot; CFSQLType = CF_SQL_INTEGER
variable = coun dbVarName = @param4>
</cfstoredproc>

<CFIF #COUN# EQ 1>
<FONT class=&quot;redheader&quot;>That SON, PON, SFX combination already exists.</font><br>