send multiple statements (like 1000 inserts, updates, or deletes) to the
database. Since I am using Oracle, I cannot put multiple SQL statements
inside of a CFQUERY. Looping over CFQUERY does not scale well for many
statements.
It would be great to be able to get a DB connection from the CF Server,
but even without that, I think it is preferable to use the Java below in
order to run many SQL statements in one call to the database using
something like:
PreparedStatement stmt = conn.prepareStatement( "INSERT INTO Users
VALUES(?,?)");
User[ ] users = ...;
for(int i=0; i<users.length; i++) {
stmt.setInt(1, users[i].getName());
stmt.setInt(2, users[i].getAge());
stmt.addBatch( );
}
int[ ] counts = stmt.executeBatch();for (insertStatement in
InsertStatements)
Jon
>>> [EMAIL PROTECTED] 5/18/2004 10:30:56 AM >>>
Thanks for everyone's help.. with a little help from the datadirect
documentation, I was actually able to get the following to work:
<cfscript>
clazz = CreateObject("java", "java.lang.Class");
// replace the package/class name of your db driver
clazz.forName("macromedia.jdbc.MacromediaDriver");
driverManager = CreateObject("java", "java.sql.DriverManager");
// replace w/ your server, database name, username & password
conurl =
"jdbc:macromedia:oracle://********:1521;SID=*********;user=*********;password=*********";
connection = driverManager.getConnection(conurl);
query = "DELETE FROM IDS WHERE ID=1";
preparedStatement = connection.prepareStatement(query);
result = preparedStatement.executeUpdate();
WriteOutput("result = " & result);
connection.close();
</cfscript>
For what it's worth, this is the second time I've resorted to using the
datadirect documentation to solve a driver-related issue with Cold
Fusion :)
- Rick
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
