Tag Archives | SQL

ColdFusion and Oracle settings for Idle timeout and SQLDeveloper

Over at the ColdFusion Muse by Mark Kruger he posted an interesting blog entry titled “Coldfusion and Oracle 8i Performance”. It is an interesting read and shows how important it is to fine tune any Enterprise system.

To see all the defined parameters and other settings we like to recommend the use of Oracle’s own SQLDeveloper application. It is a great tool, not only for setting up tables, but for reporting as well. In my works with other developers I have seen that many people don’t know the report functionality of SQLDeveloper.

With the Reports one is able to fine tune all the parameters of the Oracle database and also see the connected users, see the most used SQL’s and other things. Here is a screenshot of the “All Parameters” of the Reports.

Oracle SQL Developer _ All Parameters.png

This is taken from the current 1.2.x release of SQLDeveloper which is available for free. You can also define your own reports and have them spiced up with some nice graphics. To read more about it I suggest you head over to the product side of SQLDeveloper.

Comments { 52 }

Configure the JDBC driver for Oracle on ColdFusion 8 Standard Edition

We have had a lot of people asking us if they could use the Oracle database with their ColdFusion Standard Edition. Of course, Adobe would prefer to sell you the Enterprise Edition for using Oracle, but fortunately ColdFusion is based on Java and thus we can use the free and existing Oracle JDBC Driver.

Note: If you do go with the Oracle JDBC “Thin Client” option you will NOT be able to return result sets from stored procedures. So, if you depend upon PL/SQL stored procedures for returning queries, then you will be out of luck without the ColdFusion Enterprise Edition. On the other hand one could also buy the same JDBC drivers as Adobe is using from DataDirect.

Now, before you will be able to use the JDBC Driver you will need to download the appropriate JDBC Oracle Driver. Make sure, that you download the correct driver for the correct version of your database. They are not all the same! Also, download the “ojdbc14.jar” file, since this is for the JDK 1.4 and JDK 1.5.

Grab the “ojdbc14.jar” file and copy/move it to “ColdFusion8/wwwroot/WEB-INF/lib”. Once done, you will need to restart the ColdFusion Server.

Go the the ColdFusion Administration and go to your “data sources”. Add a new “data source” and choose “Other” from the Database driver options.

On the following page enter “jdbc:oracle:thin:@10.10.10.11:1521:ORCL” in the “JDBC URL” Textarea. In the “Driver Class” textfield enter “oracle.jdbc.OracleDriver”. Of course, enter your username and password. Once done you should connect to your Oracle database without fail.

Here is a screenshot to visualize the above.

ColdFusion Administrator.png

Comments { 116 }

Oracle date handling and ColdFusion on INSERT TO

To issue a “INSERT TO” with a “SELECT” statement is a common practice to copy records from one table to another. A statement like this might look like;

<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal; color: #800000">&lt;cfquery<span style="color: #000000"> </span>datasource=<span style="color: #0000ff">"mydb"</span>&gt;</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #000066">INSERT</span> <span style="color: #0000ff">INTO</span> mytable</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal">(id, somefield)</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">SELECT</span> <span style="color: #ff0000">'1'</span>, thefield</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">FROM</span> demo</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">WHERE</span> id = 1</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal; color: #0000ff"><span class="Apple-style-span" style="color: #800000">&lt;/cfquery&gt;</span></p>

No big deal. Al right, now many times one also adds the current date to the table. Now as ColdFusion developers you might me inclined to write this code;

<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal; color: #800000">&lt;cfquery<span style="color: #000000"> </span>datasource=<span style="color: #0000ff">"mydb"</span>&gt;</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #000066">INSERT</span> <span style="color: #0000ff">INTO</span> mytable</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal">(id, somefield, thedate)</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">SELECT</span> <span style="color: #ff0000">'1'</span>, thefield, '#now()#'</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">FROM</span> demo</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">WHERE</span> id = 1</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal; color: #0000ff"><span class="Apple-style-span" style="color: #800000">&lt;/cfquery&gt;</span></p>
 

But what a surprise the code above will not work since Oracle is not able to interpret the “#now()#’ field (yes, we are on a CFM page, but it still does not work).Simple solution is to use the Oracle timestamp with;

<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal; color: #800000">&lt;cfquery<span style="color: #000000"> </span>datasource=<span style="color: #0000ff">"mydb"</span>&gt;</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #000066">INSERT</span> <span style="color: #0000ff">INTO</span> mytable</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal">(id, somefield, thedate)</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">SELECT</span> <span style="color: #ff0000">'1'</span>, thefield, systimestamp</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">FROM</span> demo</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">WHERE</span> id = 1</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal; color: #0000ff"><span class="Apple-style-span" style="color: #800000">&lt;/cfquery&gt;</span></p>
 

The “systimestamp” will insert the current date and time for you.

Comments { 1 }

Timeout on cfstoredproc, cfquery and in general

While working on importing video data into the Oracle database I came upon an continued error message from ColdFusion that went like (among others):

“The request has exceeded the allowable time limit Tag: CFSTOREDPROC”

This was strange because I have used the code in question for a long time and uploaded even large video files (50MB or more) to the database without problems. After some further investigation trough the code I could not find anything that went wrong. So I decided to read up upon <cfstoredproc> in the Adobe manual (when are they going to change this to make it really work? As of today it is still very slow and a pain to read). Anyhow, I could not find anything to pass or to fix it. I then tried to put a <cfsetting Requesttimeout = “xxx”> on top of my page to let ColdFusion know that this page takes longer then usual. Unfortunately this did not solve the problem, also. The reason is written up in this Adobe Technote where it states that:

“There are certain tags that will not “obey” a timeout, whether it’s set in the ColdFusion Administrator or in code on the CFM page. These tags are [code]]czoxMjpcImNmc3RvcmVkcHJvY1wiO3tbJiomXX0=[[/code],[code]]czo5OlwiY2Zjb250ZW50XCI7e1smKiZdfQ==[[/code], [code]]czo1OlwiY2ZmdHBcIjt7WyYqJl19[[/code], [code]]czo5OlwiY2ZleGVjdXRlXCI7e1smKiZdfQ==[[/code] and [code]]czo4OlwiY2ZvYmplY3RcIjt7WyYqJl19[[/code]. Also, CFX tags fall into the category of not obeying the ColdFusion timeout setting. While these tags are executing, ColdFusion must wait until the action completes before it can check for a Request Timeout. Therefore, if ColdFusion never receives a response from either the database or the object[code]]czo4OlwiY2ZvYmplY3RcIjt7WyYqJl19[[/code] is calling, a hung thread occurs and only restarting the ColdFusion service will clear this out.”

So this left me with…. nothing…..not quite right.

Walking trough my code let me think that what I actually do and need is some sort of “serialization” of my whole code statement (it was not only the stored procedure that executed but many more commands like file i/o operations, db selects, etc.). Funny thing is that the solution is quite easy and I have used it many times before to make sure that no other request could interfere with my current process and that is a simple wrapping of my whole code with <cftransaction>.

Reading up on the LiveDocs about cftransaction will help us out some more what is going on but I think the important part is this sentence:

“CFTRANSACTION can be used to group multiple queries that use CFQUERY into one business event. Changes to data that is requested by the queries are not committed to the datasource until all actions within the transaction block have executed successfully.”

Looks like <cftransaction> can also help out with cfstoredproc and other processes within it. So far, since I used cftransaction around my code all is working out fine and all commands work as expected.

Comments { 40 }