jdbc query cached? OR how do I get current results?

Question

Two CF pages exist: a "list" page and an "edit" page. The user selects a record from the "list" page via a link to the "edit" page. The "edit" page is a form which submits back to itself to update the record, then returns to the "list" page via cflocation.

There are literally hundreds of mdb files which could be accessed via these pages, based on the user login information. The path to the files is known by their login, and we don't want to maintain hundreds of odbc connections, so the jdbc driver within cfscript is being used to access the data, with the path to the database file reference in the getConnection function.

The problem is this: after the record is updated and cflocation returns to the list page, the list page does not return the current database information (the record which was just edited still shows the old information). If the page is refreshed manually (F5 type stuff), then the updated data is shown. It's like the query on the "list" page is being cached or something. I have tried a meta tag to expire the page, but that doesn't help.

Another set of data is edited on these pages but resides on SQL Server and used cfquery. This problem does not exist with that set of code.

CF MX7 is the CF Server version.

Here's the cfscript used on the "list" page:

<cfscript>
classLoader = createObject("java", "java.lang.Class");
classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
dm = createObject("java","java.sql.DriverManager");
con = dm.getConnection("jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};Dbq=#fullPathToMDBFile#;Uid=;Pwd=;");
st = con.createStatement();
rs = st.ExecuteQuery( "#GroupQuery#" );
group = createObject("java", "coldfusion.sql.QueryTable").init(rs);
</cfscript>

And the cfscript on the "edit" page:

   <cfscript>
    classLoader = createObject("java", "java.lang.Class");
    classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    dm = createObject("java","java.sql.DriverManager");
    con = dm.getConnection("jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};Dbq=#fullPathToMDBFile#;Uid=;Pwd=;");
    st = con.createStatement();
    st.Execute( "#UpdateQuery#" );
   </cfscript>

Which is followed by:

At the suggestion of a java-knowledgeable friend, I tried:

st.close();
con.close();
after the st.ExecuteQuery, but still got the same results.

Thoughts??

Thanks.

-__cfSearching__- · Answer

Yes, always do that anyway. It is not good to have open connections lying around.

Are you sure it is not just a persistent case of browser caching? Try the old trick of tacking a random number onto the url each time, using randRange(). See if it makes a difference.

As far as datasources, you could try creating a single datasource and using it to query all the other databases. IIRC it should be listed under external tables in the MS Access help files. I can never remember the syntax, but is something like:

SELECT Columns

FROM TableA IN 'c:\databaseFolder\someDatabaseName.mdb'

IIRC MS Access databases let you query another databases using a special syntax fro

Update: Disclaimer, I have not used Access in years. So I have no idea about the potential issues, other than the obvious (ie Access is not really designed for this)

At the suggestion of a java-knowledgeable friend, I tried:
st.close();
con.close();
after the st.ExecuteQuery, but still got the same results.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded