Dynamic DSN

[Sid Wing]

I am looking for a way to setup a DSN for an application to use - but I need to assign the information (database type, db name, credentials, port, etc) from an external source (info from another registry).  I can pull in and parse the info from the other registry - I just need to know how (if there is a way) to create a DSN from inside my application code (like in the Application.cfc's onApplicationStart method).

[Seth Johnson]

Hi Sid,

Mark Drew blogged about that feature in Railo.

http://blog.getrailo.com/post.cfm/railo-4-1-explicit-datasources-in-application-cfc

If you want to change the DSN on the fly, you should be able to do something like this:

<cfset var connstring = "jdbc:postgresql://#getKeys.link#:5432/#getKeys.dbname#?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory">

  

  <cfadmin action="updateDatasource"

   type="web"

   password="xxxxxx"

   classname="org.postgresql.Driver"

   dsn="#connstring#"

name="mydsn"

newname="mydsn"

   port="5432"

   dbusername="#decrypted_username#"

   dbpassword="#decrypted_password#"

allowed_select="true"

   allowed_insert="false"

   allowed_update="false"

   allowed_delete="false"

   allowed_alter="false"

   allowed_drop="false"

   allowed_revoke="false"

   allowed_create="false"

   allowed_grant="false">

Seth

On Thu, Apr 9, 2015 at 3:14 PM, Sid Wing <sid....@gmail.com> wrote:

I am looking for a way to setup a DSN for an application to use - but I need to assign the information (database type, db name, credentials, port, etc) from an external source (info from another registry).  I can pull in and parse the info from the other registry - I just need to know how (if there is a way) to create a DSN from inside my application code (like in the Application.cfc's onApplicationStart method).

--
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/b4ae5e0f-6256-4b39-b403-34466f1ae0f2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Chris Blackwell]

You can set the datasource directly in your Application.cfc

// Application.cfc

component {

  this.name = "myapp"

  this.datasources = {

    "mydsn":  {

"class": "org.gjt.mm.mysql.Driver"

,"connectionString": "jdbc:mysql://127.0.0.1:3306/test"

,"username": "user"

,"password": "password"

    }

    //  add as many as you like here

  }

  // set the default datasource used for queries or orm

  this.datasource = "mydsn";

}

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAE8ZmabUMQ8oQw9JnemcSVKJcBcRJmK4R%3DKy0NHFJKgYyf%2Bm3w%40mail.gmail.com.

[Dan Kraus]

That is my preferred method too. You can then wrap all that up in environment detection and your app becomes more portable so you can deploy and not fiddle around with Admin settings first before it starts working.

[Sid Wing]

What would the classname value be for an Oracle DB? org.Oracle.Driver?  I'm not finding a place to find a list of classname values for that CFAdmin tag...

--
You received this message because you are subscribed to a topic in the Google Groups "Lucee" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/lucee/XeOQJV8kSSA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to lucee+un...@googlegroups.com.

To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/b968fa44-b972-4f73-9956-4c59149ed387%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Sid Wing
"We are dreamers, shapers, singers, and makers. We study the mysteries of laser and circuit, crystal and scanner, holographic demons and invocations of equations. These are the tools we employ, and we know many things." - Elric

[Chris Blackwell]

you probably want

classname="oracle.jdbc.OracleDriver"
dsn="jdbc:oracle:thin:<user>/<password>@<database>"

I would also caution against setting insert/update/delete/etc = false when you create the datasource, for two reasons.

1) this places an overhead on Lucee, that it must parse every sql statement and try and determine if it is allowed, this could be a real performance hit

2) Lucee's parsing is not fool-proof and you should not assume that these settings make your connection truly readonly.

implement the correct security settings on the database server, if you want readonly, create a readonly user.

Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAJ-%2Bs7vt3b%3DtdKmM%2B-i3mNNffJeviODWa80iCpta9LAe-311wg%40mail.gmail.com.

[Sid Wing]

Chris - 

Can I omit all the "allowed_" attributes and simply let the DB Server handle permissions then?

That would give me something like this:

<cfset var connstring = "jdbc:oracle:thin:<user>/<password>@<database>">

<cfadmin action="updateDatasource"

  type="web"

          password="xxxxxx"

          classname="oracle.jdbc.OracleDriver"

 dsn="#connstring#"

name="mydsn"

newname="mydsn"

  port="5432"

  dbusername="#decrypted_username#"

  dbpassword="#decrypted_password#">

Would that be correct?

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAB%3DtfTqXicFxTva3VLF7RX4Eq2QRW5kynw2MhD6WmSbaN%3DC7dw%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

[Chris Blackwell]

absolutely, yes you can

you need to replace <user>/<password>@<database>  with your values.

I'm not familiar with oracle, so i'm not sure about the exact format of the connection string.

take a look here -> http://docs.oracle.com/cd/B28359_01/java.111/b31224/jdbcthin.htm

theres an example that shows a connection string like "jdbc:oracle:thin:@prodHost:1521:ORCL";

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAJ-%2Bs7uLhWC%2BefzUXif7biFULXHB0xk%2BUo3hhUOk-gauh_Hs-Q%40mail.gmail.com.

[Michael van Leest]

@Sid These settings will be removed from the admin as database security should be handled by the DB server.

So yes, you can omit the allowed parameters or just set them all to true.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/CAB%3DtfTrDZKpCK_Bkkv9Yog%3DrhxEqVbppnM2NNTaRHaeagq7UfQ%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

Michael van Leest

[Sid Wing]

Michael - 

Thanks for the heads up!  I always thought that it was best to let the DB server handle as much of the weight as possible (as that is what they are designed for).