ColdFusioning: Pronunciation \kold-fy�-zhn-ing\ Noun: The action of one that writes ColdFusion.

Using ColdFusion and Oracle To Get A Table's Primary Key

Related Categories: Database, ColdFusion

I recently ran into a situation where I needed to get the primary key of a table, knowing only the table name. Using the following code, you can pass it a table name, and it will return the primary key:

<cfcomponent>
    <cffunction name="getPrimaryKeyName" output="false">
        <cfargument name="Table_Name">
            
            <cfquery name="getPrimaryKeyName" datasource="#request.maindsn#">
            SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
            FROM all_constraints cons, all_cons_columns cols
            WHERE cols.table_name = <cfqueryparam cfsqltype="cf_sql_text" value="#UCase(arguments.Table_Name)#">
            AND cons.constraint_type = 'P'
            AND cons.constraint_name = cols.constraint_name
            AND cons.owner = cols.owner
            ORDER BY cols.table_name, cols.position
            </cfquery>
            
        <cfreturn getPrimaryKeyName.column_name>
    </cffunction>
</cfcomponent>

Then you only need to call the function to get the primary key name:
<cfinvoke method = "getPrimaryKeyName" returnVariable = "request.PrimaryKeyName">
    <cfinvokeargument name="Table_Name" value="#arguments.Table_Name#">
</cfinvoke>


Comment Comments (0) | Posted At : Aug 25, 2009 3:20 PM | Views: 587
Follow me on Twitter Follow me on Twitter | del.ico.us del.icio.us | Digg It! Digg It! | Linking Blogs Linking Blogs

Maximum Open Cursors Exceeded on Oracle with ColdFusion

Related Categories: Database, ColdFusion

After beginning a new project for a client who required I use Oracle (I'm using version 11.1.0.6.0) for the database backend, I started running into the following error:


Error Executing Database Query.
[Macromedia][Oracle JDBC Driver][Oracle]ORA-01000: maximum open cursors exceeded

On solution is to raise the number of cursors in the Oracle configuration. Since this is not always possible or could be difficult to figure out without a dba, there is a solution in ColdFusion.

The problem arises from using cfqueryparam. ColdFusion pools queries so when the parameter changes, but the rest of the query doesn't, it can execute faster. For ColdFusion to pool a statement though, it must keep the cursor open on the database.

To solve the problem, you could remove all the cfqueryparam from your queries. However this is HIGHLY DISCOURAGED because you will lose the performance gains and also open your code up for SQL injection attacks.

Instead, simply go into the ColdFusion Administrator and lower the "Max Pooled Statements" setting. ColdFusion 9 has a default of 300. Lowering the setting to 100 solved the problem. You can experiment with the highest maximum based on your database settings.

To see how many open cursors you currently have open, run the following query:

SELECT COUNT(v.value) as OPENCURSORCOUNT
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid

To view other cursor information, use the following query:

SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid



Comment Comments (3) | Posted At : Aug 8, 2009 9:14 AM | Views: 1217
Follow me on Twitter Follow me on Twitter | del.ico.us del.icio.us | Digg It! Digg It! | Linking Blogs Linking Blogs