Getting a JDBC database connection without using Servicefactory

Question

I'm using the following java code (in ColdFusion) to get a database connection:

//Creating an object of servicefactory class
local.theServiceFactory = createObject('java','coldfusion.server.ServiceFactory');

//Creating the connection object simply by passing the DSN name
local.connect = theServiceFactory.getDataSourceService().getDataSource('dsnnane').getConnection();

Works great. The only problem is I'm told I can't use ServiceFactory because of security reasons. Is there another way to get the connection object?

What is the reason for using the lower level connection object at all instead of just using the datasource name in a `cfquery`? Also are you on shared hosting? — Leigh, Aug 29 '12 at 18:57
Because using the datasource name in a cfquery doesn't give me access to the methods I need ie. setting AutoCommit to false. I'm doing a Batch Insert. CF doesn't know what a Batch Insert . — jim collins, Aug 30 '12 at 15:13
But your database does... Are there ways to set the settings you're after in the SQL itself? For example, setting `autocommit` to false: http://stackoverflow.com/questions/1090240/how-do-you-set-autocommit-in-an-sql-server-session — Dan Short, Aug 30 '12 at 15:39
@Dan - Haha, I was just writing almost the exact same thing when I saw your response! — Leigh, Aug 30 '12 at 15:40

score 1 · Answer 1 · edited May 23 '17 at 12:12

1

Instead of dropping lower in the call stack to do this kind of thing, I'd look at ways to set your options directly via the SQL itself. As I posted in my comment, you can set the autocommit values in SQL Server via T-SQL itself.

edited May 23 '17 at 12:12

Community

1
1

answered Aug 30 '12 at 15:40

Dan Short

9,598
2
28
53

Yep, most databases allow you to override settings within the sql, meaning you can run the commands within a cfquery OR a stored procedure. (Edit) To answer the original question, you cannot tap into the CF datasource without access to the `ServiceFactory` afaik. You could create a *new* jdbc connection, but that seems unnecessary here. – Leigh Aug 30 '12 at 15:45
Well, seeing as Im using the connection object to prepare the SQL, dont think thats the approach. local.connect.prepareStatement(local.sql) start loop..local.sqlStatement.setString(1,"foo")local.sqlStatement.setString(2,"bar") end loop..local.sqlStatement.executeBatch(); local.connect.commit(); local.sqlStatement.close(); local.connect.close(); – jim collins Aug 30 '12 at 16:21
I guess what's not clear to us is why you can't run that in a regular old `` with a ``s. What's precluding you from going back to standard CF code that we know will do what you're after :). – Dan Short Aug 30 '12 at 17:00
Because it's slower than death. Oracle Batch Load is literally 1000 times faster than multiple inserts. What the standard code does is run out of heap space after 4000 inserts and forces a server restart. This is after, like, forever. – jim collins Aug 30 '12 at 17:31
Rather than screwing around with this, I'm creating a JDBC connection and using that instead. But I'm still curious to know what the answer is to my original question, unless you want me to keep justifying asking it. I'm assuming your all asking me "why are you doing this" is because you're curious about the benefits of Oracle Batch Loading. Speed. It's blindingly fast, as in "holy crap that was fast!". – jim collins Aug 30 '12 at 17:35
The answer was in the comments in that "it's not possible" to set it at the JAVA level as far as we know. I completely understand what you mean about the batch loading, and I've had to deal with similar issues with bulk inserts in SQL Server. I sympathize, but don't have an answer for you. – Dan Short Aug 30 '12 at 18:57
And I may have to take that back... check this out: http://www.mindfiresolutions.com/Batch-Processing-In-ColdFusion-1524.php – Dan Short Aug 30 '12 at 18:59
Well the method in the link is still using `ServiceFactory`. You can do the same by creating a *new* jdbc connection as mentioned earlier. But you lose the benefits of CF's connection pooling (usually enabled). I have used the batch approach in one app and even with the penalty of opening a new connection, it was faster than individual cfquery's. But still nowhere near as fast as say MS SQL's `BULK INSERT` tool. (Edit) I do not use Oracle much, so I cannot speak for Batch Load. – Leigh Aug 30 '12 at 19:34
@Dan - Re: *"it's not possible" to set it at the JAVA level* I think you may have misread that part :) I meant AFAIK it is not possible to tap into the existing *CF datasource connection* without permissions to `ServiceFactory`. But you can certainly create a new jdbc connection manually and change the settings. – Leigh Aug 30 '12 at 19:44
1

@jimcollins - One other thing. I rarely use Oracle, but with MS SQL you can also execute multiple statements within a single cfquery. (MS SQL 2008 also supports multiple `VALUES` clauses). Then the commands are sent to the database in one shot, rather than one at a time. Essentially doing the same thing as `executeBatch`. – Leigh Aug 30 '12 at 22:36

score 0 · Answer 2 · answered Oct 29 '14 at 13:21

0

I use it like this

var datasourceService = createObject("Java", "coldfusion.server.ServiceFactory").getDataSourceService();
    var ds = datasourceService.getDatasource(variables.dsn).getConnection().getPhysicalConnection();
    ds.setAutoCommit(false);
    ...
    ds.setAutoCommit(true);

answered Oct 29 '14 at 13:21

Tim Garver

61
4

Please re-read the question. It says they *can't* use `ServiceFactory` because of security restrictions. – Leigh Oct 29 '14 at 19:04

Getting a JDBC database connection without using Servicefactory

2 Answers2