theblindprophet theblindprophet - 6 months ago 12
SQL Question

sid not returned by cfprocparam that is connected to oracle database

The application in question runs on

Coldfusion 11
with
hotfix 7
and running on a
Linux
server that is connected to a
Windows Oracle database
. The goal is to make the application run on
Linux
which is a step by step process (which is why the database in on
Windows server
).

I know the database works because it connects successfully via the
Coldfusion
administrator panel and this
SQL
statement returns an
user_id
successfully:

<cfquery name="qUser" datasource="#Application.datasource#">
SELECT td_user_id
FROM td_user
WHERE ROWNUM <= 1
</cfquery>


The code in question is such:

<cfstoredproc procedure="td_session_pkg.new_session" datasource="#application.datasource#">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" value="#arguments.username#">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" value="#arguments.password#">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" value="#cgi.remote_addr#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#rand_int#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#application.app_id#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="1">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" value="#pwdRequired#">
<cfprocparam type="Out" cfsqltype="CF_SQL_CHAR" variable="sid">
</cfstoredproc>

<!--- Test --->
<cfquery name="testS" datasource="#Application.datasource#">
SELECT
sys_context('USERENV','SID') AS theSid
FROM dual
</cfquery>
<cfthrow message="SID: #sid# and #testS.theSid#">


The
cfthrow
returns


SID: (blank here) and 37


I've had tried many things but have yet to return the
sid
correctly (it is needed to continue user authentication). The values inside the
cfprocparam
tags are variables stated above and all have valid values.


  • Coldfusion variables are not case sensitive, as stated by Adobe



Any insight as to why this wouldn't return the
sid
and why the
SQL
statement would? (from my research I believe they are the same thing).

Thanks for the help, I am will and answer any questions quickly.

MT0 MT0
Answer

Since the code I posted in comments has the white-space mangled:

SET SERVEROUTPUT ON;

DECLARE
  sid VARCHAR2(20);
BEGIN
  td_session_pkg.new_session(
    'username',
    'password',
    'remote_addr',
    0,
    0,
    1,
    0,
    sid
  );
  DBMS_OUTPUT.PUT_LINE( 'SID' || sid );
END;
/

Run this in an SQL client (i.e. SQL developer or SQL/Plus) from the user you would connect to via ColdFusion.

The aim is to test that the database:

  1. Has the procedure.
  2. The database user has access to it.
  3. The procedure runs in the database.
  4. The procedure gives the expected result.

Running the query successfully will tell you that #1-#3 are fine and you can check #4 against your expectations.

If any aren't as expected then you know the problem is at the database end and not in ColdFusion.

If they run as expected then you can start looking at the CF settings to make sure your datasources are pointing where you expect (i.e. are they connecting to the correct user) and that the datasource has permissions to execute procedures.

Then if that is all correct, look at the variables you are passing into the <CFSTOREDPROC> and <CFPROCPARAM> tags - are the datasource and variables as expected.

If you want to formalize it as you go then you could write unit & integration tests which cover the steps you try and next time you want to test things you just run the test suites.